Mixing joins and include conflicts


#1

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.


#2

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


#3

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


#4

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


#5

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