summarizing table

I have a table to keep track of incoming orders with 4 fields order_id, product_id, date and quantity.

order_id is just a unique id, product_id refers to a table with products. It is possible to order the same product on the same day multiple times at different quantities. (This feature has to remain).

My objective is simple: a summary of the total quantity for a product on a single day.

My sql should be something like this:

select product_id,date, sum(quantity) from orders group by product_id,date

I have no clue how to achieve this from my orders model.

I have tried the following strategies without success

A self::stat relation in the products model, but I can’t seem to add a group by statement that works. The sql generated reads

select product_id,sum(quantity) as sq from orders group by product_id,date

which is partially correct. It doesn’t select the date, so it renders the wrong sum, because only product_id is linked to orders.

My second approach was to create a named scope for the orders model




'scope_sq'=>array(

    'select'=>array('product_id','date','sum(quantity) as sq'),

    'group'=>'product_id,date',

);



So far so good




$t=Order::model()->scope_sq()->find();//retrieves the first line of data

echo $t->product_id; // renders fine

echo $t->date; // renders fine

echo $t->sq;//renders an error: Order.sq is not defined



I’m at a loss.

I read on this forum an article how to parse sql directly. I would rather not use this feature, because then I’m back at the start, writing raw sql.

So try to define Order.sq:


public $sq;

Sometimes the answer can be so simple :blink: .

Thanks a million.