Left Outer Join with multiple tables and group by counts

rubyonrails-talk
1

Hi,

Is it possible to do a left outer join in Rails4. This is my model and I am trying to write a scope which will do a left outer join of users with the message, comments and likes tables and then group by id to get total count.

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!

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](http://cuusers.id)').

group('[cuusers.id](http://cuusers.id)').

having('count([comments.id](http://comments.id)) + count([likes.id](http://likes.id)) > 2')}

end

Thanks,

Ganesh
2

Couple thoughts:

  • plain joins is going to do an INNER JOIN. You might want something like this:

includes(:comments, :likes).references(:comments, :likes).select(‘cuusers.id’).group(‘cuusers.id’).having(…)

  • BUT: that query is going to be fairly inefficient, since it’s going to have to compute every group before filtering them with HAVING.

Instead, you might want to do this bookkeeping differently by using the built-in counter caching mechanism. More info here:

Using counter caches in your example will mean adding a comments_count and likes_count column to your cuusers table. Then your scope could just use where to compare them…

–Matt Jones