UNION, ORDER BY y LIMIT, aplicando filtros a consultas sobre subconsultas
Ratio: 0 / 5
El otro día un cliente nos pidio una "simple modificación" -cito textualmente- en su lista de productos. Ahora quería que los productos se mostrasen por un orden nuevo, en lugar de sólo por precio entraba en el juego un nuevo factor, el filtrado de los que están en stock; mostrandose inicialmente los de stock cero, y despues todos los demás pero en ellos el orden debía de no ser el stock, si no el precio.
Y así es como una simple consulta de una línea paso a ser esto:
$products_sql = "
SELECT t1.products_id, t1.products_price, t1.`products_quantity`,
t1.`products_model`, t1.`products_image`,
t1.`products_date_added`, t1.`products_last_modified`,
t1.`products_date_available`, t1.`products_weight`,
t1.`products_status`, t1.`products_tax_class_id`,
t1.`manufacturers_id`, t1.`products_ordered`, t1.`ft_p2`, t1.`ft_p3`,
t1.language_id, t1.products_name, t1.products_description,
t1.products_url, t1.products_viewed
FROM
((SELECT p.products_id, p.products_price, p.`products_quantity`,
p.`products_model`, p.`products_image`, p.`products_date_added`,
p.`products_last_modified`, p.`products_date_available`, p.`products_weight`,
p.`products_status`, p.`products_tax_class_id`,
p.`manufacturers_id`, p.`products_ordered`, p.`ft_p2`, p.`ft_p3`,
d.language_id, d.products_name, d.products_description,
d.products_url, d.products_viewed
FROM `products` p, `products_description` d
WHERE (p.products_id = d.products_id)
AND (d.language_id=" .$osc_lang_id. ")
AND (p.products_quantity=0)
ORDER BY p.products_price DESC
LIMIT 0, 10000
) UNION (
SELECT p2.products_id, p2.products_price, p2.`products_quantity`,
p2.`products_model`, p2.`products_image`, p2.`products_date_added`,
p2.`products_last_modified`, p2.`products_date_available`, p2.`products_weight`,
p2.`products_status`, p2.`products_tax_class_id`,
p2.`manufacturers_id`, p2.`products_ordered`, p2.`ft_p2`, p2.`ft_p3`,
d2.language_id, d2.products_name, d2.products_description,
d2.products_url, d2.products_viewed
FROM `products` p2, `products_description` d2
WHERE (p2.products_id = d2.products_id)
AND (d2.language_id=" .$osc_lang_id. ")
AND (p2.products_quantity>0)
ORDER BY p2.products_price DESC
LIMIT 0, 10000
))t1
LIMIT " . $osc_start . " , " . OSC_MAX_LIST . ";
";
