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

Navigation

Sub-selects using Propel

Let say you want to write a sub-select, and have a Propel object returned.

As an example, lets generate the following SQL:

SELECT
    orders.ID,
    orders.TYPE,
    orders.STATUS
FROM
    orders
WHERE
    orders.STATUS IN (
        SELECT
              STATUS.NAME
        FROM
              STATUS
        WHERE
             STATUS.ORDER_TYPE = 'purchase'
    )

Propel does not natively handle this sort of SQL. There are however two ways to create this SQL or to get this data set and have Propel objects returned.

Overview of the different methods

  1. Use of Criteria::CUSTOM
  2. Rewrite the SQL using JOINs
    • PRO: Very readable code
    • CON: Not using a sub-select
    • CON: You may be using a sub-select for SQL performance, so this may be slower

Way 1: Use of Criteria::CUSTOM

$c = new Criteria();
$subSelect = "orders.STATUS IN (
    SELECT
          status.NAME
    FROM
          status
    WHERE
         status.ORDER_TYPE = 'purchase'
    )";
$c->add(StatusPeer.STATUS, $subSelect, Criteria::CUSTOM);
$orders = StatusPeer::doSelect($c);

Way 2: Rewrite the SQL using JOINs

Example of the rewritten SQL:

SELECT
    orders.ID,
    orders.TYPE,
    orders.STATUS
FROM
    orders,
    STATUS
WHERE
    orders.STATUS = STATUS.NAME
    AND STATUS.ORDER_TYPE = 'purchase'

This can be written as follows in your action:

$c = new Criteria();
$c->addJoin (OrderPeer::STATUS, StatusPeer::NAME);
$c->add(StatusPeer.ORDER_TYPE, 'purchase');
$orders = StatusPeer::doSelect($c);
by Greg Militello on 2006-08-17, tagged criteria  propel  query  sql 

Comments on this snippet

gravatar icon
#1 Stephen Riesenberg on 2006-11-29 at 05:50

I'm a little amazed that it is actually impossible to build a criteria object and add it to a criteria or criterion as a subselect. Logically, you'd think this would work:

//get criterion object from main criteria
$crit = $criteria->getNewCriterion(TablePeer::FIELD, 'somevalue');
//create new search criteria for subselect
$c = new Criteria();
//use select distinct
$c->clearSelectColumns();
$c->addSelectColumn(SomeTablePeer::ID);
$c->setDistinct();
//add filter for subselect
$c->add(SomeTablePeer::FIELD, 'somefiltervalue');
//add the subselect to the criterion
$crit->addAnd($criteria->getNewCriterion(TablePeer::SOME_TABLE_ID, $c, Criteria::IN));

I bet we can either overload the getNewCriterion function, or write a custom function that can allow this functionality. I think I'll work on it soon.

gravatar icon
#2 Jordi Backx on 2007-01-03 at 10:02

I want to do a subselect in the FROM clause .... I suspect you can't do a CUSTOM on the addJoin method? The API doens't say it at least.

gravatar icon
#3 Jill Elaine on 2008-04-09 at 04:45

There is a small typo in the 'Way 1: Use of Criteria::CUSTOM' code: $c->add(StatusPeer.STATUS, $subSelect, Criteria::CUSTOM); SHOULD BE $c->add(StatusPeer::STATUS, $subSelect, Criteria::CUSTOM);

I managed to use this 'way' for a subquery where I needed to find the files that were not already associated with a particular course session: public function executeFiletosessions() { $primary_group_id = //retrieve value $sessionid = //retrieve value $c = new Criteria(); $c->clearSelectColumns(); $c->addAscendingOrderByColumn(FilePeer::FILE_NAME); $c->addJoin(FilePeer::ID,FileToSessionPeer::FILE_ID, Criteria::LEFT_JOIN); $c->add(FilePeer::SF_GUARD_GROUP_ID, $primary_group_id); $subSelect = "file.id NOT IN ( SELECT file_id FROM file_to_session WHERE file_to_session.course_session_id=$sessionid)"; $c->addAnd(FileToSessionPeer::COURSE_SESSION_ID, $subSelect, Criteria::CUSTOM);

$this->files = FilePeer::doSelect($c); }

I'm always amazed when I get something to work correctly!

gravatar icon
#4 jestep on 2009-06-29 at 05:27

The Way 1 is incorrectly formatted.

It should be: $c->add(StatusPeer::STATUS, $subSelect, Criteria::CUSTOM);

and not: $c->add(StatusPeer.STATUS, $subSelect, Criteria::CUSTOM);

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