Yii2, how to add a LIMIT on a leftjoin to get only 1 result back

Question

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();

actually try to remove AND before LIMIT, cause its not correct syntax — Angel Deykov, Nov 17 '20 at 09:47
Tried that and this will return an error: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 1 LEFT JOIN — user759235, Nov 17 '20 at 12:44
check this - https://stackoverflow.com/questions/15626493/left-join-only-first-row or use inner select — vvpanchev, Nov 17 '20 at 13:47

Tibor Nagy · Accepted Answer · 2020-11-18T09:28:13.790

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.

This may be extremely inefficient for large datasets. Probably adding `GROUP BY` by product ID may give the same result but with much better performance. — rob006, Nov 18 '20 at 12:42

Yii2, how to add a LIMIT on a leftjoin to get only 1 result back

1 Answers1