Re: Regarding queries across relationships

Rails
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

Horacio J.
Peñ[email protected]
[email protected]

2

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…)