9

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

Mathew Magante
  • 1,280
  • 3
  • 19
  • 28
Ronser
  • 1,855
  • 6
  • 20
  • 38

4 Answers4

18

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

Ronser
  • 1,855
  • 6
  • 20
  • 38
9

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();
Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
  • welcome buddy @JarekTkaczyk, I did the same with slight variation and before you posted this +1 – Ronser Dec 06 '14 at 04:56
  • @Ronser 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. – Jarek Tkaczyk Dec 06 '14 at 10:13
  • Dude, you saved me big time! Thanks buddy. I asked similar question and got no valid answer. Here http://stackoverflow.com/questions/32676622/conditional-table-joins – Bhargav Nanekalva Oct 06 '15 at 11:17
  • For group By, in laravel with MySql go to file config/database.php and it change in array MySql mode strict to false. – Satish May 06 '22 at 12:26
1

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();
Amin
  • 579
  • 7
  • 23
0

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.

Tom Burns
  • 41
  • 2