chapter | title |
---|---|
SQL |
Queries |
All relations come with the default #by_pk
method. It supports composite PKs too.
# with a single PK
users.by_pk(1)
# with a composite [post_id, tag_id] PK
posts_tags.by_pk(1, 2)
To explicitly select columns you can either use a list of symbols or relation schema:
class Users < ROM::Relation[:sql]
schema(infer: true)
def index
select(:id, :name)
# or
select(*schema.project(:id, :name))
# or
select(self[:id], self[:name])
end
end
In a basic case, which is selecting unqualified columns using their canonical names,
a list of symbols is all you need. Schemas and their attributes are useful in more
complex cases, so it's beneficial to know that you can use them in select
method.
If you have a relation with some columns already selected and you want to add more,
you can use select_append
method:
class Users < ROM::Relation[:sql]
schema(infer: true)
def index
select(:id, :name)
end
def details
index.select_append(:email, :created_at, :updated_at)
end
end
Both select
and select_append
accept a block which exposes projection DSL.
You can use it for simple selection of columns, or results of SQL functions.
Within the block you can refer to relation attributes by their names and use api::rom-sql::SQL API for projections:
class Users < ROM::Relation[:sql]
schema(infer: true)
def index
select { [id, name] }
end
end
Apart from returning column values, you can also project function results:
class Users < ROM::Relation[:sql]
schema(infer: true)
def index
select { [name, int::count(id).as(:count)] }.group(:id)
# SELECT "name", COUNT("id") AS "count" ...
end
end
Functions can accept any number of arguments:
class Users < ROM::Relation[:sql]
schema(infer: true)
def index
select { string::concat(id, '-', name).as(:uid) }
# SELECT CONCAT("id", '-', "name") AS "uid" ...
end
end
To restrict a relation you can use where
method which accepts a hash with
conditions or a block for more advanced usage.
If you pass a hash to where
all conditions will be translated into SQL and ANDed together:
class Users < ROM::Relation[:sql]
schema(infer: true)
def by_name(name)
where(name: name)
# ... WHERE ("name" = 'Jane') ...
end
def admin_by_name(name)
where(name: name, admin: true)
# ... WHERE ("name" = 'Jane') AND ("admin" IS TRUE) ...
end
def by_ids(ids)
where(ids: ids)
# ... WHERE ("id" IN (1, 2)) ...
end
end
If you pass a block to where
you can use restriction DSL to compose more complex conditions:
class Users < ROM::Relation[:sql]
schema(infer: true)
def query
where { (id < 10) | (id > 20) }
# (("id" < 10) OR ("id" > 20))
where { id.not(10..20) }
# (("id" < 10) OR ("id" > 20))
where { id.in(1..10) & id.in(20..100) }
# (("id" >= 1) AND ("id" <= 10) AND ("id" >= 20) AND ("id" <= 100))
where { name.ilike('%an%') }
# ("name" ILIKE '%an%' ESCAPE '\\')
end
end
To create HAVING
clause simply use having
method, which works in a similar way as where
and supports creating aggregate functions for your conditions:
class Users < ROM::Relation[:sql]
schema(infer: true)
def email_duplicates
select { [email, int::count(id).as(:count)] }.
group(:email).
having { count(id) >= 1 }
# ... HAVING (count("id") >= 1) ...
end
end
order
method with block will order your query:
class Users < ROM::Relation[:sql]
schema(infer: true)
def query
order { created_at.desc }
# ... ORDER BY "created_at" DESC
order { created_at.asc }
# ... ORDER BY "created_at" ASC
end
end
Check out API documentation: