php - 左联接可在Laravel中获得单行

我一直未能成功尝试左联接并获取所需的数据

这是我的代码:

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

这些评论是我几次尝试中的一些...

我只想从照片表中获得单个记录，该记录与首先更新且状态为1的外键album_id匹配

请帮助...

最佳答案

我已经使用DB::raw()来实现这一目标

$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的编码与我所需的相似，并且显示了相同的结果，因此，特别感谢他的时间和精力...

但是比较quires的执行时间，我仍然像上面的片段一样去挖掘

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'

拿了520μs - 580μs
和@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`

拿了640μs - 750μs但都做了相同的事情...

关于php - 左联接可在Laravel中获得单行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27317227/