How to create 'WHERE IN' queries?

How to create 'WHERE IN' and 'WHERE NOT IN' queries?

My current attempt is something like:

$select = $query->newSelect()->cols(['*'])->where('id IN (?)', [1, 2, 3]);
$select = $query->newSelect()->cols(['*'])->where('id NOT IN :id', ['id' => [1, 2, 3]]);

Expected result:

SELECT * FROM table WHERE id IN (1,2,3);
SELECT * FROM table WHERE id NOT IN (1,2,3);

A special methode like whereIn and whereNotIn would also be very nice :-)

Hey @odan -- using 2.x, or 3.x ?

Version 2.7.1

Hi @odan ,

The way you did is correct for

$select = $query->newSelect()->from('table')->cols(['*'])->where('id IN (?)', [1, 2, 3]);

Your expected result is wrong.

SELECT * FROM table WHERE id IN (1,2,3);

Aura.SqlQuery doesn't provide this. This creates something like

SELECT * FROM table WHERE id IN (:_1_, :_2_, :_3_);

You can get $select->bindValues() to get the bind values. Use Aura.Sql or similar to execute the queries to produce the expected result you mentioned here.

See tests

Aura.SqlQuery/tests/Common/SelectTest.php

Line 706 in 722174f

public function testAutobind()

I have not tried this, so I doubt about this. Normally used bindValues .

$select = $query->newSelect()->cols(['*'])->where('id NOT IN :id', ['id' => [1, 2, 3]]);

You can see if the query produced is correct via echo $select->__toString();

UPDATE : By the way it seems you don't have from also.

For questions you probably want to consider sending to https://groups.google.com/forum/#!forum/auraphp . We have many more people to help you there. It is good to open bug reports probably on github.

@harikt Thank you! Now it works :-)

$query = new QueryFactory('mysql');

// IN
$select = $query->newSelect()->from('users')->cols(['*'])->where('id IN (?, ?, ?)', 1, 2, 3);

// NOT IN
$select = $query->newSelect()->from('users')->cols(['*'])->where('id NOT IN (?, ?, ?)', 1, 2, 3);

@odan would you be interested to add an example over https://github.com/auraphp/Aura.SqlQuery/tree/2.x#select place after the current select example ?

You can look into the syntax etc and make it look similar .

I noticed there are lots of questions for the where in queries.

Thank you.

@harikt Yes very much. I will create a PR.