Left Outer Join with multiple tables and group by counts

[Ganesh Ranganathan]

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').
		group('cuusers.id').
		having('count(comments.id) + count(likes.id) > 2')}
end


Thanks,
Ganesh

[Matt Jones]

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:

http://guides.rubyonrails.org/association_basics.html#counter-cache

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