#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.

  • 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.

  • 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.

  • 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”
  • 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.

  • yolo

    yolo April 22nd, 2022 @ 03:45 AM

    กติกาไพ่แคง
    Full casino, baccarat, slots, fish shooting games and other games

  • yolo

    yolo April 22nd, 2022 @ 03:48 AM

    กติกาไพ่แคง
    Full casino, baccarat, slots, fish shooting games and other games

  • yolo

    yolo April 22nd, 2022 @ 03:58 AM

    ราคาบอล0.5
    Where else can you find a better casino

  • yolo

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