Forum: Ruby on Rails ActiveRecord find

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
r00by n00by (Guest)
on 2006-01-23 00:53
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 ?
Kelly Dwight F. (Guest)
on 2006-01-23 10:12
(Received via mailing list)
Eager associations gets you part of the way.

http://api.rubyonrails.com/classes/ActiveRecord/As...

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
r00by n00by (Guest)
on 2006-01-23 13:20
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?
Brian V. Hughes (Guest)
on 2006-01-23 19:02
(Received via mailing list)
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
Kelly Dwight F. (Guest)
on 2006-01-23 19:45
(Received via mailing list)
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.
r00by n00by (Guest)
on 2006-01-23 21:50
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.
Brian V. Hughes (Guest)
on 2006-01-23 22:14
(Received via mailing list)
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
r00by n00by (Guest)
on 2006-01-23 23:28
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
Kelly Dwight F. (Guest)
on 2006-01-24 07:39
(Received via mailing list)
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
r00by n00by (Guest)
on 2006-01-24 13:33
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?
Kelly Dwight F. (Guest)
on 2006-01-24 21:46
(Received via mailing list)
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
r00by n00by (Guest)
on 2006-01-24 22:13
OK, I'll take your advice and deal with performance issues later.
Thanks!
This topic is locked and can not be replied to.