Forum: Ruby on Rails Left Outer Join with multiple tables and group by counts

61b2cb27fe3d25f42ecc0364c33420f8?d=identicon&s=25 Ganesh Ranganathan (Guest)
on 2014-08-26 15:11
(Received via mailing list)
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
6883e5ef03484d4fcef507d7b4f1d243?d=identicon&s=25 Matt Jones (Guest)
on 2014-08-27 22:59
(Received via mailing list)
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:

http://guides.rubyonrails.org/association_basics.h...

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
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.