named_scope in rails with a has_many association

Question

Hi all,

I am trying to achieve what I think will be a fairly complex query using the magic of Rails without having lots of ugly looking SQL in the code.

Since my database is dealing with rather specialised biomedical models I'll translate the following to a more real world scenario.

I have a model Book that

has_many :chapters

and Chapter that

belongs_to :book

Say for instance Chapters have a name attribute and the names can be preface ,introduction and appendix, and a Book could have a Chapter named preface and a Chapter named introduction but no Chapter named appendix. In fact any combination of these

I am looking to find all Books that have both Chapters named preface and introduction.

At present I have a named_scope as follows

Book
  named_scope :has_chapter?, lambda { |chapter_names|
    condition_string_array = []
    chapter_names.size.times{condition_string_array << "chapters.name = ?"}
    condition_string = condition_string_array.join(" OR ")
    {:joins => [:chapters]  , :conditions => [condition_string, * chapter_names]}
  }

If I call Book. has_chapter? ["preface", "introduction"] this will find me all books that have either a Chapter named preface or introduction. How could I do a similar thing that would find me isolates that both Chapters preface and introduction?

I am not that familiar with SQL so am not quite sure what kind of join would be needed and whether this could be achieved in a named scope.

Many thanks

Anthony

Answer 1

I am looking to find all Books that have both Chapters named preface and introduction.

At present I have a named_scope as follows

You can do this without a named_scope.

class Book < ActiveRecord::Base
  has_many :chapters

  def self.has_chapter?(chapter_names)
    Chapter.find_all_by_name(chapter_names, :include => [:book]).map(&:book)
  end
end

Answer 2

I like the condensing of code to

condition_string = Array.new(chapter_names.size, "chapters.name=?").join(" AND ")

and this does indeed work for a join with "OR".However the AND join will not work since this means that chapters.name in a single row of a join has to be both 'preface' and 'introduction' for example.

An even neater way of doing my original "OR" join is

 named_scope :has_chapter?, lambda { | chapter_names |
   {:joins => [: chapters]  , :conditions => { :chapters => {:name => chapter_names}}}
 }

Thanks to Duplex on Rails Forum (Post link) In order to achieve my original problem though DUPLEX suggested this

named_scope :has_chapters, lambda { |chapter_names|
  {:joins => :chapters  , :conditions => { :chapters => {:name => chapter_names}},
   :select => "books.*, COUNT(chapters.id) AS c_count",
   :group => "books.id", :having => "c_count = #{chapter_names.is_a?(Array) ? chapter_names.size : 1}" }
}

This may work in some flavour of SQL but not in PostgreSQL. I had to use the following

named_scope : has_chapter?, lambda { | chapter_names |
  {:joins => :chapters  , :conditions => { :chapters => {:name => chapter_names}},
   :select => "books.* , COUNT(chapters.id)",
   :group => Book.column_names.collect{|column_name| "books.#{column_name}"}.join(","), :having => "COUNT(chapters.id) = #{chapter_names.is_a?(Array) ? chapter_names.size : 1}" }
}

The code

Book.column_names.collect{|column_name| "books.#{column_name}"}.join(",")

is required because in PostgreSQL you can't select all columns with books.* and then GROUP BY books.* each column has to be named individually :(

Answer 3

I am looking to find all Books that have both Chapters named preface and introduction.

I use mysql, not postgres, but I assume postgres has support for sub-queries? You could try something like:

class Book

  named_scope :has_preface, :conditions => "books.id IN (SELECT book_id FROM chapters WHERE chapters.name = 'preface')"
  named_scope :has_introduction, :conditions => "books.id IN (SELECT book_id FROM chapters WHERE chapters.name = 'introduction')"

end

and then:

Book.has_preface.has_introduction