3

I have following scope in my eloquent model and I want to add two conditions to it. I need help in doing it. 

public function scopeImages($query) {
    $query->join('images as i', function ($join) {
        $join->on('i.vessel_id', '=', 'vessel.id')
        ->where('i.sort', '=', 'min(i.sort)');
    })
    ->leftjoin('users', 'users.id', '=', 'vessel.user_id')
    ->select('vessel.*', 'i.image as image', 'users.name as brokername');
}

images table has featured and sort columns. I want to select one row where images.featured is 1 and min sort of the returned results. If there are no images.featured=1 then I want to select min of sort.

Currently the above scope selects image of min sort for each vessel_id

Bhargav Nanekalva
  • 606
  • 9
  • 31

2 Answers2

0

If you order by featured (if featured is boolean) in desc and sort in asc, it will list featured 1 in highest priority and then list sort from min to max. Now, if you take 1st row, you will get what you want.

$query->join('images', 'images.vessel_id', '=', 'vessel.id')
        ->leftjoin('users', 'users.id' ,'=', 'vessel.user_id')
        ->select('vessel.*', 'images.image as image', 'users.name as brokername')
        ->orderBy('images.featured', 'DESC')
        ->orderBy('images.sort', 'ASC')
        ->take(1);
Bishal Paudel
  • 1,896
  • 2
  • 21
  • 28
  • Thanks for the answer. What I'm trying to do is, for every `image.vessel_id=vessel.id` I want to `where images.sort=min(images.sort)`. Example, see the raw query http://stackoverflow.com/questions/7588142/mysql-left-join-min – Bhargav Nanekalva Sep 20 '15 at 09:15
0

You need a correlated subquery

https://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html

    SELECT  v.*, 
(SELECT `image` FROM images WHERE vessel_id = v.id ORDER BY featured DESC, sort LIMIT 1) AS image
    FROM `vessel` AS v

@EDIT You Can give this a try:

public function scopeImages($query) {
    return $query
        ->leftjoin('users', 'users.id', '=', 'vessel.user_id')
        ->select(\DB::raw("vessel.*, (SELECT `image` FROM images WHERE vessel_id = vessel.id ORDER BY featured DESC, sort LIMIT 1) AS image, users.name as brokername"));
}
ad4s
  • 304
  • 1
  • 7