dimanche 10 mars 2019

ActiveRecord: is it possible do a join 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 ActiveRecord 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

Aucun commentaire:

Enregistrer un commentaire