Positioning on `order` attribute not supported

[davycardinaal]

First of all, thanks for this gem! It is exactly what I was looking for.

In our application, we have some models which use the order column as attribute for ordering. So the model was configured with:

positioned on: :relation, column: :order

This will cause the following error during the creation/updating process:

/usr/local/bundle/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:894:in `exec_params': PG::SyntaxError: ERROR:  syntax error at or near "order" (ActiveRecord::StatementInvalid)
LINE 1: UPDATE "books" SET order = order * -1 WHERE "boo...

Which is an error on this complete SQL query:

UPDATE "books" SET order = order * -1 WHERE "books"."author_id" = 1 AND "books"."order" >= 1;

I believe it is cause because order is preserved word in SQL, so you should prefix your column (like rails does in the where clause).

To fix the issue for above example, the Rails code would be:

book.update("#{Book.table_name}.order >= 1")

So for the positioning gem it should be something like that for all @column references:

scope.where("#{@table_name}.#{@column}")
scope.update_all("#{@table_name}.#{@column} = #{@table_name}.#{@column} * -1")
# etc.

[brendon]

Lol, yes I remember we had to do this for acts_as_list. I'll look to sort that out this week.

I wonder if we can just get away with quoting the column name?

[brendon]

The key here is using quote_table_name_for_assignment which deals with quoting for all three database types and also prepending the table name for MySQL only. Will cut a new gem shortly.

[brendon]

Try version 0.2.1.