Laravel eloquent - 如何在自定义关系中使用 DB raw
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33042983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel eloquent - how to use DB raw inside custom relationship
提问by user804293
I have laravel models : Post and Comment. As normal, post will have many comment tables as bellow:
我有 Laravel 模型：发布和评论。正常情况下，帖子会有很多评论表，如下所示：
Posts table
--------------------------------------
|id |title | |
--------------------------------------
|1 |test |
comments table:
--------------------------------------
|id |post_id | created_at |
--------------------------------------
|1 |1 |2015-09-12 09:01:02 |
--------------------------------------
|2 |1 |2015-09-12 09:03:02 |
now I want to query for the post with the most number of comments from current date back to last 7 days. Here is my code in Laravel Controller:
现在我想查询从当前日期到过去 7 天评论数量最多的帖子。这是我在 Laravel 控制器中的代码：
$posts= Post::whereHas(
'comments', function ($q) {
$q->select(DB::raw("DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s')"),'post_id',DB::raw('count(*) as cmt_count'))
->where(DB::raw('`comments`.`created_at` BETWEEN NOW()-INTERVAL 8 DAY AND NOW()'))
->groupBy('post_id');
})
->take(5)->get();
got the ERROR:
得到错误：
QueryException in Connection.php line 651: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 3 column(s) (SQL: select * from `posts` where (select DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s'), `post_id`, count(*) as cmt_count from `comments` where `comments`.`post_id` = `posts`.`id` and `comments`.`created_at` BETWEEN NOW()-INTERVAL 8 DAY AND NOW() is null group by `post_id`) >= 1 limit 5)
Please help to point me what am I doing wrong ?
请帮助指出我做错了什么？
回答by user804293
OK. after some digging with the suggestions above, I solve the problem myself. Post this answer here for anyone who having same problem could find it useful. bellow is the query (assumed you already setup the relationship )
好的。在对上述建议进行了一些挖掘之后，我自己解决了问题。在这里发布此答案，供遇到相同问题的任何人使用。波纹管是查询（假设您已经设置了关系）
$posts = Post::select(DB::raw('posts.id,posts.title,count(*) as cmnt_count'))
->join('comments',function($q){
$q->on('posts.id','=','comments.post_id')
->where(DB::raw("DATE_FORMAT(comments.created_at,'%Y-%m-%d %H:%i:%s')),'>=',Carbon::now()->subDays(7));
})->groupBy('posts.id')
->orderBy('cmnt_count')->get();
now I can get what I want: retrieve all posts which have comments within 7 days and sort by number of comments.
现在我可以得到我想要的：检索所有在 7 天内有评论的帖子并按评论数量排序。
回答by MaGnetas
I'm not sure about the whole logic of your SQL. But the problem is with the following part:
我不确定你的 SQL 的整个逻辑。但问题在于以下部分：
`comments`.`created_at` BETWEEN NOW()-INTERVAL 8 DAY AND NOW() is null
Now the part of your query builder responsive for this piece is:
现在，您的查询构建器响应这一部分的部分是：
where(DB::raw('`comments`.`created_at` BETWEEN NOW()-INTERVAL 8 DAY AND NOW()'))
This is quite clear - you want to use a raw where expression. but where method usually takes 2 or three params.
这很清楚 - 您想使用原始 where 表达式。但是 where 方法通常需要 2 或 3 个参数。
Why don't you try replacing it with whereRaw?
你为什么不尝试用 whereRaw 替换它？
Your builder would look like this:
您的构建器将如下所示：
$posts= Post::whereHas(
'comments', function ($q) {
$q->select(DB::raw("DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s')"),'post_id',DB::raw('count(*) as cmt_count'))
->whereRaw('`comments`.`created_at` BETWEEN NOW()-INTERVAL 8 DAY AND NOW()')
->groupBy('post_id');
})
->take(5)->get();
This should use the whereRaw argument as a raw piece of the final SQL.
这应该使用 whereRaw 参数作为最终 SQL 的原始部分。
Let me know if this solves your issue.
如果这能解决您的问题，请告诉我。
Take care
小心
回答by Crystalwing Bakaboe
Don't use $q->select(), whereHas()expecting the query to return a number (typically count(*)).
The whereHas()is likely to select parent records where the count of its relative records is more than 1.
不要使用$q->select()，whereHas()期望查询返回一个数字（通常是 count(*)）。在whereHas()可能选择父记录其中的相对的记录数超过1。
Try ->toSql()instead of ->take(5)->get()to see your raw SQL query.
尝试->toSql()而不是->take(5)->get()查看您的原始 SQL 查询。
====== EDIT ========
====== 编辑 ========
I think you could do something like this.
我认为你可以做这样的事情。
$posts= Post::whereHas(
'comments', function ($q) {
$q->where(DB::raw('`comments`.`created_at` BETWEEN NOW()-INTERVAL 8 DAY AND NOW()'))
->groupBy('post_id');
})
->with('comment_counts')
->take(5)->get();
Or if you want to use Carbon:
或者，如果您想使用Carbon：
$q->where('created_at', '>=', Carbon::now()->addWeek(-1))
In your model:
在您的模型中：
class Post extends Model {
//...
public function comment_counts() {
return $this->hasMany('App\Comment')->select([ DB::raw("DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s')"),'post_id',DB::raw('count(*) as cmt_count') ]);
}
}
回答by Kiran Subedi
Try this query, I haven't try but it must work
试试这个查询，我还没试过，但它一定能用
$posts= Post::whereHas(
'comments', function ($q) {
$q->select(DB::raw("DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s')"),'post_id',DB::raw('count(*) as cmt_count'))
->groupBy('post_id')
->where('created_at','>',DB::raw('date_sub(CURDATE(), INTERVAL 1 WEEK)'))
->where(DB::raw('DATE(created_at)'),'<=',DB::raw('CURDATE()'));
})
->take(5)->get();
