Suppose I have three tables - authors, posts and topics.
Every post have a topic and every topic has an author, so I have
posts.topic_id and topic.author_id. When the author is guest (not
registered), author_id is nil.
I need to find all the posts (in one query) where every post have all
the information of the topic and the auther, so if p is one of those
posts I could get the name of the author like this:
name = p.topic.author.name
If the author is a guest, then name should be “Guest” (or nil if it’s
more simple).
How do I do that with ActiveRecord find ?
Eager associations gets you part of the way.
http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html
So with one query you could load all of the posts and all of the topics.
Unfortunately you can’t load the associated authors.
posts = Post.find(:all, :include=> :topic)
Do you really need a single query? The combination of eager loading and
the
simple caching may give you sufficient performance, if that is the
issue.
-Kelly
Can you post your associations? It looks like:
author has_many topics
topic belongs_to author
topic has_many posts
post belongs_to topic
Is that correct? And it also looks like it’s possible for a topic to
exist
without an owning author, since authors can be guests. Right?
-Brian
I want the posts ordered by the name of the author and by other
attributes from the authors table.
I could sort it after I get the query result, but it would be a waste,
don’t you think?
Ah, now I understand the challenge. Yes I experienced the exact
situation
recently (different models, of course).
I don’t have the code in front of me but I can point you in the right
direction.
You can add a join clause to your find. If you do this you will get all
of
the attributes from all of the tables in the join. So to limit that you
use
the select option.
Basically, this is one of the cases where the normally helpful ORM
system is
getting in your way.
So you find would be something like this:
posts = Post.find(:all,
:joins=>“JOIN topics ON posts.topic_id=topics.id JOIN authors ON
topics.author_id=authors.id”,
:select=>" [list your posts columns here, comma separated] ",
:order=> “authors.name”)
This will give you your posts in order of author.name.
Brian V. Hughes wrote:
Can you post your associations? It looks like:
author has_many topics
topic belongs_to author
topic has_many posts
post belongs_to topic
Is that correct? And it also looks like it’s possible for a topic to
exist
without an owning author, since authors can be guests. Right?
-Brian
Not exactly. What I am thinking of is:
author has_many topics
topic belongs_to author
topic has_one post
post belongs_to topic
Maybe you don’t understand why a topic has just one post, but it
supposed to be like that. It don’t think it matters. The problem is the
same in both cases.
Kelly Dwight F. wrote:
So you find would be something like this:
posts = Post.find(:all,
:joins=>“JOIN topics ON posts.topic_id=topics.id JOIN authors ON
topics.author_id=authors.id”,
:select=>" [list your posts columns here, comma separated] ",
:order=> “authors.name”)
This will give you your posts in order of author.name.
What do I do if I need the name (not only for sorting)?
I tried to add authors.name to :select , but it was ignored.
r00by n00by wrote:
author has_many topics
topic belongs_to author
topic has_one post
post belongs_to topic
Maybe you don’t understand why a topic has just one post, but it
supposed to be like that. It don’t think it matters. The problem is the
same in both cases.
You’re right, I don’t understand why a topic has only one post, since it
seems
like posts are the things that authors create, it looks like the model
would
make more sense if author had many posts and posts had one topic.
However, I’m not sure that really matters that much, for getting the
data you
want. Given that there’s a 1-to-1 map between topics and posts, getting
all
posts (with their topic) yields the same data as getting all topics
(with their
post). The difference is one is a list of post objects the other is a
list of
topic objects.
The reason this is a good thing, for you, is it will allow you to do a
find like:
topics = Topic.find(:all, :include => [:post, :author], :order =>
‘authors.name’)
Now, what you’ll have is a group of topics, but if you iterate over
topics |t|,
you can simply reference t.post and t.author. And t.author will be nil
when
there is no author for the topic.
Does that give you something you can work with?
-Brian
Brian V. Hughes wrote:
r00by n00by wrote:
author has_many topics
topic belongs_to author
topic has_one post
post belongs_to topic
Maybe you don’t understand why a topic has just one post, but it
supposed to be like that. It don’t think it matters. The problem is the
same in both cases.
You’re right, I don’t understand why a topic has only one post, since it
seems
like posts are the things that authors create, it looks like the model
would
make more sense if author had many posts and posts had one topic.
However, I’m not sure that really matters that much, for getting the
data you
want. Given that there’s a 1-to-1 map between topics and posts, getting
all
posts (with their topic) yields the same data as getting all topics
(with their
post). The difference is one is a list of post objects the other is a
list of
topic objects.
The reason this is a good thing, for you, is it will allow you to do a
find like:
topics = Topic.find(:all, :include => [:post, :author], :order =>
‘authors.name’)
Now, what you’ll have is a group of topics, but if you iterate over
topics |t|,
you can simply reference t.post and t.author. And t.author will be nil
when
there is no author for the topic.
Does that give you something you can work with?
-Brian
That’s an interesting solution. It could be even better than my original
plan (I mean Post.find).
However, what if there is another table to include?
Here is an example: we have 4 tables - songs, albums, artist and
websites. An album belongs to a single artist which may have a website.
Each album has songs (which are all preformed by the same artist, so
there is no artist id in the songs table, only in albums).
If we want to find all the songs with the artists, we do:
albums = Album.find(:all, :include => [:song, :artist] )
but what if we want to get the wesite as well? I didn’t check, but I’m
sure this
albums = Album.find(:all, :include => [:song, :artist, :website] )
won’t work
I would get the posts in the order I want.
Then, if I needed to display I would use the association, like
post.topic.author.name
-Kelly
This means one SQL query for each of the records, unless I’ve already
cached the list of authors, but I don’t know how to do that.
Can you show me please?
I think you may be overly concerned with the queries. Try it before you
optimize it. I’m always tempted to
reduce the number of queries through caching. Then I think about the
best
place to do such caching. Then
I realize it is probably already there. Also caching is a trade off
between
access and memory. Preloading
all authors takes memory. For a small number of authors it may be
insignifcant. For a small number of
authors the lookup will be insignificant also.
Build it the easy way first then deal with performance issues. Hard for
us
engineers to do, but
it is the wise thing to do.
If you need to load all of the authors with a single query, you could do
this:
@authors = Hash.new
Author.find(:all).collect{|a| @authors[a.id]=a}
Now you have all your authors in a hash. Anywhere you have an author_id
just
grab the author with
@authors[author_id]
-Kelly
OK, I’ll take your advice and deal with performance issues later.
Thanks!