Left join to get a single row in Laravel

Question

I have been unsuccessfully trying to leftjoin 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();

The comments are some of my several trying...

I want to get only a single record from the photos table matching the foreign key album_id which was updated first and also with status 1

pls help...

Answer 1

I have used DB::raw() in order 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();

@JarekTkaczyk 's coding was similar and displayed the same result as I needed, so a special thanks to him for his time and effort...

But comparing the execution time for the quires I stayed to mine as 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 's

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 it using either leftJoin or rightJoin (but the latter would return Photo models, so probably you won't need that):

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