This project is archived and is in readonly mode.

#302 ✓resolved
Ryan Bates

Association count with include doesn't alias join table

Reported by Ryan Bates | June 2nd, 2008 @ 09:25 PM

Let's say we have a has_many :through association:

class Author < ActiveRecord::Base
  has_many :authorships
  has_many :books, :through => :authorships
end

If we are performing a find on books we have the ability to include authorships:

author.books.find(:all, :include => :authorships, :conditions => ["authorships.primary = ?", true])

This works without error. However, if we try to do the same thing for a count:

author.books.count(:include => :authorships, :conditions => ["authorships.primary = ?", true])

We get an error (at least on mysql and sqlite) because it doesn't like it that we are including the authorships table even though it is already included in the join. This leads to an error like this:

ActiveRecord::StatementInvalid: Mysql::Error: Not unique table/alias: 'authorships'

The find call is smart enough to alias the join table with another name, but the count call isn't. The attachment includes a failing test to duplicate this.

Now you may be wondering why add the redundant :include option. In this simple example it's not really necessary, but in more complex examples it can be. Either way I think the count join should behave like the find join.

Seeing this change in behavior makes me think there's a way to DRY up how joins are handled between find and count.

Comments and changes to this ticket

  • Bernardo Padua

    Bernardo Padua June 7th, 2008 @ 06:11 PM

    IMO there are lots of discrepancies between count and find, this other one bugged be a lot: #348. They should be DRYed up.

  • Edvin Aghanian

    Edvin Aghanian July 16th, 2008 @ 02:06 AM

    • Tag set to activerecord, assertions, has_many, joins, tested

    I've also run into this problem. I'm surprised this is not higher priority since the use of collections, etc, calls on count and immediately produces an error.

    To work around this, I have simply been logging the object, which seems to allow the object set to be used as a collection in the view without calling 'count'.

  • Jeremy Kemper

    Jeremy Kemper July 16th, 2008 @ 07:23 AM

    • Milestone set to 2.1.1
    • State changed from “new” to “open”
    • Assigned user set to “Jeremy Kemper”
  • mech

    mech August 22nd, 2008 @ 02:08 PM

    This problem also occur at will_paginate when it uses count. For example:

    
    @interviews = @job_posting.interviews.paginate(:include => :job_application)
    

    where the JobPosting class is as follow:

    
    class JobPosting < AR
      has_many :job_applications
      has_many :interviews, :through => :job_applications
    end
    
  • Tarmo Tänav

    Tarmo Tänav August 26th, 2008 @ 05:04 PM

    Possible fix for this (at least seems to satisfy the test and does actually cause the table to be aliased). Included the test from Ryan's patch as it didn't apply cleanly.

    The basic problem was that JoinDependency was only aware of options[:joins] and not the tables that were joined throug scope(:find, :joins) so it didn't know the included association had to be aliased, my fix is simply to generate the full joins string earlier and pass that to JoinDependency. Tests pass, but in reality only around 15 tests hit that code branch and only two actually contain any joins.

  • Repository

    Repository August 28th, 2008 @ 07:33 AM

    • State changed from “open” to “resolved”
    • Tag changed from activerecord, assertions, has_many, joins, tested to activerecord, assertions, has_many, joins, patch, tested

    (from [0ed29df6fa704349fd0af0d9521581d6a8eb109c]) Alias included associations if needed when doing a count

    [#302 state:resolved]

    Signed-off-by: Jeremy Kemper jeremy@bitsweat.net http://github.com/rails/rails/co...

  • James Le Cuirot

    James Le Cuirot September 16th, 2010 @ 04:19 PM

    • Milestone cleared.
    • Assigned user cleared.
    • Importance changed from “” to “”

    I know this ticket is really old but could you guys please take a look at ticket #3339 ? I have come up against a situation where tables are still being joined twice and a stronger solution is required. For count, it could mean removing this existing solution. I'd appreciate your comments.

  • Jeff Kreeftmeijer

    Jeff Kreeftmeijer October 19th, 2010 @ 07:09 AM

    • Tag cleared.

    Automatic cleanup of spam.

Create your profile

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

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Referenced by

Pages