将Laravel DB :: raw()查询与join连接到Eloquent

我有一个模型'图片'(数据库表'uploaded_pictures')有许多'喜欢'(s)< / p>

 私有函数likeWrapper(){
返回$ this-&gt; hasMany('Like','typeId');
}

公共函数likes(){

返回$ this-&gt; likeWrapper() - &gt; where('type','=',Config :: get('enums.content_types.PICTURE')) - &gt; get();
}
</ code> </ pre>

现在我想创建一个页面,分页,返回按喜欢计数排序的所有图片。 我使用Collection sortBy方法完成了这项工作:</ p>

  $ pictures = Picture :: get() - &gt; sortByDesc(function($ picture){
return $ picture- &gt; likes() - &gt; count();
});
</ code> </ pre>

但是此结果无法分页。 我有一个原始查询来完成这项工作,但我很难将其转换为Eloquent查询构建器:</ p>

  $ query =“SELECT p。*,COUNT(l.id)AS  countLikes 
FROM uploaded_pictures AS p
LEFT OUTER JOIN
(SELECT id,typeId,type FROM likes WHERE type ='picture')AS l
ON p.id = l.typeId
WHERE date&lt; ='$ referenceDate'
GROUP BY p.id,l.typeId
ORDER BY countLikes DESC“;

$ pictures = DB :: select(DB :: raw($ query));
</ code> < / pre>

Eloquent中此查询的等效内容是什么? 我正在使用Laravel 4.2.11。</ p>
</ div>

展开原文

原文

I have a Model 'Picture' (database table 'uploaded_pictures') that has many 'Like'(s)

private function likeWrapper() {
    return $this->hasMany( 'Like', 'typeId');
}

public function likes() {
    return $this->likeWrapper()->where( 'type', '=', Config::get( 'enums.content_types.PICTURE' ) )->get();
}

Now I want to create a page, paginated, that returns all Pictures ordered by the count of likes. I made this work using the Collection sortBy method:

$pictures = Picture::get()->sortByDesc( function($picture) {
        return $picture->likes()->count();
});

But this result can't be paginated. I have a raw query that does the job, but I struggle translate it to Eloquent query builder:

        $query = "SELECT p.*,  COUNT(l.id) AS countLikes
            FROM uploaded_pictures AS p
            LEFT OUTER JOIN
                (SELECT id, typeId, type FROM likes WHERE type='picture') AS l
            ON p.id = l.typeId
            WHERE  date<='$referenceDate'
            GROUP BY p.id, l.typeId
            ORDER BY countLikes DESC";

    $pictures = DB::select(DB::raw($query));

What is the equivalent of this query in Eloquent? I'm using Laravel 4.2.11.

1个回答



我希望它能起作用:</ p>

  $ pictures = Picture :: leftjoin(  'likes','pictures.id','=','likes.typeId')

  • &gt;选择(DB :: raw('pictures。*,COUNT(likes.id)as countLikes'))
  • &gt; where('likes.type','=','Picture')
  • &gt; orWhere('likes.typeId')
  • &gt; orderBy('countLikes','DESC')
  • &gt; groupBy('pictures.id')
  • &gt; get(); </ code> </ pre> </ div>

展开原文

原文

I hope it will work :

        $pictures = Picture::leftjoin('likes', 'pictures.id', '=', 'likes.typeId')
                    ->select(DB::raw('pictures.*, COUNT(likes.id) as countLikes'))
                    ->where('likes.type', '=', 'Picture')
                    ->orWhere('likes.typeId')
                    ->orderBy('countLikes', 'DESC')
                    ->groupBy('pictures.id')
                    ->get();

dongqieli4164
dongqieli4164 很好,它的工作原理。 非常感谢你!
5 年多之前 回复
doukao5073
doukao5073 好的伙伴,我明白了:)看到编辑
5 年多之前 回复
dtwr2012
dtwr2012 是的,我们越来越接近:)最后一点是没有喜欢的图片缺失了
5 年多之前 回复
dongliuxia9495
dongliuxia9495 抱歉,我忘了添加select(DB :: raw('pictures。*,COUNT(*)as countLikes'))。 我编辑了我的帖子,我希望它更符合你的想法
5 年多之前 回复
dongpi9494
dongpi9494 不,Picture属性完全缺失。 这转换为选择COUNT(*)作为countLikes来自'uploaded_pictures'内连接'喜欢''on uploaded_pictures'。'id'='喜欢'。'typeId'其中'喜欢'。'type'=? group by'upload_pictures'。'id'按'countLikes'desc limit 3排序
5 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问