Skip to main content

rok_db_core/
expr.rs

1use std::fmt;
2use std::marker::PhantomData;
3use std::ops::Not;
4
5use std::sync::Arc;
6
7use crate::sql::Sql;
8use crate::{Model, Projected, Select, Value};
9
10/// A column of model `M`.
11///
12/// `#[derive(Model)]` generates one associated constant per field, named
13/// after the field in `SCREAMING_SNAKE_CASE` (`User::EMAIL`, `User::ID`, …).
14/// Columns are tied to their model, so a `Post` column can't accidentally be
15/// used to filter `User`s.
16pub struct Column<M> {
17    name: &'static str,
18    _model: PhantomData<fn() -> M>,
19}
20
21impl<M> Column<M> {
22    /// Create a column handle. Normally generated by `#[derive(Model)]`.
23    pub const fn new(name: &'static str) -> Self {
24        Self {
25            name,
26            _model: PhantomData,
27        }
28    }
29
30    /// The column name in the database.
31    pub const fn name(&self) -> &'static str {
32        self.name
33    }
34}
35
36impl<M: Model> Column<M> {
37    /// The column as a SQL value expression.
38    pub(crate) fn term(self) -> Term {
39        Term::Column {
40            table: M::TABLE,
41            name: self.name,
42        }
43    }
44
45    /// `"table"."column"`, for raw SQL fragments.
46    fn qualified(self) -> String {
47        crate::sql::qualified(M::TABLE, self.name)
48    }
49
50    fn cmp(self, op: &'static str, value: impl Into<Value>) -> Expr<M> {
51        Expr::new(Cond::Cmp {
52            term: self.term(),
53            op,
54            value: value.into(),
55        })
56    }
57
58    /// `column = value`
59    pub fn eq(self, value: impl Into<Value>) -> Expr<M> {
60        self.cmp("=", value)
61    }
62
63    /// `column <> value`
64    pub fn ne(self, value: impl Into<Value>) -> Expr<M> {
65        self.cmp("<>", value)
66    }
67
68    /// `column > value`
69    pub fn gt(self, value: impl Into<Value>) -> Expr<M> {
70        self.cmp(">", value)
71    }
72
73    /// `column >= value`
74    pub fn gte(self, value: impl Into<Value>) -> Expr<M> {
75        self.cmp(">=", value)
76    }
77
78    /// `column < value`
79    pub fn lt(self, value: impl Into<Value>) -> Expr<M> {
80        self.cmp("<", value)
81    }
82
83    /// `column <= value`
84    pub fn lte(self, value: impl Into<Value>) -> Expr<M> {
85        self.cmp("<=", value)
86    }
87
88    /// `column LIKE pattern`
89    pub fn like(self, pattern: impl Into<Value>) -> Expr<M> {
90        self.cmp("LIKE", pattern)
91    }
92
93    /// `column NOT LIKE pattern`
94    pub fn not_like(self, pattern: impl Into<Value>) -> Expr<M> {
95        self.cmp("NOT LIKE", pattern)
96    }
97
98    /// Case-insensitive `column ILIKE pattern`
99    pub fn ilike(self, pattern: impl Into<Value>) -> Expr<M> {
100        self.cmp("ILIKE", pattern)
101    }
102
103    /// `column LIKE '%text%'`, escaping `%`, `_` and `\` in `text`.
104    pub fn contains(self, text: &str) -> Expr<M> {
105        self.like(format!("%{}%", escape_like(text)))
106    }
107
108    /// `column LIKE 'text%'`, escaping `%`, `_` and `\` in `text`.
109    pub fn starts_with(self, text: &str) -> Expr<M> {
110        self.like(format!("{}%", escape_like(text)))
111    }
112
113    /// `column LIKE '%text'`, escaping `%`, `_` and `\` in `text`.
114    pub fn ends_with(self, text: &str) -> Expr<M> {
115        self.like(format!("%{}", escape_like(text)))
116    }
117
118    /// `column IN (…)`. An empty list matches nothing.
119    pub fn is_in<V: Into<Value>>(self, values: impl IntoIterator<Item = V>) -> Expr<M> {
120        Expr::new(Cond::In {
121            table: M::TABLE,
122            column: self.name,
123            values: values.into_iter().map(Into::into).collect(),
124            negated: false,
125        })
126    }
127
128    /// `column NOT IN (…)`. An empty list matches everything.
129    pub fn not_in<V: Into<Value>>(self, values: impl IntoIterator<Item = V>) -> Expr<M> {
130        Expr::new(Cond::In {
131            table: M::TABLE,
132            column: self.name,
133            values: values.into_iter().map(Into::into).collect(),
134            negated: true,
135        })
136    }
137
138    /// `column IN (subquery)`, where the subquery selects one column.
139    ///
140    /// ```ignore
141    /// // Users who wrote a post with more than 100 views.
142    /// User::filter(User::ID.in_subquery(
143    ///     Post::filter(Post::VIEWS.gt(100)).select(Post::AUTHOR_ID),
144    /// ))
145    /// ```
146    pub fn in_subquery<N: Model>(self, subquery: Projected<N>) -> Expr<M> {
147        Expr::new(Cond::Sub {
148            kind: SubKind::In {
149                table: M::TABLE,
150                column: self.name,
151                negated: false,
152            },
153            render: Render::new(move |sql| subquery.write_into(sql)),
154        })
155    }
156
157    /// `column NOT IN (subquery)`. Beware that `NOT IN` matches nothing if
158    /// the subquery returns any `NULL`; prefer [`Expr::not_exists`] then.
159    pub fn not_in_subquery<N: Model>(self, subquery: Projected<N>) -> Expr<M> {
160        Expr::new(Cond::Sub {
161            kind: SubKind::In {
162                table: M::TABLE,
163                column: self.name,
164                negated: true,
165            },
166            render: Render::new(move |sql| subquery.write_into(sql)),
167        })
168    }
169
170    /// Compare this column to a column of the *enclosing* query's model, for
171    /// correlated subqueries: `"posts"."author_id" = "users"."id"`.
172    ///
173    /// ```ignore
174    /// // Users with at least one post.
175    /// User::filter(Expr::exists(Post::filter(Post::AUTHOR_ID.eq_outer(User::ID))))
176    /// ```
177    pub fn eq_outer<O: Model>(self, outer: Column<O>) -> Expr<M>
178    where
179        M: Model,
180    {
181        Expr::new(Cond::Columns {
182            left: (M::TABLE, self.name),
183            op: "=",
184            right: (O::TABLE, outer.name),
185        })
186    }
187
188    /// `column BETWEEN low AND high`
189    pub fn between(self, low: impl Into<Value>, high: impl Into<Value>) -> Expr<M> {
190        Expr::new(Cond::Between {
191            table: M::TABLE,
192            column: self.name,
193            low: low.into(),
194            high: high.into(),
195        })
196    }
197
198    /// `column IS NULL`
199    pub fn is_null(self) -> Expr<M> {
200        Expr::new(Cond::Null {
201            table: M::TABLE,
202            column: self.name,
203            negated: false,
204        })
205    }
206
207    /// `column IS NOT NULL`
208    pub fn is_not_null(self) -> Expr<M> {
209        Expr::new(Cond::Null {
210            table: M::TABLE,
211            column: self.name,
212            negated: true,
213        })
214    }
215
216    // ----- PostgreSQL: arrays ------------------------------------------------
217
218    /// `column = ANY($1)` with a single array parameter — like
219    /// [`is_in`](Self::is_in), but one parameter however many values, so it
220    /// suits very large lists.
221    pub fn eq_any<T>(self, values: impl IntoIterator<Item = T>) -> Expr<M>
222    where
223        Vec<T>: Into<Value>,
224    {
225        let values: Vec<T> = values.into_iter().collect();
226        Expr::raw(format!("{} = ANY(?)", self.qualified()), [values.into()])
227    }
228
229    /// Array column contains every element of `values` (`column @> $1`).
230    pub fn array_contains(self, values: impl Into<Value>) -> Expr<M> {
231        self.cmp("@>", values)
232    }
233
234    /// Array column shares at least one element with `values` (`column && $1`).
235    pub fn array_overlaps(self, values: impl Into<Value>) -> Expr<M> {
236        self.cmp("&&", values)
237    }
238
239    /// Array column contains `value` (`$1 = ANY(column)`).
240    pub fn array_has(self, value: impl Into<Value>) -> Expr<M> {
241        Expr::raw(format!("? = ANY({})", self.qualified()), [value.into()])
242    }
243
244    // ----- PostgreSQL: JSONB -------------------------------------------------
245
246    /// JSONB column has the top-level key `key` (`column ? $1`).
247    pub fn json_has_key(self, key: &str) -> Expr<M> {
248        self.cmp("?", key)
249    }
250
251    /// JSONB column has any of `keys` (`column ?| $1`).
252    pub fn json_has_any_key<K: Into<String>>(self, keys: impl IntoIterator<Item = K>) -> Expr<M> {
253        let keys: Vec<String> = keys.into_iter().map(Into::into).collect();
254        self.cmp("?|", keys)
255    }
256
257    /// JSONB column has all of `keys` (`column ?& $1`).
258    pub fn json_has_all_keys<K: Into<String>>(self, keys: impl IntoIterator<Item = K>) -> Expr<M> {
259        let keys: Vec<String> = keys.into_iter().map(Into::into).collect();
260        self.cmp("?&", keys)
261    }
262
263    /// JSONB column contains `json` (`column @> $1`), e.g.
264    /// `User::PREFS.json_contains(json!({"theme": "dark"}))`.
265    #[cfg(feature = "json")]
266    pub fn json_contains(self, json: serde_json::Value) -> Expr<M> {
267        self.cmp("@>", json)
268    }
269
270    /// The text value at `key` of a JSON/JSONB column (`column ->> $1`), to
271    /// filter, select or order by: `User::PREFS.json_text("theme").eq("dark")`.
272    pub fn json_text(self, key: &str) -> Projection<M> {
273        Projection::raw(format!("{} ->> ?", self.qualified()), [key])
274    }
275
276    /// The text value at a nested path (`column #>> $1`):
277    /// `Event::DATA.json_path_text(["user", "id"])`.
278    pub fn json_path_text<K: Into<String>>(
279        self,
280        path: impl IntoIterator<Item = K>,
281    ) -> Projection<M> {
282        let path: Vec<String> = path.into_iter().map(Into::into).collect();
283        Projection::raw(format!("{} #>> ?", self.qualified()), [path])
284    }
285
286    // ----- PostgreSQL: full-text search ----------------------------------------
287
288    /// Full-text search on a text column with the server's default text
289    /// search configuration:
290    /// `to_tsvector(column) @@ websearch_to_tsquery($1)`.
291    ///
292    /// `query` uses web-search syntax: `rust -java "query builder" or orm`.
293    /// For speed, index `to_tsvector(column)` or use a generated `tsvector`
294    /// column with [`ts_matches`](Self::ts_matches).
295    pub fn search(self, query: &str) -> Expr<M> {
296        Expr::raw(
297            format!(
298                "to_tsvector({}) @@ websearch_to_tsquery(?)",
299                self.qualified()
300            ),
301            [query],
302        )
303    }
304
305    /// [`search`](Self::search) with an explicit configuration such as
306    /// `"english"` (enables stemming and stop words).
307    pub fn search_in(self, config: &str, query: &str) -> Expr<M> {
308        Expr::raw(
309            format!(
310                "to_tsvector(?::regconfig, {}) @@ websearch_to_tsquery(?::regconfig, ?)",
311                self.qualified()
312            ),
313            [config, config, query],
314        )
315    }
316
317    /// Match a `tsvector` column: `column @@ websearch_to_tsquery($1)`.
318    pub fn ts_matches(self, query: &str) -> Expr<M> {
319        Expr::raw(
320            format!("{} @@ websearch_to_tsquery(?)", self.qualified()),
321            [query],
322        )
323    }
324
325    /// Relevance of a text column for `query`, to order results:
326    /// `.order_by(Post::BODY.search_rank("rust orm").desc())`.
327    pub fn search_rank(self, query: &str) -> Projection<M> {
328        Projection::raw(
329            format!(
330                "ts_rank(to_tsvector({}), websearch_to_tsquery(?))",
331                self.qualified()
332            ),
333            [query],
334        )
335    }
336
337    /// `COUNT(column)` — number of non-`NULL` values.
338    pub fn count(self) -> Projection<M> {
339        Projection::func("COUNT", Some((M::TABLE, self.name)), false)
340    }
341
342    /// `COUNT(DISTINCT column)`
343    pub fn count_distinct(self) -> Projection<M> {
344        Projection::func("COUNT", Some((M::TABLE, self.name)), true)
345    }
346
347    /// `SUM(column)`. PostgreSQL returns `BIGINT` for `SMALLINT`/`INTEGER`
348    /// columns and `NUMERIC` for `BIGINT` columns; use
349    /// [`Projection::cast`] to pick the Rust type you decode into.
350    pub fn sum(self) -> Projection<M> {
351        Projection::func("SUM", Some((M::TABLE, self.name)), false)
352    }
353
354    /// `AVG(column)`, returned as `NUMERIC` for integer columns; see
355    /// [`Projection::cast`].
356    pub fn avg(self) -> Projection<M> {
357        Projection::func("AVG", Some((M::TABLE, self.name)), false)
358    }
359
360    /// `MIN(column)`
361    pub fn min(self) -> Projection<M> {
362        Projection::func("MIN", Some((M::TABLE, self.name)), false)
363    }
364
365    /// `MAX(column)`
366    pub fn max(self) -> Projection<M> {
367        Projection::func("MAX", Some((M::TABLE, self.name)), false)
368    }
369
370    /// Ascending ordering on this column.
371    pub fn asc(self) -> Order<M> {
372        Order::new(M::TABLE, self.name, Direction::Asc)
373    }
374
375    /// Descending ordering on this column.
376    pub fn desc(self) -> Order<M> {
377        Order::new(M::TABLE, self.name, Direction::Desc)
378    }
379}
380
381impl<M> Clone for Column<M> {
382    fn clone(&self) -> Self {
383        *self
384    }
385}
386
387impl<M> Copy for Column<M> {}
388
389impl<M> fmt::Debug for Column<M> {
390    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
391        f.debug_tuple("Column").field(&self.name).finish()
392    }
393}
394
395impl<M> fmt::Display for Column<M> {
396    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
397        f.write_str(self.name)
398    }
399}
400
401fn escape_like(text: &str) -> String {
402    let mut out = String::with_capacity(text.len());
403    for c in text.chars() {
404        if matches!(c, '%' | '_' | '\\') {
405            out.push('\\');
406        }
407        out.push(c);
408    }
409    out
410}
411
412/// A boolean condition on model `M`, used in `WHERE` clauses.
413///
414/// Combine conditions with [`Expr::and`], [`Expr::or`] and `!` (negation),
415/// or build them from raw SQL with [`Expr::raw`].
416pub struct Expr<M> {
417    pub(crate) cond: Cond,
418    _model: PhantomData<fn() -> M>,
419}
420
421impl<M> Expr<M> {
422    fn new(cond: Cond) -> Self {
423        Self {
424            cond,
425            _model: PhantomData,
426        }
427    }
428
429    /// A raw SQL condition where each `?` is bound to the next parameter.
430    ///
431    /// ```ignore
432    /// Expr::raw("lower(email) = lower(?)", ["Ann@Example.com"])
433    /// ```
434    pub fn raw<V: Into<Value>>(
435        sql: impl Into<String>,
436        params: impl IntoIterator<Item = V>,
437    ) -> Self {
438        Self::new(Cond::Raw {
439            sql: sql.into(),
440            params: params.into_iter().map(Into::into).collect(),
441        })
442    }
443
444    /// A condition that is always true.
445    pub fn all() -> Self {
446        Self::new(Cond::And(Vec::new()))
447    }
448
449    /// A condition that is always false.
450    pub fn none() -> Self {
451        Self::new(Cond::Or(Vec::new()))
452    }
453
454    /// `EXISTS (subquery)`. Combine with [`Column::eq_outer`] to correlate
455    /// the subquery with the outer query.
456    pub fn exists<N: Model>(subquery: Select<N>) -> Self {
457        Self::new(Cond::Sub {
458            kind: SubKind::Exists { negated: false },
459            render: Render::new(move |sql| subquery.write_exists_body(sql)),
460        })
461    }
462
463    /// `NOT EXISTS (subquery)`.
464    pub fn not_exists<N: Model>(subquery: Select<N>) -> Self {
465        Self::new(Cond::Sub {
466            kind: SubKind::Exists { negated: true },
467            render: Render::new(move |sql| subquery.write_exists_body(sql)),
468        })
469    }
470
471    /// Both `self` and `other` must hold.
472    pub fn and(self, other: Expr<M>) -> Self {
473        Self::new(match self.cond {
474            Cond::And(mut list) => {
475                list.push(other.cond);
476                Cond::And(list)
477            }
478            cond => Cond::And(vec![cond, other.cond]),
479        })
480    }
481
482    /// Either `self` or `other` must hold.
483    pub fn or(self, other: Expr<M>) -> Self {
484        Self::new(match self.cond {
485            Cond::Or(mut list) if !list.is_empty() => {
486                list.push(other.cond);
487                Cond::Or(list)
488            }
489            cond => Cond::Or(vec![cond, other.cond]),
490        })
491    }
492
493    /// Every condition in `exprs` must hold (`TRUE` when empty).
494    pub fn all_of(exprs: impl IntoIterator<Item = Expr<M>>) -> Self {
495        Self::new(Cond::And(exprs.into_iter().map(|e| e.cond).collect()))
496    }
497
498    /// At least one condition in `exprs` must hold (`FALSE` when empty).
499    pub fn any_of(exprs: impl IntoIterator<Item = Expr<M>>) -> Self {
500        Self::new(Cond::Or(exprs.into_iter().map(|e| e.cond).collect()))
501    }
502}
503
504impl<M> Expr<M> {
505    /// Re-tag a condition for another model (used by joins, whose
506    /// conditions are already table-qualified).
507    pub(crate) fn retag<N>(self) -> Expr<N> {
508        Expr::new(self.cond)
509    }
510}
511
512impl<M> Not for Expr<M> {
513    type Output = Expr<M>;
514
515    fn not(self) -> Self::Output {
516        Self::new(Cond::Not(Box::new(self.cond)))
517    }
518}
519
520impl<M> Clone for Expr<M> {
521    fn clone(&self) -> Self {
522        Self::new(self.cond.clone())
523    }
524}
525
526impl<M> fmt::Debug for Expr<M> {
527    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
528        self.cond.fmt(f)
529    }
530}
531
532/// Something that renders to a SQL value expression.
533#[derive(Debug, Clone)]
534pub(crate) enum Term {
535    Column {
536        table: &'static str,
537        name: &'static str,
538    },
539    Func {
540        func: &'static str,
541        arg: Option<(&'static str, &'static str)>,
542        distinct: bool,
543    },
544    Cast(Box<Term>, &'static str),
545    Raw(String, Vec<Value>),
546}
547
548impl Term {
549    pub(crate) fn write(&self, sql: &mut Sql) {
550        match self {
551            Term::Column { table, name } => {
552                sql.push_column(table, name);
553            }
554            Term::Func {
555                func,
556                arg,
557                distinct,
558            } => {
559                sql.push(func).push("(");
560                if *distinct {
561                    sql.push("DISTINCT ");
562                }
563                match arg {
564                    Some((table, column)) => sql.push_column(table, column),
565                    None => sql.push("*"),
566                };
567                sql.push(")");
568            }
569            Term::Cast(inner, ty) => {
570                sql.push("CAST(");
571                inner.write(sql);
572                sql.push(" AS ").push(ty).push(")");
573            }
574            Term::Raw(raw, params) => {
575                sql.push("(").push_raw(raw, params).push(")");
576            }
577        }
578    }
579}
580
581/// Renders a subquery into the enclosing statement (sharing its parameter
582/// numbering).
583#[derive(Clone)]
584pub(crate) struct Render(Arc<dyn Fn(&mut Sql) + Send + Sync>);
585
586impl Render {
587    pub(crate) fn new(f: impl Fn(&mut Sql) + Send + Sync + 'static) -> Self {
588        Self(Arc::new(f))
589    }
590}
591
592impl fmt::Debug for Render {
593    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
594        let mut sql = Sql::new();
595        (self.0)(&mut sql);
596        f.write_str(sql.as_str())
597    }
598}
599
600#[derive(Debug, Clone)]
601pub(crate) enum SubKind {
602    /// `(a, b) IN (subquery)` for composite keys.
603    InRow {
604        table: &'static str,
605        columns: &'static [&'static str],
606    },
607    In {
608        table: &'static str,
609        column: &'static str,
610        negated: bool,
611    },
612    Exists {
613        negated: bool,
614    },
615}
616
617#[derive(Debug, Clone)]
618pub(crate) enum Cond {
619    Sub {
620        kind: SubKind,
621        render: Render,
622    },
623    Columns {
624        left: (&'static str, &'static str),
625        op: &'static str,
626        right: (&'static str, &'static str),
627    },
628    Cmp {
629        term: Term,
630        op: &'static str,
631        value: Value,
632    },
633    In {
634        table: &'static str,
635        column: &'static str,
636        values: Vec<Value>,
637        negated: bool,
638    },
639    Between {
640        table: &'static str,
641        column: &'static str,
642        low: Value,
643        high: Value,
644    },
645    Null {
646        table: &'static str,
647        column: &'static str,
648        negated: bool,
649    },
650    Raw {
651        sql: String,
652        params: Vec<Value>,
653    },
654    And(Vec<Cond>),
655    Or(Vec<Cond>),
656    Not(Box<Cond>),
657}
658
659impl Cond {
660    pub(crate) fn write(&self, sql: &mut Sql) {
661        match self {
662            Cond::Sub { kind, render } => {
663                match kind {
664                    SubKind::InRow { table, columns } => {
665                        sql.push("(")
666                            .push_list(columns.iter(), ", ", |sql, c| {
667                                sql.push_column(table, c);
668                            })
669                            .push(") IN (");
670                    }
671                    SubKind::In {
672                        table,
673                        column,
674                        negated,
675                    } => {
676                        sql.push_column(table, column).push(if *negated {
677                            " NOT IN ("
678                        } else {
679                            " IN ("
680                        });
681                    }
682                    SubKind::Exists { negated } => {
683                        sql.push(if *negated { "NOT EXISTS (" } else { "EXISTS (" });
684                    }
685                }
686                (render.0)(sql);
687                sql.push(")");
688            }
689            Cond::Columns { left, op, right } => {
690                sql.push(&crate::sql::qualified(left.0, left.1));
691                sql.push(" ").push(op).push(" ");
692                sql.push(&crate::sql::qualified(right.0, right.1));
693            }
694            Cond::Cmp { term, op, value } => {
695                term.write(sql);
696                sql.push(" ").push(op).push(" ");
697                sql.bind(value.clone());
698            }
699            Cond::In {
700                table,
701                column,
702                values,
703                negated,
704            } => {
705                if values.is_empty() {
706                    sql.push(if *negated { "TRUE" } else { "FALSE" });
707                    return;
708                }
709                sql.push_column(table, column)
710                    .push(if *negated { " NOT IN (" } else { " IN (" });
711                sql.push_list(values, ", ", |sql, v| {
712                    sql.bind(v.clone());
713                });
714                sql.push(")");
715            }
716            Cond::Between {
717                table,
718                column,
719                low,
720                high,
721            } => {
722                sql.push_column(table, column).push(" BETWEEN ");
723                sql.bind(low.clone()).push(" AND ").bind(high.clone());
724            }
725            Cond::Null {
726                table,
727                column,
728                negated,
729            } => {
730                sql.push_column(table, column).push(if *negated {
731                    " IS NOT NULL"
732                } else {
733                    " IS NULL"
734                });
735            }
736            Cond::Raw { sql: raw, params } => {
737                sql.push("(").push_raw(raw, params).push(")");
738            }
739            Cond::And(list) => Self::write_group(sql, list, " AND ", "TRUE"),
740            Cond::Or(list) => Self::write_group(sql, list, " OR ", "FALSE"),
741            Cond::Not(inner) => {
742                sql.push("NOT (");
743                inner.write(sql);
744                sql.push(")");
745            }
746        }
747    }
748
749    fn write_group(sql: &mut Sql, list: &[Cond], sep: &str, empty: &str) {
750        match list {
751            [] => {
752                sql.push(empty);
753            }
754            [single] => single.write(sql),
755            many => {
756                sql.push("(");
757                sql.push_list(many, sep, |sql, c| c.write(sql));
758                sql.push(")");
759            }
760        }
761    }
762}
763
764/// A value expression of model `M` that can be selected: a column, an
765/// aggregate (`User::AGE.avg()`), a cast or raw SQL.
766///
767/// Use projections with [`Select::select`](crate::Select::select) to fetch
768/// tuples or custom row types instead of whole models, and compare them to
769/// build `HAVING` conditions:
770///
771/// ```ignore
772/// let per_role: Vec<(String, i64)> = User::query()
773///     .group_by(User::ROLE)
774///     .having(Projection::count_all().gt(10))
775///     .select((User::ROLE, Projection::count_all()))
776///     .fetch_all(&db)
777///     .await?;
778/// ```
779pub struct Projection<M> {
780    pub(crate) term: Term,
781    pub(crate) alias: Option<&'static str>,
782    _model: PhantomData<fn() -> M>,
783}
784
785impl<M> Projection<M> {
786    fn new(term: Term) -> Self {
787        Self {
788            term,
789            alias: None,
790            _model: PhantomData,
791        }
792    }
793
794    fn func(func: &'static str, arg: Option<(&'static str, &'static str)>, distinct: bool) -> Self {
795        Self::new(Term::Func {
796            func,
797            arg,
798            distinct,
799        })
800    }
801
802    pub(crate) fn retag<N>(self) -> Projection<N> {
803        Projection {
804            term: self.term,
805            alias: self.alias,
806            _model: PhantomData,
807        }
808    }
809
810    /// `COUNT(*)`
811    pub fn count_all() -> Self {
812        Self::func("COUNT", None, false)
813    }
814
815    /// Raw SQL where each `?` is bound to the next parameter.
816    pub fn raw<V: Into<Value>>(
817        sql: impl Into<String>,
818        params: impl IntoIterator<Item = V>,
819    ) -> Self {
820        Self::new(Term::Raw(
821            sql.into(),
822            params.into_iter().map(Into::into).collect(),
823        ))
824    }
825
826    /// `CAST(expr AS sql_type)`, e.g. `.cast("BIGINT")` or
827    /// `.cast("DOUBLE PRECISION")` to decode a `NUMERIC` aggregate into
828    /// `i64`/`f64`.
829    pub fn cast(self, sql_type: &'static str) -> Self {
830        Self {
831            term: Term::Cast(Box::new(self.term), sql_type),
832            ..self
833        }
834    }
835
836    /// Name the selected value (`expr AS alias`), for decoding into a struct
837    /// that derives `sqlx::FromRow`.
838    pub fn alias(mut self, alias: &'static str) -> Self {
839        self.alias = Some(alias);
840        self
841    }
842
843    fn cmp(self, op: &'static str, value: impl Into<Value>) -> Expr<M> {
844        Expr::new(Cond::Cmp {
845            term: self.term,
846            op,
847            value: value.into(),
848        })
849    }
850
851    /// `expr = value`
852    pub fn eq(self, value: impl Into<Value>) -> Expr<M> {
853        self.cmp("=", value)
854    }
855
856    /// `expr <> value`
857    pub fn ne(self, value: impl Into<Value>) -> Expr<M> {
858        self.cmp("<>", value)
859    }
860
861    /// `expr > value`
862    pub fn gt(self, value: impl Into<Value>) -> Expr<M> {
863        self.cmp(">", value)
864    }
865
866    /// `expr >= value`
867    pub fn gte(self, value: impl Into<Value>) -> Expr<M> {
868        self.cmp(">=", value)
869    }
870
871    /// `expr < value`
872    pub fn lt(self, value: impl Into<Value>) -> Expr<M> {
873        self.cmp("<", value)
874    }
875
876    /// `expr <= value`
877    pub fn lte(self, value: impl Into<Value>) -> Expr<M> {
878        self.cmp("<=", value)
879    }
880
881    /// Ascending ordering by this expression, e.g. a search rank.
882    pub fn asc(self) -> Order<M> {
883        Order::with_target(OrderTarget::Term(self.term), Direction::Asc)
884    }
885
886    /// Descending ordering by this expression.
887    pub fn desc(self) -> Order<M> {
888        Order::with_target(OrderTarget::Term(self.term), Direction::Desc)
889    }
890
891    pub(crate) fn write(&self, sql: &mut Sql) {
892        self.term.write(sql);
893        if let Some(alias) = self.alias {
894            sql.push(" AS ").push_ident(alias);
895        }
896    }
897}
898
899impl<M: Model> From<Column<M>> for Projection<M> {
900    fn from(column: Column<M>) -> Self {
901        Self::new(column.term())
902    }
903}
904
905impl<M> Clone for Projection<M> {
906    fn clone(&self) -> Self {
907        Self {
908            term: self.term.clone(),
909            alias: self.alias,
910            _model: PhantomData,
911        }
912    }
913}
914
915impl<M> fmt::Debug for Projection<M> {
916    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
917        let mut sql = Sql::new();
918        self.write(&mut sql);
919        f.debug_tuple("Projection").field(&sql.as_str()).finish()
920    }
921}
922
923/// A list of [`Projection`]s: a single column or projection, a tuple of up
924/// to 12 of them, an array or a `Vec`.
925pub trait IntoProjections<M> {
926    /// Convert into the list of selected expressions.
927    fn into_projections(self) -> Vec<Projection<M>>;
928}
929
930impl<M: Model> IntoProjections<M> for Column<M> {
931    fn into_projections(self) -> Vec<Projection<M>> {
932        vec![self.into()]
933    }
934}
935
936impl<M> IntoProjections<M> for Projection<M> {
937    fn into_projections(self) -> Vec<Projection<M>> {
938        vec![self]
939    }
940}
941
942impl<M, P: Into<Projection<M>>> IntoProjections<M> for Vec<P> {
943    fn into_projections(self) -> Vec<Projection<M>> {
944        self.into_iter().map(Into::into).collect()
945    }
946}
947
948impl<M, P: Into<Projection<M>>, const N: usize> IntoProjections<M> for [P; N] {
949    fn into_projections(self) -> Vec<Projection<M>> {
950        self.into_iter().map(Into::into).collect()
951    }
952}
953
954macro_rules! impl_into_projections_for_tuples {
955    ($(($($t:ident),+)),+ $(,)?) => {$(
956        #[allow(non_snake_case)]
957        impl<M, $($t: Into<Projection<M>>),+> IntoProjections<M> for ($($t,)+) {
958            fn into_projections(self) -> Vec<Projection<M>> {
959                let ($($t,)+) = self;
960                vec![$($t.into()),+]
961            }
962        }
963    )+};
964}
965
966impl_into_projections_for_tuples! {
967    (A),
968    (A, B),
969    (A, B, C),
970    (A, B, C, D),
971    (A, B, C, D, E),
972    (A, B, C, D, E, F),
973    (A, B, C, D, E, F, G),
974    (A, B, C, D, E, F, G, H),
975    (A, B, C, D, E, F, G, H, I),
976    (A, B, C, D, E, F, G, H, I, J),
977    (A, B, C, D, E, F, G, H, I, J, K),
978    (A, B, C, D, E, F, G, H, I, J, K, L),
979}
980
981/// Sort direction for `ORDER BY`.
982#[derive(Debug, Clone, Copy, PartialEq, Eq)]
983pub enum Direction {
984    /// Ascending (`ASC`).
985    Asc,
986    /// Descending (`DESC`).
987    Desc,
988}
989
990/// An `ORDER BY` term on model `M`, created with [`Column::asc`] or [`Column::desc`].
991pub struct Order<M> {
992    pub(crate) target: OrderTarget,
993    pub(crate) direction: Direction,
994    pub(crate) nulls: Option<bool>,
995    _model: PhantomData<fn() -> M>,
996}
997
998/// What an `ORDER BY` term sorts by.
999#[derive(Debug, Clone)]
1000pub(crate) enum OrderTarget {
1001    Column {
1002        table: &'static str,
1003        name: &'static str,
1004    },
1005    Term(Term),
1006}
1007
1008impl<M> Order<M> {
1009    pub(crate) fn new(table: &'static str, column: &'static str, direction: Direction) -> Self {
1010        Self::with_target(
1011            OrderTarget::Column {
1012                table,
1013                name: column,
1014            },
1015            direction,
1016        )
1017    }
1018
1019    fn with_target(target: OrderTarget, direction: Direction) -> Self {
1020        Self {
1021            target,
1022            direction,
1023            nulls: None,
1024            _model: PhantomData,
1025        }
1026    }
1027
1028    /// The column, if this orders by a plain column.
1029    pub(crate) fn column(&self) -> Option<&'static str> {
1030        match self.target {
1031            OrderTarget::Column { name, .. } => Some(name),
1032            OrderTarget::Term(_) => None,
1033        }
1034    }
1035
1036    pub(crate) fn retag<N>(self) -> Order<N> {
1037        Order {
1038            target: self.target,
1039            direction: self.direction,
1040            nulls: self.nulls,
1041            _model: PhantomData,
1042        }
1043    }
1044
1045    /// Put `NULL`s first.
1046    pub fn nulls_first(mut self) -> Self {
1047        self.nulls = Some(true);
1048        self
1049    }
1050
1051    /// Put `NULL`s last.
1052    pub fn nulls_last(mut self) -> Self {
1053        self.nulls = Some(false);
1054        self
1055    }
1056
1057    /// Write the sort key: the column or expression, or `alias` instead of an
1058    /// expression (used when the expression was selected under that alias).
1059    pub(crate) fn write_key(&self, sql: &mut Sql, prefix: &str, alias: Option<&str>) {
1060        match (&self.target, alias) {
1061            (OrderTarget::Column { table, name }, _) => {
1062                if prefix.is_empty() {
1063                    sql.push_column(table, name);
1064                } else {
1065                    sql.push(prefix).push_ident(name);
1066                }
1067            }
1068            (OrderTarget::Term(_), Some(alias)) => {
1069                sql.push(prefix).push_ident(alias);
1070            }
1071            (OrderTarget::Term(term), None) => term.write(sql),
1072        }
1073    }
1074
1075    pub(crate) fn write_direction(&self, sql: &mut Sql) {
1076        sql.push(match self.direction {
1077            Direction::Asc => " ASC",
1078            Direction::Desc => " DESC",
1079        });
1080        match self.nulls {
1081            Some(true) => sql.push(" NULLS FIRST"),
1082            Some(false) => sql.push(" NULLS LAST"),
1083            None => sql,
1084        };
1085    }
1086
1087    pub(crate) fn write(&self, sql: &mut Sql) {
1088        self.write_key(sql, "", None);
1089        self.write_direction(sql);
1090    }
1091}
1092
1093impl<M> Clone for Order<M> {
1094    fn clone(&self) -> Self {
1095        Self {
1096            target: self.target.clone(),
1097            direction: self.direction,
1098            nulls: self.nulls,
1099            _model: PhantomData,
1100        }
1101    }
1102}
1103
1104impl<M> fmt::Debug for Order<M> {
1105    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1106        let mut sql = Sql::new();
1107        self.write(&mut sql);
1108        f.debug_tuple("Order").field(&sql.as_str()).finish()
1109    }
1110}
1111
1112impl<M: Model> From<Column<M>> for Order<M> {
1113    fn from(column: Column<M>) -> Self {
1114        column.asc()
1115    }
1116}
1117
1118#[cfg(test)]
1119mod tests {
1120    use super::*;
1121
1122    struct T;
1123    const A: Column<T> = Column::new("a");
1124    const B: Column<T> = Column::new("b");
1125
1126    impl<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> for T {
1127        fn from_row(_: &'r sqlx::postgres::PgRow) -> Result<Self, sqlx::Error> {
1128            Ok(T)
1129        }
1130    }
1131    impl crate::Hooks for T {}
1132    impl Model for T {
1133        const TABLE: &'static str = "t";
1134        const PRIMARY_KEY: &'static str = "a";
1135        const COLUMNS: &'static [&'static str] = &["a", "b"];
1136        fn primary_key(&self) -> Value {
1137            Value::I32(None)
1138        }
1139        fn values(&self) -> Vec<(&'static str, Value)> {
1140            Vec::new()
1141        }
1142    }
1143
1144    fn render(e: Expr<T>) -> String {
1145        let mut sql = Sql::new();
1146        e.cond.write(&mut sql);
1147        sql.as_str().to_owned()
1148    }
1149
1150    #[test]
1151    fn renders_conditions() {
1152        assert_eq!(render(A.eq(1)), r#""a" = $1"#);
1153        assert_eq!(render(A.eq(1).and(B.gt(2))), r#"("a" = $1 AND "b" > $2)"#);
1154        assert_eq!(
1155            render(A.eq(1).or(B.is_null()).and(!A.is_in([1, 2]))),
1156            r#"(("a" = $1 OR "b" IS NULL) AND NOT ("a" IN ($2, $3)))"#
1157        );
1158        assert_eq!(render(A.is_in(Vec::<i32>::new())), "FALSE");
1159        assert_eq!(render(A.not_in(Vec::<i32>::new())), "TRUE");
1160        assert_eq!(render(A.between(1, 5)), r#""a" BETWEEN $1 AND $2"#);
1161        assert_eq!(render(Expr::all()), "TRUE");
1162        assert_eq!(render(Expr::any_of([])), "FALSE");
1163        assert_eq!(render(Expr::raw("lower(a) = ?", ["x"])), "(lower(a) = $1)");
1164        assert_eq!(
1165            render(A.sum().cast("BIGINT").gt(5)),
1166            r#"CAST(SUM("a") AS BIGINT) > $1"#
1167        );
1168        assert_eq!(render(Projection::count_all().gte(2)), "COUNT(*) >= $1");
1169        assert_eq!(
1170            render(B.count_distinct().eq(1)),
1171            r#"COUNT(DISTINCT "b") = $1"#
1172        );
1173    }
1174
1175    #[test]
1176    fn qualifies_columns_on_request() {
1177        let mut sql = Sql::new();
1178        sql.qualify = true;
1179        A.eq(1)
1180            .and(B.is_null())
1181            .and(A.is_in([2]))
1182            .cond
1183            .write(&mut sql);
1184        assert_eq!(
1185            sql.as_str(),
1186            r#"("t"."a" = $1 AND "t"."b" IS NULL AND "t"."a" IN ($2))"#
1187        );
1188    }
1189
1190    #[test]
1191    fn escapes_like_patterns() {
1192        let mut sql = Sql::new();
1193        A.contains("50%_off").cond.write(&mut sql);
1194        assert_eq!(sql.params()[0], Value::from(r"%50\%\_off%"));
1195    }
1196}