I have 2 tables, the first one is ITEMS and the other is GALLERY (where I have the images of every item), these 2 tables are related by item_id = item_gal_id.
In these 2 tables there are 2 important fields, item_order (in ITEMS) and img_order (in GALLERY) which I use to order the items and the images by drag & drop.
I have no problem to order and output the items in the home page with this query:
SELECT *
FROM items a
LEFT JOIN gallery b ON (a.item_id = b.item_gal_id)
WHERE a.it_public='SI'
GROUP BY a.item_id, b.item_gal_id
ORDER BY a.item_order ASC
BUT...every item shows 1 (ONE) image in the home page and that image is not the one suposed to be, I mean that I can't output the image that is in the first place of the GALLERY order...
When I hit in one of the items to go to the detail page is easy to show the images in the order I want by using this query:
SELECT *
FROM gallery
WHERE item_gal_id = (the item_id I send by GET)
ORDER BY img_order ASC
So, how can I show a list of items ordered by item_order and, in the same query, to show the image that is the first for that item having img_order, is there a way to do this?
I've been searching and reading for 2 days with no luck, trying several options but I'm really stack with this.
If you just want the to pull out the minimum item_gal_id for each item, then you can use a simple JOIN with aggregation. This requires you to explicitly list the columns from items that you want to display (I assumed item_id, col1 and col2).
SELECT i.item_id, i.col1, i.col2, MIN(g.item_gal_id)
FROM items i
LEFT JOIN gallery g ON i.item_id = g.item_gal_id
WHERE i.it_public = 'SI'
GROUP BY i.item_id, i.col1, i.col2
ORDER BY i.item_order
If you need more information from gallery than just item_gal_id, then one solution would be to use window function ROW_NUMBER() (available in MySQL 8.0 only):
SELECT *
FROM (
SELECT
i.item_id,
i.item_order,
i.col1,
i.col2,
g.item_gal_id,
g.col3,
g.col4,
ROW_NUMBER() OVER(PARTITION BY i.item_id ORDER BY g.item_gal_id) rn
FROM items i
LEFT JOIN gallery g ON i.item_id = g.item_gal_id
WHERE i.it_public = 'SI'
) x
WHERE rn = 1
ORDER BY item_order
Edit: based on the comments, here is another option without window functions, using a correlated subquery with a NOT EXITS condition to filter on the gallery record that has the lowest img_order.
SELECT i.*, g.*
FROM items i
LEFT JOIN gallery g ON i.item_id = g.item_gal_id
WHERE
i.it_public = 'SI'
AND NOT EXISTS (
SELECT 1
FROM gallery g1
WHERE i.item_id = g1.item_gal_id AND g1.img_order < g.img_order
)
ORDER BY i.item_order
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加