I have a table called articles with around 500 rows. Each article belongs_to an author. In the list page, I currently use find_all to get all articles.

However, the view contains the author’s name as a column in the table, so each page load needs 501 queries, which is unacceptable. Can I use the joins parameter for find_all to retrieve the author information at the same time?

I know about the PiggyBackQuery method, but it’s too ugly to use all the time.

——

Best practice solution:

The Agile Web Dev w Rails book, 1st ed, discusses this issue on pg 243. Their solution is to use the :include parameter. eg:

for article in Article.find(:all, :include => :author)
  puts "Article: #{article.title}\n"
  puts "  By: #{article.author.full_name}\n"
end

Note that your db needs to support left outer joins for this to work. MySQL does in current versions.

The downside of the above is that ActiveRecord will pull in all the columns from author, even if you only needed the full_name column. This is really hardly a downside.

You must disambiguate all of your column references though, to be for whatever specific table you need them from.

——

—

I have recently had good results using a database view to tidy away a nasty query with multiple joins so that rails sees it as one simple table. This assumes you are using a database that supports views of course. I’m using the view to effectively create a read-only model (not enforced in any direct way but any attempt to write to it will probably cause problems). The fact it is read only doesn’t matter too much as you can of course still update the individual models that underpin it as per normal.

—

I have had to resort to using __find_by_sql__, because (for my application at least) I do not want to SELECT * when a join is present- rather than all fields from all tables, I want all fields from the primary table with select fields from the other join tables. In your situation (as you probably know) using the joins parameter on __find_all__ would result in you receiving all fields from the authors table when you only want to grab their name for your list. I ended up writing code similar to (adapted for your situation above):

sql =<<SQL

SELECT articles.*, authors.name AS author_name

FROM articles

LEFT JOIN authors ON author_id = authors.id

SQL

@articles = Article.find_by_sql sql

——
A better way to do this:

@author = Author.find_by_name(@params['author'])  #or however you know who the author is
@articles = @author.articles.find_all()

These two queries may not be as SQL-efficient as the one query above, but it is certainly better than 501 queries.

Also, if you know your author.id already, you can also just do this: <pre><code> @articles = Article.find_by_author_id(author.id)

——
You say that PiggyBackQuery is too ugly to use all the time, but why not just wrap it up in your Article class?

class Article
  def self.find_all_with_authors
    # ... use the PiggyBackQuery technique to add an author_name attribute ...
  end
end

class ArticleController

  def list

    @articles = Article.find_all_with_authors

  end

end

——
Sorry, but if I have an existing relationship like in the first example, i am just writing

for article in Article.find :all
@authors << article.author
end

so I dont need any include or any second find.
Can anybodey tell me, where there should be a problem?

—
I think, this whole thing doesn’t get the point.
The real problem lies there, where you want do get many to many relationships.

So things like

articles
articles_authors
authors

and I want to hava all Articles where author is so and so or so.

Okay, that still does not fit the point.

What, wenn I also have
articles_categories

and want to get all articles where author is soandso and category is this and that

a :conditions doesnt do it.

So what I am doing, is to use several techniques to merge these arrays, but its slow and boring.

I could also use SQL, anyone have a better idea?