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.

score 8 · Answer 1 · answered Dec 18 '14 at 08:56

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'));

Ah you're right. I stumbled upon the addSelectQuery() method but prematurely ignored it because that method adds a subselect to the FROM clause of the select and not directly as a column value. I didn't think any further at that moment, but using it the way you suggest will most likely do the trick, I will try it out soon. SQL has so many tricks up its sleeve... :D — Subsurf, Dec 18 '14 at 11:35
So far so good, I guess this is going in the right direction. I added my subselect via addSelectQuery() which combines each row of the product table with each row of the subselect. This is the usual behavior of SQL when more than one table is given in the FROM clause. So I have to filter out only the rows that belong to each other via ->where('item_query_result_alias.product_id = Product.Id'). Problem with that is that all products that have not yet been ordered are also excluded from the resultset. I would need some sort of left join I guess instead of a multi select? — Subsurf, Dec 22 '14 at 11:57

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

1 Answers1