Filtering auto_complete pick lists – part 2: using named scopes

April 3rd 2009 — Comments and Reactions

I just updated my customized version of the auto_complete plugin to allow you to provide a named scope to auto_complete_for, in order to filter the auto_complete pick list options differently than the plugin does by default. The updated code is now on github:

http://github.com/patshaughnessy/auto_complete

This is based on the ideas from my last post, Andrew Ng’s original post and my friend Alex’s suggestion to use named scopes instead of manually modifying the find options. Here’s an example of how to use it taken from the auto_complete sample app I posted in January:

Add a named scope to your target model: For example suppose tasks belong to projects and have a named scope “by_project” which joins on the projects table and returns the tasks belonging to the project with the given name:
```
class Task < ActiveRecord::Base
  belongs_to :project
  named_scope :by_project,
    lambda { |project_name| {
      :include => :project,
      :conditions => [ "projects.name = ?", project_name ]
    } }
end
```
In the controller, pass a block to auto_complete_for to specify that a named scope should be used to generate the competion options. Here the “by_project” named scope will be used to handle the task auto complete requests, using the “project” HTTP parameter:
```
auto_complete_for :task, :name do | items, params |
  items.by_project(params['project'])
end
```

In the view, optionally specify additional parameters you might want to pass into your named scope: in my sample app I have a field called “project_name” elsewhere on my form:

<% fields_for_task task do |f| -%>
  …
  <%= f.text_field_with_auto_complete :task,
        :name,
        {},
        {
          :method => :get,
          :with =>"value + '&project=' + $F('project_name')"
        } %>
  …
  <% end -%>

So how does this work? Let’s take a look at my new implementation of auto_complete_for:

def auto_complete_for(object, method, options = {})
  define_method("auto_complete_for_#{object}_#{method}") do
    model = object.to_s.camelize.constantize
    find_options = { 
      :conditions => [ "LOWER(#{model.quoted_table_name}.#{method}) LIKE ?",
        '%' + params[object][method].downcase + '%' ], 
      :order => "#{model.quoted_table_name}.#{method} ASC",
      :limit => 10 }.merge!(options)
    @items = model.scoped(find_options)
    @items = yield(@items, params) if block_given?
    render :inline => "<%= auto_complete_result @items, '#{method}' %>"
  end
end

One minor change I made here was to call “quoted_table_name” on the given model to specify the table name in the SQL generated to retrieve the auto complete results later. This was needed in case, like in my sample application, the controller specifies a named scope that joins with another table containing columns with the same name as the target model. If this isn’t the case, adding the table name to the SQL is harmless.

However, the most important 2 lines here are in bold: first we call a function called “scoped” to create an anonymous named scope based on the default auto_complete options “find_options:”

@items = model.scoped(find_options)

The exciting thing about this line, which Alex explained in his blog post, is that the use of named scopes delays the corresponding SQL statement from being executed until later when we actually access the query results in auto_complete_result. What happens instead is that an ActiveRecord:: NamedScope::Scope object is created, containing a temporary cache of the find options.

A good way to understand how this works is to try it in the Rails console:

complex-form-examples pat$ ./script/console 
Loading development environment (Rails 2.1.0)
>> find_options = { 
?>   :conditions => [ "LOWER(`tasks`.name) LIKE ?", '%t%' ], 
?>   :order => "`tasks`.name ASC",
?>   :limit => 10 }
=> {:order=>"name ASC", :conditions=>["LOWER(name) LIKE ?", "%t%"], :limit=>10}
>> Task.scoped(find_options)
=> [#<Task id: 4, project_id: 2, name: "Task 2a", due_at: nil, created_at: "2009-04-02 16:21:54",
updated_at: "2009-04-02 16:21:54">, #<Task id: 5, project_id: 2, name: "Task 2b", due_at: nil,
created_at: "2009-04-02 16:21:54", updated_at: "2009-04-02 16:21:54">, #<Task id: 6, project_id: 2,
name: "Task 2c", due_at: nil, created_at: "2009-04-02 16:21:54", updated_at: "2009-04-02
16:21:54">, #<Task id: 1, project_id: 1, name: "Task One", due_at: nil, created_at: "2009-04-02
16:21:30", updated_at: "2009-04-02 16:21:30">, #<Task id: 3, project_id: 1, name: "Task
Three", due_at: nil, created_at: "2009-04-02 16:21:30", updated_at: "2009-04-02 16:21:30">,
#<Task id: 2, project_id: 1, name: "Task Two", due_at: nil, created_at: "2009-04-02 16:21:30",
updated_at: "2009-04-02 16:21:30">]

Wait a minute! I thought the actual SQL execution was delayed by named scopes until I needed to access the results? Here the console has already displayed the query results, so the SQL statement must have been executed already. How and why did this happen? In this case, when you enter an expression into the Rails console and press ENTER, the expression is evaluated and then the “inspect” method is called on it. The problem is that the named scopes implementation has delegated the “inspect” method to another method, which executes the SQL statement and loads the query results.

We can use a trick in the console to open the ActiveRecord::NamedScope::Scope class and override the inspect method so the SQL is not executed, and prove to ourselves that “scoped()” actually does return a named scope object without executing the SQL statement:

>> module ActiveRecord
>> module NamedScope
>> class Scope
>> def inspect
>>   super # Avoids calling ActiveRecord::Base.find and calls Object.inspect
>> end
>> end
>> end
>> end
=> nil
>> Task.scoped(find_options)
=> #<ActiveRecord::NamedScope::Scope:0x21e65d4
      @proxy_options={:conditions=>["LOWER(`tasks`.name) LIKE ?", "%t%"],
          :order=>"`tasks`.name ASC", :limit=>10},
      @proxy_scope=Task(id: integer, project_id: integer, name: string,
          due_at: datetime, created_at: datetime, updated_at: datetime)>

So here we can see that “scoped” returns an ActiveRecord::NamedScope::Scope object, and that it has two interesting instance variables: proxy_scope and proxy_options. The first of these, proxy_options, contains the find options that were passed into the scoped() function, or into the “named_scope” declaration in your model. The second value, proxy_scope, indicates the parent scope or context in which this named scope object’s SQL statement should be run. In this example, that is the Task model itself. The named scope object is essentially a cache of the query options that will be user later when the query is executed.

Let’s see how this works in the auto_complete plugin. Back again to the new implementation of auto_complete_for, we have:

@items = model.scoped(find_options)
@items = yield(@items, params) if block_given?

The first line generates a ActiveRecord::NamedScope::Scope object, which is then passed into the block provided by the controller code, if any. Let’s take a look at my sample app’s implementation of the controller:

auto_complete_for :task, :name do | items, params |
  items.by_project(params['project'])
end

This is a good example of the second very cool feature of named scopes: that they are composable… in other words, that two or more named scopes can be combined together to form a single SQL statement that is executed only once! Let’s return to the same Rails console session with our redefined “inspect” method and see if we can understand a bit more about this:

>> Task.scoped(find_options).by_project 'Project One'
=> #<ActiveRecord::NamedScope::Scope:0x21d1864
  @proxy_options={
    :conditions=>["projects.name = ?", "Project One"],
    :include=>:project},
  @proxy_scope=
    #<ActiveRecord::NamedScope::Scope:0x21d1a30
      @proxy_options={
        :conditions=>["LOWER(`tasks`.name) LIKE ?", "%t%"],
        :order=>"`tasks`.name ASC",
        :limit=>10},
      @proxy_scope=Task(id: integer, project_id: integer, name: string, due_at: datetime, created_at: datetime, updated_at: datetime)
    >
  >

Now we can see that calling scoped(find_options).by_project just returns a chain of two named scopes: the first scope object with @proxy_scope set to the second one, and the second one with @proxy_scope set to the base model class. Later when this SQL query is executed, the code in NamedScope and ActiveRecord::Base will simply walk this chain of objects, accumulate the options into a single hash, convert the hash to SQL and execute it.

In auto_complete_for after the controller’s block returns, the “@items” value in auto_complete_for above is set to the parent/child named scope chain of objects, and then passed into auto_complete_result:

render :inline => "<%= auto_complete_result @items, '#{method}' %>"

Inside of auto_complete_result the @items value is used as if it were an array… like this:

def auto_complete_result(entries, field, phrase = nil)
  return unless entries
  items = entries.map { |entry|
    content_tag("li",
                phrase ? highlight(entry[field], phrase) : h(entry[field]))
  }
  content_tag("ul", items.uniq)
end

… to generate the HTML needed for the Prototype library’s implementation of the auto complete drop down box. The interesting thing here is that the SQL statement is executed as soon as the call to “map” is executed, accessing the elements of the “@items” array. This works because the ActiveRecord::NamedScope::Scope class redirects or delegates the [] and other array methods to ActiveRecord::Base.find. Here's the single SQL that is executed with the combined, accumulated query options:

SELECT `tasks`.`id` AS t0_r0, `tasks`.`project_id` AS t0_r1,
  `tasks`.`name` AS t0_r2, `tasks`.`due_at` AS t0_r3,
  `tasks`.`created_at` AS t0_r4, `tasks`.`updated_at` AS t0_r5,
  `projects`.`id` AS t1_r0, `projects`.`name` AS t1_r1,
  `projects`.`created_at` AS t1_r2, `projects`.`updated_at` AS t1_r3
FROM `tasks`
LEFT OUTER JOIN `projects` ON `projects`.id = `tasks`.project_id
WHERE ((projects.name = 'Project One') AND
  (LOWER(`tasks`.name) LIKE '%t%'))
ORDER BY `tasks`.name ASC LIMIT 10

In fact, in the original version of the auto_complete plugin before my changes to it for named scopes, the value passed into auto_complete_result was a simple array. The fact that named scopes are used now is entirely hidden from this code!

One last note here about named scope: as described in a comment in named_scope.rb from the Rails source code, the “proxy_options” method provides a convenient way to test the behavior of named scopes without actually checking the results of an actual SQL query. Here’s one of the tests I wrote for my new version of auto_complete_for:

def test_default_auto_complete_for
  get :auto_complete_for_some_model_some_field,
      :some_model => { :some_field => "some_value" }
  default_auto_complete_find_options = @controller.items.proxy_options
  assert_equal "`some_models`.some_field ASC", 
               default_auto_complete_find_options[:order]
  assert_equal 10, default_auto_complete_find_options[:limit]
  assert_equal ["LOWER(`some_models`.some_field) LIKE ?", "%some_value%"],
               default_auto_complete_find_options[:conditions]
end

Since I didn’t want to go to the trouble of setting up an actual in-memory database using SQLite, or to introduce mocha or some other mocking framework to the auto_complete tests, all I had to do was just call @controller.items.proxy_options and check that the find options are as expected. (I also had to expose “items” in the mock controller using attr_reader.) I have another test that checks that the controller’s block is called and it’s named scope options are present as expected… this test uses the proxy_scope method to walk up the chain to the parent named scope and get it’s proxy_options. See auto_complete_test.rb for details.