Why am I unable to sort records by a column with a camelCase name

Question

My Cardset table looks like:

create_table "cardsets", force: :cascade do |t|
    t.string "name", null: false
    t.string "code", null: false
    t.integer "setOrder", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["code"], name: "index_cardsets_on_code", unique: true
end

When I run the command:

Cardset.all.order('setOrder ASC')

it responds with:

Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column 
"setorder" does not exist) LINE 1: SELECT  "cardsets".* FROM "cardsets" ORDER 
BY setOrder ASC L...

HINT:  Perhaps you meant to reference the column "cardsets.setOrder".
: SELECT  "cardsets".* FROM "cardsets" ORDER BY setOrder ASC LIMIT $1

Running the query again, sorting by any other field works normally as it should.

I have a Card table which looks exactly the same as the Cardset table, with a cardOrder field and the same problem arises when running a Order query on that table too.

The only way I can get this to work is by encapsulating the column name with "columnName" qoutation marks like so:

Cardset.all.where('"setOrder" ASC')

Can anyone explain what is going on behind the scenes here.

https://stackoverflow.com/a/45256592/2325924 – 7urkm3n Sep 12 '20 at 21:21 — 7urkm3n, Sep 12 '20 at 21:21

Jake Worth · Accepted Answer · 2020-09-12T21:41:34.243

I believe the issue here comes down to your column name, setOrder. If it was lower-cased instead of camel-cased, the issue would go away.

When you created your table, it will look like this in Postgres (excluding a few columns):

database=# \d cardsets
                                  Table "public.cardsets"
  Column  |       Type        | Collation | Nullable |               Default
----------+-------------------+-----------+----------+--------------------------------------
 id       | integer           |           | not null | nextval('cardsets_id_seq'::regclass)
 name     | character varying |           | not null |
 code     | character varying |           | not null |
 setOrder | integer           |           | not null |
Indexes:
    "cardsets_pkey" PRIMARY KEY, btree (id)

When ActiveRecord turns your order string argument into a SQL query, without the double quotes you describe, the column name is folded to lower case. See this answer or this answer for more info. setOrder becomes setorder, which doesn't exist on the table, hence the error.

I think you have two options here: change the column name to set_order which is more conventional PostgreSQL, or setorder, both of which will work from ActiveRecord without double quotes, or continue to use double quotes anytime you need ActiveRecord to query that column. If possible I'd recommend the first approach.

Did you mean `setorder` as your second solution instead of `set-order` twice. — NemyaNation, Sep 12 '20 at 21:14

Why am I unable to sort records by a column with a camelCase name

1 Answers1