Rails 4左外连接具有Group By和count的多个表

Is it possible to do a left outer join in Rails4 with group by and counts. I am trying to write a scope which will do a left outer join of users with the messages, comments and likes tables and then group by id to get total count. In case there is no association, the count should be zero.

是否可以在Rails4中使用group by和count进行左外连接。我正在尝试编写一个范围，它将使用消息，注释和喜欢的表对用户进行左外连接，然后按ID分组以获得总计数。如果没有关联，则计数应为零。

So the final result set would be cuuser.*, message_count, likes_count and comments_count. Any idea how this can be accomplished? Thanks in Advance!

所以最终的结果集将是cuuser。*，message_count，likes_count和comments_count。知道如何实现这一目标吗？提前致谢！

class Cuuser < ActiveRecord::Base
    has_and_belongs_to_many :groups
    has_many :messages
    has_many :comments
    has_many :likes

    validates :username, format: { without: /\s/ }
    scope :superusers, -> { joins(:comments, :likes).
        select('cuusers.id').
        group('cuusers.id').
        having('count(comments.id) + count(likes.id) > 2')}
end

1 个解决方案

#1

You could drop to SQL strings:

您可以删除SQL字符串：

joins("LEFT OUTER JOIN comments ON comments.cuuser_id = cuuser.id LEFT OUTER JOIN likes ON likes.cuuser_id = cuuser.id LEFT OUTER JOIN messages on messages.cuuser_id = cuuser.id")

This isn't great. You're sacrificing some portability and the ability of ActiveRecord to guess the association.

这不是很好。你牺牲了一些可移植性和ActiveRecord猜测关联的能力。

If you used the Squeel gem you could use the following format:

如果您使用Squeel gem，则可以使用以下格式：

joins{ [comments.outer, likes.outer, messages.outer] }

Squeel exposes Arel in a slightly more sane format, so you can do things like left outer joins while still guessing associations from the model definitions.

Squeel以更加理智的格式暴露了Arel，因此您可以执行左外连接等操作，同时仍然可以猜测模型定义中的关联。

You could use Arel of course, but things get very clunky very quickly.

你当然可以使用Arel，但事情很快变得非常笨重。

To get your counts, try:

要获得您的计数，请尝试：

select('cuusers.id, count(messages.id) as message_count, count(likes.id) as likes_count, count(comments.id) as comments_count').

They should be available as attributes on the returned objects, just like ordinary database fields.

它们应该作为返回对象的属性可用，就像普通的数据库字段一样。

#1