#15 ✓resolved
Prometheus

Not unique table/alias: 'table_name'

Reported by Prometheus | October 22nd, 2008 @ 04:04 PM

My domain model seems to be creating a query generate by my order_by_link that causes an error with mySQL. The generated query is: SELECT count(DISTINCT matters.id) AS count_id FROM matters LEFT OUTER JOIN contacts ON contacts.id = matters.client_id INNER JOIN groups ON ( matters.group_id = groups.id ) INNER JOIN user_group_memberships ON ( groups.id = user_group_memberships.group_id ) LEFT OUTER JOIN contacts ON contacts.id = matters.client_id WHERE (user_group_memberships.user_id = 506897951 )

If I change the query manually to

SELECT count(DISTINCT matters.id) AS count_id FROM matters LEFT OUTER JOIN contacts AS c1 ON c1.id = matters.client_id INNER JOIN groups ON ( matters.group_id = groups.id ) INNER JOIN user_group_memberships ON ( groups.id = user_group_memberships.group_id ) LEFT OUTER JOIN contacts ON contacts.id = matters.client_id WHERE (user_group_memberships.user_id = 506897951 )

The query seems to run fine. It looks like the fact that contacts is being used twice in the same query.

Is there some way I can work around this issue - it's the only problem I'm having with an otherwise fantastic plugin!

Thanks

Comments and changes to this ticket

  • Ben Johnson

    Ben Johnson October 23rd, 2008 @ 11:35 AM

    • Assigned user set to “Ben Johnson”
    • State changed from “new” to “open”

    Thanks for letting me know about this. Can you give me more information on how you are using searchgasm so I can try to recreate the problem and fix it. Searchgasm does some "magic" to ensure multiple joins are not added. The big problem is when :joins and :include conflict. As AR will try to add a join for both. Searchgasm fixes this, but for some reason your has multiple.

    Thanks.

  • Prometheus

    Prometheus October 23rd, 2008 @ 12:26 PM

    I'm using searchgasm using relationships that are described here: http://pastie.org/299010.

    >

    What I'm calling on for the client is basically current_user.matters.client.last_name.

    Let me know if that clarifies things or if I can provide anything else...

  • Ben Johnson

    Ben Johnson October 23rd, 2008 @ 12:37 PM

    I don't see contacts anywhere in your code, which is causing the problem. But I have a feeling I know what the problem is. But can you give me more details. Where is contacts coming from?

    When it comes to a library like ActiveRecord there is a line you have to draw between flexibility and ease of use. I think ActiveRecord went a little too far on flexibility because they let you do all of this chaining of scopes and then don't do anything to make sure they don't clash. Even though it is perfectly fine for them to clash. I did my best to take care of this in searchgasm, but I think there are some other places where they add in joins, especially when setting up relationships.

  • Prometheus

    Prometheus October 23rd, 2008 @ 12:50 PM

    Sorry, I forgot to mention that I changed the name of Contact to Client in the paste I made to remove irrelevant details. So Contact = Client in the original error message.

    On Thu, Oct 23, 2008 at 10:37 AM, Lighthouse support@lighthouseapp.comwrote:

  • Ben Johnson

    Ben Johnson October 23rd, 2008 @ 12:52 PM

    Well the problem is that you include clients in the has_many :matters relationship. Which is fine, you should be able to do this. But activerecord adds a join outside of the add_joins method. I'll see if I can track down where it does this and added a check.

  • Prometheus

    Prometheus October 23rd, 2008 @ 01:30 PM

    Thanks. I included :clients because it was causing me some n+1 headaches. I tried diving into the ActiveRecord internals myself, but it was honestly more than I could handle. Thanks for your efforts here - searchgasm is great!

    On Thu, Oct 23, 2008 at 10:52 AM, Lighthouse support@lighthouseapp.comwrote:

  • Ben Johnson

    Ben Johnson October 23rd, 2008 @ 01:34 PM

    Yes, activerecord is a beast. I'll look into it. Including clients is fine and what you should do, ActiveRecord just doesn't handle this properly.

  • Ben Johnson

    Ben Johnson November 6th, 2008 @ 03:33 AM

    • State changed from “open” to “hold”
  • Prometheus

    Prometheus November 24th, 2008 @ 11:53 AM

    Hi Ben,

    Does "hold" mean that this isn't likely to be addressed anytime soon?

    Cheers, Jack

  • Ben Johnson

    Ben Johnson November 24th, 2008 @ 12:49 PM

    Can you give me more information, some of this doesn't make sense. Like:

    current_users.matters.client.last_name

    Technically that shouldn't work because you are calling client on a collection of matters.

    What I really need is a simple way to reproduce this problem so I can reproduce it in my tests. Maybe you can come up with an example outside of your application that is a little simpler? I can not reproduce this problem. Once I can reproduce it, I can fix it.

    Thanks.

  • Stuart Johnston

    Stuart Johnston December 10th, 2008 @ 08:02 AM

    • Tag set to include, joins

    I've run into this problem to.

    I have in my controller

    
    @search = @activity.risks.new_search(params[:search])
    @search.include = [:category]
    @risks, @risks_count = @search.all, @search.count
    

    and in my view

    
    order_by_link :category => :name
    

    The models go something like this

    • Activity (has_many) Risks
    • Risks (belong_to) Category

    Which when clicked generates the above error.

  • Ben Johnson

    Ben Johnson December 10th, 2008 @ 02:12 PM

    I would love to solve this problem, I can't duplicate it though. I will try some more, but I really need to know the relationship structure and db schema.

  • Stuart Johnston

    Stuart Johnston December 12th, 2008 @ 11:59 AM

    Found the problem and the solution.

    On edge Rails this commit - http://www.github.com/rails/rail... - changed a couple of lines in /activerecord/lib/active_record/calculations.rb

    Consequently when the following is executed no sql is passed to SearchLogic's add_joins! method. Since it has nothing to compare with it adds all the joins for ordering.

    
    joins = ""
    add_joins!(joins, options[:joins], scope)
    

    However, later in calculations.rb the joins from :include are added back in causing the problem.

    To fix I changed calculations.rb to:

    
    joins = ""
              
    if merged_includes.any?
                join_dependency = ActiveRecord::Associations::ClassMethods::JoinDependency.new(self, merged_includes, options[:joins])
                sql << join_dependency.join_associations.collect{|join| join.association_join }.join
    end
    
    add_joins!(sql, options[:joins], scope)
    

    Which solved the problem.

    So, my fault for not declaring that I was using Edge Rails. However, it will be worth keeping an eye on this as it will break SearchLogic in the future.

  • Ben Johnson

    Ben Johnson December 12th, 2008 @ 06:15 PM

    • State changed from “hold” to “resolved”

    Alright, I fixed this issue. I am now removing duplicate joins at a lower level, so AR can do whatever it wants now. This is a much cleaner approach. Update from the repo and let me know if this fixes it. Thanks.

  • Prometheus

    Prometheus December 14th, 2008 @ 02:58 PM

    This seems to have resolved the issue. Thanks Ben!

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

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

Provides common named scopes and object based searching.

Tags

Pages