Databases
StackOverflow2013
Postsdbo
POMultiple Join Optimization in ActiveRecords Rails
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple Join Optimization in ActiveRecords Rails
    primarykey
    data
    text
    <p>There are four models: Car, Person, Country and Religion.</p> <pre><code>class Car &lt; 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 &lt; ActiveRecord::Base belongs_to :country belongs_to :religion has_one :car end class Country &lt; ActiveRecord::Base has_many :persons end class Religion &lt; ActiveRecord::Base has_many :persons end </code></pre> <p>The function get_indian_cars_owned_by_hindus inside model Cars applies some condition on tables countries &amp; religions. The query that gets generated when the function is called is this:</p> <p><code>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');</code></p> <p>I initially expected the query to be this:</p> <p><code>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');</code></p> <p>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 <code>the rails way</code>.</p> <p>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.</p> <p>EDIT: Chaging model names from A, B, C and D to Car, Person, Country and Religion</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USazi
    1. USazi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMultiple Join Optimization in ActiveRecords Rails
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMultiple Join Optimization in ActiveRecords Rails
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMultiple Join Optimization in ActiveRecords Rails
      1. This table or related slice is empty.
      1. VTUpMod
    1. COA real world example would be easier to understand, for folks like me who don't have abstract skill high enough.
      singulars
      1. POMultiple Join Optimization in ActiveRecords Rails
      1. USBilly Chan
    2. COThis would be easier to follow if there was something concrete like the usual blog example with users/posts/etc instead of abcd.
      singulars
      1. POMultiple Join Optimization in ActiveRecords Rails
      1. USKimmo Lehto
    3. COHow does the real life example matter ?
      singulars
      1. POMultiple Join Optimization in ActiveRecords Rails
      1. USYugal Jindle
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload