I have found that Rails doesn''t works well when mixing joins and
includes. For example I have the following models: Event, Attendance
and User.
class Event < ActiveRecord::Base
# User that creates the event
belongs_to :created_by,
:select => "id, name, lastname",
:foreign_key => "user_id",
:class_name => "User"
has_many :attendances
has_many :users, :through => :attendances
############################################
class Attendance < ActiveRecord::Base
belongs_to :event
belongs_to :user
############################################
class User < ActiveRecord::Base
has_many :attendances
has_many :events, :through => :attendances
The events table has a boolean attribute called private (1 if event is
private, 0 instead). So basically let''s imagine we want to fetch all
the events I can attend, that is, all the events that are public and
those that are private and I''ve been invited.
The query should be like this:
---
Event.find(:all,
:include => [:created_by],
:joins => "LEFT OUTER JOIN attendances ON
attendances.event_id = events.id",
:conditions => ["((events.private = 0) OR
(events.private = 1 AND
attendances.user_id = ?))",
current_user],
:order => "events.start_date DESC")
---
From this query I should expect something like this on my development
log:
---
SELECT events.*
FROM events
LEFT OUTER JOIN attendances ON attendances.event_id = events.id
WHERE ((events.private = 0) OR
(events.private = 1 AND
attendances.user_id = 1))
ORDER BY events.start_date DESC
SELECT id, name, lastname FROM users WHERE id IN (3,4,7)
---
But this is what really happens. Here''s the real query generated by
rails on my development log:
---
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`.`photo_file_name` AS t1_r15,
`users`.`photo_content_type` AS t1_r16, `users`.`photo_file_size` AS
t1_r17, `users`.`status_message` AS t1_r18, `users`.`status_update` AS
t1_r19, `users`.`state` AS t1_r20 FROM `events` LEFT OUTER JOIN
`users` ON `users`.id = `events`.user_id LEFT OUTER JOIN attendances
ON attendances.event_id = events.id WHERE (((events.private = 0) OR
(events.private = 1 AND attendances.user_id = 1))) ORDER BY
events.start_date DESC
---
As you can see the query sort of works, but not in the way it should.
A lot of aliases are created, the include is treated as a LEFT OUTER
JOIN and more columns are selected than the ones I specify. So well,
here I leave it and hope someone has noticed this too so we can work
on it.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 16 Oct 2008, at 17:56, elioncho wrote:> > > As you can see the query sort of works, but not in the way it should. > A lot of aliases are created, the include is treated as a LEFT OUTER > JOIN and more columns are selected than the ones I specify. So well, > here I leave it and hope someone has noticed this too so we can work > on it.That''s how include works when it thinks you are referencing included tables. unfortunately it doesn''t scan the joins clause and so it thinks its needs to use the joins version of :include. Fred> > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Are there any plans to solve this issue? How can anyone work around this problem? Imagine I want to include also the pictures related to the events, event_category etc...What approach should someone take? The query generated is far from what it should be. Thanks, Elioncho On Oct 16, 12:18 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 16 Oct 2008, at 17:56, elioncho wrote: > > > As you can see the query sort of works, but not in the way it should. > > A lot of aliases are created, the include is treated as a LEFT OUTER > > JOIN and more columns are selected than the ones I specify. So well, > > here I leave it and hope someone has noticed this too so we can work > > on it. > > That''s how include works when it thinks you are referencing included > tables. unfortunately it doesn''t scan the joins clause and so it > thinks its needs to use the joins version of :include. > > Fred > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 16 Oct 2008, at 18:30, elioncho wrote:> > Are there any plans to solve this issue? How can anyone work around > this problem? Imagine I want to include also the pictures related to > the events, event_category etc...What approach should someone take? > The query generated is far from what it should be. Thanks,The key thing is that it is hard to parse the joins fragment. In terms of plans to fix it, I''m sure a patch fixing this would be welcomed. It may be more practical in your case to do this in two goes, ie locate the relevant events and then do Event.find some_list_of_ids, :include => ... ANother thing I''ve been playing around with is the ability to do events = Event.find(:joins => ...) events.load :created_by (I''ve got a highly experimental plugin that implements this: http://github.com/fcheung/ar_result_set/tree/master i haven''t had time to do much with it recently) Fred> > > Elioncho > > On Oct 16, 12:18 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> On 16 Oct 2008, at 17:56, elioncho wrote: >> >>> As you can see the query sort of works, but not in the way it >>> should. >>> A lot of aliases are created, the include is treated as a LEFT OUTER >>> JOIN and more columns are selected than the ones I specify. So well, >>> here I leave it and hope someone has noticed this too so we can work >>> on it. >> >> That''s how include works when it thinks you are referencing included >> tables. unfortunately it doesn''t scan the joins clause and so it >> thinks its needs to use the joins version of :include. >> >> Fred >> >> > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---