Seems to me acts_as_list has a bug – I have data where the row id is a
random alphaNumeric, not a simple integer. Using acts_as_list with a
scope of a related model id, acts_as_list crashes the app due to a
faulty query in MySQL something like this:
class LineItem < ActiveRecord::Base
belongs_to :order
acts_as_list :scope => :order_id
end
Unknown column ‘UXPzIdeIuIFkz6n’ in ‘where clause’: UPDATE line_items
SET position = (position - 1) WHERE (order_id = UXPzIdeIuIFkz6n AND
position > 5)
I’ve been trying several ways to force substition to generate those
needed quotes myself, but so far no luck.
Seems to me acts_as_list has a bug – I have data where the row id is a
random alphaNumeric, not a simple integer. Using acts_as_list with a
scope of a related model id, acts_as_list crashes the app due to a
faulty query in MySQL something like this:
class LineItem < ActiveRecord::Base
belongs_to :order
acts_as_list :scope => :order_id
end
Unknown column ‘UXPzIdeIuIFkz6n’ in ‘where clause’: UPDATE line_items
SET position = (position - 1) WHERE (order_id = UXPzIdeIuIFkz6n AND
position > 5)
I’ve been trying several ways to force substition to generate those
needed quotes myself, but so far no luck.
Anyone battle & solve this? Thx.
For future reference this is one of those times that fighting Rails
conventions makes your life more difficult as a Rails developer. Rails
wants it simple incrementing integer primary keys. If you can
accommodate that wish then life for you as a developer gets simpler. If
you want a key that is non-numeric, create a separate column and put a
unique constraint on it, but let Rails have its standard simple integer
key for use in creating associations.
If you really want to fix the problem the I suggest you fork the
acts_as_list repository and fix the bug there. My guess is that the
author of acts_as_list assumed the Rails conventions and never tested
the scenario you’ve presented here.
On Monday, May 20, 2013 2:19:28 PM UTC+1, Ruby-Forum.com User wrote:
end
For future reference this is one of those times that fighting Rails
conventions makes your life more difficult as a Rails developer. Rails
wants it simple incrementing integer primary keys. If you can
accommodate that wish then life for you as a developer gets simpler. If
you want a key that is non-numeric, create a separate column and put a
unique constraint on it, but let Rails have its standard simple integer
key for use in creating associations.
This feels like it should work though - scope is not necessarily a
foreign
key column (it could easily be a status column for example
(open/closed/etc)), so it should work with string valued columns. Also
anything which allows an unquoted, user controllable string into an SQL
query is a potential security problem
For future reference this is one of those times that fighting Rails
conventions makes your life more difficult as a Rails developer.
I’ve done quite a few apps with non-numeric IDs w/o problems until now.
Apparently I never needed to scope a list (?) or the originall DHH one
didn’t have this problem maybe?
If you really want to fix the problem the I suggest you fork the
acts_as_list repository and fix the bug there…
hmm…
So, awareness, but no fix yet.
Frederick C. wrote in post #1109627:
This feels like it should work though - scope is not necessarily a
foreign
key column (it could easily be a status column for example
(open/closed/etc)), so it should work with string valued columns. Also
anything which allows an unquoted, user controllable string into an SQL
query is a potential security problem
That’s what I was thinking. Though my (probably incomplete) efforts to
inject some quotes have failed.
Anyway, I guess I’ll hack at my local copy and see what I come up
with…
Seems to me acts_as_list has a bug – I have data where the row id is a
random alphaNumeric, not a simple integer. Using acts_as_list with a
scope of a related model id, acts_as_list crashes the app due to a
faulty query in MySQL something like this:
Unknown column ‘UXPzIdeIuIFkz6n’ in ‘where clause’: UPDATE line_items
SET position = (position - 1) WHERE (order_id = UXPzIdeIuIFkz6n AND
position > 5)
Doh!! Gemfile did not have a specified version for acts_as_list and was
using an older version. Version 0.2.0 appears to be working fine. With a
simple scope, adding/deleting are working as I need them too.