too long

Question

In my Yii project, I have Order, Product, and OrderProduct models, interrelated. In order to get statistics on products sold within a certain period of time, I added the following relation in Product model: I have the following relation in Yii project:

...
'orderProductsCount' => array(self::STAT, 'OrderProduct', 'product_id', 'select' => 'SUM(quantity)'),
'orderProductsAmount' => array(self::STAT, 'OrderProduct', 'product_id', 'select' => 'SUM(total)'),
...

Overall data is returned correctly. However, when I am trying to get data for orders within specific period of time - I get same numbers as the totals (sold over all the time).

I tried making the same thing in pure MySQL - this is the query that describes my needs:

SELECT product.id,  `order`.added, COUNT( order_product.product_id ) , SUM( order_product.total ) 
FROM product
LEFT JOIN order_product ON order_product.product_id = product.id
LEFT JOIN  `order` ON  `order`.id = order_product.order_id
WHERE  `order`.added >  "2015-01-10"
GROUP BY product.id

But I don't know how to express it in a Yii way... Please help me.

PS. Tried adding condition in the relation, as follows:

'orderProductsAmount' => array(self::STAT, 'OrderProduct', 'product_id', 'select' => 'SUM(total)', 'condition' => 'orders.added < "2015-01-10"'),

Receive an error as the condition is on a related table, not product.

Answer 1

As with is not defined in CStatRelation you have to provide condition through with statement as

your relations in product model as

public function relations() {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'order' => array(self::MANY_MANY, 'Order', OrderProduct::model()->tableName().'(product_id,order_id)'),
            'amount'=>array(self::STAT, 'OrderProduct', 'product_id','select' => 'sum(total)'),
            'total'=>array(self::STAT, 'OrderProduct', 'product_id','select' => 'sum(product_id)'),
        );
    }

and your condition findAll()

$model= Product::model()->with(array(
            'order' => array(
                'alias' => 'o',
                'condition' => 'o.added > :date',
                'params' => array(':date' => '2015-06-01')
            ),
            'amount' => array(),
            'total' => array()
        ))->findAll();

CVarDumper::dump($model,10,true);