My query joins columns from mainly 2 tables and adds some more tables
'booked' , 'available', etc... computing the previous columns.
So in my View i get the results like grouped according on the table
the come from (ex 'products', 'products_wedding_wishlist', etc.). But
the computed columns do not have a table so they are grouped under
'#sql_968_0'. Can you help me to try to give a defined name like
'computed' or something else? So I can use it in the view more
efficiently
here is the query
SELECT products.product_code, products.product_short_desc,
products_wedding_wishlists.product_id,
products_wedding_wishlists.product_price,
products_wedding_wishlists.quantity AS total, IFNULL( ( SELECT
SUM( orders_products.quantity ) FROM orders_products LEFT JOIN orders
ON orders.id = orders_products.order_id WHERE orders.status = 'open'
AND orders_products.product_id =
products_wedding_wishlists.product_id ) , 0 ) AS booked,
IFNULL( ( SELECT SUM( orders_products.quantity ) FROM orders_products
LEFT JOIN orders ON orders.id = orders_products.order_id WHERE
orders.status = 'closed' AND orders_products.product_id =
products_wedding_wishlists.product_id ) , 0 ) AS purchased, ( SELECT
(total - booked - purchased) ) AS available, ( SELECT
COUNT( images_products.product_id ) FROM images_products WHERE
images_products.product_id = products_wedding_wishlists.product_id )
AS maximages FROM products_wedding_wishlists LEFT JOIN orders_products
ON orders_products.product_id = products_wedding_wishlists.product_id
LEFT JOIN orders ON orders.id = orders_products.order_id LEFT JOIN
products ON products_wedding_wishlists.product_id = products.id WHERE
products_wedding_wishlists.wedding_wishlist_id =4 GROUP BY product_id
ORDER BY products.id LIMIT 0,50
here is the query results
Array
(
[0] => Array
(
[products] => Array
(
[product_code] => 0110016
[product_short_desc] => SET 6 POST TAV/2 TON/2 OVA
GR/2 OV PI/2INSAL MEXICO BUGATTI
)
[products_wedding_wishlists] => Array
(
[product_id] => 1
[product_price] => 15.00
[total] => 1
)
[#sql_968_0] => Array
(
[booked] => 1
[purchased] => 0
[available] => 0
[maximages] => 0
)
)
[1] => Array
(
[products] => Array
(
[product_code] => 0110098
[product_short_desc] => SET 19 PZ QUADRATO ARANCIO
808 MICHELETTI
)
[products_wedding_wishlists] => Array
(
[product_id] => 8
[product_price] => 61.00
[total] => 19
)
[#sql_968_0] => Array
(
[booked] => 6
[purchased] => 0
[available] => 13
[maximages] => 0
)
)
[2] => Array
(
[products] => Array
(
[product_code] => 0110490
[product_short_desc] => SERVIZIO PIATTI 25 PZ.
ALFOLA CRAYON JAUNE GUY
)
[products_wedding_wishlists] => Array
(
[product_id] => 18
[product_price] => 23.00
[total] => 5
)
[#sql_968_0] => Array
(
[booked] => 3
[purchased] => 0
[available] => 2
[maximages] => 0
)
)
[3] => Array
(
[products] => Array
(
[product_code] => 0110695
[product_short_desc] => SERIZIO MID SUMMER ARANCIO
20 PZ PATRA
)
[products_wedding_wishlists] => Array
(
[product_id] => 22
[product_price] => 56.00
[total] => 1
)
[#sql_968_0] => Array
(
[booked] => 0
[purchased] => 0
[available] => 1
[maximages] => 0
)
)
[4] => Array
(
[products] => Array
(
[product_code] => 0110702
[product_short_desc] => SERIZIO MID SUMMER VERDE
20 PZ PATRA
)
[products_wedding_wishlists] => Array
(
[product_id] => 23
[product_price] => 69.00
[total] => 1
)
[#sql_968_0] => Array
(
[booked] => 0
[purchased] => 0
[available] => 1
[maximages] => 0
)
)
[5] => Array
(
[products] => Array
(
[product_code] => 0120666
[product_short_desc] => PIATTO PIANO PALM YELLOW
A0421056 ULTRASTRONG
)
[products_wedding_wishlists] => Array
(
[product_id] => 83
[product_price] => 26.00
[total] => 9
)
[#sql_968_0] => Array
(
[booked] => 0
[purchased] => 0
[available] => 9
[maximages] => 0
)
)
[6] => Array
(
[products] => Array
(
[product_code] => 0392541
[product_short_desc] => TAPPO PER AMPOLLA 5070 T
5041/AO ALESSI
)
[products_wedding_wishlists] => Array
(
[product_id] => 2344
[product_price] => 2.00
[total] => 5
)
[#sql_968_0] => Array
(
[booked] => 0
[purchased] => 0
[available] => 5
[maximages] => 0
)
)
[7] => Array
(
[products] => Array
(
[product_code] => 1222280
[product_short_desc] => FORCHETTA SERVIRE DRY
LUCIDO L 4180/12 ALESSI
)
[products_wedding_wishlists] => Array
(
[product_id] => 4689
[product_price] => 16.00
[total] => 5
)
[#sql_968_0] => Array
(
[booked] => 0
[purchased] => 0
[available] => 5
[maximages] => 0
)
)
)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
