Where in Editor with a variable

jboscher

I want a complex Where with a variable.
In its basic version it's something like this :

->where( function ( $q ) {
            $q->where( "wp_qspread_invite.parrain", $_SESSION['user_id'], '<>' );
            $q->where( 'wp_qspread_invite.invitation', "(SELECT ID FROM wp_qspread_invitation WHERE id_createur = 1)", 'IN', false );
    } )

But in place of
WHERE id_createur = 1

I want

WHERE id_createur = $_SESSION['user_id']

I tried different syntaxes without success.
What is the correct way to do it?
Thx

jboscher

I found a way to do it. I initialise a $_SESSION with my request e.g:
$x = "(SELECT ID FROM wp_qspread_invitation WHERE id_createur = ".$qsuserid. ")" ;
$_SESSION['mystring'] = $x;

and in my Where:
$q->where( 'wp_qspread_invite.invitation', $_SESSION['mystring'], 'IN', false );

it works but not obvious to find. I guess there is something easier...

allan

Hi,

Interesting way of doing it! Possibly the best way (and the most secure) is to bind the submitted data. So you might end up with:

->where( function ( $q ) {
            $q->where( "wp_qspread_invite.parrain", $_SESSION['user_id'], '<>' );
            $q->where( 'wp_qspread_invite.invitation', "(SELECT ID FROM wp_qspread_invitation WHERE id_createur = :id_createur)", 'IN', false );
            $q->bind( ':id_createur', $_SESSION['user_id'] );
    } )

This binding is models on PDO's own binding (which is what is being used by the Editor libraries underneath).

Allan

jboscher

Thx for your response. I try it but I got: "Call to undefined method DataTables\Database\DriverMysqlQuery::bind()"
My editor is 1.3.3.
Do I need an update version?

allan

Yes, bind() was introduced in Editor 1.4.1. The current release is 1.4.2.

Allan

jboscher

OK - thanks a lot

jboscher

It seems there is something wrong in Editor.php.
e.g. (same example - but other issue - not related to BIND)

 ->where( function ( $q ) {
    $q
        ->where( "wp_qspread_invite.parrain", $_SESSION['user_id'], '=') 
        ->where( "wp_qspread_invite.invitation", 0, '!=' )
     
        ->or_where( function ( $r ) {
            $r->where( "wp_qspread_invite.parrain", $_SESSION['user_id'], '<>' );
            $r->where( "wp_qspread_invite.invitation", $_SESSION['chaine'], 'IN', false );
        } );
    } )

The editor works fine for (removing) but not for editing or adding i.e. It does not return the right line (record) - so the page refresh is not right. In fact it returns a wrong line.
But data are correctly stored in the DB AND it works the 1st time (but from the 2nd time it returns a wrong line)

It works fine with Editor 1.3.3 but not with Editor 1.4.2 (If I replace Editor.php by its 1.3.3 version, it works).

It works when there is no or_where
To summarize it happens in Editor.php (>1.3.3) when or_where is used.

Any suggestion?

allan

Do you get any errors reported in your server's error log?

The other thing to check would be to dump the SQL that Editor is generating to a debug file - you can do that int he Query.php for the database driver that you are using. Most of those files already have a file_put_contents() call already commented out that you could use. It would be interesting to know what SQL is being generated.

Thanks,
Allan

jboscher

I found a difference in the SQL (in Query.php - SELECT)

1st case (Editor 1.3.3)

SELECT ...  LEFT JOIN ... 
WHERE (wp_qspread_invite.parrain = :where_1 AND wp_qspread_invite.invitation != :where_2 OR(wp_qspread_invite.parrain <> :where_4 AND wp_qspread_invite.invitation IN (SELECT ID FROM wp_qspread_invitation WHERE id_createur = 1) ))
AND wp_qspread_invite.id = :where_8 

2nd case (Editor 1.4.2)

SELECT ...LEFT JOIN ...
WHERE wp_qspread_invite.parrain = :where_0 AND wp_qspread_invite.invitation != :where_1 OR(wp_qspread_invite.parrain <> :where_3 AND wp_qspread_invite.invitation IN (SELECT ID FROM wp_qspread_invitation WHERE id_createur = 1) )
AND wp_qspread_invite.id = :where_6 

As you can see in #1 - we have

WHERE (wp_qspread_invite.parrain = :where_1 
AND wp_qspread_invite.invitation != :where_2 
OR(wp_qspread_invite.parrain <> :where_4 
AND wp_qspread_invite.invitation 
IN (SELECT ID FROM wp_qspread_invitation 
WHERE id_createur = 1) ))

) and in #2 we have

WHERE wp_qspread_invite.parrain = :where_0 
AND wp_qspread_invite.invitation != :where_1 
OR(wp_qspread_invite.parrain <> :where_3 
AND wp_qspread_invite.invitation 
IN (SELECT ID FROM wp_qspread_invitation 
WHERE id_createur = 1) )

More brackets in

1 : WHERE (wp_qspread_invite

than in

2 WHERE wp_qspread_invite

I hope it helps.
Thx

allan

Hi,

Thanks for the trace information. The outer brackets shouldn't really make any different. The one thing that does strike me is the different where_{x} variables in the two different statements, which suggests that there are other conditions in the table as well.

Could you possibly give me a dump of the full statements please?

The PHP shown above certainly matches what I would expect to be generated for the WHERE condition shown above.

Thanks,
Allan

jboscher

I send u to you by mail because it's too big.

allan

Thanks for the files - I understand the issue now. it looks like you have an additional ->where() statement beyond the closure function that is shown above.

Let me look into it a bit more and I'll get back to you.

Allan

allan

I think the correct thing to do here is to use the where_group() method in your closure function.

->where( function ( $q ) {
   $q
       ->where_group( true )
       ->where( "wp_qspread_invite.parrain", $_SESSION['user_id'], '=')
       ->where( "wp_qspread_invite.invitation", 0, '!=' )
     
       ->or_where( function ( $r ) {
           $r->where( "wp_qspread_invite.parrain", $_SESSION['user_id'], '<>' );
           $r->where( "wp_qspread_invite.invitation", $_SESSION['chaine'], 'IN', false );
       } );

       $q->where_group( false );
   } )

The reason being is that as a closure function it should provide access to the raw query - unmodified, the fact that it was including the brackets in 1.3 was an unintended consequence of other parts of the code!

Regards,
Allan

jboscher

It works! Congratulations Allan. Thank you.