mysql - Laravel eloquent - 如何在自定义关系中使用 DB raw

Question

我有 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 |

现在我想查询从当前日期到过去 7 天评论数量最多的帖子。这是我在 Laravel Controller 中的代码:

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

收到错误:

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)

请帮我指出我做错了什么？

Answer 1

好的。经过对上述建议的一番挖掘后，我自己解决了这个问题。在此发布此答案，对于遇到相同问题的任何人都可能会发现它很有用。下面是查询(假设您已经设置了关系)

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

现在我可以得到我想要的:检索 7 天内有评论的所有帖子，并按评论数排序。