Best way to alias attribute names

#1

We are using a legacy DB and would like to alias columns on the relation so we can query attributes with our own terminology. I see it’s possible to add a :meta key on the type with a possible qualified :sql_expr, but it’s a rather tedious interface. Is there a better way to accomplish this task?

#2

There’s also attribute :user_id, Types::Int.meta(alias: :id) which seems to be the simplest option I have found (consult the API -> rom -> Attribute).

EDIT
There’s a direct link to Attribute definition.

#3

Thank you @apohllo

I have confirmed this works well with SQLite, but for some reason it is not working correctly with tinytds sequel adapter.

# schema(infer: false) do
#   attribute :product_type, Types::String.meta(alias: :producttype)
# end

[1] pry(main)> ProductsRepo.open.products.where(product_type: 'Carpet').first
Sequel::DatabaseError: TinyTds::Error: Invalid column name 'PRODUCT_TYPE'.

Not sure if the issue is a rom thing or a sequel thing

#4

Hi!

I have also faced a similar problem.
I aliased an attribute in a relation and tried to use that alias in a query (as stated here), but the Sequel::DatabaseError occured. Here is the snippet:

class UsersRelation < ROM::Relation[:sql]
  schema(:users, infer: true) do
    attribute :username, Types::String.meta(alias: :name)
  end
end

ROM.env.relations[:users].where(name: "some name").one
# Sequel::DatabaseError (PG::UndefinedColumn: ERROR:  column "name" does not exist)

I believe it is the DB thing, because SQL evaluates WHERE clause before the SELECT list. Thus aliases aren’t available for the WHERE clause. On the other hand I would expect ROM::Relation class to deal with that (i.e. preprocess the query that uses any aliases). Can it be considered as a bug, or should we use attribute aliases in some different way?

#5

I guess you have landed in this already known bug, which is currently being addressed:

For now, try with:

users = ROM.env.relations[:users]
users.where { users[:username].canonical.is("some name") }.one

If you want to access the attribute though the alias, you can define the following method in your relation:

  def >>(name_or_alias)
    self[name_or_alias]
  rescue KeyError => e
    schema.attributes.find do |attr|
      attr.alias == name_or_alias
    end || (raise e)
  end

And then you can do:

users = ROM.env.relations[:users]
users.where { (users >> :name).canonical.is("some name") }.one

When I have the time I’ll try to push a PR with something similar to >> to rom repository.

#6

Thanks so much for the reply.