Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

SQL queries with WHERE IN are incorrectly generated for named scopes

#583 
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.

Reported by Szymon Nowak · July 9th, 2008 @ 11:43 AM

State: resolved
Milestone: 2.x
Assigned to: nobody
Importance: none

Activity

  1. Philip Hallstrom
    Philip Hallstrom
    • 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

    August 15th, 2008 @ 06:54 AM

  2. Pratik
    Pratik
    • 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

    August 16th, 2008 @ 08:23 PM

  3. Pratik
    Pratik
    • State changed from new to incomplete

    Missing tests. Also, please check http://rails.lighthouseapp.com/p...

    Thanks.

    August 16th, 2008 @ 08:32 PM

  4. Philip Hallstrom
    Philip Hallstrom

    August 18th, 2008 @ 09:11 PM

  5. Philip Hallstrom
    Philip Hallstrom

    Patch with tests attached.

    August 18th, 2008 @ 09:11 PM

  6. Pratik
    Pratik
    • State changed from incomplete to open

    August 18th, 2008 @ 09:14 PM

  7. Tony Pitale
    Tony Pitale

    +1 That's a nice improvement, good catch

    August 18th, 2008 @ 09:26 PM

  8. Repository
    Repository
    • 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...

    August 21st, 2008 @ 04:12 PM

  9. Repository
    Repository

    (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...

    August 25th, 2008 @ 09:31 AM