CODEWITHSUNDEEP
ruby-on-railsjoinscope
DarkFurios
DarkFurios

Reputation: 13

Advanced Rails 3 scope with CROSS JOIN

I want a scope which returns me a list of (eg.) books they have chapters. I found this post.

It helped me to select books they have chapters. But if I want want to select those they have no chapters like:

    class Book
      scope :long, joins(:chapters).
        select('books.id, count(chapters.id) as n_chapters').
        group('books.id').
        having('n_chapters = 0')
    end

This scope returns me nothing. Can you help me out?

Upvotes: 1

Views: 2327

Answers (1)

jdoe
jdoe

Reputation: 15771

Using joins leads to creating relation with all the combinations of book+chapter that already have connection. This is done vie INNER JOIN SQL-clause. You have to make OUTER JOIN if you want all the possible combinations to be created (including book+nullified chapter).

replace

joins(:chapters).

with

joins('LEFT OUTER JOIN chapters ON books.id=chapters.book_id').

But in this case consider using the NOT IN SQL-clause instead. Like here:

scope :long, lambda { where('id NOT IN (%s)' % Chapter.select(:book_id).to_sql) }

It is considerably smaller/faster and more readable/maintainable construction.

Upvotes: 2

Related Questions