Hi Everyone,
I'm looking for the "Cake" way to paginate a model with two sub-queries joined as tables :
MY TABLES
products :
id | name
1 | Product A
2 | Product B
sales:
| id | product_id | date | amount
| 1 | 1 | 2012-01-01 | 20.00
| 2 | 1 | 2012-01-02 | 15.00
| 3 | 2 | 2012-01-01 | 10.00
| 4 | 2 | 2012-01-02 | 10.00
cost :
id | product_id | date | amount
1 | 1 | 2012-01-01 | 10.00
2 | 1 | 2012-01-02 | 5.00
3 | 2 | 2012-01-01 | 10.00
4 | 2 | 2012-01-02 | 15.00
The goal is to get these stats form a date range
product_id | name | total_cost | total_sale | profit
1 | Product A | 15.00 | 25.00 | 10.00
2 | Product B | 25.00 | 20.00 | -05.00
The Query will be :
SELECT
Product.id ,
Product.name,
Sale.total_sale,
Cost.sum_cost,
(Sale.total_sale - Cost.total_cost) AS profit,
FROM( SELECT id,name FROM products ) AS Product
LEFT JOIN (
SELECT product_id, SUM(amount) AS total_sale
FROM sales
WHERE `date` BETWEEN "2012-01-01" AND "2012-01-02"
GROUP BY product_id
) AS Sale ON Sale.product_id = Product.id
LEFT JOIN
(
SELECT product_id, SUM(amount) AS total_cost
FROM cost
WHERE `date` BETWEEN "2012-01-01" AND "2012-01-02"
GROUP BY product_id
) AS Cost ON Cost.product_id = Product.id
GROUP BY Product.id
ORDER BY profit DESC;
I've try to join the table directly but i'm getting duplicate data
--
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to
[hidden email].
To unsubscribe from this group, send email to
[hidden email].
Visit this group at
http://groups.google.com/group/cake-php?hl=en-US.
Locked
