Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails ActiveRecord joins the same table twice

I have problems with table join. ActiveRecord produces SQL which joins the same table twice. Assume we have models:

class Product < ActiveRecord::Base
  has_many :characteristics
  has_many :properties, through: :characteristics
  has_many :values, through: :characteristics
end

class Value < ActiveRecord::Base
  has_many :characteristics
end

class Property < ActiveRecord::Base
  has_many :characteristics
end

class Characteristic < ActiveRecord::Base
  belongs_to :product
  belongs_to :property
  belongs_to :value
end

So I want to join both properties and values. Product.joins(:properties).joins(:values) will produce the next SQL:

SELECT `products`.*
FROM `products`
INNER JOIN `characteristics` ON `characteristics`.`product_id` = `products`.`id`
INNER JOIN `properties` ON `properties`.`id` = `characteristics`.`property_id`
INNER JOIN `characteristics` `characteristics_products_join` ON `characteristics_products_join`.`product_id` = `products`.`id`
INNER JOIN `values` ON `values`.`id` = `characteristics_products_join`.`value_id`

There are two same joins to the table characteristics.
How can I avoid this?

like image 921
yivo Avatar asked Nov 09 '22 19:11

yivo


1 Answers

I am in search for a more elegant solution and stumbled upon this. But this will work, just not as Rails-y as I'd like.

Product.joins(characteristics: :properties)
       .joins('INNER JOIN `characteristics` `characteristics_products_join` ON `characteristics_products_join`.`product_id` = `products`.`id`')
       .joins('INNER JOIN `values` ON `values`.`id` = `characteristics_products_join`.`value_id`')
like image 141
Edmund Lee Avatar answered Nov 14 '22 22:11

Edmund Lee