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 W. 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.

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

Fred

Robert W. 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 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…

Thanks to both

– gw

On Monday, May 20, 2013 11:19:40 PM UTC-4, Ruby-Forum.com User wrote:

If you really want to fix the problem the I suggest you fork the
foreign

Thanks to both

– gw


Posted via http://www.ruby-forum.com/.

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

github.com/rails/acts_as_list

Greg W. 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

mike wrote in post #1109708:

On Monday, May 20, 2013 11:19:40 PM UTC-4, Ruby-Forum.com User wrote:

If you really want to fix the problem the I suggest you fork the
foreign

Thanks to both

– gw


Posted via http://www.ruby-forum.com/.

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

github.com/rails/acts_as_list

Thx, but we’re not talking about the position column.

(and that link is to a much older version)

– gw