#583 SQL queries with WHERE IN are incorrectly generated for named scopes - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#583 ✓resolved

SQL queries with WHERE IN are incorrectly generated for named scopes

Reported by Szymon Nowak | July 9th, 2008 @ 11:43 AM | in 2.x

class Post < ActiveRecord::Base
  named_scope :recent, :conditions => [...]
end

class Author < ActiveRecord::Base
  belongs_to :post
end

posts = Post.recent
Author.find_all_by_post_id(posts)

This generates the following query:

SELECT * FROM `authors` WHERE (`authors`.`post_id` = 107,108,109)

instead of using WHERE IN clause.

Casting ActiveRecord::NamedScope::Scope object into Array object using posts.to_a fixes the problem, but it should be done automatically by dynamic finder method.

Comments and changes to this ticket

You flagged this item as spam.
Philip Hallstrom August 15th, 2008 @ 06:54 AM
- Title changed from “SQL queries with WHERE IN are incorrectly generated for named scopes ” to “PATCH: SQL queries with WHERE IN are incorrectly generated for named scopes ”
- Tag changed from “2.1, activerecord, bug, named_scope” to “2.1, activerecord, bug, named_scope, patch”
Below is a patch that fixes this. It has a slight drawback in that in cases where the named scope only returns one value it will generate "WHERE id IN (123)" which may or may not be more efficient than "WHERE id = 123" depending on the database.

It looks like making it smart enough to optimize that out would require additional changes as one would need to check the length of the Scope and where this code is being generated only the class name is available as an argument.
```
diff --git a/activerecord/lib/active_record/base.rb b/activerecord/lib/active_record/base.rb
index 92a24ec..5d3a86f 100755
--- a/activerecord/lib/active_record/base.rb
+++ b/activerecord/lib/active_record/base.rb
@@ -1717,7 +1717,7 @@ module ActiveRecord #:nodoc:
         def attribute_condition(argument)
           case argument
             when nil   then "IS ?"
-            when Array, ActiveRecord::Associations::AssociationCollection then "IN (?)"
+            when Array, ActiveRecord::Associations::AssociationCollection, ActiveRecord::NamedScope::Scope then "IN (?)"
             when Range then "BETWEEN ? AND ?"
             else            "= ?"
           end
```
Pratik August 16th, 2008 @ 08:23 PM
- Title changed from “PATCH: SQL queries with WHERE IN are incorrectly generated for named scopes ” to “SQL queries with WHERE IN are incorrectly generated for named scopes ”
Pratik August 16th, 2008 @ 08:32 PM
- State changed from “new” to “incomplete”
Missing tests. Also, please check http://rails.lighthouseapp.com/p...

Thanks.
Philip Hallstrom August 18th, 2008 @ 09:11 PM
- no changes were found...
- patch-for-sql-named-scope-find-by-all.diff 2.4 KB
You flagged this item as spam.
Philip Hallstrom August 18th, 2008 @ 09:11 PM
Patch with tests attached.
Pratik August 18th, 2008 @ 09:14 PM
- State changed from “incomplete” to “open”
You flagged this item as spam.
Tony Pitale August 18th, 2008 @ 09:26 PM
+1 That's a nice improvement, good catch
Repository August 21st, 2008 @ 04:12 PM
- State changed from “open” to “resolved”
(from [49c0e1e594c95d7e8446ebabecc9147afa62de7d]) Fix generated WHERE IN query for named scopes. [#583 state:resolved]

Signed-off-by: Pratik Naik pratiknaik@gmail.com http://github.com/rails/rails/co...
Repository August 25th, 2008 @ 09:31 AM
(from [2092f26edbd6de6d542a7bbd5b4dd30d5fd66ef4]) Fix generated WHERE IN query for named scopes. [#583 state:resolved]

Signed-off-by: Pratik Naik pratiknaik@gmail.com http://github.com/rails/rails/co...