Weird query on development log

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

[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.

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!

Long version: http://www.spacevatican.org/2008/4/29/include-and-conditions
Short version: If you reference a table other than the 'main' table in
a query, the rails assumes you need to old style of :include (which
has rather dubious semantics when combined with conditions) because
rails assumes that the table you're referencing is a table that would
be joined by :include (which it isn't in this case).

Fred

Thanks for that!

I decided to do the query on sql directly but handle some things the
way its specified on the article you told me to read. So then I did
this

    @events = Event.paginate_by_sql("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 AND attendances.user_id = 1))
                                     ORDER BY events.created_at
DESC", :page => params[:page], :per_page => 10)

    User.find :all, :select => "users.id as users_id, users.name as
users_name, users.lastname, users.neighborhood_id",
                    :conditions => ["id in (?)",
@events.collect(&:user_id)]

    EventCategory.find :all, :select =>
"event_categories.name", :conditions => ["id in (?)",
@events.collect(&:event_category_id)]

    Having 2 more queries to find the users and event_category names
instead of only on query with all the joined tables. I am still
figuring out how to ask for a specificially value of user or
event_category through the @events collection.

Thanks a lot,

Elioncho

Thanks for that!

I decided to do the query on sql directly but handle some things the
way its specified on the article you told me to read. So then I did
this

    @events = Event.paginate_by_sql("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 AND attendances.user_id = 1))
                                     ORDER BY events.created_at
DESC", :page => params[:page], :per_page => 10)

    User.find :all, :select => "users.id as users_id, users.name as
users_name, users.lastname, users.neighborhood_id",
                    :conditions => ["id in (?)",
@events.collect(&:user_id)]

    EventCategory.find :all, :select =>
"event_categories.name", :conditions => ["id in (?)",
@events.collect(&:event_category_id)]

    Having 2 more queries to find the users and event_category names
instead of only on query with all the joined tables. I am still
figuring out how to ask for a specificially value of user or
event_category through the @events collection.

Thanks a lot,

Elioncho