thr3ads.net - Rails - 2 associations to same table [Dec 2007]

If this information is useful, please help other people find it:
Share via:

Luca Roma

2007-Dec-20 16:26 UTC

2 associations to same table

I have a problem with this find.

I want include the association of forum_messages for last message and
for first_message.

is possible set 2 associations to 1 table with dinstict roles?

 ForumTopic.find(:all,:include=>[:forum_messages,:users],
           :finder_sql => "
            SELECT forum_topics.id,forum_topics.title,reply,lecture,
              last_message_id,last_user.user_id as
last_message_user_id,last_user.name as last_message_user_name,
              last_message.created_at,last_user.image as
last_message_user_image,last_user.color as last_message_user_color,
              first_user.name as author_name,first_user.id as
author_id,first_user.image as author_image, first_user.color as
author_color
            FROM forum_topics
            RIGHT JOIN (SELECT MAX(forum_messages.id) as
last_message_id,forum_topic_id,user_id as first_user_id
                    FROM forum_messages
                    GROUP BY forum_topic_id)as last on
last.forum_topic_id=forum_topics.id
            LEFT JOIN forum_messaggi as last_message on
last_message.id=last_message_id
            LEFT JOIN users as first_user on first_user_id=first_user.id
            LEFT JOIN users as last_user on
last_message.utente=last_user.id
            LEFT JOIN (SELECT count(*) as reply,forum_topic_id FROM
forum_messages GROUP BY forum_topic_id) as reply ON
reply.forum_topic_id=forum_topics.id
            WHERE forum_topics.forum_section_id=#{section}
              ORDER BY last_message.created_at DESC LIMIT
#{offset},#{length}",
           :column_mapping => {
               :primary_key => ''id'',
               :columns => {
                    ''id''  => ''id'',
                    ''title'' => ''title'',
                    ''reply'' => ''reply'',
                    ''lecture'' =>
''lecture'',
                },
                :associations=>{
                  :forum_messages => {
                    :primary_key => ''last_message_id'',
                    :columns => {
                      ''id''        =>
''last_message_id'',
                      ''created_at'' =>
''created_at''
                    },
                    :associations=>{
                        :users => {
                          :primary_key =>
''last_message_user_id'',
                          :columns => {
                            ''id'' =>
''last_message_user_id'',
                            ''name'' =>
''last_message_user_name'',
                            ''image'' =>
''last_message_user_image'',
                            ''color'' =>
''last_message_user_color''
                          }
                        }
                    }
                  },
                    :forum_messages => {
                      :primary_key => ''first_message_id'',
                      :columns => {
                        ''id''        =>
''first_message_id'',
                        ''created_at'' =>
''created_at''
                      },
                      :associations=>{
                          :users => {
                            :primary_key =>
''first_message_user_id'',
                            :columns => {
                              ''id'' =>
''first_message_user_id'',
                              ''name'' =>
''first_message_user_name'',
                              ''image'' =>
''first_message_user_image'',
                              ''color'' =>
''first_message_user_color''
                            }
                          }
                      }
                  }
                }
           })

Thanks
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ryan Bigg

2007-Dec-20 20:02 UTC

head link

Re: 2 associations to same table

Um. Wow.

What are your table names and the associations defined between the models? A
long SQL statement is not necessarily the answer to your problems.

On Dec 21, 2007 2:56 AM, Luca Roma
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
> I have a problem with this find.
>
> I want include the association of forum_messages for last message and
> for first_message.
>
> is possible set 2 associations to 1 table with dinstict roles?
>
>  ForumTopic.find(:all,:include=>[:forum_messages,:users],
>           :finder_sql => "
>            SELECT forum_topics.id,forum_topics.title,reply,lecture,
>              last_message_id,last_user.user_id as
> last_message_user_id,last_user.name as last_message_user_name,
>              last_message.created_at,last_user.image as
> last_message_user_image,last_user.color as last_message_user_color,
>              first_user.name as author_name,first_user.id as
> author_id,first_user.image as author_image, first_user.color as
> author_color
>            FROM forum_topics
>            RIGHT JOIN (SELECT MAX(forum_messages.id) as
> last_message_id,forum_topic_id,user_id as first_user_id
>                    FROM forum_messages
>                    GROUP BY forum_topic_id)as last on
> last.forum_topic_id=forum_topics.id
>            LEFT JOIN forum_messaggi as last_message on
> last_message.id=last_message_id
>            LEFT JOIN users as first_user on first_user_id=first_user.id
>            LEFT JOIN users as last_user on
> last_message.utente=last_user.id
>            LEFT JOIN (SELECT count(*) as reply,forum_topic_id FROM
> forum_messages GROUP BY forum_topic_id) as reply ON
> reply.forum_topic_id=forum_topics.id
>            WHERE forum_topics.forum_section_id=#{section}
>              ORDER BY last_message.created_at DESC LIMIT
> #{offset},#{length}",
>           :column_mapping => {
>               :primary_key => ''id'',
>               :columns => {
>                    ''id''  => ''id'',
>                    ''title'' =>
''title'',
>                    ''reply'' =>
''reply'',
>                    ''lecture'' =>
''lecture'',
>                },
>                :associations=>{
>                  :forum_messages => {
>                    :primary_key => ''last_message_id'',
>                    :columns => {
>                      ''id''        =>
''last_message_id'',
>                      ''created_at'' =>
''created_at''
>                    },
>                    :associations=>{
>                        :users => {
>                          :primary_key =>
''last_message_user_id'',
>                          :columns => {
>                            ''id'' =>
''last_message_user_id'',
>                            ''name'' =>
''last_message_user_name'',
>                            ''image'' =>
''last_message_user_image'',
>                            ''color'' =>
''last_message_user_color''
>                          }
>                        }
>                    }
>                  },
>                    :forum_messages => {
>                      :primary_key =>
''first_message_id'',
>                      :columns => {
>                        ''id''        =>
''first_message_id'',
>                        ''created_at'' =>
''created_at''
>                      },
>                      :associations=>{
>                          :users => {
>                            :primary_key =>
''first_message_user_id'',
>                            :columns => {
>                              ''id'' =>
''first_message_user_id'',
>                              ''name'' =>
''first_message_user_name'',
>                              ''image'' =>
''first_message_user_image'',
>                              ''color'' =>
''first_message_user_color''
>                            }
>                          }
>                      }
>                  }
>                }
>           })
>
> Thanks
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>

-- 
Ryan Bigg
http://www.frozenplague.net

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Phillip Koebbe

2007-Dec-20 20:49 UTC

head link

Re: 2 associations to same table

On Dec 20, 2007, at 10:26 AM, Luca Roma wrote:
>
> I have a problem with this find.
>
> I want include the association of forum_messages for last message and
> for first_message.
>
If I understand correctly, this might work for you:

class User
	has_many :forum_messages
	has_one :most_recent_forum_message, :class_name =>  
''ForumMessage'', :order => ''created_at
desc''
	has_one :first_forum_message, :class_name =>
''ForumMessage'', :order
=> ''created_at''
end

then you would use it this way:

u = User.find(:first)
u.most_recent_forum_message.subject (or whatever)
u.first_forum_message.created_at (or whatever)

Disclaimer: I''ve never used this code.  I actually used AWDWR 2 page  
334 as a basis and made some changes as necessary.  It *should* work,  
though.

Peace,
Phillip


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Luca Roma

2007-Dec-20 22:11 UTC

head link

Re: 2 associations to same table

Phillip Koebbe wrote:> On Dec 20, 2007, at 10:26 AM, Luca Roma wrote:
> 
>>
>> I have a problem with this find.
>>
>> I want include the association of forum_messages for last message and
>> for first_message.
>>
> 
> If I understand correctly, this might work for you:
> 
> class User
>   has_many :forum_messages
>   has_one :most_recent_forum_message, :class_name =>
> ''ForumMessage'', :order => ''created_at
desc''
>   has_one :first_forum_message, :class_name =>
''ForumMessage'', :order
> => ''created_at''
> end
> 
> then you would use it this way:
> 
> u = User.find(:first)
> u.most_recent_forum_message.subject (or whatever)
> u.first_forum_message.created_at (or whatever)
> 
> Disclaimer: I''ve never used this code.  I actually used AWDWR 2
page
> 334 as a basis and made some changes as necessary.  It *should* work,
> though.
> 
> Peace,
> Philliphas_one :most_recent_forum_message, :class_name
=>''ForumMessage'', :order
=> ''created_at desc''
has_one :most_old_forum_message, :class_name
=>''ForumMessage'', :order =>
''created_at''

If i use this:
test=ForumTopic.find(:all,:include=>[{:forum_messages => :user}],
          :conditions=>"forum_section_id=#{section}",:limit =>
length,
:offset=>offset*length)

for i in test
  i.first_forum_message.user.id
end

the number of query executed are high.


Else if i use this:
test=ForumTopic.find(:all,:include=>[{:forum_messages => 
:user},:most_recent_forum_message,:most_old_forum_message],
          :conditions=>"forum_section_id=#{section}",:limit =>
length,
:offset=>offset*length)

for i in test
  i.first_forum_message.user.id
end

The number of query executed is one but the time of executed is very 
high (10 sec)

What is the solution for my problem?
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Phillip Koebbe

2007-Dec-20 22:42 UTC

head link

Re: 2 associations to same table

On Dec 20, 2007, at 4:11 PM, Luca Roma wrote:>
> What is the solution for my problem?
I think I see what you''re wanting to do.  On a particular forum, you  
want to display the poster''s first message (date or something) and  
most recent message (date or something) *for every topic*.  My advice  
is to use something like a counter cache column.  For each user,  
store either the dates of the first and most recent post or store the  
ids so you can easily get to them.  With every post the user makes,  
update the cache column.  If you try to find that information  
dynamically, you''re going to have continually slower and slower  
performance as your topic count increases.

Peace,
Phillip

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ryan Bigg

2007-Dec-20 22:43 UTC

head link

Re: 2 associations to same table

Perhaps you could use a less complex layout?

class Section < ActiveRecord::Base
has_many :forums
end

class Forum < ActiveRecord::Base
belongs_to :section
has_many :topics
has_many :posts, :through => :topics
end

class Topic < ActiveRecord::Base
belongs_to :forum
has_many :posts
end

class Post < ActiveRecord::Base
belongs_to :topic
belongs_to :user
end

To get the most recent forum post for a user it''s simply:

@user = User.find(id)
@user.posts.last

To get the most recent message for a topic:

@topic.posts.last

To get the most recent post for a forum:

@forum.posts.last

I don''t see why you''re doing all this ugly SQL all the time.
That''s why your
statements have a 10 sec exec.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Luca Roma

2007-Dec-20 22:51 UTC

head link

Re: 2 associations to same table

Phillip Koebbe wrote:> On Dec 20, 2007, at 4:11 PM, Luca Roma wrote:
>>
>> What is the solution for my problem?
> 
> I think I see what you''re wanting to do.  On a particular forum,
you
> want to display the poster''s first message (date or something) and
> most recent message (date or something) *for every topic*.  My advice
> is to use something like a counter cache column.  For each user,
> store either the dates of the first and most recent post or store the
> ids so you can easily get to them.  With every post the user makes,
> update the cache column.  If you try to find that information
> dynamically, you''re going to have continually slower and slower
> performance as your topic count increases.
> 
> Peace,
> Phillip
not If i execute this query:

 SELECT forum_topics.id,forum_topics.title,reply,lecture,
              last_message_id,last_user.user_id as
last_message_user_id,last_user.name as last_message_user_name,
              last_message.created_at,last_user.image as
last_message_user_image,last_user.color as last_message_user_color,
              first_user.name as author_name,first_user.id as
author_id,first_user.image as author_image, first_user.color as
author_color
            FROM forum_topics
            RIGHT JOIN (SELECT MAX(forum_messages.id) as
last_message_id,forum_topic_id,user_id as first_user_id
                    FROM forum_messages
                    GROUP BY forum_topic_id)as last on
last.forum_topic_id=forum_topics.id
            LEFT JOIN forum_messaggi as last_message on
last_message.id=last_message_id
            LEFT JOIN users as first_user on first_user_id=first_user.id
            LEFT JOIN users as last_user on
last_message.utente=last_user.id
            LEFT JOIN (SELECT count(*) as reply,forum_topic_id FROM
forum_messages GROUP BY forum_topic_id) as reply ON
reply.forum_topic_id=forum_topics.id
            WHERE forum_topics.forum_section_id=#{section}
              ORDER BY last_message.created_at DESC LIMIT
#{offset},#{length}

it Is fast and is one.
But i dont know how get for example the attribute "reply" that not 
exists in forum_topics table (it is the number of messages of topic).
Can you help me to get this parameter ?
Thanks.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Luca Roma

2007-Dec-20 22:53 UTC

head link

Re: 2 associations to same table

> I don''t see why you''re doing all this ugly SQL all the
time.
Beacuse with sql i can get more data in less time.


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ryan Bigg

2007-Dec-20 22:59 UTC

head link

Re: 2 associations to same table

At the moment it appears you''re getting more data in more time :)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Mark Wilden

2007-Dec-21 02:33 UTC

head link

Re: 2 associations to same table

Luca, it looks like ActiveRecord#find_by_sql will let you access
"reply."

Here''s a short excerpt from the doc:

"If you call a complicated SQL query which spans multiple tables the
columns specified by the SELECT will be attributes of the model,
whether or not they are columns of the corresponding table."

Isn''t Rails great? :)

Obviously, it would be desirable to not use SQL for this, but you have
performance problems. I would advise against doing your own caching of
the latest and oldest post until you find you have no choice. It''s
more Rails-y, but it''s also more code to go wrong.

///ark
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - Dec 2007 - 2 associations to same table

2 associations to same table

Re: 2 associations to same table

Re: 2 associations to same table

Re: 2 associations to same table

Re: 2 associations to same table

Re: 2 associations to same table

Re: 2 associations to same table

Re: 2 associations to same table

Re: 2 associations to same table

Re: 2 associations to same table