Regarding queries across relationships

> 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

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