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

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

Brandon_VoorveltBrandon_Voorvelt Posts: 5Questions: 2Answers: 0

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?

This question has accepted answers - jump to:

Answers

  • rf1234rf1234 Posts: 2,448Questions: 78Answers: 353
    edited August 24 Answer ✓

    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

  • allanallan Posts: 57,300Questions: 1Answers: 9,127 Site admin
    Answer ✓

    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_VoorveltBrandon_Voorvelt Posts: 5Questions: 2Answers: 0

    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!

Sign In or Register to comment.