how to make NaN value as the lowest when ordering Active Record objects?

Question

I want to sort my table but it have NaN value in it,

the problem is in ruby/rails NaN is considered as highest and I want to make it as lowest instead?

I'm thinking about removing the NaN from the list ordering the non NaN list and adding the NaN's after that

anyone have more convenience approach?

my databse is postgresql and the column type is Decimal/BigDecimal and the values is Decimal/BigDecimal

the schema is

create_table "download_speeds", force: :cascade do |t|
    t.bigint "location_id", null: false
    t.string "operator_name"
    t.decimal "avg"
    t.decimal "min"
    t.decimal "max"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["location_id"], name: "index_download_speeds_on_location_id"
  end

and I want to sort/give rank to the downloads_speeds based on avg column descendingly

highest avg is on top (rank 1)

What database are you using? What is the type of the column you want to sort? What kind of values are in there? — Josien, Aug 06 '21 at 13:16
That's just how PG's `NaN` sorting works; if you want something different you'll probably need to (a) post-process the returned data, (b) only sort value-ful rows and retrieve the `NaN`s separately and concat, or (c) use a meaningful value instead of `NaN` in the first place. That said, I'm no PG expert at all :) — Dave Newton, Aug 06 '21 at 14:39
@DaveNewton thanks for the insight this question give solution too https://stackoverflow.com/questions/48725372/postgres-order-column-with-nan-values — buncis, Aug 06 '21 at 20:02

score 0 · Answer 1 · answered Aug 06 '21 at 17:43

0

It looks like the value of avg should always be a positive number. So substituting 0 for NaN in an sql query should give you the sort you're looking for. You don't have to write the whole query in sql, just the order clause:

  DownloadSpeed.where(created_at: Date.today).order("CASE WHEN avg='NaN' THEN 0 ELSE 1 END, avg")

answered Aug 06 '21 at 17:43

Les Nightingill

5,662
1
29
32

yeah but I prefer to keep the NaN instead replacing it with 0 – buncis Aug 06 '21 at 17:45
1

you're only replacing it in the order clause, not in the db or the result – Les Nightingill Aug 06 '21 at 17:46
1

I'll try it later, what if I want this NaN to be below under 0 (when there is 0 avg) change it into -0.1 or -1 maybe? – buncis Aug 06 '21 at 17:56
You might investigate, too, why 'NaN's are getting into this column. They may be legit, but I would imagine that an avg value should be >=0 or NULL. If there is not a legit reason, my approach would be to update the db to NULL's iso 'NaN's, and fix the calculation that is producing the 'NaN'. Then you can control the sort behaviour of NULLs with `nulls first` or `nulls last` in a Postgresql sort snippet. – Les Nightingill Aug 06 '21 at 18:30
the condition is I use `panda.rb` to get the values from `.xlsm` files I think I can't do much about that – buncis Aug 06 '21 at 19:24
your syntax has an error btw `ActiveRecord::UnknownAttributeReference (Query method called with non-attribute argument(s): "CASE WHEN avg='NaN' THEN 0 ELSE 1 END, avg")` – buncis Aug 06 '21 at 19:25
@buncis you can replace the "NaN" values in pandas fairly easily [See Here](https://www.geeksforgeeks.org/replace-nan-values-with-zeros-in-pandas-dataframe/) the syntax for pandas.rb is very similar it seems. The error you have is because Rails 6 does not allow "non-safe" order arguments. If you still want to use this you just need to wrap it in `Arel.sql` e.g. `order(Arel.sql("CASE WHEN avg='NaN' THEN 0 ELSE 1 END, avg"))` – engineersmnky Aug 06 '21 at 19:29
@engineersmnky yeah I aware of that problem is I want to leave it just as is, I just realize about that Arel.sql but any idea how to pass variable for the :desc or :asc in that syntax? – buncis Aug 06 '21 at 19:31
my current workaround is using `.reverse` for result from that syntax – buncis Aug 06 '21 at 19:37
for asc/desc the syntax is `order(Arel.sql("CASE WHEN avg='NaN' THEN 0 ELSE 1 END, avg ASC|DESC"))` – buncis Aug 06 '21 at 19:40
when I use the syntax with DESC options it still make the NaN to be on top/first `order(Arel.sql("CASE WHEN avg='NaN' THEN 0 ELSE 1 END, avg DESC"))` – buncis Aug 06 '21 at 19:49
so the correct syntax is o`rder(Arel.sql("CASE WHEN avg='NaN' THEN 0 ELSE 1 END, avg")).reverse` or `order(Arel.sql("CASE WHEN avg='NaN' THEN 0 ELSE 1 END, avg ASC")).reverse` anyone know why? – buncis Aug 06 '21 at 20:00
1

Eliminate `.reverse` it's a hack. The `asc` and `desc` should give you what you need. To put the "NaN"s before or after the records that have an actual value, interchange the 0 and 1 in the case statement. Now b/c you appear to want two different sorts (asc/desc), the easiest way is to use `.reverse` to switch direction. Else the 'NaN's will not be where you want them in the sort. – Les Nightingill Aug 06 '21 at 20:53
@buncis `Arel::sql` creates a `Arel::Nodes::SqlLiteral` which has `asc` and `desc` methods so your best bet is likely `order(Arel.sql("CASE WHEN avg='NaN' THEN 0 ELSE 1 END").public_send(direction), avg: direction)` where `direction` is one of asc or desc. This way you can sort Nan values and avg separately if you wanted. – engineersmnky Aug 06 '21 at 23:01

buncis · Accepted Answer · 2021-08-07T08:38:24.367

0

apparently it is just the way postgresql ordering with NaN

the workaround for this is using SQL syntax to change the NaN into null then set an options to set the null value to be the last when ordering

 DownloadSpeed.order(Arel.sql("nullif(avg, 'NaN') desc nulls last"))

how to make NaN value as the lowest when ordering Active Record objects?

2 Answers2