acts_as_list crashes with non-numeric id

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.

Greg Willits wrote in post #1109539:

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.

Greg Willits wrote in post #1109539:

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.

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

Fred

Robert Walker wrote in post #1109609:

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...
https://github.com/swanandp/acts_as_list/pull/69

So, awareness, but no fix yet.

Frederick Cheung 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...

Thanks to both

-- gw

The position column in the mapped table needs to be an integer. See:

github.com/rails/acts_as_list

mike wrote in post #1109708:

Greg Willits wrote in post #1109539:

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.

-- gw