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.