左联接可在Laravel中获得单行 [英] Left join to get a single row in Laravel

问题描述

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

I have been unsuccessfully trying to leftjoin and get the required data

这是我的代码:

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

我想从照片表中仅获取一条记录，该记录与首先更新且状态为1的外键album_id相匹配

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

请帮助...

推荐答案

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

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

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

但是比较quires的执行时间，我还是按照上面的代码片段来挖掘

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'

选择520μs - 580μs

和@JarekTkaczyk的

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`

使用640μs - 750μs，但是两者都做的相同...

took 640μs - 750μs But both did the same...

这篇关于左联接可在Laravel中获得单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！