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