Yii Framework Forum: Get first found value from db - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Get first found value from db

#1 User is offline   mark dijkstra 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 35
  • Joined: 04-January 18

Posted 13 February 2018 - 06:53 AM

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

0

#2 User is offline   Silvio Porcellana 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 31-January 18

Posted 13 February 2018 - 06:03 PM

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...
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users