Left Outer Join with multiple tables and group by counts

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

Thanks,
Ganesh

2

On Tuesday, 26 August 2014 09:10:23 UTC-4, Ganesh Ranganathan wrote:

having('count(comments.id) + count(likes.id) > 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 J.