Get first found value from db

So getting some data from the DB I encountered an issue, as the example below im joining a couple of tables, one of these tabels is ‘product_image’ which should have in most case per ‘product_id’ 1 ‘image_path’ value, but sadly some can have several values, which will add duplicated values to the output array.

In short, how can I get the first found value of the image_path column per product_id.

// used code now


$query->select( ['a.language , a.translation , CONCAT( "/" , c.translation ) as module , a.product_id , d.image_path'] )

                       ->from( ['a' => 'product_translation'] )

                       ->leftJoin( ['b' => 'product'] ,

                                    'b.id = a.product_id')

                       ->leftJoin( ['c' => 'category_translation'] ,

                                    'b.category_id = c.category_id AND c.attribute = a.attribute AND c.language = a.language' )

                       ->leftJoin( ['d' => 'product_image'] ,

                                    'd.product_id = a.product_id AND d.image_type = "product_image"' )

                       ->where( ['b.is_deleted' => 0,

                                 'b.is_enabled' => 1,

                                 'a.attribute'  => 'slug'

                               ])

                       ->all();

// example how ti want to be





// maybe (:first) ?

$query->select( ['a.language , a.translation , CONCAT( "/" , c.translation ) as module , a.product_id , d.image_path :first'] )


                       ->from( ['a' => 'product_translation'] )

                       ->leftJoin( ['b' => 'product'] ,

                                    'b.id = a.product_id')

                       ->leftJoin( ['c' => 'category_translation'] ,

                                    'b.category_id = c.category_id AND c.attribute = a.attribute AND c.language = a.language' )

                       ->leftJoin( ['d' => 'product_image'] ,


// maybe (AND LIMIT = 1)?

'd.product_id = a.product_id AND d.image_type = "product_image AND LIMIT =1"' )




                       ->where( ['b.is_deleted' => 0,

                                 'b.is_enabled' => 1,

                                 'a.attribute'  => 'slug'

                               ])

                       ->all();

From what I recall from my days using SQL extensively, one way to do this is to use subqueries. Basically you would say something like this (this uses sample tables and record names but I hope you get the concept):


SELECT * FROM products LEFT JOIN images ON images.product_id = products.product_id WHERE images.image_id = ( SELECT image_id FROM images ORDER BY image_id LIMIT 0, 1)

I am not sure you can convert this into an ActiveQuery so you might want to use raw SQL here…