0

I have a column called internal_code in my Customer model. Since some users may use it as alphanumeric and others as only numeric, I need to suggest a number to the user before confirmation, in the UI.

I'm using this code right now:

previous_number = Company.where(:company_id => self.company_id).maximum(:internal_code)

But this is not working as expected, given for some reason, sometimes it's returning "999" when the latest value is "1000", or in another example "2290" when the latest value "2291".

I've been digging in the official documentation on maximum and calculate for Active Record, but didn't found if it's not intended to work with String columns. Maybe it's just obvious, but I wanted to ask here before I confirm my thoughts.

tadman
  • 208,517
  • 23
  • 234
  • 262
Ricardo Gamarra
  • 83
  • 5

2 Answers2

1

If this is a text column you may be getting the ASCIIabetical "max" instead of the numerical max. "999" sorts after "2291".

You need a numerical column type (e.g. INT) in order to do numerical maximums.

This should be a simple migration to change the column type if those values are purely numerical and fit in a 32-bit or 64-bit integer.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Thanks https://stackoverflow.com/users/87189/tadman, but this column needs to be alphanumerical. I mentioned: > some users may use it as alphanumeric and others as only numeric. But the ASCIIabetical "max" makes sense, since `"999" > "1000" = true` – Ricardo Gamarra Aug 19 '19 at 20:36
  • If you have a mix of alphabetical and numerical values then you need to define a derived sortable value. This could be zero padded, for example, to make things line up properly. The database can't do it for you since those rules are often ambiguous: Is `"A2A2"` before or after `"A11A1"`? Look at [other examples](https://stackoverflow.com/questions/9173558/postgresql-order-by-issue-natural-sort) for inspiration. – tadman Aug 19 '19 at 21:43
0

The definitive solution I found:

# Note: the ORDER clause is the KEY. Length means biggest number, ::bytea means sort it alphanumerically
filtered_result = Company.where.not(:internal_code => [nil,'']).where(:company_id => self.company_id).order("length(internal_code) DESC, internal_code::bytea DESC").first

previous_number = filtered_result.internal_code.scan(/\d{2,5}/).last

This complexity is given because of the possible values the users can put in this field. E.g. "ABD123" AS2134FG AS34SD2342

Hope it helps in similar issues. Thanks all.

Ricardo Gamarra
  • 83
  • 5