module

PgORM::Query::Methods(T)

Instance methods

all

Query all records from the database. Doesn't actually issue any SQL query. See Cache#to_a to load all records from the database into an Array.

Source

average(column_name : Symbol | String) : Float64

Calculates the average of a column. See #sum for details.

Source

count(column_name : Symbol | String = "*", distinct = builder.distinct?) : Int64

Counts how many records match the SQL query.

You can count all columns or a specific column::

User.count
User.count(:name)

You can specify a raw SQL query with a String:

User.count("LENGTH(name)")

Source

delete_all

Executes a DELETE SQL query.

User.where(group_id: 1).delete_all
# => DELETE FROM "users" WHERE "group_id" = 1;

Source

distinct(value = true) : self

Specify a DISTINCT statement for the query.

Source

dup(builder : Builder)

Source

exists?(id : T::PrimaryKeyType) : Bool

Returns true when a record identified by primary key exists in the database with the current conditions.

User.where(group_id: 1).exists?(2)
# => SELECT 1 AS one FROM "users" WHERE "group_id" = 1 AND "id" = 2 LIMIT 1;

Source

exists?

Returns true if the SQL query has at least one result.

User.where(group_id: 1).exists?
# => SELECT 1 AS one FROM "users" WHERE "group_id" = 1 LIMIT 1;

Source

find(id : T::PrimaryKeyType) : T

Loads a record by id from the database. Raises a Error::RecordNotFound exception when the record doesn't exist.

user = User.find(1)
# => SELECT * FROM "users" WHERE "id" = 1 LIMIT 1;

Source

find?(id : T::PrimaryKeyType) : T | Nil

Same as #find but returns nil when the record doesn't exist.

Source

find_by

Loads a record by arguments from the database. Raises a RecordNotFound exception when the record doesn't exist. For example:

user = User.find_by(name: "user", group_id: 2)
# => SELECT * FROM "users" WHERE "name" = 'user' AND "group_id" = 2 LIMIT 1;

See #where for more details on conditions.

Source

find_by?

Same as #find_by but returns nil when no record could be found in the database.

Source

first

Loads the first record from the database, ordering by the primary key in ascending order unless an order has been specified.

Merely takes the last entry in the cached result set if the relation was previously loaded.

Prefer #take if you don't need an ordering or already specified one.

user = User.first
# => SELECT * FROM "users" ORDER BY "id" ASC LIMIT 1;

user = User.order(name: :desc).last
# => SELECT * FROM "users" ORDER BY "name" DESC LIMIT 1;

user = User.order("name ASC, group_id DESC").last
# => SELECT * FROM "users" ORDER BY name ASC, group_id DESC LIMIT 1;

Source

first?

Same as #first? but returns nil when no record could be found in the database.

Source

group_by(*columns : Symbol | String) : self

Source

ids

Loads all primary key values of rows matching the SQL query.

Source

join(type : JoinType, model : Base.class, fk : Symbol, pk : Base.class | Nil = nil) : self

Source

join(type : JoinType, model : Base.class, on : String) : self

Source

last

Loads the last record from the database, ordering by the primary key in ascending order unless an order has been specified.

Merely takes the last entry in the cached result set if the relation was previously loaded.

Prefer #take if you don't need an ordering or already specified one.

user = User.last
# => SELECT * FROM "users" ORDER BY "id" DESC LIMIT 1;

user = User.order(name: :desc).last
# => SELECT * FROM "users" ORDER BY "name" ASC LIMIT 1;

user = User.order("name ASC, group_id DESC").last
# => SELECT * FROM "users" ORDER BY name DESC, group_id ASC LIMIT 1;

Source

last?

Same as #last? but returns nil when no record could be found in the database.

Source

limit(value : Int32) : self

Specify a LIMIT for the query.

Source

maximum(column_name : Symbol | String)

Returns the maximum value for a column. See #sum for details.

Source

minimum(column_name : Symbol | String)

Returns the minimum value for a column. See #sum for details.

Source

none

Ensures that the query will never return anything from the database.

Source

offset(value : Int32) : self

Specify an OFFSET for the query.

Source

or(other : self) : self

Combines two query scopes with OR logic (ActiveRecord-style). Each scope's conditions are wrapped in parentheses and joined with OR.

User.where(name: "John").or(User.where(name: "Jane"))
# => SELECT * FROM "users" WHERE (name = 'John') OR (name = 'Jane')

User.where(active: true).where(role: "admin")
  .or(User.where(active: true).where(role: "moderator"))
# => WHERE (active = true AND role = 'admin') OR (active = true AND role = 'moderator')

Source

order(columns : Hash(Symbol, Symbol)) : self

Specify an ORDER for the query. This is added to any previous order definition. For example:

User.order({name: :asc, group_id: :desc})
# => SELECT * FROM "users" ORDER BY "name" ASC, "group_id" DESC;

Source

order(*columns : Symbol | String) : self

Specify an ORDER column for the query. This is added to any previous order definition. For example:

User.order(:name)
# => SELECT * FROM "users" ORDER BY "name" ASC;

Source

order

Specify an ORDER for the query. This is added to any previous order definition. For example:

User.order(name: :asc, group_id: :desc)
# => SELECT * FROM "users" ORDER BY "name" ASC, "group_id" DESC;

Source

pluck(column_name : Symbol | String) : Array(Value)

Loads values of a single column as an Array.

names = User.pluck(:name)
# => SELECT "name" FROM "users";
# => ["user", "alice", ...]

Source

reorder(columns : Hash(Symbol, Symbol)) : self

Specify an ORDER for the query, replacing any previous ORDER definition. See #order for details.

Source

reorder(*columns : Symbol | String) : self

Specify an ORDER column for the query, replacing any previous ORDER definition. See #order for details.

Source

reorder

Specify an ORDER for the query, replacing any previous ORDER definition. See #order for details.

Source

search(search_query : FullTextSearch::SearchQuery) : self

Performs full-text search using PostgreSQL's tsvector and tsquery

Article.search("crystal & programming", :title, :content)

Source

search_ranked(search_query : FullTextSearch::SearchQuery) : self

Performs full-text search and orders by relevance rank

Article.search_ranked("crystal programming", :title, :content)

Source

select(sql : String) : self

Specify a raw SELECT statement for the query.

Source

select(*columns : Symbol) : self

Specify SELECT columns for the query.

Source

size

Returns how many records match the SQL query. Uses the cached result set if the query was previously loaded, otherwise executes a COUNT SQL query.

Source

sum(column_name : Symbol | String) : Int64 | Float64

Calculates the sum of a column.

You can specify a column name:

User.sum(:salary)

You can specify a raw SQL query with a String:

User.sum("LENGTH(name)")

Source

take

Loads one record from the database, without any ordering. Raises a Error::RecordNotFound exception when no record could be found.

user = User.take
# => SELECT * FROM "users" LIMIT 1;

Source

take?

Same as #take but returns nil when no record could be found in the database.

Source

to_sql

Returns the generated SQL query. Useful for debugging.

Source

unscope(*args) : self

Resets previously set SQL statement(s). For example:

users = User.where(group_id: 1).limit(10)
users.unscope(:limit)         # == User.where(group_id: 1)
users.unscope(:where)         # == User.limit(10)
users.unscope(:where, :limit) # == User.all

Available properties:

:select
:where
:order
:limit
:offset

Source

update_all(attributes : Hash | NamedTuple) : Nil

Executes an UPDATE SQL query.

User.where(id: 1).update_all({group_id: 2})
# => UPDATE "users" SET "group_id" = 2 WHERE "id" = 1;

Source

update_all

Executes an UPDATE SQL query.

User.where(id: 1).update_all(group_id: 2)
# => UPDATE "users" SET "group_id" = 2 WHERE "id" = 1;

Source

where(conditions : Hash(Symbol, Value | Array(Value)) | NamedTuple) : self

Specify WHERE conditions for the query. For example:

conditions = {
  :name     => "user",
  :group_id => 2,
}
User.where(conditions)
# => SELECT * FROM "users" WHERE "name" = 'user' AND "group_id" = 2;

The condition value may be nil:

User.where({:group_id => nil})
# => SELECT * FROM "users" WHERE "group_id" IS NULL;

The condition value may also be an Array of values:

User.where({:group_id => [1, 2, 3]})
# => SELECT * FROM "users" WHERE "group_id" IN (1, 2, 3);

Source

where(sql : String, *splat : Value) : self

Specify a raw WHERE condition for the query. You can specify arguments as ? and pass them to the method. For example:

User.where("LENGTH(name) > ?", 10)
# => SELECT * FROM "users" WHERE LENGTH(name) > 10;

Source

where

Specify WHERE conditions for the query. For example:

User.where(name: "user", group_id: 2)
# => SELECT * FROM "users" WHERE "name" = 'user' AND "group_id" = 2;

The condition value may be nil:

User.where(group_id: nil)
# => SELECT * FROM "users" WHERE "group_id" IS NULL;

The condition value may also be an Array of values:

User.where(group_id: [1, 2, 3])
# => SELECT * FROM "users" WHERE "group_id" IN (1, 2, 3);

Source

where_between(column : Symbol | String, min : Value, max : Value) : self

BETWEEN range comparison.

User.where_between(:age, 18, 65)
# => SELECT * FROM "users" WHERE "age" BETWEEN 18 AND 65

Article.where_between(:created_at, 1.week.ago, Time.utc)
# => SELECT * FROM "articles" WHERE "created_at" BETWEEN '...' AND '...'

Source

where_gt(column : Symbol | String, value : Value) : self

Greater than comparison.

User.where_gt(:age, 18)
# => SELECT * FROM "users" WHERE "age" > 18

Source

where_gte(column : Symbol | String, value : Value) : self

Greater than or equal comparison.

User.where_gte(:age, 18)
# => SELECT * FROM "users" WHERE "age" >= 18

Source

where_ilike(column : Symbol | String, pattern : String) : self

Pattern matching with ILIKE operator (case-insensitive, PostgreSQL-specific).

User.where_ilike(:email, "%@EXAMPLE.com")
# => SELECT * FROM "users" WHERE "email" ILIKE '%@EXAMPLE.com'

Article.where_ilike(:domain, "%example%")
# => SELECT * FROM "articles" WHERE "domain" ILIKE '%example%'

Source

where_like(column : Symbol | String, pattern : String) : self

Pattern matching with LIKE operator (case-sensitive).

User.where_like(:email, "%@example.com")
# => SELECT * FROM "users" WHERE "email" LIKE '%@example.com'

User.where_like(:name, "John%")
# => SELECT * FROM "users" WHERE "name" LIKE 'John%'

Source

where_lt(column : Symbol | String, value : Value) : self

Less than comparison.

User.where_lt(:age, 65)
# => SELECT * FROM "users" WHERE "age" < 65

Source

where_lte(column : Symbol | String, value : Value) : self

Less than or equal comparison.

User.where_lte(:age, 65)
# => SELECT * FROM "users" WHERE "age" <= 65

Source

where_not(conditions : Hash(Symbol, Value | Array(Value)) | NamedTuple) : self

Source

where_not

Source

where_not_between(column : Symbol | String, min : Value, max : Value) : self

NOT BETWEEN range comparison.

User.where_not_between(:age, 18, 65)
# => SELECT * FROM "users" WHERE "age" NOT BETWEEN 18 AND 65

Source

where_not_ilike(column : Symbol | String, pattern : String) : self

Negated pattern matching with NOT ILIKE operator.

User.where_not_ilike(:email, "%@SPAM.com")
# => SELECT * FROM "users" WHERE "email" NOT ILIKE '%@SPAM.com'

Source

where_not_like(column : Symbol | String, pattern : String) : self

Negated pattern matching with NOT LIKE operator.

User.where_not_like(:email, "%@spam.com")
# => SELECT * FROM "users" WHERE "email" NOT LIKE '%@spam.com'

Source