Propel 2: Using a subquery as the value for a virtual column

Question

We have a table containing all our products and a separate table containing all ordered items. The ordered items are basically a copy of the product that has been ordered, with a relation to their source product (via foreign key) and additional data which is only relevant to the ordered item, like the ordered quantity.

This way we ensure consistency of our order data, because even though we may delete an old product in the future, old orders still have all of the ordered products in form of the order items. Orders and order items are connected via a simple cross reference table (like the one in the Propel documentation) with only two fields, order_id and item_id.

Now I have to implement a feature that calculates the ordered quantity of a product that has not yet been shipped, so we can keep track of how much of our stock is still available for sale and how much of it is actually already sold, but not yet shipped.

To achieve this I had to select all items which relate to the given source product and which belong only to unshipped orders, then SUM up the quantity field of those items. That looks like the following in Propel2:

$ordered_qty = ItemQuery::create()
    // Sum up the quantity of all ordered items and only select this one column
    ->withColumn('SUM(Item.Qty)', 'ordered_qty')
    ->select('ordered_qty')
    // join with the cross referencing table between orders and order items 
    // so we can join with the actual order data in the next step
    ->leftJoinOrderItemCrossRef()
    // join with the order data so that we can filter out 
    // already shipped (= archived) orders
    ->leftJoin('OrderItemCrossRef.Order')
    ->addJoinCondition('Order', 'Order.IsArchived != 1')
    // only find the items relating to the desired product
    ->where('Item.ProductId = ?', $product_id)
    ->groupByProductId()
    ->findOne(); 

This query works like a charm, findOne() returns the ordered quantity that has not yet been shipped. But the query alone is of little use to me, I need to add the result to the product models.

I thought about adding a custom method to the ProductQuery which adds a virtual column 'OrderedQty' to every product, so I can select all products like this:

$products = ProductQuery::create()
    ->withOrderedQty()
    ->find();

foreach($products as $product)
{
    echo 'Ordered Qty of ' . $product->getName() . ': ' . $product->getOrderedQty() . '\n';
}

But I have no idea how I can use a subquery as the value for a virtual column in Propel.

Answer 1

If I understand you question correctly, you can achieve the desired effect with a addSelectQuery method. I don't have the needed table structure, so I'll just give a hint on how it works.

addSelectQuery adds a Criteria as subQuery in the from clause.

// some query from item table
$itemQuery = \ItemQuery::create()->...;

$productQuery = \ProductQuery::create()
    ->addSelectQuery($itemQuery, 'item_query_result_alias', false);

After this you'll get a query carcase like this:

SELECT item.*
FROM item, (/* here will be the $itemQuery select */) AS item_query_result_alias

Then just add a virutal column with withColumn method:

$products = \ProductQuery::create()
    ->withColumn('item_query_result_alias.ordered_qty', 'ordered_qty')
    ->addSelectQuery($itemQuery, 'item_query_result_alias', false)
    ->find();

The query will be:

SELECT item.*, item_query_result_alias.ordered_qty AS ordered_qty
FROM item, (/* here will be the $itemQuery select */) AS item_query_result_alias

Get the result:

var_dump($products->getFirst()->getVirtualColumn('ordered_qty'));