Subqueries with Propel can be complicated. The premise is simple, do a query, then take the results and use them to build the remainder of the query. However, if the nested portion of your query is complicated, Propel has difficulty bubbling the subquery results up.
One common approach, which will suffice in many instances, would be to do the following:
// query some date from item table
$itemQuery = ItemQuery::create()->...;
// use the first query as an alias parameter when building the final query
$productQuery = ProductQuery::create()->addSelectQuery($itemQuery, 'item_query_result_alias', false);
Then we can simply add the virutal column using the `withColumn` method:
// use the first query as a parameter when building the final query
$products = ProductQuery::create()
->withColumn('item_query_result_alias.ordered_qty', 'ordered_qty')
->addSelectQuery($itemQuery, 'item_query_result_alias', false)
->find();
That use case works fine if the nested portion is a basic select; however, if it is not then you’ll need to move to what Prople calls Criteria statements.
Let’s say we have items that have a `dataAdded` and `dateRemoved` field and we want items added and removed on the same day where the source is `website`.
// raw sql
$sql = "item.id IN (SELECT id FROM item WHERE DATE_FORMAT(item.dataAdded,'%Y-%m-%d') = DATE_FORMAT(item.dateRemoved,'%Y-%m-%d') AND source = `website` order by dateAdded ASC)";
$c = new Criteria();
$c->add( ItemPeer::ID, $sql, Criteria::CUSTOM );
$items = ItemPeer::doSelect( $c );
Bingo, now you have a fully qualified results set of objects to iterate on!
