Querying with Propel Criteria using “NOT IN” (criteria::not_in)

It’s fairly common to use “NOT IN” database queries in web development.

Symfony when using Propel does not have native support for using NOT IN queries in reference to another tables. You can use NOT IN and an array, but cannot use NOT IN with reference to another table.

Here’s how to use NOT IN other_table with Propel and Symfony. Let’s pretend we need to run this query.

SELECT * FROM my_table_1 WHERE id NOT IN (
SELECT id FROM my_table_2);

Using Propel and Criteria, there is no native way of running this query. But, with the Criteria::CUSTOM modifier, we can force this query through propel.

It’s actually very easy.

 
$c = new Criteria;
 
/* Example query
$not_in_query = 'my_table_1.id NOT IN (
SELECT id
FROM my_table_2)';
*/
 
$not_in_query = '%s NOT IN (
SELECT %s
FROM %s)';
 
$not_in_query = sprintf(
$not_in_query,
MyTable1Peer::ID,
MyTable2Peer::ID,
MyTable2Peer::TABLE_NAME
);
 
$c->add(MyTable1Peer::ID, $not_in_query, Criteria::CUSTOM);
 
$result = MyTable1Peer::doSelect($c);

You can add whatever other Criteria to the query as well. So far this is the only reliable way to run these sort of queries that I’ve found.