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:

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

On 3 Aug 2008, at 09:21, elioncho wrote:

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

On Aug 3, 6:06 am, Frederick C. [email protected]

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

On Aug 3, 6:06 am, Frederick C. [email protected]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs