29
Jun
8

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.

Enjoyed reading this post?
Subscribe to the RSS feed and have all new posts delivered straight to you.
8 Comments:
  1. Jose D'Silva 16 Mar, 2010

    This was brilliant. Thanks.

  2. Ashton 23 Mar, 2010

    Excellent, thanks very much!!

  3. Manel 13 Sep, 2010

    You’ve saved my day!!!

    Thanks

  4. Ward 7 Jun, 2011

    So glad I found this after a lot of searching. Thank you man. Very nice solution.

  5. Timon 10 Jun, 2011

    Excellent, thanks very much!!

  6. Samrat 5 Jan, 2012

    Thanks a lot!

  7. Håvar 17 Jan, 2012

    Awesome! Thanks

  8. zao 10 Jan, 2013

    jestep. You are a genious.

    I spend a lot of time trying do the same with addSelectColumn(), but your solution is brilliant.

Post your comment




Copyright © 2017 SayNoToFlash, Jamie Estep, All Rights Reserved · Theme design by Themes Boutique