Ruby Object Mapper - How to change the default order field in a Create command?

Question

I am current working on a project that I want to use Ruby Object Mapper with an existing sql database. I am running into a problem when I execute a create command, the returned result seems to sort by table_name.id by default. This is a problem because the existing tables that I am working with has a primary key named other than id, and it causes a unknown column 'id' exception.

I just wonder if there is any possible way to change the default order column?

Can you give an example of your schema? Removing the ID column is often a mistake. Why can't you add a secondary index on the other column you're using as a primary key instead? — tadman, Mar 08 '16 at 17:37
As an example, I have a `accounts` table that has no `id` column but a `twt_account_id` column as primary key. The reasons behind it is that the tables are used to store entities from a third party API e.g. Twitter, and we decided to give it another name to avoid confusion with `id` which is usually internal. Plus the database is already in production for long time and has a lot of data in it. — Edward, Mar 08 '16 at 17:48
Define "a lot"? Is it impractical to adjust the schema? Is Ruby Object Mapper the only ORM you can use here? In cases like this you should have your own internal `id` column and then a secondary and possibly `UNIQUE` index on something like `twt_account_id` where that is an external identifier. Usually there's an option in your ORM for specifying the alternate name for a primary key. — tadman, Mar 08 '16 at 17:50
It is possible to to adjust the schema, but the amount of work is high (1. There are other projects using the schema. 2. Millions of rows of data being affected. Hidden problem that might have impact to production). I agree that switching ORM is an alternative. However, if a SQL database allows you to use a different primary key as a feature, than why shouldn't an ORM working with such database supports that as well? — Edward, Mar 08 '16 at 17:57
I'm not as familiar with Ruby Object Mapper, but ActiveRecord and Sequel both support alternate primary keys. — tadman, Mar 08 '16 at 18:01
Ruby Object Mapper does support alternate primary key as well, but it seems like the default ordering won't be changed by setting it. And ROM uses Sequel as well. If that is a missing feature, then I might as well submit a feature request. — Edward, Mar 08 '16 at 18:02
If you're running up against limits of the software, doesn't hurt to try and search their tickets and see if this has come up before. I've used [Sequel](https://github.com/jeremyevans/sequel) directly and it's treated me pretty well, so you might want to evaluate that approach. — tadman, Mar 08 '16 at 18:06

Edward · Accepted Answer · 2016-03-08T19:55:16.540

Just got answer from Github:

This is an issue related with rom-sql (https://github.com/rom-rb/rom-sql/blob/master/lib/rom/sql/relation.rb#L39).

The solution is to declare your dataset explicitly in the relation of your command. Try something like this:

class MyRelation < ROM::Relation[:sql]
  dataset { order(:my_column) }
end

https://github.com/rom-rb/rom/issues/339#issuecomment-193904733

Update:

Alternatively you can also set that in the container definition using :macros:

rom_container = ROM.container(:sql, 'mysql2://root@localhost/db_name') do |rom|
  rom.use :macros

  rom.relation(:users) do |r|
    r.primary_key :my_column
    r.dataset { order(:my_column) }
  end
end

Ruby Object Mapper - How to change the default order field in a Create command?

1 Answers1