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