CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecordrails-activerecord
Shanaka Kuruwita
Shanaka Kuruwita

Reputation: 699

ActiveRecord: is it possible do a join relation with a scope

Here is my original problem. I have two models as below.

class Author < ActiveRecord::Base
  has_many :books
end

and

class Book < ActiveRecord::Base
  belongs_to :author

  scope :available, ->{ where(available: true) }
end

I would like to left join Author to Book's scope "available". Following is the query I would like to execute on DB. Is it possible to do this ?

Select authors.* , count(books.*) as books_count 
From authors left outer join books on books.author_id = authors.id 
and books.available = true 
group by (authors.id) order by books_count;

I have tried following method 

 Author.joins("LEFT OUTER JOIN authors on books.author_id = authors.id")
.merge(Book.available)
.select("authors.* , count(books.*) as books_count")
.group("authors.id").order("books_count")

But that result in following query.

Select authors.* , count(books.*) as books_count 
From authors left outer join books on books.author_id = authors.id 
where books.available = true 
group by (authors.id) order by books_count;

So it remove all the authors who are not having a book.

Important: My AR version doesn't have left_outer_joins method

Upvotes: 0

Views: 2641

Answers (3)

Lorin Thwaits
Lorin Thwaits

Reputation: 399

If your app is on Rails 4.2 then if you want true left_outer_joins support just install the gem "brick" and it automatically adds the Rails 5.0 implementation of left_outer_joins. You would probably want to turn off the rest of its functionality, that is unless you want an automatic "admin panel" kind of thing available in your app!

Upvotes: 0

engineersmnky
engineersmnky

Reputation: 29328

While it does not use your scope. (mostly because your scope includes a where clause that you do not want as part of your query)

You could do this with arel such as: 

book_table = Book.arel_table
author_table = Author.arel_table
author_join = author_table.join(book_table,Arel::Nodes::OuterJoin)
  .on(book_table[:author_id].eq(author_table[:id]).and(
       book_table[:available].eq(true)
    )
  )
Author.joins(author_join.join_sources)
  .select("authors.* , count(books.*) as books_count")
  .group("authors.id").order("books_count")

This will result in the following SQL: 

SELECT 
  authors.* , 
  count(books.*) as books_count
FROM 
  authors 
  LEFT OUTER JOIN books ON books.author_id = authors.id
    AND books.available = true
GROUP BY 
  authors.id
ORDER BY 
  books_count

Upvotes: 0

Vishal
Vishal

Reputation: 7361

You need to add one more query which will check if author don't have books then we will need to add it. please try below query

 Author.joins("LEFT OUTER JOIN authors on books.author_id = authors.id")
.merge(Book.available)
.where("books.author_id IS NULL")
.select("authors.* , count(books.*) as books_count")
.group("authors.id").order("books_count")

Upvotes: 1

Related Questions