Editor - WHERE field_in_set

Apezdr

See below

Field::inst( 'DataCollection.tasktypeid' )
            ->options( 'TaskType', 'id', array('TaskTitle','TaskType','UserIDAssociatedTo','UserGroupVisiblity'), function($q){
                $q->where('TaskType.UserIDAssociatedTo', 0);
                $q->or_where( function ( $r ) {
                    $r->where( 'TaskType.UserIDAssociatedTo', "find_in_set('" . $_SESSION['usrID'] . "', cast(`UserIDAssociatedTo` as char)) > 0 ", "=" , false);
                } );
                $q->where( 'TaskType.UserGroupVisiblity', null );
                $q->or_where( function ( $rs ) {
                    $rs->where( 'TaskType.UserGroupVisiblity', $_SESSION['loggedinUserGroup'] );
                } );
            }, function($row){
                    
                return $row['TaskTitle'];
            } )

I am having a problem with my query, i need it to read like this

find_in_set('" . $_SESSION['usrID'] . "', cast(`UserIDAssociatedTo` as char)) > 0

The use case here is that I have a column that is formatted as varchar that is in comma separated values format.

Apezdr

My issue was

 $r->where( 'TaskType.UserIDAssociatedTo', "find_in_set('" . $_SESSION['usrID'] . "', cast(`UserIDAssociatedTo` as char)) > 0 ", "=" , false);

FIXED below

$q->where("find_in_set('" . $_SESSION['usrID'] . "', cast(`UserIDAssociatedTo` as char)) > 0 OR find_in_set('" . $_SESSION['loggedinUserGroup'] . "', cast(`UserGroupVisiblity` as char)) > 0 OR find_in_set('0', cast(`UserIDAssociatedTo` as char)) > 0 AND `UserGroupVisiblity`");

This fix I made to my code works but you'll be confused with the peculiar nature of the SQL Query Builder that spits out the SQL query the table uses.

)) > 0 OR find_in_set('0', cast(`UserIDAssociatedTo` as char)) > 0 AND `UserGroupVisiblity` IS NULL ) IS NULL )

(this is a snippet from the end of the line where the WHERE is defined)
IS NULL was at the end of the WHERE query but I was able to make this work to my advantage. Knowing that the command was adding this I was able to manipulate my WHERE statement to fall into perfect harmony with this closing IS NULL.

Find

datatables\lib\Database\Driver\Mysql\Query.php

Uncomment the snippet in the file as shown below.

file_put_contents( '/tmp/editor_sql', $sql."\n", FILE_APPEND );

Go to the root of the host server and go into the /tmp/ folder open up the file editor_sql and my bread and butter came from the last line in the SQL Query, of course mileage may vary with my solution but this is the process I took.

allan

Thanks for posting back. Good to hear you got it working for what you need!

Allan