Version 3.0

SQL » Attributes

Relations provide a powerful API for working with their attributes. They can be used to rename canonical columns, qualify them, compose more complex WHERE conditions, or create SQL functions.

Qualifying and Renaming Attributes

Joining relations introduces a problem of having conflicting attribute names. To solve this you often need to qualify and rename columns.

To qualify all attributes in a relation:

class Users < ROM::Relation[:sql]
  schema(infer: true) do
    associations do
      has_many :tasks
      has_many :posts
    end
  end

  def with_tasks
    join(:tasks, user_id: :id).qualified

    # the same will be done when you use a shortcut:
    join(tasks)
  end
  # produces "SELECT users.id, users.name ..."
end

To rename all attributes in a relation:

class Users < ROM::Relation[:sql]
  schema(infer: true) do
    associations do
      has_many :tasks
      has_many :posts
    end
  end

  def with_tasks
    prefix(:user).qualified
  end
  # produces "SELECT users.id AS user_id, users.name AS user_name ..."
end

To rename attributes in block-based DSLs you can use as method:

class Users < ROM::Relation[:sql]
  schema(infer: true)

  def index
    select { [id.as(:user_id), name.as(:user_name) }
  end
  # produces "SELECT users.id AS user_id, users.name AS user_name ..."
end

Creating functions from attributes

You can use an attribute to create a function. This is useful in cases where you would like to append a function that's created from an attribute coming from another relation. For example:

class Users < ROM::Relation[:sql]
  schema(infer: true)

  def index
    select(:id, :name, tasks[:id].func { int::count(id).as(:task_count) }).
      left_join(tasks).
      group(:id)
  end
  # SELECT "users"."id", "users"."name", COUNT("tasks"."id") AS "task_count" FROM "users" LEFT JOIN "tasks" ON ("users"."id" = "tasks"."user_id") GROUP BY "users"."id" ORDER BY "users"."id"
end

Using attributes for restrictions

You can use Attribute API in restriction methods such as #where:

class Users < ROM::Relation[:sql]
  schema(infer: true)

  def index
    where { name.is('Jane') | name.is('Joe') }
  end
  # SELECT "id", "name" FROM "users" WHERE (("name" = 'Jane') OR ("name" = 'Joe')) ORDER BY "users"."id""
end

Learn more

Check out API documentation: