在Rails中查找具有多个外键值的记录

I've got two models User and Post where a :user has_many :posts

我有两个模型User和Post,其中a:user has_many:posts

How do I find a list of users who have more than n posts?

如何查找超过n个帖子的用户列表?

I'm looking for a simple statement like where rather than something to do with using scopes.

我正在寻找一个简单的声明,而不是使用范围。

3 个解决方案

#1

The where query does not allow aggregate functions like count inside it, the ideal method to tackle this problem is using the having method.

where查询不允许聚合函数如count在其中,解决此问题的理想方法是使用having方法。

User.joins(:posts).group('users.id').having('COUNT(*) >= ?', n)

This will be mapped to

这将映射到

SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" GROUP BY users.id HAVING COUNT(*) >= n

There is another method possible which is more easier, by using counter cache inside the Post Model and keeping the count as a posts_count column on the user table.

通过在Post Model中使用计数器缓存并将计数保持为用户表上的posts_count列,可以更容易地实现另一种方法。

belongs_to :user, counter_cache:true 
User.where('posts_count > ?', n)

But I prefer the first method as it does not involve any DB changes and is pretty straight forward

但我更喜欢第一种方法,因为它不涉及任何数据库更改,而且非常简单

#2

User.join(:posts).group('users.id').having('count(user_id) > n')

Another way of solving this is to use counter_cache and add a posts_count column on the user table.

另一种解决方法是使用counter_cache并在user表上添加posts_count列。

belongs_to :user, counter_cache:true # Inside the Post model

Then you can do

那你可以做

User.where('posts_count > ?', n)

#3

-1

Let try this:

试试这个:

User.where('id IN (SELECT user_id 
                   FROM posts
                   GROUP BY user_id
                   HAVING COUNT(*) > ?
                  )', n)

#1