Skip to content

order_by

Order by clause for sorting query results.

Asc

Bases: ClauseBase

Represents an ascending sort order for a column.

from embar.query.order_by import Asc
from embar.column.base import ColumnBase, ColumnInfo

col = ColumnBase()
col.info = ColumnInfo(
    _table_name=lambda: "users",
    name="age",
    col_type="INTEGER",
    py_type=int,
    primary=False,
    not_null=False
)
asc = Asc(col, nulls="last")
sql = asc.sql(lambda: 0)
assert sql.sql == '"users"."age" ASC NULLS LAST'
Source code in src/embar/query/order_by.py 
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
class Asc(ClauseBase):
    """
    Represents an ascending sort order for a column.

    ```python
    from embar.query.order_by import Asc
    from embar.column.base import ColumnBase, ColumnInfo

    col = ColumnBase()
    col.info = ColumnInfo(
        _table_name=lambda: "users",
        name="age",
        col_type="INTEGER",
        py_type=int,
        primary=False,
        not_null=False
    )
    asc = Asc(col, nulls="last")
    sql = asc.sql(lambda: 0)
    assert sql.sql == '"users"."age" ASC NULLS LAST'
    ```
    """

    clause: ColumnBase | ClauseBase
    nulls: NullsOrdering | None

    def __init__(self, clause: ColumnBase | ClauseBase, nulls: NullsOrdering | None = None):
        """
        Create an ascending sort order.

        Args:
            col: The column to sort by
            nulls: Optional nulls ordering ("first" or "last")
        """
        self.clause = clause
        self.nulls = nulls

    @override
    def sql(self, get_count: GetCount) -> QuerySingle:
        """Generate the SQL fragment."""
        return _asc_or_desc_sql(
            self.clause,
            self.nulls,
            True,
            get_count,
        )

__init__(clause, nulls=None)

Create an ascending sort order.

Parameters:

Name Type Description Default
col

The column to sort by

 required
nulls NullsOrdering | None

Optional nulls ordering ("first" or "last")

 None
Source code in src/embar/query/order_by.py 
144
145
146
147
148
149
150
151
152
153
def __init__(self, clause: ColumnBase | ClauseBase, nulls: NullsOrdering | None = None):
    """
    Create an ascending sort order.

    Args:
        col: The column to sort by
        nulls: Optional nulls ordering ("first" or "last")
    """
    self.clause = clause
    self.nulls = nulls

sql(get_count)

Generate the SQL fragment.

Source code in src/embar/query/order_by.py 
155
156
157
158
159
160
161
162
163
@override
def sql(self, get_count: GetCount) -> QuerySingle:
    """Generate the SQL fragment."""
    return _asc_or_desc_sql(
        self.clause,
        self.nulls,
        True,
        get_count,
    )

BareColumn

Bases: ClauseBase

Represents a bare column reference (defaults to ASC).

This is used internally when a column is passed directly to order_by().

from embar.query.order_by import BareColumn
from embar.column.base import ColumnBase, ColumnInfo

col = ColumnBase()
col.info = ColumnInfo(
    _table_name=lambda: "users",
    name="id",
    col_type="INTEGER",
    py_type=int,
    primary=False,
    not_null=False
)
bare = BareColumn(col)
sql = bare.sql(lambda: 0)
assert sql.sql == '"users"."id"'
Source code in src/embar/query/order_by.py 
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
class BareColumn(ClauseBase):
    """
    Represents a bare column reference (defaults to ASC).

    This is used internally when a column is passed directly to order_by().

    ```python
    from embar.query.order_by import BareColumn
    from embar.column.base import ColumnBase, ColumnInfo

    col = ColumnBase()
    col.info = ColumnInfo(
        _table_name=lambda: "users",
        name="id",
        col_type="INTEGER",
        py_type=int,
        primary=False,
        not_null=False
    )
    bare = BareColumn(col)
    sql = bare.sql(lambda: 0)
    assert sql.sql == '"users"."id"'
    ```
    """

    col: ColumnBase

    def __init__(self, col: ColumnBase):
        """Create a bare column reference."""
        self.col = col

    @override
    def sql(self, get_count: GetCount) -> QuerySingle:
        """Generate the SQL fragment (just the column FQN)."""
        return QuerySingle(sql=self.col.info.fqn(), params=None)

__init__(col)

Create a bare column reference.

Source code in src/embar/query/order_by.py 
241
242
243
def __init__(self, col: ColumnBase):
    """Create a bare column reference."""
    self.col = col

sql(get_count)

Generate the SQL fragment (just the column FQN).

Source code in src/embar/query/order_by.py 
245
246
247
248
@override
def sql(self, get_count: GetCount) -> QuerySingle:
    """Generate the SQL fragment (just the column FQN)."""
    return QuerySingle(sql=self.col.info.fqn(), params=None)

Desc

Bases: ClauseBase

Represents a descending sort order for a column.

from embar.query.order_by import Desc
from embar.column.base import ColumnBase, ColumnInfo

col = ColumnBase()
col.info = ColumnInfo(
    _table_name=lambda: "users",
    name="age",
    col_type="INTEGER",
    py_type=int,
    primary=False,
    not_null=False
)
desc = Desc(col, nulls="first")
sql = desc.sql(lambda: 0)
assert sql.sql == '"users"."age" DESC NULLS FIRST'
Source code in src/embar/query/order_by.py 
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
class Desc(ClauseBase):
    """
    Represents a descending sort order for a column.

    ```python
    from embar.query.order_by import Desc
    from embar.column.base import ColumnBase, ColumnInfo

    col = ColumnBase()
    col.info = ColumnInfo(
        _table_name=lambda: "users",
        name="age",
        col_type="INTEGER",
        py_type=int,
        primary=False,
        not_null=False
    )
    desc = Desc(col, nulls="first")
    sql = desc.sql(lambda: 0)
    assert sql.sql == '"users"."age" DESC NULLS FIRST'
    ```
    """

    clause: ColumnBase | ClauseBase
    nulls: NullsOrdering | None

    def __init__(self, clause: ColumnBase | ClauseBase, nulls: NullsOrdering | None = None):
        """
        Create a descending sort order.

        Args:
            col: The column to sort by
            nulls: Optional nulls ordering ("first" or "last")
        """
        self.clause = clause
        self.nulls = nulls

    @override
    def sql(self, get_count: GetCount) -> QuerySingle:
        """Generate the SQL fragment."""
        return _asc_or_desc_sql(
            self.clause,
            self.nulls,
            False,
            get_count,
        )

__init__(clause, nulls=None)

Create a descending sort order.

Parameters:

Name Type Description Default
col

The column to sort by

 required
nulls NullsOrdering | None

Optional nulls ordering ("first" or "last")

 None
Source code in src/embar/query/order_by.py 
192
193
194
195
196
197
198
199
200
201
def __init__(self, clause: ColumnBase | ClauseBase, nulls: NullsOrdering | None = None):
    """
    Create a descending sort order.

    Args:
        col: The column to sort by
        nulls: Optional nulls ordering ("first" or "last")
    """
    self.clause = clause
    self.nulls = nulls

sql(get_count)

Generate the SQL fragment.

Source code in src/embar/query/order_by.py 
203
204
205
206
207
208
209
210
211
@override
def sql(self, get_count: GetCount) -> QuerySingle:
    """Generate the SQL fragment."""
    return _asc_or_desc_sql(
        self.clause,
        self.nulls,
        False,
        get_count,
    )

OrderBy dataclass

Represents an ORDER BY clause for sorting query results.

from embar.query.order_by import OrderBy, Asc, Desc, BareColumn
from embar.column.base import ColumnBase, ColumnInfo

col1 = ColumnBase()
col1.info = ColumnInfo(
    _table_name=lambda: "users",
    name="age",
    col_type="INTEGER",
    py_type=int,
    primary=False,
    not_null=False
)

col2 = ColumnBase()
col2.info = ColumnInfo(
    _table_name=lambda: "users",
    name="name",
    col_type="TEXT",
    py_type=str,
    primary=False,
    not_null=False
)

order = OrderBy((
    Desc(col1),
    Asc(col2, nulls="first"),
))
sql = order.sql(lambda: 0)
print(sql)
assert sql.sql == '"users"."age" DESC, "users"."name" ASC NULLS FIRST'
Source code in src/embar/query/order_by.py 
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
@dataclass
class OrderBy:
    """
    Represents an ORDER BY clause for sorting query results.

    ```python
    from embar.query.order_by import OrderBy, Asc, Desc, BareColumn
    from embar.column.base import ColumnBase, ColumnInfo

    col1 = ColumnBase()
    col1.info = ColumnInfo(
        _table_name=lambda: "users",
        name="age",
        col_type="INTEGER",
        py_type=int,
        primary=False,
        not_null=False
    )

    col2 = ColumnBase()
    col2.info = ColumnInfo(
        _table_name=lambda: "users",
        name="name",
        col_type="TEXT",
        py_type=str,
        primary=False,
        not_null=False
    )

    order = OrderBy((
        Desc(col1),
        Asc(col2, nulls="first"),
    ))
    sql = order.sql(lambda: 0)
    print(sql)
    assert sql.sql == '"users"."age" DESC, "users"."name" ASC NULLS FIRST'
    ```
    """

    clauses: tuple[ClauseBase, ...]

    def sql(self, get_count: GetCount) -> QuerySingle:
        """
        Generate the full ORDER BY SQL clause.

        ```python
        from embar.query.order_by import OrderBy, Asc, BareColumn
        from embar.column.base import ColumnBase, ColumnInfo

        col1 = ColumnBase()
        col1.info = ColumnInfo(
            _table_name=lambda: "users",
            name="id",
            col_type="INTEGER",
            py_type=int,
            primary=False,
            not_null=False
        )

        col2 = ColumnBase()
        col2.info = ColumnInfo(
            _table_name=lambda: "users",
            name="name",
            col_type="TEXT",
            py_type=str,
            primary=False,
            not_null=False
        )

        order = OrderBy((BareColumn(col1), Asc(col2)))
        sql = order.sql(lambda: 0)
        assert sql.sql == '"users"."id", "users"."name" ASC'
        ```
        """

        queries = [clause.sql(get_count) for clause in self.clauses]
        params = {k: v for d in queries for k, v in d.params.items()}

        sql = ", ".join(q.sql for q in queries)
        return QuerySingle(sql=sql, params=params)

sql(get_count)

Generate the full ORDER BY SQL clause.

from embar.query.order_by import OrderBy, Asc, BareColumn
from embar.column.base import ColumnBase, ColumnInfo

col1 = ColumnBase()
col1.info = ColumnInfo(
    _table_name=lambda: "users",
    name="id",
    col_type="INTEGER",
    py_type=int,
    primary=False,
    not_null=False
)

col2 = ColumnBase()
col2.info = ColumnInfo(
    _table_name=lambda: "users",
    name="name",
    col_type="TEXT",
    py_type=str,
    primary=False,
    not_null=False
)

order = OrderBy((BareColumn(col1), Asc(col2)))
sql = order.sql(lambda: 0)
assert sql.sql == '"users"."id", "users"."name" ASC'
Source code in src/embar/query/order_by.py 
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
def sql(self, get_count: GetCount) -> QuerySingle:
    """
    Generate the full ORDER BY SQL clause.

    ```python
    from embar.query.order_by import OrderBy, Asc, BareColumn
    from embar.column.base import ColumnBase, ColumnInfo

    col1 = ColumnBase()
    col1.info = ColumnInfo(
        _table_name=lambda: "users",
        name="id",
        col_type="INTEGER",
        py_type=int,
        primary=False,
        not_null=False
    )

    col2 = ColumnBase()
    col2.info = ColumnInfo(
        _table_name=lambda: "users",
        name="name",
        col_type="TEXT",
        py_type=str,
        primary=False,
        not_null=False
    )

    order = OrderBy((BareColumn(col1), Asc(col2)))
    sql = order.sql(lambda: 0)
    assert sql.sql == '"users"."id", "users"."name" ASC'
    ```
    """

    queries = [clause.sql(get_count) for clause in self.clauses]
    params = {k: v for d in queries for k, v in d.params.items()}

    sql = ", ".join(q.sql for q in queries)
    return QuerySingle(sql=sql, params=params)

RawSqlOrder

Bases: ClauseBase

Represents raw SQL in an ORDER BY clause.

from embar.query.order_by import RawSqlOrder
from embar.sql import Sql
from embar.table import Table
from embar.column.common import Integer

class User(Table):
    id: Integer = Integer()

raw = RawSqlOrder(Sql(t"{User.id} DESC"))
sql = raw.sql(lambda: 0)
assert sql.sql == '"user"."id" DESC'
Source code in src/embar/query/order_by.py 
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
class RawSqlOrder(ClauseBase):
    """
    Represents raw SQL in an ORDER BY clause.

    ```python
    from embar.query.order_by import RawSqlOrder
    from embar.sql import Sql
    from embar.table import Table
    from embar.column.common import Integer

    class User(Table):
        id: Integer = Integer()

    raw = RawSqlOrder(Sql(t"{User.id} DESC"))
    sql = raw.sql(lambda: 0)
    assert sql.sql == '"user"."id" DESC'
    ```
    """

    sql_obj: Sql

    def __init__(self, sql_obj: Sql):
        """Create a raw SQL order clause."""
        self.sql_obj = sql_obj

    @override
    def sql(self, get_count: GetCount) -> QuerySingle:
        """Generate the SQL fragment."""
        return QuerySingle(sql=self.sql_obj.sql(), params=None)

__init__(sql_obj)

Create a raw SQL order clause.

Source code in src/embar/query/order_by.py 
272
273
274
def __init__(self, sql_obj: Sql):
    """Create a raw SQL order clause."""
    self.sql_obj = sql_obj

sql(get_count)

Generate the SQL fragment.

Source code in src/embar/query/order_by.py 
276
277
278
279
@override
def sql(self, get_count: GetCount) -> QuerySingle:
    """Generate the SQL fragment."""
    return QuerySingle(sql=self.sql_obj.sql(), params=None)