This project is archived and is in readonly mode.

#1077 ✓ committed
David Stevenson

problems with aliased joins

Reported by David Stevenson | September 19th, 2008 @ 11:12 PM

Named scopes are designed to be chained together in any order/combination. If you join a table in one scope, and join the same table in another scope, it selects the same table twice and fails.

You can alias each join to a different name, but this leads to large number of more-or-less identical joins.

Solution: allow joins to be passed in string-array form:


named_scope :something {:join => ["INNER JOIN x", "INNER JOIN y", ...]}
named_scope :something_else {:join => "INNER JOIN x"}
# calling Model.something.something_else should only join in x once

Build a complete list of joins as an array from the beginning of the scoping process. Carry the array all the way to add_joins! where the list of string-joins is uniqued and finally converted into a single string.

This doesn't solve the aliasing issues with using :join => :assoc AND :join => "INNER JOIN assoc_table...".

-David Stevenson & Joseph Palermo from PivotalLabs

Comments and changes to this ticket

  • Michael Koziarski

    Michael Koziarski September 23rd, 2008 @ 01:11 PM

    • Assigned user set to “Michael Koziarski”

    So silently removing the duplicates is always safe? There aren't any situations where we should be raising or warning the developer?

  • Joseph Palermo

    Joseph Palermo September 23rd, 2008 @ 05:46 PM

    Since the merged includes have to be string identical, it's just as safe or safer to always merge when possible.

  • Michael Koziarski

    Michael Koziarski September 23rd, 2008 @ 07:30 PM

    • Milestone cleared.

    OK, can you format the patch as per the guides:

    http://rails.lighthouseapp.com/p...

  • David Stevenson
  • Repository

    Repository September 24th, 2008 @ 12:35 PM

    • State changed from “new” to “committed”

    (from [487758b3b88a38da3a75900839aea03774904fe1]) Allowed passing arrays-of-strings to :join everywhere. Merge duplicate join strings to avoid table aliasing problems.

    Signed-off-by: Michael Koziarski michael@koziarski.com [#1077 state:committed] http://github.com/rails/rails/co...

  • JensC

    JensC November 1st, 2008 @ 11:19 AM

    • Title changed from “Chaining scopes with duplicate joins causes alias problem” to “problems with aliased joins”

    Hi,

    I have two associations between two tables. One is a one to many and the other is many to may through. To be able to use named_scopes I must have aliased joins to address the right attribute. However if I do, rails avoids to have the two different joins because it thinks its a duplication of the joins.

    Here is an example:

    search members in one or many country

    scope = scope.scoped( :conditions => ["property_country.short IN (?)", country_keys], :joins => 'INNER JOIN countries AS property_country ON property_country.id = memberships.property_country_id' ) if country_keys.size > 0

    my_country

    scope = scope.scoped( :conditions => ["vacation_countries.short = ?", my_country], :joins => "INNER JOIN join_vacation_countries ON (memberships.id = join_vacation_countries.membership_id) INNER JOIN countries AS vacation_countries ON (vacation_countries.id = join_vacation_countries.country_id)" ) unless my_country.blank?

    I guess that is a limitation of the patch above and needs further refinement.

    Thanks Jens

  • Michael Koziarski

    Michael Koziarski November 1st, 2008 @ 11:23 AM

    • State changed from “committed” to “open”

    JensC: Can you provide us with a test case for active record which demonstrates this bug.

    David & Joseph: Any thoughts?

    Moving to block 2.2 final

  • David Stevenson

    David Stevenson November 1st, 2008 @ 07:00 PM

    Koz & Jens,

    I'm afraid I don't see the issue that Jens is trying to show us. The patch that we submitted simply removes two completely-string-identical joins. These two would be removed:

    
    scope = scope.scoped(:joins => "INNER JOIN countries AS country1 ON something")
    scope = scope.scoped(:joins => "INNER JOIN countries AS country1 ON something")
    

    These two would not be removed, as they are not string identical:

    
    scope = scope.scoped(:joins => "INNER JOIN countries AS country1 ON something")
    scope = scope.scoped(:joins => "INNER JOIN countries AS country2 ON something")
    

    It's a very conservative removal. If an association generates a join, and a named scope also includes a string identical join, they will of course be "uniq"d into a single join (which should not have any negative consequences, since one copy is still included). It even does not remove them in this case, when they should be obviously removed:

    
    scope = scope.scoped(:joins => "INNER JOIN countries AS country1 ON something")
    scope = scope.scoped(:joins => "INNER JOIN countries AS country1 ON something INNER JOIN something_else ON whatever")
    

    The "right" way to write the above so that they are uniq'd would be this:

    
    scope = scope.scoped(:joins => "INNER JOIN countries AS country1 ON something")
    scope = scope.scoped(:joins => ["INNER JOIN countries AS country1 ON something", "INNER JOIN something_else ON whatever"])
    

    Can I get some clarification, perhaps a failing test on what the issue is from Jens?

  • Michael Koziarski

    Michael Koziarski November 1st, 2008 @ 07:08 PM

    Neither, it seems it should work to me...

    Jens, can you please give us an example either in the rails tests or a simple stand-alone application?

  • JensC

    JensC November 3rd, 2008 @ 10:55 AM

    Oh hell! After creating an example that should show exactly what doesn't work ... it worked! After some more digging I noticed in my environment.rb that I still had

    RAILS_GEM_VERSION = '2.1.2' unless defined? RAILS_GEM_VERSION

    defined. It works in 2.2.0 RC 1. I am soooo sorry for that :-/. Next time I promise to provide an example before starting a bug report.

    Once again sorry guys anf Thnx a lot. Jens

  • DHH

    DHH November 4th, 2008 @ 05:41 PM

    • State changed from “open” to “invalid”
  • David Stevenson

    David Stevenson November 4th, 2008 @ 05:43 PM

    DHH - Why did you just mark this invalid? Jens said there was no actual bug, and he made a mistake in commenting on this at all?

  • DHH

    DHH November 4th, 2008 @ 05:47 PM

    • State changed from “invalid” to “open”

    Ah, read it wrong. Reopened.

  • Michael Koziarski

    Michael Koziarski November 6th, 2008 @ 06:41 PM

    • State changed from “open” to “committed”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

Tickets have moved to Github

The new ticket tracker is available at https://github.com/rails/rails/issues

Shared Ticket Bins

Referenced by

Pages