acts_as_list crashes with non-numeric id

rubyonrails-talk
1

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.

2

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.

3

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

4

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

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

5

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

6

mike wrote in post #1109708:

7

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