2 associations to same table

Rails
1

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

2

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 R. [email protected]
wrote:

       SELECT forum_topics.id,forum_topics.title,reply,lecture,
               FROM forum_messages
       WHERE forum_topics.forum_section_id=#{section}
           :associations=>{
                       'id' => 'last_message_user_id',
                   'id'        => 'first_message_id',
                       }

–
Ryan B.

3

On Dec 20, 2007, at 10:26 AM, Luca R. 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

4

Phillip K. wrote:

On Dec 20, 2007, at 10:26 AM, Luca R. 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
has_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?

5

On Dec 20, 2007, at 4:11 PM, Luca R. 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

6

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.

7

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.

8

At the moment it appears you’re getting more data in more time :slight_smile:

9

Phillip K. wrote:

On Dec 20, 2007, at 4:11 PM, Luca R. 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.

10

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? :slight_smile:

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