Snippets
Create an account or login to be able to add, comment and rate snippets.

Navigation

subqueries with criteria

I haven't found a way to use subqueries as alias with criteria in the book.

Here is a small piece of code that works very well:

the subquery is in an alias defined by Criteria::addAsColumn($alias, $expr)

it can be used to order the result, or in case you have to deal with foreign keys, etc.

$c = new Criteria();
 
$c->addAsColumn('brandname', '(SELECT brand.name FROM brand WHERE brand.id=brand_id)');
 
$c->addAscendingOrderByColumn($c->getColumnForAs('brandname'));
 
$this->products = ProductPeer::doSelect($c);
 

Product has a brand_id that references the id key of the brand table that contains the 'name' field which is used to order the result.

Patrice Blanchardie

by noname noname on 2008-01-26, tagged criteria  order  propel  sql  subquery 

Comments on this snippet

gravatar icon
#1 Romain Dorgueil on 2008-02-13 at 06:33

Nice tip, but to keep with propel logic (who said it has no logic at all?) I'd keep using Criteria for subqueries, like I did in sfPropelImpersonator Custom Criteria examples (look at http://www.dakrazy.net/document/sf_propel_impersonator_documentation/quick_examples.html ). I'll add now an example with a FROM subquery like there.

gravatar icon
#2 noname noname on 2008-02-25 at 03:09

sfPropelImpersonator is exactly what my example lacks :-) this plug-in makes propel really usable, thank you Romain@sensio

You need to create an account or log in to post a comment or rate this snippet.