I am trying to get all records from the product table, these records are unique but i use a leftjoin to add a second table with images of these products but when I do it will return some products more than once as some products have 1 or more images. How can I use a limit on the product_images table to make sure it will just get 1 image instead of all. The example below does not work so is this possible?
$query->select( ['a.id', 'b.image as category_image' , 'c.image as product_image'] ) ->from( ['a' => 'product'] ) ->leftJoin( ['b' => 'product_images'] ,'a.id = b.id AND b.type = "category" AND LIMIT = 1') ->leftJoin( ['c' => 'product_images'] ,'a.id = c.id AND b.type = "product" AND LIMIT = 1') ->all();
Advertisement
Answer
It can be solved by sub queries:
$query->select(['a.id', 'b.image as category_image' , 'c.image as product_image']) ->from(['a' => 'product']) ->leftJoin(['b' => 'product_images'], 'a.id = b.product_id AND b.id = ( select id from product_images where product_id = a.id AND type = "category" order by id limit 1)') ->leftJoin(['c' => 'product_images'], 'a.id = c.product_id AND c.id = ( select id from product_images where product_id = a.id AND type = "product" order by id limit 1)') ->all();
You have to use composit indexes on the table product_images
to have goood performance.
You could use other sorting as order by id
, e.g. you could introduce an ordering field in the table product_images
.
I think a.id = b.id
and a.id = c.id
was a simple mistake. It’s about a.id = b.product_id
and a.id = c.product_id
.