Hi I have the following query which looks for events which are private
and public. I want to show first till what point the development log
shows the query as it should be:
[code=] @events = Event.paginate(:all, :include =>
[:created_by, :event_category],
:select => “DISTINCT events.id,
events.start_date, events.title, events.place, events.user_id,
events.event_category_id”,
:joins => “INNER JOIN attendances ON
attendances.event_id = events.id”,
:conditions => "(events.neighborhood_id =
- AND ((events.private = 0) OR (events.private = 1))",
:order => “events.created_at DESC”, :page
=> params[:page], :per_page => 10)[/code]
The development log shows the following which is ok:
SELECT DISTINCT events.id, events.start_date, events.title,
events.place, events.user_id, events.event_category_id FROM `events`
INNER JOIN attendances ON attendances.event_id = events.id WHERE
((events.neighborhood_id = 1) AND ((events.private = 0) OR
(events.private = 1))) ORDER BY events.created_at DESC
But when I add the next line on the conditions : “AND
attendances.user_id = 1”. Calling the attendances table seems to ruin
it all, generating something like this, which is not only totally
wrong but also fetches for the wrong data.
SELECT `events`.`id` AS t0_r0, `events`.`start_date` AS t0_r1,
`events`.`title` AS t0_r2, `events`.`place` AS t0_r3,
`events`.`description` AS t0_r4, `events`.`user_id` AS t0_r5,
`events`.`event_category_id` AS t0_r6, `events`.`created_at` AS t0_r7,
`events`.`updated_at` AS t0_r8, `events`.`neighborhood_id` AS t0_r9,
`events`.`end_date` AS t0_r10, `events`.`private` AS t0_r11,
`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, `event_categories`.`id` AS
t2_r0, `event_categories`.`name` AS t2_r1 FROM `events` LEFT OUTER
JOIN `users` ON `users`.id = `events`.user_id LEFT OUTER JOIN
`event_categories` ON `event_categories`.id =
`events`.event_category_id INNER JOIN attendances ON
attendances.event_id = events.id WHERE ((events.neighborhood_id = 1)
AND ((events.private = 0) OR (events.private = 1 AND
attendances.user_id = 1))) ORDER BY events.created_at DESC
I next tested the query without the ":include =>
[:created_by, :event_category] and it worked right and fetched the
right rows (without the queries on the include of course).
…code of the includes on the event model…nothing rare
belongs_to :created_by, :select => "id, name, lastname,
neighborhood_id", :foreign_key => "user_id", :class_name => "User"
belongs_to :event_category
So I am wondering what is happening on Rails internally? What’s going
wrong? What’s the issue? It seems that the includes make some kind of
conflict with the conditions on the query if I have one of them that
asks for some value on the joined table. Anyone has any ideas. This
has been bugging me for a long time. Thanks a lot!
Elioncho