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.