Regarding queries across relationships

rubyonrails-talk
1

> I'm trying to build a query that looks like this:

> @book_pages, @books = paginate(:books, > :per_page => 20, > :conditions => ['book.author.name like ?', '%jim%], > :order_by => @sort_order)

thats how it should look like : --->> paginate(:book, :include=>[:author], ..., :conditions=>["authors.name like ?", '%jim%']) the include statement operates a join in the sql query. See eager loading for more explanations.

The problem with eager loading is that it uses LEFT JOIN. So it will do something like:

SELECT ... FROM books   LEFT JOIN authors   ON books.author_id = authors.id   WHERE authors.name like '%jim%';

At least on mysql this forces the server to scan first the books table and only then the authors table, so the optimizer cannot do its job properly.

Is there some :include -like option that uses inner joins?

Thanks!           HoraPe

2

The problem with eager loading is that it uses LEFT JOIN. So it will do something like:

SELECT ... FROM books   LEFT JOIN authors   ON books.author_id = authors.id   WHERE authors.name like '%jim%';

At least on mysql this forces the server to scan first the books table and only then the authors table, so the optimizer cannot do its job properly.

Is there some :include -like option that uses inner joins?

Yup, you must use the :join statement and manually write the join: Book.find(:all, ...., :joins=>["INNER JOIN authors ON bla bla bla"], :conditions=>bla...)