[Betternestedset-talk] Odd errors with scoping

[Jonathan Mischo]

Hi folks,

 

I've been working on an application that has separate notions of users and
people, where:

 

user belongs_to :person

 

and

 

person acts_as_nested_set (using BNS, of course)

 

When Person is defined with (among other things):

 

  belongs_to :organization

  acts_as_nested_set :scope => :organization

 

and I call Person.roots, I get this lovely error (which is very similar to
the error I get if I call Person.destroy while using scoping):

 

Mysql::Error: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '?
? "organization_id IS NULL" : "organization_id = #{organization_id}" AND
paren' at line 1: SELECT * FROM people WHERE ((organization_id.nil? ?
"organization_id IS NULL" : "organization_id = #{organization_id}" AND
parent_id IS NULL))  ORDER BY lft

 

Which is accompanied by this stack trace:

 

K:/Rails/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ac
tive_record/connection_adapters/abstract_adapter.rb:128:in `log'
K:/Rails/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ac
tive_record/connection_adapters/mysql_adapter.rb:243:in `execute'
K:/Rails/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ac
tive_record/connection_adapters/mysql_adapter.rb:399:in `select'
K:/Rails/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ac
tive_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all'
K:/Rails/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ac
tive_record/base.rb:427:in `find_by_sql'
K:/Rails/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ac
tive_record/base.rb:997:in `find_every'
K:/Rails/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ac
tive_record/base.rb:418:in `find'
#{RAILS_ROOT}/vendor/plugins/better_nested_set/lib/better_nested_set.rb:128:
in `roots'
#{RAILS_ROOT}/app/helpers/application_helper.rb:5:in `build_nested_tree'
#{RAILS_ROOT}/app/views/system/_users_box.rhtml:9:in
`_run_rhtml_47app47views47system47_users_box46rhtml'
#{RAILS_ROOT}/app/views/system/manageusers.rhtml:1:in
`_run_rhtml_47app47views47system47manageusers46rhtml'
-e:4:in `load'
-e:4

 

Houston, we have a problem!  Does anyone have any ideas?  For now, I can
turn off the scoping during development, but scoping is a necessity for
production.  I've tried monkeying around with it a bit, and I've been able
to get a bit closer, but I still wind up with an error on a SELECT statement
that looks like this:

 

Mysql::Error: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ''
at line 1: SELECT * FROM people WHERE ((organization_id = #{organization_id}
AND parent_id IS NULL))  ORDER BY lft

 

This occurs when I change this line (line 79 in better_nested_set.rb) from A
to B:

 

A:

 

options[:scope] = %(#{options[:scope].to_s}.nil? ? "#{options[:scope].to_s}
IS NULL" : "#{options[:scope].to_s} = \#{#{options[:scope].to_s}}")

 

B:

 

            if %(#{options[:scope].to_s}).nil?

                        options[:scope] = "#{options[:scope].to_s} IS NULL"

            else

                        options[:scope] = "#{options[:scope].to_s} =
\#{#{options[:scope].to_s}}"

            end

 

The problem seems to be that the right side of the quoted comparison in the
else portion of the above block doesn't properly evaluate to organization_id
in situ.  I've tried a few different things to get it to work properly, but
I can't seem to figure out where it'd get a value for organization_id (i.e.
the scope field).  I'll look at this again tomorrow, with fresh eyes, but so
far, I'm at my wit's end.  At least I've made some progress, including
trying to get the root and roots methods to evaluate the string (as that's
where I expect it should be happening), I just can't seem to figure out how
to make it fill in the blanks.

 

Any advice would be welcome!  I'd love to see a patch get committed, as
it'll make BNS better, and it'll make my application work properly in
production.

 

Thanks,

-Jon Mischo

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://rubyforge.org/pipermail/betternestedset-talk/attachments/20070605/315ad563/attachment-0001.html