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 = 1) 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:
[code=]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[/code] 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.
[code=]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 [/code]
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 [code] belongs_to :created_by, :select => "id, name, lastname, neighborhood_id", :foreign_key => "user_id", :class_name => "User" belongs_to :event_category[/code]
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