CODEWITHSUNDEEP
silverstripesilverstripe-4
Wai Yan Hein
Wai Yan Hein

Reputation: 14821

SilverStripe SQLSelect where clause or where or where in

I am working on a SilverStripe project. In my project, I need to write the manual/ custom query. I am trying to figure out a where to set "or where" "where in" in my query.

This is my current code

    $sqlQuery = new SQLSelect();
    $sqlQuery->setSelect('Player.*');
    $sqlQuery->setFrom('Player');
    $sqlQuery->addWhere([ '"Player"."ID"' => 1 ]);
    $sqlQuery->addWhere([ '"Player"."ID"' => 2 ]);
    $sqlQuery->addWhere([ '"Player"."ID"' => 3 ]);

What I am trying to do is that I am trying to select the records where the play id is 1 or 2 or 3. But it does not work because it is going to be joined with AND instead of OR. How can I use where in or or where?

I thought of using setWhereAny. The thing is that we cannot set the same array key for an array in PHP as below.

$sqlQuery->setWhereAny([ 'ID' => 1, 'ID' => 2 ])

This also does not work

$sql->setWhereAny([ 'ID' => 1 ])->setWhereAny([ 'ID' => 2 ]);

Upvotes: 0

Views: 590

Answers (1)

patJnr
patJnr

Reputation: 174

Your 'where' should be like either of these two;

$aIDs = [1,2,3];
1. $sqlQuery->addWhere(['ID IN (?)'=>implode(',',$aIDs)]);
2. $sqlQuery->addWhere('ID = 1 OR ID = 2 OR ID = 3');

addWhere When building the SQL query, the 'where' clauses are in array value and each time you use ->addWhere() those values are merged. So the array key is the Table Column.

setWhereAny; In your case, this is not applicable as you only have 1 Column to filter. The correct use of setWhereAny will be like

$sqlQuery->addWhereAny(array(
     'ID' => 1,
     'OtherID' => 3
));

And the corresponding query will be

SELECT * FROM Player WHERE ((ID = ?) OR (OtherID = ?))

Hope this helps

Upvotes: 1

Related Questions