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

Hatcher

Does adding $query->take(1); before the two commented lines helps? – Cacophonous

Call to undefined method lluminate\Database\Query\JoinClause::take() error @Cacophonous – Hatcher

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

Hatcher

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

Hambletonian

welcome buddy @JarekTkaczyk, I did the same with slight variation and before you posted this +1 – Hatcher

@Hatcher you have the answer there, it's correct. And if it is not what you want to achieve, then rephrase the question and say precisely what result you expect, and then let me know. – Hambletonian

Dude, you saved me big time! Thanks buddy. I asked similar question and got no valid answer. Here https://mcmap.net/q/1170352/-conditional-table-joins – Sapindaceous

For group By, in laravel with MySql go to file config/database.php and it change in array MySql mode strict to false. – Deconsecrate

Answer 3

As a straightforward answer which results in 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();

Merganser

Answer 4

Are you trying to check for albums that have the status of '1'? If this is the case you are missing an equals sign from your final where.

Try:

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

Alternatively you may be able to achieve this with an 'on' instead of a 'where' inside the join function. You also don't need to split up query inside of the function and can do it as one line with the '->' :

$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 'first', as it will return a single row of the first result. Get() will return an array.

Wicklund

i need list of all albums with a single photo – Hatcher

Albums::->where this is wrong, correct Albums::where – Marcoux