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 ? -- Posted via http://www.ruby-forum.com/.
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 On 1/22/06, r00by n00by <0539y2fupguaui80@jetable.com> wrote:> > 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 ? > > > > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060123/ccb13819/attachment.html
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? -- Posted via http://www.ruby-forum.com/.
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 r00by n00by wrote:> 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 ?
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. On 1/23/06, Brian V. Hughes <brianvh@alum.dartmouth.org> 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 > > r00by n00by wrote: > > 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 ? > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060123/3bcaa078/attachment-0001.html
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? > > -BrianNot 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 Felkins 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. -- Posted via http://www.ruby-forum.com/.
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? > > -BrianThat''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 -- Posted via http://www.ruby-forum.com/.
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 On 1/23/06, Kelly Dwight Felkins <railsinator@gmail.com> wrote:> > 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. > > > On 1/23/06, Brian V. Hughes <brianvh@alum.dartmouth.org> 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 > > > > r00by n00by wrote: > > > 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 ? > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/9976f5de/attachment.html
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? -- Posted via http://www.ruby-forum.com/.
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
On 1/24/06, r00by n00by <ul1c7x7o60zuqnd@jetable.org>
wrote:>
> 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?
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/f19a9714/attachment.html