module Lustra::SQL::Query::OrderBy

Overview

Encode for:

ORDER BY expression [ASC | DESC | USING operator] [NULLS FIRST | NULLS LAST];

Current implementation:

[x] Multiple Order by clauses [x] ASC/DESC [x] NULLS FIRST / NULLS LAST [ ] NOT IMPLEMENTED: USING OPERATOR

Direct including types

Defined in:

lustra/sql/query/order_by.cr

Instance Method Summary

Instance Method Detail

def clear_order_bys #

Remove all order by clauses


[View source]
def in_order_of(column : Symbol, values : Array(T)) forall T #

Applies an ORDER BY clause based on a given column, ordered and filtered by a specific set of values.

Generates a CASE expression in the ORDER BY clause so rows appear in the given value order. Rows whose column value is not present in the list sort last.

Post.query.in_order_of(:status, ["started", "enrolled", "completed"])
# ORDER BY CASE "status" WHEN 'started' THEN 0 WHEN 'enrolled' THEN 1 WHEN 'completed' THEN 2 ELSE 3 END ASC

Post.query.in_order_of(:priority, [3, 1, 2])
# ORDER BY CASE "priority" WHEN 3 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 3 END ASC

Can be combined with other #order_by calls; the CASE clause is appended to the existing ORDER BY list.


[View source]
def in_order_of(column : String, values : Array(T)) forall T #

Applies an ORDER BY clause based on a given column, ordered and filtered by a specific set of values.

Generates a CASE expression in the ORDER BY clause so rows appear in the given value order. Rows whose column value is not present in the list sort last.

Post.query.in_order_of(:status, ["started", "enrolled", "completed"])
# ORDER BY CASE "status" WHEN 'started' THEN 0 WHEN 'enrolled' THEN 1 WHEN 'completed' THEN 2 ELSE 3 END ASC

Post.query.in_order_of(:priority, [3, 1, 2])
# ORDER BY CASE "priority" WHEN 3 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 3 END ASC

Can be combined with other #order_by calls; the CASE clause is appended to the existing ORDER BY list.

When passing a String, the column expression is used as-is, which allows table-qualified references such as "\"posts\".\"status\"".


[View source]
def order_by(tuple : NamedTuple) #

Add multiple ORDER BY clause using a tuple:

query = Lustra::SQL.select.from("users").order_by(id: :desc, name: {:asc, :nulls_last})
query.to_sql # > SELECT * FROM users ORDER BY "id" DESC, "name" ASC NULLS LAST

[View source]
def order_by(expression : Symbol, direction : Symbol = :asc, nulls : Symbol | Nil = nil) #

Add one ORDER BY clause

query = Lustra::SQL.select.from("users").order_by(:id, :desc, nulls_last)
query.to_sql # > SELECT * FROM users ORDER BY "id" DESC NULLS LAST

[View source]
def order_by(expression : String, direction : Symbol = :asc, nulls : Symbol | Nil = nil) #

Add one ORDER BY clause

query = Lustra::SQL.select.from("users").order_by(:id, :desc, nulls_last)
query.to_sql # > SELECT * FROM users ORDER BY "id" DESC NULLS LAST

[View source]
def order_by(**tuple) #

Add multiple ORDER BY clause using a tuple:

query = Lustra::SQL.select.from("users").order_by(id: :desc, name: {:asc, :nulls_last})
query.to_sql # > SELECT * FROM users ORDER BY "id" DESC, "name" ASC NULLS LAST

[View source]
def reverse_order_by #

Flip over all order bys by switching the ASC direction to DESC and the NULLS FIRST to NULLS LAST

query = Lustra::SQL.select.from("users").order_by(id: :desc, name: :asc, company: {:asc, :nulls_last})
query.reverse_order_by
query.to_sql # SELECT * FROM users ORDER BY "id" ASC, "name" DESC, "company" DESC NULLS FIRST

return self


[View source]