!= Operator on same table

flowte

I have a SQL query working but for some reason the same query when translated in datatables isn't working. The where clause on same table doesn't appear to affect the query.

The line from each of the below that is not being parsed correctly in datatables but works as expected in SQL:

DataTable:

->where('user_manager.id_user', 'user_manager.manager_user', '!=')

SQL:

AND user_manager.id_user != user_manager.manager_user

Can anyone advise why this is the case and if there is a workaround?

I've included full queries below:

DataTable:

Editor::inst( $db, 'user_manager' )
            ->field(
                Field::inst( 'user.id' ),
                Field::inst( 'user.fname' ),
                Field::inst( 'user.lname' ),
                Field::inst( 'user_timeoff.id' ),
                Field::inst( 'user_timeoff.start_datetime' )
                    ->validator( 'Validate::dateFormat', array(
                    "format"  => Format::DATE_ISO_8601,
                    "message" => "Please enter a date in the format yyyy-mm-dd"
                ) )
                ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
                ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
                Field::inst( 'user_timeoff.end_datetime' )
                    ->validator( 'Validate::dateFormat', array(
                    "format"  => Format::DATE_ISO_8601,
                    "message" => "Please enter a date in the format yyyy-mm-dd"
                ) )
                ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
                ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
                Field::inst( 'user_timeoff.total_day' ),
                Field::inst( 'user_timeoff.timeoff_reason' ),
                Field::inst( 'user_timeoff.approval_reason' )
            )
        ->where('user_manager.id_venue', $_SESSION['login'][2])
        ->where('user_manager.id_user', 'user_manager.manager_user', '!=')
        ->leftJoin('user', 'user.id', '=', 'user_manager.id_user')
        ->leftJoin('user_login', 'user_login.id_user', '=', 'user_manager.manager_user')
            ->where('user_login.skey', $_SESSION['login'][3])
            ->where('user_login.ip', $_SERVER['REMOTE_ADDR'])
        ->leftJoin('user_timeoff', 'user.id', '=', 'user_timeoff.id_user')
            ->where('user_timeoff.approval', '0')
        ->process($_POST)
        ->json();

SQL:

SELECT 
user_timeoff.id,
user_timeoff.start_datetime,
user_timeoff.end_datetime,
user_timeoff.total_day,
user_timeoff.timeoff_reason,
user_timeoff.approval_reason,
user.id,
user.fname,
user.lname
FROM 
user_manager 
LEFT JOIN user ON user_manager.id_user = user.id 
LEFT JOIN user_login ON user_login.id_user = user_manager.manager_user 
LEFT JOIN user_timeoff ON user.id = user_timeoff.id_user
WHERE 
user_manager.id_venue = :idVenue
AND user_login.skey = :sKey
AND user_login.ip = :IP
AND user_timeoff.approval = 0
AND user_manager.id_user != user_manager.manager_user

allan

Hi,

I must confess that I don't see anything in your code that would trigger any issues I'm aware of - it should work okay (but obviously isn't).

We are going to need to do a little extra debugging on this one I'm afraid - specifically taking a look at the SQL query that Editor is building. The best way of doing this is to dump it to a file (you could echo it, but that will result in JSON errors, which might be acceptable for you while debugging...).

What to do is look in the Database/Drivers/Mysql/Query.php file (assuming you are using MySQL - the others have a similar file) and then look at the _prepare method. Near the bottom you will find a file_put_contents() function call that has been commented out. Comment it back in, or add whatever debugging you want to get the information from the $sql parameter and that will show the generated query.

Its a bit messy at the moment I know - in future I'll add a debugging option that will give this information more easily.

Then if you could let me know what the SQL statement is, that might shed some light on the problem.

Allan

flowte

Hi Allan,

Thanks for getting back to me on this. Having followed your steps I'm getting the below output:

SELECT  user_manager.id as 'user_manager.id', user.id as 'user.id', user.fname as 'user.fname', user.lname as 'user.lname', user_timeoff.id as 'user_timeoff.id', user_timeoff.start_datetime as 'user_timeoff.start_datetime', user_timeoff.end_datetime as 'user_timeoff.end_datetime', user_timeoff.total_day as 'user_timeoff.total_day', user_timeoff.timeoff_reason as 'user_timeoff.timeoff_reason', user_timeoff.approval_reason as 'user_timeoff.approval_reason' 
FROM  user_manager 
LEFT JOIN user ON user.id = user_manager.id_user  
LEFT JOIN user_login ON user_login.id_user = user_manager.manager_user  
LEFT JOIN user_timeoff ON user.id = user_timeoff.id_user 
WHERE 
user_manager.id_venue = :where_0 
AND user_manager.id_user != :where_1 
AND user_login.skey = :where_2
AND user_login.ip = :where_3 
AND user_timeoff.approval = :where_4 

I've also just got the values of the parameters from Database/Query.php:

:where_0:1 
:where_1:user_manager.manager_user 
:where_2:77679066697670 
:where_3:::1 
:where_4:0

allan

Ah! I should have realised...

The issue is that the where() method is treating the second parameter as a string rather than a column name.

Try:

->where( function ($q) {
  $q->where( 'user_manager.id_user', 'user_manager.manager_user', '!=', false);
} )

The fourth parameter will stop the second being bound as a value. You need to use the closure to be able to access the Query method to use this style of Query->where(). There is some documentation about this here.

Allan

flowte

Perfect!

Thanks Allan for the help in resolving this, I'd tried the where as function but hadn't used the false to not bind it.