CODEWITHSUNDEEP
mysqlruby-on-railsrubyactiverecordruby-on-rails-4
azi
azi

Reputation: 929

Multiple Join Optimization in ActiveRecords Rails

There are four models: Car, Person, Country and Religion.

class Car < ActiveRecord::Base
  belongs_to :person
  has_one :country, through: :person
  has_one :religion, through: :person

  def self.get_indian_cars_owned_by_hindus
    self.joins(:country).joins(:religion).where("countries.name=? and religions.name=?", 'India', 'Hindu').count
  end
end

class Person < ActiveRecord::Base
  belongs_to :country
  belongs_to :religion
  has_one :car
end

class Country < ActiveRecord::Base
  has_many :persons
end

class Religion < ActiveRecord::Base
  has_many :persons
end

The function get_indian_cars_owned_by_hindus inside model Cars applies some condition on tables countries & religions. The query that gets generated when the function is called is this:

SELECT COUNT(*) FROM `cars` INNER JOIN `persons` ON `persons`.`id` = `cars`.`person_id` INNER JOIN `countries` ON `countries`.`id` = `persons`.`country_id` INNER JOIN `persons` `persons_cars_join` ON `persons_cars_join`.`id` = `cars`.`person_id` INNER JOIN `religions` ON `religions`.`id` = `persons_cars_join`.`religion_id` WHERE (`countries`.`name` = 'India') AND (`religions`.`name` = 'Hindu');

I initially expected the query to be this:

SELECT COUNT(*) FROM `cars` INNER JOIN `persons` ON `persons`.`id` = `cars`.`person_id` INNER JOIN `countries` ON `countries`.`id` = `persons`.`country_id` INNER JOIN `religions` ON `religions`.`id` = `persons`.`religion_id` WHERE (`countries`.`name` = 'India') AND (`religions`.`name` = 'Hindu');

ActiveRecords here is not optimizing an extra join between persons and cars tables. While I believe mysql will optimize this at it's end, what's the way to write this correctly in the rails way.

I obviously can move the method inside model persons, but that's not the solution I am looking for. There can be cases where there are more than one nodes.

EDIT: Chaging model names from A, B, C and D to Car, Person, Country and Religion

Upvotes: 3

Views: 1132

Answers (1)

rscarvalho
rscarvalho

Reputation: 552

The best bet would be changing the join call to not use the has_many :through relationships. Instead you can nest the deep relationships in an array, like this:

def self.get_indian_cars_owned_by_hindus
  self.joins(:person => [:country, :religion]).
  where("countries.name=? and religions.name=?", 'India', 'Hindu').
  count
end

Upvotes: 1

Related Questions