Acts_as_list with 2 fields in the scope

Hi Railers,

I’ve got a Categories table.
I want it to act as a list within the scope of the parent_id AND the
site_id.

Categories table :
id
label
site_id
parent_id

So, in my Category class, I have :
acts_as_list :scope => ‘site_id = #{site_id} AND parent_id = #
{parent_id}’

The problem is that when I try to move_up a Category with a parent_id
that is null, the generated query (found in my development.log) is
“SELECT * FROM categories WHERE (site_id = 1 AND parent_id = AND
position = 3)” which gives me an error (because of “parent_id = AND”).

So, my question is “how can I solve this problem?”.

Many thanks in advance for you help that will be much appreciated!

Thomas B…

P.S. : if I only set the scope to parent_id, I have no problem, the
generated query is “SELECT * FROM categories WHERE (parent_id IS NULL)”

Thomas,

This happens because the code for acts_as_list behaves differently
when the scope is a symbol versus a string like the one you’re using.
When the scope is a symbol, acts_as_list knows how to check for null
and generate “IS NULL” as the condition. When you pass a string as
the scope, that string is used verbatim as the condition, with
runtime substitution of the values of any interpolated variables (the
stuff inside the #{…}).

Perhaps it would help to look at the source code at http://
ar.rubyonrails.com/classes/ActiveRecord/Acts/List/
ClassMethods.html#M000022. Be sure to click on the “Show Source” link
at the bottom of the acts_as_list entry, if it isn’t already showing.

Line 35 sets up some default key / value pairs. These will be
augmented (and possibly overridden) at line 36 by any that you pass
in, like :scope.

If you pass in “:scope => :some_symbol”, lines 41 - 49 define a
method called scope_condition() that looks like this:

def scope_condition
if parent_id.nil?
“parent_id IS NULL”
else
“parent_id = #{parent_id}”
end
end

and behaves as you expect when the runtime value of the symbol is nil.

If your :scope value is anything but a symbol (a string, in your
case), line 51 is executed instead. It generates a method that looks
like:

def scope_condition()
“site_id = #{site_id} AND parent_id = #{parent_id}”
end

Unfortunately, all that happens here when parent_id is nil is what
you saw in your log; i.e., it is interpolated as no character. You
might expect it to create something like:

site_id = 123 AND parent_id = nil

at runtime, but even that wouldn’t be helpful. You can see a
difference in how nil is handled by opening an irb session and
entering “puts nil”, followed by “puts #{nil}”.

If it’s any consolation, the example used in the acts_as_list
documentation (“acts_as_list :scope => ?todo_list_id = #
{todo_list_id} AND completed = 0?”) will fail in the same way that
yours does if todo_list_id is nil.

Hope this helps,
David

Hello David,

Thank you for your help!
I noticed the same thing, and I submitted a bug report regarding this.
You can read it here : http://dev.rubyonrails.org/ticket/3018.
It was posted 6 days ago, but is still not being managed.

Kind regards,
Thomas.