ruby script/plugin install svn://rubyforge.org/var/svn/eagerfindersql

Consider the following query relating authors with many books:

  Author.find(:all, :include => :books)

This would likely produce the following SQL:

  SELECT authors.id AS t0_c0, authors.name AS t0_c1,
         books.id AS t1_c0, books.author_id AS t1_c1, books.title AS t1_c2
    FROM authors
    LEFT OUTER JOIN books ON books.author_id = authors.id

The query might be written with books as the driver, rather than authors as follows:

  SELECT authors.id, authors.name, books.id AS book_id, books.name AS book_name
    FROM books
    JOIN authors ON authors.id = books.author_id

The Rails query would then be written as follows:

  Author.find(:all, :include => books,
              :finder_sql => "
                SELECT authors.id, authors.name, books.id AS book_id, books.name AS book_name
                  FROM books
                  JOIN authors ON authors.id = books.author_id".
              :column_mapping => {
                :primary_key => 'id',
                :columns => {
                  'id'  => 'id',
                  'name => 'name'
                },
                :associations => {
                  :books => {
                    :primary_key => book_id,
                    :columns => {
                      'id'        => 'book_id',
                      'author_id' => 'id',
                      'name'      => 'book_name
                    }
                  }
                }
              })

This is more verbose, but allows for absolute control of the SQL used to return results across multiple model
associations.

A more complicated example would be the following:

  QUERY = "
  SELECT
    a.id AS author_id,
    a.name AS author_name,
    p.id AS post_id,
    p.title AS post_title,
    p.body AS post_body,
    p.type AS post_type,
    c.id AS comment_id,
    c.body AS comment_body,
    c.type AS comment_type
  FROM authors a
  LEFT JOIN posts p ON p.author_id = a.id
  LEFT JOIN comments c ON c.post_id = p.id"

  MAPPING = {
    :primary_key => 'author_id',
    :columns     => [['id', 'author_id'],
                     ['name', 'author_name', ]],                         
    :associations=> {
      :posts => {
        :primary_key  => 'post_id',
        :columns      => [['id', 'post_id', ],
                          ['title', 'post_title'],
                          ['author_id', 'author_id'],
                          ['body', 'post_body'],
                          ['type', 'post_type']],
        :associations => {
          :comments => {
            :primary_key  => 'comment_id',
            :columns      => [['id', 'comment_id'],
                              ['post_id', 'post_id'],
                              ['author_id', 'author_id'],
                              ['body', 'comment_body'],
                              ['type', 'comment_type']]
               }          
        }
      }
    }
  }
  Author.find(:all, :include=>{:posts=>:comments}, :order=>"authors.id",
              :finder_sql => QUERY, :column_mapping => MAPPING)

This example shows the recursive nature of associations. It can also be used to render deeper structures
that may reference the same model multiple times, such as the following:

  Firm.find(:all, :include=>{:account=>{:firm=>:account}}, :order=>"companies.id")

(Here, the query and mapping is left to the reader; or, you can look at the unit test which documents
many more possibilities).

There is currently no advanced documentation for this plugin.