How could I get this current row and use it in a where clause (for options)

Brandon_Voorvelt

Hey DataTables Team, haven't seem something like this in the forums so I'm not too sure how to approach this.

I have this PHP code for the Editor:

   ........
   $editor = Editor::inst($db, 'groups', 'id')
      ->fields(
            Field::inst('id')->set(false),
            Field::inst('clis')
            ->getFormatter('Format::explode')
            ->setFormatter('Format::implode')
            ->options(
                  Options::inst()
                        ->table('clis')
                        ->value('cli')
                        ->label('cli')
                        ->where( function ( $q ) {

                           # Need the row id(groups.id) for where clause:
                              $q->where( 'assigned', 'groups.id' ,'=')

                                  ->or_where( function ( $r ){
                                      $r->where('assigned',null,'=');
                                  } );
                          } )
                        
            )
      );
   ........

I have 2 database tables, "groups" and "clis". The "clis" table has a column called "assigned" which will have the ID of a row in the "groups" table.

For example, if a group has the id of 850 and a cli (phone number) is assigned to that group - then in the "assigned" column in the "clis" table I would reference the group ID = 850.

The options I need to display would be for "clis" that either have not been assigned to a group (so the column "assigned" would be null) or display the "clis" that have been assigned to that group.

Is there a way of getting/referencing the row id in the options where clause?

rf1234

The options instance can't do left join (unless it has been added in the meantime). What you can do is an old fashioned inner join through the where clause. Or you roll your own query using Editor's "raw()" method and return the options from that query.

This thread should help you out:
https://datatables.net/forums/discussion/comment/177739

allan

unless it has been added in the meantime

Nope - not yet. It is something that does need to get done - it will probably be Editor 2.2.

Another option in addition to @rf1234's is to use a VIEW which will do the join for you and then you can just query against the view.

Allan

Brandon_Voorvelt

Thank you @rf1234 @allan for your answers, I didn't think of using a VIEW or the raw method - I'm sure this will work perfectly!