Geek Answers Handbook

Propel 2: using a subquery as a value for a virtual column

We have a table containing all our products and a separate table containing all ordered elements. Items to be ordered are basically a copy of the ordered product with relation to the original product (through a foreign key) and additional data that apply only to the ordered product, for example, to the quantity ordered.

In this way, we ensure the consistency of our order data, because although we may delete the old product in the future, the old orders still have all ordered products as order items. Orders and order items are linked through a simple cross-reference table (for example, in the Propel documentation) with only two fields order_id and item_id.

Now I need to implement a function that calculates an ordered quantity of a product that has not yet been sent, so we can track how many of our stocks are available for sale, and how many of them are actually sold, but not yet sent.

To achieve this, I had to select all the elements related to this source product and belonging only to unauthorized orders, then SUM to increase the field of the quantity of these elements. This is as follows 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 an ordered quantity that has not yet been sent. But the request itself is unattractive, I need to add the result to the product models.

ProductQuery, "OrderedQty" , , :

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

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

, , Propel.

+4
1

, addSelectQuery. , , .

addSelectQuery Criteria subQuery from.

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

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

carcase :

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

virutal withColumn:

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

:

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

:

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

More articles:


All Articles