CODEWITHSUNDEEP
mysqljoinsubquery
RS7
RS7

Reputation: 2361

MySQL join or subquery - get product data along with first product image

I'd like to be able to return a thumbnail (first image of a set) with my product data - what is the best way to accomplish this (performance)?

I have the following tables:

products (product data)
products_images (relation between products and images)
images (image data)

So for every product, it'd return the first image of a set associated with that product.

Two things:

I'm thinking of doing a subquery since I don't know how to about ordering and limiting image results in a join.

What do you suggest?

Edit:

The image order field is present in the products_images table. That table has the following fields (product_id, image_id, order)

Upvotes: 3

Views: 1660

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

SELECT 
    p.*
  , i.thumbnail
FROM 
    products AS p
  LEFT JOIN
    images AS i
      ON i.image_id =
         ( SELECT image_id
           FROM products_images AS pi
           WHERE pi.product_id = p.product_id
           ORDER BY `order` ASC                      --- or DESC
           LIMIT 1
         )

An index on (product_id, order, image_id) in table products_images would be useful for the subquery to run faster.

Upvotes: 2

Noah Goodrich
Noah Goodrich

Reputation: 25263

This should give you the first image by the order column in the product_images table:

SELECT *
FROM products p
LEFT JOIN (
    SELECT pi.product_id, i.*
    FROM images i
    INNER JOIN (
        SELECT product_id, image_id
        FROM products_images pi
        ORDER BY `order`        
    ) pi ON i.image_id = i.id
    GROUP BY product_id
) i ON i.product_id = p.id

Upvotes: 0

Related Questions