1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
|
SELECT DISTINCT
p.`id` AS `product_id`,
p.`name` AS `product_name`,
p.`uuid`,
up.`created_at` AS `tagged_date`,
p.`price`,
p.`url`,
p.`category_id`,
c.`name` AS `category_name`,
b.`name` AS `brand_name`,
pimg.`id` AS `product_image_id`,
pimg.`image`,
pimg.`original_image_path`
FROM
(
SELECT `id`,`product_id`, `created_at` AS `tagged_date`
FROM `user_products` a
WHERE NOT EXISTS(
SELECT 1
FROM `user_products` b
WHERE b.product_id = a.product_id
AND b.created_at > a.created_at
)
ORDER BY `tagged_date` DESC
LIMIT 0,20
) up
INNER JOIN `products` p
ON (up.`product_id` = p.`id`)
LEFT JOIN `product_brands` pb
ON (p.`id` = pb.`product_id`)
LEFT JOIN `brands` b
ON (b.`id` = pb.`brand_id`)
LEFT JOIN `product_images` pimg
ON (pimg.`product_id` = p.`id`)
LEFT JOIN `categories` c
ON (c.`id` = p.`category_id`)
LEFT JOIN `product_retailers` pr
ON (p.`id` = pr.`product_id`)
LEFT JOIN `retailers` r
ON (r.`id` = pr.`retailer_id`)
LEFT JOIN `experience_items` ei
ON (
ei.`item_id` = up.`id`
AND ei.`item_type` = 'UserProduct'
)
LEFT JOIN `experiences` e
ON (e.`id` = ei.`experience_id`)
ORDER BY `tagged_date` DESC
LIMIT 0, 20 |
Partager