# ActiveRecord find **URL:** \ **Category:** Rails\ **Created:** [January 22, 2006, 11:53pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272 "2006-01-22T23:53:25Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 22, 2006, 11:53pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/1 "2006-01-22T23:53:25Z")
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 ? ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 23, 2006, 9:12am UTC](https://www.ruby-forum.com/t/activerecord-find/64272/2 "2006-01-23T09:12:12Z")
Eager associations gets you part of the way. [http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html](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 ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 23, 2006, 6:02pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/3 "2006-01-23T18:02:35Z")
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 ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 23, 2006, 12:20pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/4 "2006-01-23T12:20:18Z")
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? ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 23, 2006, 6:45pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/5 "2006-01-23T18:45:21Z")
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. ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 23, 2006, 8:50pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/6 "2006-01-23T20:50:11Z")
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. ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 23, 2006, 9:14pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/7 "2006-01-23T21:14:46Z")
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 ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 23, 2006, 10:28pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/8 "2006-01-23T22:28:27Z")
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 ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 24, 2006, 6:39am UTC](https://www.ruby-forum.com/t/activerecord-find/64272/9 "2006-01-24T06:39:33Z")
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 ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 24, 2006, 12:33pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/10 "2006-01-24T12:33:40Z")
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? ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 24, 2006, 8:46pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/11 "2006-01-24T20:46:31Z")
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 ---
**Author:** ![r00by\_n00by](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/839c29/32.png) [@r00by\_n00by](https://www.ruby-forum.com/u/r00by_n00by)\ **Post date:** [January 24, 2006, 9:13pm UTC](https://www.ruby-forum.com/t/activerecord-find/64272/12 "2006-01-24T21:13:08Z")
OK, I’ll take your advice and deal with performance issues later. Thanks!