Can we add left join in options?

Smti Yogyakarta

someone please help me, i use editor with an select type. but i want that just returned spesific value that can be accessed with left join and where caluse
here my sample code

Editor::inst( $this->editorDb, 'perusahaan_bio', 'id' )
            ->fields(
                Field::inst( 'siswa_pkl.idperusahaan' )
                    ->options( 'perusahaan_bio', 'id', 'nama' ), 
                Field::inst( 'perusahaan_bio.nama' ),
                Field::inst( 'siswa_pkl.nis' )
                    ->options( 'siswa_bio', 'nis', 'nama' ), 
                    ->leftjoin(sometable) 
                    ->where(someclause)

Smti Yogyakarta

Hy Allan,
Hy Datatables forums,

i have some code like that, please help solve my problem

rf1234

As far as I know you can't do a "leftjoin" in an options instance but you can use an "old fashioned" inner join through the where clause (1990's style). You target your table PLUS the table you want to join with and do the join through the WHERE clause. This is an implicit inner join. Here is an example from my coding:

Field::inst( 'contract.govdept_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
        ->options( Options::inst()
            ->table('govdept, gov')
            ->value('govdept.id')
            ->label( array('gov.name', 'govdept.name', 'gov.type', 'gov.regional_12') )
            ->render( function ( $row ) {               
                return $row['gov.name'].' / '.$row['govdept.name'].' ('.$row['gov.regional_12'].'); '
                        .renderGovType($row['gov.type']); 
            } )
            ->order( 'gov.name asc' )
            //where clause MUST be a closure function in Options!!!
            ->where( function($q) {
                //only govs that are already clients can be selected
                $q ->where( function($r) {
                    $r ->where('gov.is_client', 1 );
                    $r ->where('govdept.gov_id', 'gov.id', '=', false); //join
                });
            } )
        ),

The primary table in this example is "govdept" and I join with table "gov" in order to retrieve additional attributes which are being rendered as the options to choose from. Based on this the "govdept_id" of the "contract" is being selected.

Two lines of code are essential for this inner join:
->table('govdept, gov') - means you select from the primary table and the table you want to join with
$r ->where('govdept.gov_id', 'gov.id', '=', false); - this is the actual inner join of the tables. You need the "false" in order to avoid "gov.id" being escaped as a string.

allan

Nice solution!

The other option is to use ->options() with an anonymous function which will fetch the data from the database, giving you complete control over it, and not limiting you to the Options class' API.

Allan

Smti Yogyakarta

ah great solution, thanks rf1234.

thanks Alllan, but still confuse when use that for left join ,
can we add manual query to produce some data?

rf1234

I cannot think of options for foreign keys where a left join really makes sense. Why? It will return values even if there is no row matched in the joined table. And a left join checking for IS NOT NULL for the fields of the joined table is the same as an inner join. But anyway ... You could do your left join like this.

Field::inst( 'contract.approver_id' )
    ->options( function () {
        global $yourDatabaseHandler;
         <your own query to return your results including
         the id that should be inserted into the field above>
       return $formattedArrayOfResults;
    } )
    ->setFormatter( function ( $val, $data, $opts ) {
           <extract the id from what was returned from the client>
           return $extractedIdfieldtoBeInserted;
      } ),

https://editor.datatables.net/manual/php/joins#Options

One caveat: I have never used this myself because I prefer the solution above that uses the Editor functionality for this. Good luck!!

allan

The Options class doesn't have a left join option since, as @rf1234 says, I also can't think of a use case for it.

Perhaps you can show us your tables and explain why it is needed so I can get a better understanding of the issue?

Thanks,
Allan

Smti Yogyakarta

Here my database sample
(attached file)

And here my sample code

Editor::inst( $this->editorDb, 'field_practice', 'id' )
            ->fields(
                Field::inst( 'field_practice.factory_id' )
                    ->options( 'factory', 'factory_id', 'name' ), 
                Field::inst( 'factory.name' ),
                Field::inst( 'field_practice.student_id' )
                    ->options( 'students', 'student_id', 'name' )
                    //Here the issue. I want show students name but just some data with criteria:
                      - academic.status active
                      - class id_class like "11%"

Hope you can give the solution..

rf1234

Won't write your code, sorry. Please take a look at my example above. It has everyhting you need to write your own little WHERE clause to retrieve the data you require.

Smti Yogyakarta

Yeah, I've seen your example. That's great solution and solved my problem.
Thanks @ rf1234 and thanks Allan ..

That images just for show why i need use 'join' for show the options

My apologies about this question:
"Can we add manual query to produce some data?"
It should by in another question, but you answered to, so thanks again.

you can mark this as answered question and close this discussion.