# Mixing joins and include conflicts

**URL:** <https://www.ruby-forum.com/t/mixing-joins-and-include-conflicts/151091>\
**Category:** Rails\
**Created:** [October 16, 2008, 6:57pm UTC](https://www.ruby-forum.com/t/mixing-joins-and-include-conflicts/151091 "2008-10-16T18:57:57Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![elioncho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ed8c4c/32.png) [@elioncho](https://www.ruby-forum.com/u/elioncho)\
**Post date:** [October 16, 2008, 6:57pm UTC](https://www.ruby-forum.com/t/mixing-joins-and-include-conflicts/151091/1 "2008-10-16T18:57:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![elioncho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ed8c4c/32.png) [@elioncho](https://www.ruby-forum.com/u/elioncho)\
**Post date:** [October 16, 2008, 7:19pm UTC](https://www.ruby-forum.com/t/mixing-joins-and-include-conflicts/151091/2 "2008-10-16T19:19:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![elioncho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ed8c4c/32.png) [@elioncho](https://www.ruby-forum.com/u/elioncho)\
**Post date:** [October 16, 2008, 7:30pm UTC](https://www.ruby-forum.com/t/mixing-joins-and-include-conflicts/151091/3 "2008-10-16T19:30:53Z")

</div>

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](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![elioncho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ed8c4c/32.png) [@elioncho](https://www.ruby-forum.com/u/elioncho)\
**Post date:** [October 16, 2008, 7:38pm UTC](https://www.ruby-forum.com/t/mixing-joins-and-include-conflicts/151091/4 "2008-10-16T19:38:32Z")

</div>

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:

> **[GitHub - fcheung/ar\_result\_set: TODO...](https://github.com/fcheung/ar_result_set/tree/master)**
>
> master

i haven’t had time to do much with it recently)

Fred

---

<div class="post-metadata">

**Author:** ![elioncho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/ed8c4c/32.png) [@elioncho](https://www.ruby-forum.com/u/elioncho)\
**Post date:** [October 17, 2008, 1:04am UTC](https://www.ruby-forum.com/t/mixing-joins-and-include-conflicts/151091/5 "2008-10-17T01:04:42Z")

</div>

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](mailto:removed_email_address@domain.invalid)