Hello
I have the following code which looks for all the comments on a
specified blog post and neighborhood.
comment_activity = PostComment.find(:all, :select =>
“post_comments.*”,
:joins => “INNER JOIN posts
ON posts.id = post_comments.post_id”,
:conditions =>
[“posts.neighborhood_id = ? AND post_comments.created_at >= ?”,
@neighborhood.id, 5.days.ago],
:order =>
“post_comments.created_at DESC”)
It works great and generates the following SQL:
SELECT post_comments.*
FROM post_comments
INNER JOIN posts ON posts.id = post_comments.post_id
WHERE (posts.neighborhood_id = 1 AND post_comments.created_at >=
‘2008-07-19 02:50:54’)
ORDER BY post_comments.created_at DESC
I added next the following code on the post_comment model to try and
fetch the users who wrote each post comment. The post_comments table
has a user_id column that references the user who wrote it.
belongs_to :comment_by, :select => “id, name, lastname”, :foreign_key
=> “user_id”, :class_name => “User”
I added the line “:include => [:comment_by]” to my query to load the
user data like this:
comment_activity = PostComment.find(:all, :include =>
[:comment_by], :select => “post_comments.*”,
:joins => “INNER JOIN posts
ON posts.id = post_comments.post_id”,
:conditions =>
[“posts.neighborhood_id = ? AND post_comments.created_at >= ?”,
@neighborhood.id, 5.days.ago],
:order =>
“post_comments.created_at DESC”)
But then I get the following generated sql query on my log which is
not what I am thriving to do:
SELECT post_comments.id AS t0_r0, post_comments.user_id AS
t0_r1, post_comments.post_id AS t0_r2, post_comments.comment
AS t0_r3, post_comments.created_at AS t0_r4,
post_comments.updated_at AS t0_r5, users.id AS t1_r0,
users.email AS t1_r1, users.crypted_password AS t1_r2,
users.salt AS t1_r3, users.name AS t1_r4, users.lastname
AS t1_r5, users.gender AS t1_r6, users.zipcode AS t1_r7,
users.birthday AS t1_r8, users.remember_token AS t1_r9,
users.remember_token_expires_at AS t1_r10,
users.activation_code AS t1_r11, users.activated_at AS t1_r12,
users.created_at AS t1_r13, users.updated_at AS t1_r14,
users.neighborhood_id AS t1_r15, users.photo_file_name AS
t1_r16, users.photo_content_type AS t1_r17,
users.photo_file_size AS t1_r18, users.status_message AS
t1_r19, users.status_update AS t1_r20 FROM post_comments LEFT
OUTER JOIN users ON users.id = post_comments.user_id INNER JOIN
posts ON posts.id = post_comments.post_id WHERE (posts.neighborhood_id
= 1 AND
post_comments.created_at >= ‘2008-07-19 02:52:10’) ORDER BY
post_comments.created_at DESC
What do I am doing wrong? Any help or advice? Thanks!