Equivalent of mysql IN operator

INTONE

Is there a way to do a IN operator query on the server fields for example something like this:

   $data->leftJoin( 'ad_agency_users', 'ad_agency_users.user_id', '=', 'users.user_id' )
            ->where( $key = 'ad_agency_users.ad_agency_id', $value = '18,19,20', $op = 'IN');

allan

Yes, but you need to use a closure for the where method, rather than the simple form. For example:

->where( function ( $q ) {
  $q->where( 'ad_agency_users.ad_agency_id', '(18,19,20)', 'IN', false );
} )

There are further details about this in my post here.

Writing up how to do conditions for Editor into proper manual pages is on my task list for tomorrow!

Out of interest, why did you put $key = etc in the where() call. They are completely redundant, but I keep seeing it in questions, so I'm guessing it is coming from my documentation somewhere. Asking so I can fix it!

Allan

INTONE

Thank you. You just brought joy to my day. as it relates to the redundancy in the code above as asked, I am pretty sure I got that from another forum post when learning editor and always done it that way since.

Your code does work , I however have one more fix that i am asking of you. how do I provide a grouping on the outer query? From memory i am thinking this is not possible. In the docs i did see this : https://editor.datatables.net/docs/1.4.0/php/source-class-DataTables.Database.Query.html#574-589 but I am not able to get it to work.

here is what i have so far.

      $data =  Editor::inst( $db, 'rights_management', 'rights_management_id' )
->fields(
 Field::inst( 'rights_management.rights_name' )->validator( 'Validate::required' ),
    Field::inst( 'users.email' ),
    Field::inst( 'rights_management.write_user_roles' ),
    Field::inst( 'rights_management.read_user_roles' ),
    Field::inst( 'rights_management.delete_user_roles' ),
    Field::inst( 'rights_management.write_user_account' ),
    Field::inst( 'rights_management.read_user_account' ),
    Field::inst( 'rights_management.delete_user_account' ),
    Field::inst( 'rights_management.read_ad_agency' ),
Field::inst( 'rights_management.user_type' )
->setFormatter(function($val,$data,$opts){
       return set_user_type($val);
 }),
    Field::inst( 'rights_management.write_ad_agency' ),
    Field::inst( 'rights_management.delete_ad_agency' ),
    Field::inst( 'rights_management.read_ad_agency_branches' ),
    Field::inst( 'rights_management.write_ad_agency_branches' ),
    Field::inst( 'rights_management.delete_ad_agency_branches' ),
    Field::inst( 'rights_management.read_ad_agency_client' ),
    Field::inst( 'rights_management.write_ad_agency_client' ),
    Field::inst( 'rights_management.delete_ad_agency_client' )
        );

    $data->leftJoin( 'users','users.user_id', '=',  'rights_management.created_by_user_id' );
     $data->leftJoin( 'rights_management_admin_users','rights_management_admin_users.rights_management_id', '=', 'rights_management.rights_management_id' );



      if($session->getVar('user_type') === "AGENCY"){
//$data->where( $key = 'users.user_id', $value = $session->getVar('user_id'), $op = '=');
$data->leftJoin( 'ad_agency_users', 'ad_agency_users.user_id', '=', 'rights_management_admin_users.user_id' )
     ->where( function ( $q ) {
           $q->where( 'ad_agency_users.ad_agency_id', '(SELECT ad_agency_id FROM ad_agency_users WHERE user_id = "'.setLastUserId().'" )', 'IN', false );
      } );


      }

If it is not possible i could always fix it from the client side but just checking.

allan

Grouping as in SQL GROUP BY? Currently, unfortunately that is not possible with the Editor libraries - they always expect to be editing a single row and group by can make that complex.

Allan

INTONE

Ok. I can easily remove duplicate records using this. I had already found a clientside solution:

       drawCallback: function( settings ) {

            var api = this.api();

            // Output the data for the visible rows to the browser's console
            //console.log( api.rows( {page:'current'} ).data() );

            $.each(api.rows( {page:'current'} ).data(),function(i,v){
                //console.log(v.ad_client.company_name);
                console.log(v.DT_RowId);
                var ids = $('[id="' + v.DT_RowId + '"]');
               if (ids.length > 1) $('[id="' + v.DT_RowId + '"]:gt(0)').remove();
            })
        }