On Tuesday, 26 August 2014 09:10:23 UTC-4, Ganesh Ranganathan wrote:
>
> 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')}
>
>
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
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/5a9a57b8-3a9d-48fd-9435-418355f7ec32%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
