Conditional table join

Question

I have the following area in my eloquent model, and I want to add two conditions to it. I need help with this.

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'); } 

Table

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

Currently, the specified area selects a min sort image for each vessel_id

Answer 1

If you order by attributes (if the function is boolean) in desc and sort in ascending order, it displays list 1 with the highest priority, and then sorts the list from min to max. Now, if you take the 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); 

Answer 2

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 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")); }