Skip to content

Add a subquery as where clause to your propel criteria or statement?

So you are still using the propel - the blazing fast php orm but you want to narrow/scope your result by a complex/not daily condition?
No problem you can use a subquery and put it to the where condition area.

$mySubqueryForFoo = '(
    SELECT
        COUNT(*)
    FROM
        ' . FooPeer::TABLE_NAME . '
    WHERE
        ' . FooPeer::BAR_ID . ' = ' . BarPeer::ID . '
) > 0';

//if you are using the criteria object
$criteria->add(
    'my_subquery_for_foo',
    $mySubqueryForFoo,
    Criteria::CUSTOM
);

//if you are using the query object
BarQuerycreate()
->add(
    'my_subquery_for_foo',
    $mySubqueryForFoo,
    Criteria::CUSTOM
);

PHP Propel - add a column (with a sub select) to an result

Assuming you want to add a counting column like "number_of_foo" but you want to use your propel environment. Propel, of course, provides a way how you can achive this.

$criteria = new Criteria();
$criteria->addAsColumn(
    'number_of_foo',
    'SELECT
        COUNT(*)
    FROM
        ' . FooPeer::TABLE_NAME . '
    WHERE
    ' . FooPeer::BAR_ID . ' = ' . BarPeer::ID . '); )

"Quelle surprise", propel can deal with that also in the cooler query way.
$result = BarQuery::create()->addAsColumn(
    'number_of_foo',
    'SELECT
        COUNT(*)
    FROM
        ' . FooPeer::TABLE_NAME . '
    WHERE
        ' . FooPeer::BAR_ID . ' = ' . BarPeer::ID . ');
)

source