Left join to get one row in Laravel

Question

I tried unsuccessfully to leave and get the required data

Here is my code:

$album = Albums::->where('users_id',$user_id) ->leftJoin('photos',function($query){ $query->on('photos.albums_id','=','albums.id'); $query->where('photos.status','=',1); // $query->limit(1); // $query->min('photos.created_at'); }) ->where('albums.status',1)->get(); 

Comments are some of my several attempts ...

I want to get only one entry from the photo table by matching the foreign key album_id , which was updated first, as well as with status 1

Help pls ...

Answer 1

I used DB::raw() to achieve this

 $album = Albums::select( 'albums.*', DB::raw('(select photo from photos where albums_id = albums.id and status = 1 order by id asc limit 1) as photo') ) ->where('users_id',$user_id) ->where('albums.status',1)->get(); 

Coding

@JarekTkaczyk was similar and displayed the same result as me, so special thanks to him for his time and efforts ...

But, comparing the runtime for quires , I stayed on my own like my above snippet

 select `albums`.*, (select photo from photos where albums_id = albums.id and status = 1 order by id asc limit 1) as photo from `albums` where `users_id` = '1' and `albums`.`status` = '1' 

took 520μs - 580μs

and @JarekTkaczyk

 select `albums`.*, `p`.`photo` from `albums` left join `photos` as `p` on `p`.`albums_id` = `albums`.`id` and `p`.`created_at` = (select min(created_at) from photos where albums_id = p.albums_id) and `p`.`status` = '1' where `users_id` = '1' and `albums`.`status` = '1' group by `albums`.`id` 

took 640μs - 750μs but both did the same ...

Answer 2

You can achieve this using leftJoin or rightJoin (but the latter will return Photo models, so you probably won't need this):

 Albums::where('users_id', $user_id) ->leftJoin('photos as p', function ($q) { $q->on('photos.albums_id', '=', 'albums.id') ->on('photos.updated_at', '=', DB::raw('(select min(updated_at) from photos where albums_id = p.albums_id)')) ->where('photos.status', '=', 1); }) ->where('albums.status', 1) ->groupBy('albums.id') ->select('albums.*', fields from photos table that you need ) ->get(); 

Answer 3

Are you trying to check albums with status "1"? If so, you are missing the equal sign from your finals, where.

Try:

  ->where('albums.status','=',1)->first(); 

Alternatively, you can achieve this by using 'on' instead of "where" inside the join function. You also do not need to split the request inside the function and do it as a single line using "->":

 $album = Albums::->where('users_id',$user_id) ->leftJoin('photos',function($query){ $query->on('photos.albums_id','=','albums.id') ->on('photos.status','=',1); }) ->where('albums.status','=',1)->first(); 

You need to make sure that you are using the "first", as it will return one row of the first result. Get () will return an array.

Answer 4

As a simple answer that leads to a single object, I suggest the following query:

 $album = DB::table('albums')->select('albums.*', 'photos.photo') ->join('photos', 'photos.id', '=', 'albums.id') ->where('users_id',$user_id) ->where('albums.status',1) ->first();