Association chains that mention the same table twice produce incorrect results

[GFunk911]

For the example, take the following schema:

Product: id
ProductCategory: id, product_id, category_id
Category: id

Rows: You have one product in multiple categories
Product id: 1

ProductCategory id: 1, category_id: 1, product_id: 1
ProductCategory id: 2, category_id: 2, product_id: 1

Category id: 1
Category id: 2

Say you want to find all categories that contain a product which is also in category X.

The following scope returns 2 copies of category 1, since the where sql references the wrong product_categories table. It should return categories 1 and 2

Category.product_categories_product_product_categories_category_id_equals(1)

SELECT categories.* FROM categories
INNER JOIN product_categories ON product_categories.category_id = categories.id
INNER JOIN products ON products.id = product_categories.product_id
INNER JOIN product_categories product_categories_products ON product_categories_products.product_id = products.id
WHERE (product_categories.category_id = 235)

[ghost]

Yeah, I see what you're doing, AR supports this, I just need to figure out how to tap into it.

[guioum]

I also have something similar:

class Direction < ActiveRecord::Base
  belongs_to :start_point, :class_name => "Point"
  belongs_to :end_point, :class_name => "Point"
end
class Point < ActiveRecord::Base
  has_one :direction
end

> Direction.start_point_name_eq("A").end_point_name_eq("B")

Will give:
SELECT directions.* FROM directions INNER JOIN points ON points.id = directions.end_point_id INNER JOIN points start_points_directions ON start_points_directions.id = directions.start_point_id WHERE ((points.name = 'A') AND (points.name = 'B'))

[elecnix]

I'm facing the same problem.

[softa]

I'm having a similar (or the same) problem, but with a single self-relationship in a table:

Create a project

rails searchlogic_test
cd searchlogic_test
script/generate model client name:string agency_id:integer

Add to db/seeds.rb

Client.create(:name => "Daniel (has no agency)")
the_agency = Client.create(:name => "TheAgency (is an agency)")
Client.create(:name => "Roberto (has agency)", :agency_id => the_agency.id)

app/models/client.rb

class Client < ActiveRecord::Base
belongs_to :agency, :class_name => 'Client', :foreign_key => :agency_id
end

Setup the database

rake db:migrate
rake db:seed

Add to config/environment.rb

config.gem "searchlogic"

Test on console

script/console
c = Client.agency_name_like("the")

You'll see it runs the following SQL

SELECT "clients".* FROM "clients" INNER JOIN "clients" agencies_clients ON "agencies_clients".id = "clients".agency_id WHERE (clients.name LIKE '%the%')

But I think it should be

SELECT "clients".* FROM "clients" INNER JOIN "clients" agencies_clients ON "agencies_clients".id = "clients".agency_id WHERE (agencies_clients.name LIKE '%the%')

[elecnix]

There were serious changes in Rails 3 that addresses this; Arel was introduced: "New Active Record chainable query language built on top of relational algebra". See http://guides.rails.info/3_0_release_notes.html

[mryan43]

Same issue here.

[iamsolarpowered]

Has anyone found a workaround for this? Seems I'm running into a related issue:

Player.has_many :seasons, :as => :statable
Player.has_many :teams, :through => :seasons

Player.search(:name_like => 'joe', :seasons_g_gte => 30, :teams_name_like => 'sun').all

Throws this error:
SQLite3::SQLException: ambiguous column name: seasons.g: SELECT "players".* FROM "players" INNER JOIN "seasons" ON "seasons".statable_id = "players".id AND "seasons".statable_type = 'Player' INNER JOIN "seasons" ON ("players"."id" = "seasons"."statable_id" AND "seasons"."statable_type" = 'Player') INNER JOIN "teams" ON "seasons"."team_id" = "teams"."id" WHERE (((seasons.g >= 30) AND (teams.name LIKE '%sun%')) AND (players.name LIKE '%joe%'))

Player.search(:name_like => 'joe', :seasons_g_gte => 30).all
and
Player.search(:name_like => 'joe', :teams_name_like => 'sun').all
both work fine, though.

[justqyx]

Oh,I'm facing the same problem! And then ,who can provide the solution!! Thanks!!!