Can we add left join in options?

Can we add left join in options?

Smti YogyakartaSmti Yogyakarta Posts: 10Questions: 2Answers: 0
edited August 2017 in DataTables 1.10

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)

This question has accepted answers - jump to:

Answers

  • Smti YogyakartaSmti Yogyakarta Posts: 10Questions: 2Answers: 0

    Hy Allan,
    Hy Datatables forums,

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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 YogyakartaSmti Yogyakarta Posts: 10Questions: 2Answers: 0

    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?

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited August 2017

    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!!

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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 YogyakartaSmti Yogyakarta Posts: 10Questions: 2Answers: 0

    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..

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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 YogyakartaSmti Yogyakarta Posts: 10Questions: 2Answers: 0

    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. :)

This discussion has been closed.