Forum: Ruby on Rails Mixing joins and include conflicts

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Elias O. (Guest)
on 2008-10-16 20:57
(Received via mailing list)
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.
Frederick C. (Guest)
on 2008-10-16 21:19
(Received via mailing list)
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
Elias O. (Guest)
on 2008-10-16 21:30
(Received via mailing list)
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 C. <removed_email_address@domain.invalid>
Frederick C. (Guest)
on 2008-10-16 21:38
(Received via mailing list)
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
Elias O. (Guest)
on 2008-10-17 03:04
(Received via mailing list)
Thanks Fred,

I'll look into it. Its good to know I am not alone on this issues.

Elioncho

On Oct 16, 12:37 pm, Frederick C. <removed_email_address@domain.invalid>
This topic is locked and can not be replied to.