Databases
StackOverflow2013
Postsdbo
PORails 3.1 scope ignored when joining twice to the same table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORails 3.1 scope ignored when joining twice to the same table
    text
    copied!<p>I am running into a problem when joining to the same table twice in separate scopes using rails 3.1.4. One of the scopes is entirely ignored, including both the join and where clauses. This removal happens without an error or notice.</p> <p>This is a simplified example of what is causing the problem:</p> <p>Task is a standard rails model with a polymorphic relationship to the model SavedOutput. SavedOuput is used as a cache to store the results of complex methods.</p> <p>The task model looks like this:</p> <pre><code>class Task &lt; ActiveRecord::Base has_many :saved_outputs scope :saved_lates, lambda { joins(:saved_outputs).where( "saved_outputs.method" =&gt; "late?", "saved_outputs.output" =&gt; true )} scope :saved_completes, lambda { joins(:saved_outputs).where( "saved_outputs.method" =&gt; "complete?", "saved_outputs.output" =&gt; true )} ... </code></pre> <p>With this code, I can call <code>Task.saved_lates</code> instead of calling something like <code>Task.all.select(&amp;:late?)</code> assuming the cached data is current. </p> <p>The problem is that calling <code>Task.saved_lates.saved_completes</code> does not work. I believe rails' duplicate query detection kicks in and removes the second scope. Even if that didn't happen, the query would still fail because you can't join to the same table twice without using an alias in MYSQL.</p> <p>I have a partial solution with a manually written join and table alias.</p> <pre><code> scope :saved_lates, lambda { joins("INNER JOIN saved_outputs AS so1 ON so1.object_type='Task' AND so1.object_id=tasks.id").where( "so1.method" =&gt; "late?", "so1.output" =&gt; true )} scope :saved_completes, lambda { joins(INNER JOIN saved_outputs AS so2 ON so2.object_type='Task' AND so2.object_id=tasks.id).where( "so2.method" =&gt; "complete?", "so2.output" =&gt; true )} </code></pre> <p>The problem with this solution is that the alias <code>so1</code> needs to be unique throughout the entire project. Considering that the SavedOutput model saves the outputs of many different models, I would need to use a global unique id or unique hash system to label the aliases.</p> <p>Is there a solution to the silent removal of a scope from a query?<br> Is there a way to force rails to create a unique table alias on every standard join?<br> Is it bad practice to use the joins scope with association symbols as arguments?</p> <hr> <p>Here is a complete example of what I am looking at:</p> <pre><code>class ScopeTest &lt; ActiveRecord::Migration def change create_table :foos do |t| t.string :name t.boolean :active, :default =&gt; true end create_table :bars do |t| t.integer :foo_id t.boolean :method_value end end end class Foo &lt; ActiveRecord::Base has_many :bars scope :ones, lambda { joins(:bars).where("bars.method_value" =&gt; true) } scope :zeroes, lambda { joins(:bars).where("bars.method_value" =&gt; false) } end </code></pre> <p>The results of running the scopes and chaining the scopes:</p> <pre><code>irb(main):022:0&gt; Foo.ones.to_sql =&gt; "SELECT `foos`.* FROM `foos` INNER JOIN `bars` ON `bars`.`foo_id` = `foos`.`id` WHERE `bars`.`method_value` = 1" irb(main):023:0&gt; Foo.zeroes.to_sql =&gt; "SELECT `foos`.* FROM `foos` INNER JOIN `bars` ON `bars`.`foo_id` = `foos`.`id` WHERE `bars`.`method_value` = 0" irb(main):024:0&gt; Foo.ones.zeroes.to_sql =&gt; "SELECT `foos`.* FROM `foos` INNER JOIN `bars` ON `bars`.`foo_id` = `foos`.`id` WHERE `bars`.`method_value` = 0" irb(main):025:0&gt; Foo.zeroes.ones.to_sql =&gt; "SELECT `foos`.* FROM `foos` INNER JOIN `bars` ON `bars`.`foo_id` = `foos`.`id` WHERE `bars`.`method_value` = 1" </code></pre> <p>When I chain the queries, I want to get the intersection of results from the two scopes. I want the sql to have the same meaning as this:</p> <pre><code>SELECT `foos`.* from `foos` INNER JOIN `bars` AS `bars1` ON `bars1`.`foo_id` = `foos`.`id` INNER JOIN `bars` AS `bars2` ON `bars2`.`foo_id` = `foos`.`id` WHERE `bars1`.`method_value` = 1 AND `bars2`.`method_value` = 0 </code></pre> <p>How do I do that?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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