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 [email protected]. To unsubscribe from this group, send email to [email protected]. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
