Skip to main content

rok_db_core/
query.rs

1use std::fmt;
2use std::marker::PhantomData;
3use std::time::Duration;
4
5use futures_core::stream::BoxStream;
6use sqlx::postgres::PgRow;
7use sqlx::{Decode, FromRow, Postgres, Row, Type};
8
9use crate::cursor::{Cursor, CursorPage};
10use crate::exec;
11use crate::expr::Direction;
12use crate::expr::{Cond, IntoProjections, Projection};
13use crate::model::{push_columns, push_returning};
14use crate::sql::Sql;
15use crate::{Column, Error, Executor, Expr, Model, Order, Page, Result, Value};
16
17fn push_where<'a>(sql: &mut Sql, keyword: &str, filters: impl IntoIterator<Item = &'a Cond>) {
18    let mut filters = filters.into_iter().peekable();
19    if filters.peek().is_none() {
20        return;
21    }
22    sql.push(keyword);
23    // Groups render their own parentheses, so a top-level AND chain is safe.
24    sql.push_list(filters, " AND ", |sql, cond| cond.write(sql));
25}
26
27/// Which soft-deleted rows a query sees (only relevant for models with a
28/// `deleted_at` column).
29#[derive(Debug, Clone, Copy, PartialEq, Eq)]
30pub(crate) enum Trashed {
31    Exclude,
32    Include,
33    Only,
34}
35
36impl Trashed {
37    /// The implicit soft-delete condition for model `M`, if any.
38    fn cond<M: Model>(self) -> Option<Cond> {
39        let column = M::DELETED_AT_COLUMN?;
40        match self {
41            Trashed::Exclude => Some(Column::<M>::new(column).is_null().cond),
42            Trashed::Only => Some(Column::<M>::new(column).is_not_null().cond),
43            Trashed::Include => None,
44        }
45    }
46}
47
48/// Implicit conditions: the tenant, the soft-delete scope and, unless
49/// `unscoped`, the model's default scope.
50fn implicit<M: Model>(trashed: Trashed, scoped: bool, all_tenants: bool) -> Vec<Cond> {
51    let default = scoped.then(M::default_scope).flatten().map(|e| e.cond);
52    tenant_cond::<M>(all_tenants)
53        .into_iter()
54        .chain(trashed.cond::<M>())
55        .chain(default)
56        .collect()
57}
58
59/// `tenant = current` inside a tenant scope; `FALSE` outside one (fail
60/// closed) unless `all_tenants`.
61fn tenant_cond<M: Model>(all_tenants: bool) -> Option<Cond> {
62    let column = M::TENANT_COLUMN.filter(|_| !all_tenants)?;
63    Some(match crate::tenant::current() {
64        Some(tenant) => Column::<M>::new(column).eq(tenant).cond,
65        None => {
66            tracing::warn!(
67                target: "rok_db::tenant",
68                table = M::TABLE,
69                "query on a tenant model outside `with_tenant`; matching no rows (use `all_tenants()` to opt out)"
70            );
71            Expr::<M>::none().cond
72        }
73    })
74}
75
76/// Lock mode appended to a `SELECT`.
77#[derive(Debug, Clone, Copy, PartialEq, Eq)]
78enum Lock {
79    Update,
80    Share,
81}
82
83/// A `SELECT` query over model `M`, created with [`Model::query`] or [`Model::filter`].
84///
85/// Builders are cheap, cloneable values: nothing touches the database until
86/// one of the async terminal methods (`all`, `first`, `count`, …) is awaited.
87pub struct Select<M> {
88    pub(crate) filters: Vec<Cond>,
89    pub(crate) order: Vec<Order<M>>,
90    pub(crate) group_by: Vec<(&'static str, &'static str)>,
91    pub(crate) having: Vec<Cond>,
92    limit: Option<u64>,
93    offset: Option<u64>,
94    lock: Option<Lock>,
95    trashed: Trashed,
96    scoped: bool,
97    primary: bool,
98    all_tenants: bool,
99    pub(crate) joins: Vec<JoinClause>,
100    _model: PhantomData<fn() -> M>,
101}
102
103/// One `JOIN` of a [`Joined`](crate::Joined) query.
104#[derive(Debug, Clone)]
105pub(crate) struct JoinClause {
106    pub(crate) left: bool,
107    pub(crate) table: &'static str,
108    pub(crate) on: Cond,
109    /// The joined model's implicit conditions (tenant, soft delete, default
110    /// scope), evaluated at render time.
111    pub(crate) scope: fn() -> Vec<Cond>,
112    /// Whether this join can produce several rows per root row.
113    pub(crate) multiplies: bool,
114}
115
116/// The implicit conditions of a joined model `N`.
117pub(crate) fn join_scope<N: Model>() -> Vec<Cond> {
118    implicit::<N>(Trashed::Exclude, true, false)
119}
120
121impl<M: Model> Default for Select<M> {
122    fn default() -> Self {
123        Self::new()
124    }
125}
126
127impl<M: Model> Select<M> {
128    /// A `SELECT` over every row of `M`'s table.
129    pub fn new() -> Self {
130        Self {
131            filters: Vec::new(),
132            order: Vec::new(),
133            group_by: Vec::new(),
134            having: Vec::new(),
135            limit: None,
136            offset: None,
137            lock: None,
138            trashed: Trashed::Exclude,
139            scoped: true,
140            primary: false,
141            all_tenants: false,
142            joins: Vec::new(),
143            _model: PhantomData,
144        }
145    }
146
147    /// Add a `WHERE` condition. Multiple calls are combined with `AND`.
148    pub fn filter(mut self, expr: Expr<M>) -> Self {
149        self.filters.push(expr.cond);
150        self
151    }
152
153    /// Query every tenant's rows of a `#[rok(tenant)]` model, ignoring the
154    /// current tenant scope (for admin tools and background jobs).
155    pub fn all_tenants(mut self) -> Self {
156        self.all_tenants = true;
157        self
158    }
159
160    /// Run on the primary even when read replicas are configured, e.g. to
161    /// read your own writes without replication lag.
162    pub fn on_primary(mut self) -> Self {
163        self.primary = true;
164        self
165    }
166
167    /// Whether this read may go to a replica: not forced to the primary and
168    /// not locking rows.
169    pub(crate) fn use_replica(&self) -> bool {
170        !self.primary && self.lock.is_none()
171    }
172
173    /// Apply a reusable query fragment ("named scope"):
174    ///
175    /// ```ignore
176    /// fn active(q: Select<User>) -> Select<User> { q.filter(User::ACTIVE.eq(true)) }
177    /// fn newest(q: Select<User>) -> Select<User> { q.order_by(User::ID.desc()) }
178    ///
179    /// User::query().scope(active).scope(newest).limit(10)
180    /// ```
181    pub fn scope(self, scope: impl FnOnce(Self) -> Self) -> Self {
182        scope(self)
183    }
184
185    /// Drop the model's default scope (`#[rok(default_scope = …)]`) for this
186    /// query. Soft-delete filtering is controlled separately with
187    /// [`with_trashed`](Self::with_trashed).
188    pub fn unscoped(mut self) -> Self {
189        self.scoped = false;
190        self
191    }
192
193    /// Include soft-deleted rows (models with `#[rok(soft_delete)]` exclude
194    /// them by default). No effect on other models.
195    pub fn with_trashed(mut self) -> Self {
196        self.trashed = Trashed::Include;
197        self
198    }
199
200    /// Only soft-deleted rows. No effect on models without soft deletes.
201    pub fn only_trashed(mut self) -> Self {
202        self.trashed = Trashed::Only;
203        self
204    }
205
206    /// Add a condition only when `condition` is true.
207    pub fn filter_if(self, condition: bool, expr: impl FnOnce() -> Expr<M>) -> Self {
208        if condition { self.filter(expr()) } else { self }
209    }
210
211    /// Add a condition built from an optional value, e.g. a search parameter.
212    ///
213    /// ```ignore
214    /// User::query().filter_opt(params.role, |r| User::ROLE.eq(r))
215    /// ```
216    pub fn filter_opt<T>(self, value: Option<T>, expr: impl FnOnce(T) -> Expr<M>) -> Self {
217        match value {
218            Some(v) => self.filter(expr(v)),
219            None => self,
220        }
221    }
222
223    /// Append an `ORDER BY` term. Pass a column for ascending order, or
224    /// `column.desc()`.
225    pub fn order_by(mut self, order: impl Into<Order<M>>) -> Self {
226        self.order.push(order.into());
227        self
228    }
229
230    /// Append a `GROUP BY` column; combine with [`select`](Self::select).
231    pub fn group_by(mut self, column: Column<M>) -> Self {
232        self.group_by.push((M::TABLE, column.name()));
233        self
234    }
235
236    /// Add a `HAVING` condition, typically on an aggregate
237    /// (`User::AGE.avg().gt(30)`). Multiple calls are combined with `AND`.
238    pub fn having(mut self, expr: Expr<M>) -> Self {
239        self.having.push(expr.cond);
240        self
241    }
242
243    /// Limit the number of returned rows.
244    pub fn limit(mut self, limit: u64) -> Self {
245        self.limit = Some(limit);
246        self
247    }
248
249    /// Skip the first `offset` rows.
250    pub fn offset(mut self, offset: u64) -> Self {
251        self.offset = Some(offset);
252        self
253    }
254
255    /// Lock selected rows with `FOR UPDATE` (use inside a transaction).
256    pub fn for_update(mut self) -> Self {
257        self.lock = Some(Lock::Update);
258        self
259    }
260
261    /// Lock selected rows with `FOR SHARE` (use inside a transaction).
262    pub fn for_share(mut self) -> Self {
263        self.lock = Some(Lock::Share);
264        self
265    }
266
267    /// Select specific columns or aggregates instead of whole models.
268    ///
269    /// ```ignore
270    /// let emails: Vec<(i64, String)> = User::query()
271    ///     .select((User::ID, User::EMAIL))
272    ///     .fetch_all(&db)
273    ///     .await?;
274    /// ```
275    pub fn select(self, projections: impl IntoProjections<M>) -> Projected<M> {
276        Projected {
277            select: self,
278            items: projections.into_projections(),
279        }
280    }
281
282    /// Cache this query's results for `ttl` in the pool's
283    /// [`QueryCache`](crate::QueryCache).
284    ///
285    /// Writes made through rok-db invalidate the cached results of the
286    /// table. When the executor has no cache (it wasn't enabled with
287    /// [`DbBuilder::query_cache`](crate::DbBuilder::query_cache), or a plain
288    /// sqlx pool or connection is used) the query simply runs every time.
289    pub fn memoize(self, ttl: Duration) -> Memoized<M> {
290        Memoized { select: self, ttl }
291    }
292
293    /// Turn this query into a bulk `UPDATE` with the same filters.
294    pub fn update(self) -> Update<M> {
295        Update {
296            filters: self.filters,
297            sets: Vec::new(),
298            trashed: self.trashed,
299            scoped: self.scoped,
300            all_tenants: self.all_tenants,
301            _model: PhantomData,
302        }
303    }
304
305    /// Render the `SELECT` statement.
306    pub fn to_sql(&self) -> Sql {
307        let mut sql = self.new_sql();
308        if self.multiplies() {
309            self.write_distinct_rows(&mut sql, "", self.limit, self.offset);
310            return sql;
311        }
312        sql.push("SELECT ");
313        push_columns::<M>(&mut sql);
314        self.write_tail(&mut sql);
315        sql
316    }
317
318    /// A statement buffer; columns are table-qualified when there are joins.
319    pub(crate) fn new_sql(&self) -> Sql {
320        let mut sql = Sql::new();
321        sql.qualify = !self.joins.is_empty();
322        sql
323    }
324
325    /// Whether fetching root rows needs de-duplication: a join may produce
326    /// several rows per root row and there is no `GROUP BY`.
327    fn multiplies(&self) -> bool {
328        self.group_by.is_empty() && self.joins.iter().any(|j| j.multiplies)
329    }
330
331    fn write_primary_keys(sql: &mut Sql) {
332        sql.push_list(M::PRIMARY_KEYS, ", ", |sql, c| {
333            sql.push_column(M::TABLE, c);
334        });
335    }
336
337    /// One row per root record for a query whose joins may multiply rows:
338    ///
339    /// ```sql
340    /// SELECT <head> d.* FROM (
341    ///     SELECT DISTINCT ON (pk) <columns>, <order keys> AS __rok_o{i}
342    ///     FROM … WHERE … ORDER BY pk, <order>
343    /// ) d ORDER BY d.__rok_o{i} … LIMIT … OFFSET …
344    /// ```
345    fn write_distinct_rows(
346        &self,
347        sql: &mut Sql,
348        head: &str,
349        limit: Option<u64>,
350        offset: Option<u64>,
351    ) {
352        sql.push("SELECT ")
353            .push(head)
354            .push(r#""__rok_d".* FROM (SELECT DISTINCT ON ("#);
355        Self::write_primary_keys(sql);
356        sql.push(") ");
357        push_columns::<M>(sql);
358        self.write_order_aliases(sql);
359        self.write_from_where(sql);
360        sql.push(" ORDER BY ");
361        Self::write_primary_keys(sql);
362        for o in &self.order {
363            sql.push(", ");
364            o.write(sql);
365        }
366        sql.push(r#") "__rok_d""#);
367        self.write_alias_order(sql, r#""__rok_d"."#);
368        if let Some(limit) = limit {
369            sql.push(&format!(" LIMIT {limit}"));
370        }
371        if let Some(offset) = offset {
372            sql.push(&format!(" OFFSET {offset}"));
373        }
374    }
375
376    /// `, <order key> AS "__rok_o{i}"` for every order term.
377    fn write_order_aliases(&self, sql: &mut Sql) {
378        for (i, o) in self.order.iter().enumerate() {
379            sql.push(", ");
380            o.write_key(sql, "", None);
381            sql.push(" AS ").push_ident(&format!("__rok_o{i}"));
382        }
383    }
384
385    /// ` ORDER BY <prefix>"__rok_o{i}" …` for every order term.
386    fn write_alias_order(&self, sql: &mut Sql, prefix: &str) {
387        if !self.order.is_empty() {
388            sql.push(" ORDER BY ")
389                .push_list(self.order.iter().enumerate(), ", ", |sql, (i, o)| {
390                    sql.push(prefix).push_ident(&format!("__rok_o{i}"));
391                    o.write_direction(sql);
392                });
393        }
394    }
395
396    /// Everything after the select list.
397    pub(crate) fn write_tail(&self, sql: &mut Sql) {
398        self.write_from_where(sql);
399        if !self.group_by.is_empty() {
400            sql.push(" GROUP BY ")
401                .push_list(&self.group_by, ", ", |sql, (t, c)| {
402                    sql.push_column(t, c);
403                });
404        }
405        push_where(sql, " HAVING ", &self.having);
406        self.write_order(sql, "");
407        if let Some(limit) = self.limit {
408            sql.push(&format!(" LIMIT {limit}"));
409        }
410        if let Some(offset) = self.offset {
411            sql.push(&format!(" OFFSET {offset}"));
412        }
413        match self.lock {
414            Some(Lock::Update) => sql.push(" FOR UPDATE"),
415            Some(Lock::Share) => sql.push(" FOR SHARE"),
416            None => sql,
417        };
418    }
419
420    fn write_from_where(&self, sql: &mut Sql) {
421        sql.push(" FROM ").push_ident(M::TABLE);
422        for join in &self.joins {
423            sql.push(if join.left {
424                " LEFT JOIN "
425            } else {
426                " INNER JOIN "
427            })
428            .push_ident(join.table)
429            .push(" ON ");
430            let scope = (join.scope)();
431            sql.push_list(
432                std::iter::once(&join.on).chain(&scope),
433                " AND ",
434                |sql, c| c.write(sql),
435            );
436        }
437        let implicit = implicit::<M>(self.trashed, self.scoped, self.all_tenants);
438        push_where(sql, " WHERE ", self.filters.iter().chain(&implicit));
439    }
440
441    /// `SELECT 1 FROM … WHERE …`, the body of an `EXISTS` subquery.
442    pub(crate) fn write_exists_body(&self, sql: &mut Sql) {
443        sql.push("SELECT 1");
444        self.write_tail(sql);
445    }
446
447    /// `ORDER BY …`; with a `prefix` (the outer query of `paginate`),
448    /// expression orders refer to their `__rok_o{i}` aliases.
449    fn write_order(&self, sql: &mut Sql, prefix: &str) {
450        if !self.order.is_empty() {
451            sql.push(" ORDER BY ")
452                .push_list(self.order.iter().enumerate(), ", ", |sql, (i, o)| {
453                    let alias = (!prefix.is_empty()).then(|| format!("__rok_o{i}"));
454                    o.write_key(sql, prefix, alias.as_deref());
455                    o.write_direction(sql);
456                });
457        }
458    }
459
460    fn count_sql(&self) -> Sql {
461        let mut sql = self.new_sql();
462        self.write_count_head(&mut sql);
463        self.write_from_where(&mut sql);
464        sql
465    }
466
467    /// `SELECT COUNT(*)`, or `COUNT(DISTINCT pk)` when joins may multiply rows.
468    fn write_count_head(&self, sql: &mut Sql) {
469        if self.multiplies() {
470            sql.push("SELECT COUNT(DISTINCT ");
471            if M::PRIMARY_KEYS.len() > 1 {
472                sql.push("(");
473                Self::write_primary_keys(sql);
474                sql.push(")");
475            } else {
476                Self::write_primary_keys(sql);
477            }
478            sql.push(")");
479        } else {
480            sql.push("SELECT COUNT(*)");
481        }
482    }
483
484    fn exists_sql(&self) -> Sql {
485        let mut sql = self.new_sql();
486        sql.push("SELECT EXISTS(SELECT 1");
487        self.write_from_where(&mut sql);
488        sql.push(")");
489        sql
490    }
491
492    fn aggregate_sql(&self, projection: Projection<M>) -> Sql {
493        let mut sql = Sql::new();
494        sql.push("SELECT ");
495        projection.write(&mut sql);
496        self.write_from_where(&mut sql);
497        sql
498    }
499
500    /// Fetch every matching row.
501    pub async fn all<'e, E: Executor<'e>>(self, executor: E) -> Result<Vec<M>> {
502        exec::fetch_all(executor, &self.to_sql(), &[], self.use_replica()).await
503    }
504
505    /// Fetch the first matching row, or `None`.
506    pub async fn first<'e, E: Executor<'e>>(self, executor: E) -> Result<Option<M>> {
507        let replica = self.use_replica();
508        exec::fetch_optional(executor, &self.limit(1).to_sql(), &[], replica).await
509    }
510
511    /// Fetch the first matching row, failing with [`Error::NotFound`].
512    pub async fn one<'e, E: Executor<'e>>(self, executor: E) -> Result<M> {
513        self.first(executor)
514            .await?
515            .ok_or_else(|| Error::not_found::<M>(None))
516    }
517
518    /// Stream matching rows one at a time instead of loading them all into
519    /// memory. Consume it with `futures::TryStreamExt` (re-exported in the
520    /// rok-db prelude):
521    ///
522    /// ```ignore
523    /// let mut users = User::query().stream(&db);
524    /// while let Some(user) = users.try_next().await? {
525    ///     // …
526    /// }
527    /// ```
528    pub fn stream<'e, E: Executor<'e> + 'e>(self, executor: E) -> BoxStream<'e, Result<M>> {
529        let replica = self.use_replica();
530        exec::stream(executor, self.to_sql(), replica)
531    }
532
533    /// Count the matching rows (ignores ordering, limit and offset).
534    pub async fn count<'e, E: Executor<'e>>(self, executor: E) -> Result<i64> {
535        exec::fetch_scalar(executor, &self.count_sql(), self.use_replica()).await
536    }
537
538    /// `true` if at least one row matches.
539    pub async fn exists<'e, E: Executor<'e>>(self, executor: E) -> Result<bool> {
540        exec::fetch_scalar(executor, &self.exists_sql(), self.use_replica()).await
541    }
542
543    /// `SUM(column)` over the matching rows as `BIGINT` (`None` when no
544    /// rows match). Use [`select`](Self::select) with
545    /// [`Projection::cast`] for other result types.
546    pub async fn sum<'e, E: Executor<'e>>(
547        self,
548        executor: E,
549        column: Column<M>,
550    ) -> Result<Option<i64>> {
551        let sql = self.aggregate_sql(column.sum().cast("BIGINT"));
552        exec::fetch_scalar(executor, &sql, self.use_replica()).await
553    }
554
555    /// `AVG(column)` over the matching rows as `DOUBLE PRECISION`.
556    pub async fn avg<'e, E: Executor<'e>>(
557        self,
558        executor: E,
559        column: Column<M>,
560    ) -> Result<Option<f64>> {
561        let sql = self.aggregate_sql(column.avg().cast("DOUBLE PRECISION"));
562        exec::fetch_scalar(executor, &sql, self.use_replica()).await
563    }
564
565    /// `MIN(column)` over the matching rows.
566    pub async fn min<'e, T, E>(self, executor: E, column: Column<M>) -> Result<Option<T>>
567    where
568        T: Type<Postgres> + for<'r> Decode<'r, Postgres> + Send + Unpin,
569        E: Executor<'e>,
570    {
571        exec::fetch_scalar(
572            executor,
573            &self.aggregate_sql(column.min()),
574            self.use_replica(),
575        )
576        .await
577    }
578
579    /// `MAX(column)` over the matching rows.
580    pub async fn max<'e, T, E>(self, executor: E, column: Column<M>) -> Result<Option<T>>
581    where
582        T: Type<Postgres> + for<'r> Decode<'r, Postgres> + Send + Unpin,
583        E: Executor<'e>,
584    {
585        exec::fetch_scalar(
586            executor,
587            &self.aggregate_sql(column.max()),
588            self.use_replica(),
589        )
590        .await
591    }
592
593    /// Fetch one page of results together with the total number of matches,
594    /// in a single round trip. `page` is 1-based.
595    pub async fn paginate<'e, E: Executor<'e>>(
596        self,
597        executor: E,
598        page: u64,
599        per_page: u64,
600    ) -> Result<Page<M>> {
601        if per_page == 0 {
602            return Err(Error::InvalidQuery(
603                "`per_page` must be greater than 0".into(),
604            ));
605        }
606        let page = page.max(1);
607        let rows = exec::fetch_rows(
608            executor,
609            &self.paginate_sql(page, per_page),
610            &[],
611            self.use_replica(),
612        )
613        .await?;
614
615        let mut total = 0;
616        let mut items = Vec::with_capacity(rows.len());
617        for row in &rows {
618            total = row.try_get::<i64, _>("__rok_total")?;
619            if row.try_get::<Option<bool>, _>("__rok_present")?.is_some() {
620                items.push(M::from_row(row)?);
621            }
622        }
623        Ok(Page::new(items, total.max(0) as u64, page, per_page))
624    }
625
626    fn paginate_sql(&self, page: u64, per_page: u64) -> Sql {
627        if !self.joins.is_empty() {
628            return self.joined_paginate_sql(page, per_page);
629        }
630        // `count LEFT JOIN LATERAL (page)` always yields at least one row, so
631        // the total is known even when the requested page is empty.
632        let mut sql = Sql::new();
633        sql.push(r#"SELECT c."__rok_total", p.* FROM (SELECT COUNT(*) AS "__rok_total""#);
634        self.write_from_where(&mut sql);
635        sql.push(r#") c LEFT JOIN LATERAL (SELECT TRUE AS "__rok_present", "#);
636        push_columns::<M>(&mut sql);
637        // Expression orders are selected so the outer query can sort by them.
638        for (i, o) in self.order.iter().enumerate() {
639            if o.column().is_none() {
640                sql.push(", ");
641                o.write_key(&mut sql, "", None);
642                sql.push(" AS ").push_ident(&format!("__rok_o{i}"));
643            }
644        }
645        self.write_from_where(&mut sql);
646        self.write_order(&mut sql, "");
647        let offset = (page - 1).saturating_mul(per_page);
648        sql.push(&format!(" LIMIT {per_page} OFFSET {offset}) p ON TRUE"));
649        self.write_order(&mut sql, "p.");
650        sql
651    }
652
653    /// [`paginate_sql`](Self::paginate_sql) for queries with joins: every
654    /// order key is selected under an alias so the outer query can sort by
655    /// it, and multiplying joins are de-duplicated.
656    fn joined_paginate_sql(&self, page: u64, per_page: u64) -> Sql {
657        let mut sql = self.new_sql();
658        sql.push(r#"SELECT c."__rok_total", p.* FROM ("#);
659        self.write_count_head(&mut sql);
660        sql.push(r#" AS "__rok_total""#);
661        self.write_from_where(&mut sql);
662        sql.push(") c LEFT JOIN LATERAL (");
663        let offset = (page - 1).saturating_mul(per_page);
664        let head = r#"TRUE AS "__rok_present", "#;
665        if self.multiplies() {
666            self.write_distinct_rows(&mut sql, head, Some(per_page), Some(offset));
667        } else {
668            sql.push("SELECT ").push(head);
669            push_columns::<M>(&mut sql);
670            self.write_order_aliases(&mut sql);
671            self.write_from_where(&mut sql);
672            self.write_order(&mut sql, "");
673            sql.push(&format!(" LIMIT {per_page} OFFSET {offset}"));
674        }
675        sql.push(") p ON TRUE");
676        self.write_alias_order(&mut sql, "p.");
677        sql
678    }
679
680    /// Delete every matching row and return how many were deleted.
681    ///
682    /// For models with soft deletes this sets `deleted_at = now()` instead;
683    /// use [`force_delete`](Self::force_delete) to remove rows for good.
684    pub async fn delete<'e, E: Executor<'e>>(self, executor: E) -> Result<u64> {
685        exec::execute(executor, &self.delete_sql(), &[M::TABLE]).await
686    }
687
688    /// Render the statement [`delete`](Self::delete) would run.
689    pub fn delete_sql(&self) -> Sql {
690        match M::DELETED_AT_COLUMN {
691            Some(deleted_at) => self
692                .clone()
693                .update()
694                .set_raw(Column::new(deleted_at), "now()", [] as [Value; 0])
695                .to_sql(),
696            None => self.force_delete_sql(),
697        }
698    }
699
700    /// `DELETE` every matching row, even for models with soft deletes.
701    /// Soft-deleted rows are only matched with
702    /// [`with_trashed`](Self::with_trashed) or [`only_trashed`](Self::only_trashed).
703    pub async fn force_delete<'e, E: Executor<'e>>(self, executor: E) -> Result<u64> {
704        exec::execute(executor, &self.force_delete_sql(), &[M::TABLE]).await
705    }
706
707    pub(crate) fn force_delete_sql(&self) -> Sql {
708        let mut sql = Sql::new();
709        sql.push("DELETE");
710        self.write_from_where(&mut sql);
711        sql
712    }
713
714    /// Restore every matching soft-deleted row (`deleted_at = NULL`) and
715    /// return how many were restored.
716    pub async fn restore<'e, E: Executor<'e>>(self, executor: E) -> Result<u64> {
717        let Some(deleted_at) = M::DELETED_AT_COLUMN else {
718            return Err(Error::InvalidQuery(format!(
719                "`{}` has no soft-delete column",
720                M::TABLE
721            )));
722        };
723        self.only_trashed()
724            .update()
725            .set_raw(Column::new(deleted_at), "NULL", [] as [Value; 0])
726            .exec(executor)
727            .await
728    }
729
730    /// Every table this query reads: the root and joined tables.
731    pub(crate) fn tables(&self) -> Vec<&'static str> {
732        std::iter::once(M::TABLE)
733            .chain(self.joins.iter().map(|j| j.table))
734            .collect()
735    }
736
737    /// A fresh `SELECT` of `M` restricted to the root rows this (joined)
738    /// query matches: `pk IN (SELECT root.pk FROM … JOIN … WHERE …)`.
739    /// Used for bulk writes through joins.
740    pub(crate) fn key_subselect(&self) -> Select<M> {
741        let inner = self.clone();
742        let render = crate::expr::Render::new(move |sql: &mut Sql| {
743            let previous = std::mem::replace(&mut sql.qualify, true);
744            sql.push("SELECT ");
745            Self::write_primary_keys(sql);
746            inner.write_from_where(sql);
747            sql.qualify = previous;
748        });
749        let kind = if M::PRIMARY_KEYS.len() == 1 {
750            crate::expr::SubKind::In {
751                table: M::TABLE,
752                column: M::PRIMARY_KEY,
753                negated: false,
754            }
755        } else {
756            crate::expr::SubKind::InRow {
757                table: M::TABLE,
758                columns: M::PRIMARY_KEYS,
759            }
760        };
761        Select {
762            filters: vec![Cond::Sub { kind, render }],
763            trashed: self.trashed,
764            scoped: self.scoped,
765            all_tenants: self.all_tenants,
766            ..Select::new()
767        }
768    }
769
770    /// The ordering used for keyset pagination: the query's `ORDER BY`
771    /// columns plus the primary key as a tiebreaker.
772    fn keyset_order(&self) -> Vec<(&'static str, Direction)> {
773        let mut order: Vec<_> = self
774            .order
775            .iter()
776            .filter_map(|o| o.column().map(|c| (c, o.direction)))
777            .collect();
778        for key in M::PRIMARY_KEYS {
779            if !order.iter().any(|(c, _)| c == key) {
780                order.push((key, Direction::Asc));
781            }
782        }
783        order
784    }
785
786    /// Keyset ("cursor") pagination: fetch up to `limit` rows after
787    /// `cursor`, following the query's `ORDER BY` (the primary key is added
788    /// as a tiebreaker). Unlike [`paginate`](Self::paginate), the cost
789    /// doesn't grow with the page number and rows aren't skipped or repeated
790    /// when data changes between requests.
791    ///
792    /// Sort keys (columns or expressions) must be `NOT NULL`; expression
793    /// orders and joined queries paginate over a wrapped subquery. Pass
794    /// `None` for the first page,
795    /// then `page.next` for the following ones:
796    ///
797    /// ```ignore
798    /// let page = Post::order_by(Post::CREATED_AT.desc())
799    ///     .cursor_paginate(&db, None, 20)
800    ///     .await?;
801    /// let token = page.next.map(|c| c.to_string()); // hand to the client
802    ///
803    /// let cursor: Cursor = token.unwrap().parse()?;
804    /// let page2 = Post::order_by(Post::CREATED_AT.desc())
805    ///     .cursor_paginate(&db, Some(&cursor), 20)
806    ///     .await?;
807    /// ```
808    pub async fn cursor_paginate<'e, E: Executor<'e>>(
809        self,
810        executor: E,
811        after: Option<&Cursor>,
812        limit: u64,
813    ) -> Result<CursorPage<M>> {
814        if limit == 0 {
815            return Err(Error::InvalidQuery("`limit` must be greater than 0".into()));
816        }
817        if self.needs_wrapped_keyset() {
818            return self.wrapped_cursor_paginate(executor, after, limit).await;
819        }
820        let select = self.cursor_select(after, limit)?;
821        let order = select.keyset_order();
822        let mut items = select.all(executor).await?;
823        let next = if items.len() as u64 > limit {
824            items.truncate(limit as usize);
825            let last = items.last().expect("limit > 0");
826            let values = order
827                .iter()
828                .map(|(c, _)| {
829                    last.value_of(c).ok_or_else(|| {
830                        Error::InvalidQuery(format!("`{c}` is not a column of `{}`", M::TABLE))
831                    })
832                })
833                .collect::<Result<Vec<_>>>()?;
834            Some(Cursor::new(values))
835        } else {
836            None
837        };
838        Ok(CursorPage { items, next })
839    }
840
841    /// Joined queries and expression orders paginate over aliased sort keys.
842    fn needs_wrapped_keyset(&self) -> bool {
843        !self.joins.is_empty() || self.order.iter().any(|o| o.column().is_none())
844    }
845
846    /// The order with the root primary key appended as a tiebreaker.
847    fn keyset_orders(&self) -> Vec<Order<M>> {
848        let mut order = self.order.clone();
849        for key in M::PRIMARY_KEYS {
850            let present = order.iter().any(|o| {
851                matches!(o.target, crate::expr::OrderTarget::Column { table, name } if table == M::TABLE && name == *key)
852            });
853            if !present {
854                order.push(Column::<M>::new(key).asc());
855            }
856        }
857        order
858    }
859
860    /// ```sql
861    /// SELECT * FROM (
862    ///     SELECT [DISTINCT ON (pk)] <columns>, <sort keys> AS __rok_o{i} FROM … WHERE …
863    /// ) k WHERE (k.__rok_o0 > $ OR (k.__rok_o0 = $ AND k.__rok_o1 > $) …)
864    /// ORDER BY k.__rok_o0, … LIMIT n + 1
865    /// ```
866    fn wrapped_cursor_sql(&self, after: Option<&Cursor>, limit: u64) -> Result<(Sql, usize)> {
867        const ALIASES: [&str; 16] = [
868            "__rok_o0",
869            "__rok_o1",
870            "__rok_o2",
871            "__rok_o3",
872            "__rok_o4",
873            "__rok_o5",
874            "__rok_o6",
875            "__rok_o7",
876            "__rok_o8",
877            "__rok_o9",
878            "__rok_o10",
879            "__rok_o11",
880            "__rok_o12",
881            "__rok_o13",
882            "__rok_o14",
883            "__rok_o15",
884        ];
885        let mut keyed = self.clone();
886        keyed.order = self.keyset_orders();
887        let n = keyed.order.len();
888        if n > ALIASES.len() {
889            return Err(Error::InvalidQuery(
890                "too many sort keys for keyset pagination".into(),
891            ));
892        }
893        let mut sql = keyed.new_sql();
894        sql.qualify = true;
895        sql.push("SELECT * FROM (SELECT ");
896        let multiplies = keyed.multiplies();
897        if multiplies {
898            sql.push("DISTINCT ON (");
899            Self::write_primary_keys(&mut sql);
900            sql.push(") ");
901        }
902        push_columns::<M>(&mut sql);
903        keyed.write_order_aliases(&mut sql);
904        keyed.write_from_where(&mut sql);
905        if multiplies {
906            sql.push(" ORDER BY ");
907            Self::write_primary_keys(&mut sql);
908            for o in &keyed.order {
909                sql.push(", ");
910                o.write(&mut sql);
911            }
912        }
913        sql.push(r#") "__rok_k""#);
914        let key = |i: usize| crate::expr::Term::Column {
915            table: "__rok_k",
916            name: ALIASES[i],
917        };
918        if let Some(cursor) = after {
919            if cursor.values.len() != n {
920                return Err(Error::InvalidCursor(format!(
921                    "expected {n} values, got {}",
922                    cursor.values.len()
923                )));
924            }
925            let cmp = |i: usize, op: &'static str| Cond::Cmp {
926                term: key(i),
927                op,
928                value: cursor.values[i].clone(),
929            };
930            let branches = (0..n)
931                .map(|i| {
932                    let step = match keyed.order[i].direction {
933                        crate::expr::Direction::Asc => cmp(i, ">"),
934                        crate::expr::Direction::Desc => cmp(i, "<"),
935                    };
936                    Cond::And((0..i).map(|j| cmp(j, "=")).chain([step]).collect())
937                })
938                .collect();
939            sql.push(" WHERE ");
940            Cond::Or(branches).write(&mut sql);
941        }
942        sql.push(" ORDER BY ").push_list(0..n, ", ", |sql, i| {
943            key(i).write(sql);
944            keyed.order[i].write_direction(sql);
945        });
946        sql.push(&format!(" LIMIT {}", limit + 1));
947        Ok((sql, n))
948    }
949
950    async fn wrapped_cursor_paginate<'e, E: Executor<'e>>(
951        self,
952        executor: E,
953        after: Option<&Cursor>,
954        limit: u64,
955    ) -> Result<CursorPage<M>> {
956        let (sql, n) = self.wrapped_cursor_sql(after, limit)?;
957        let rows = exec::fetch_rows(executor, &sql, &[], self.use_replica()).await?;
958        let more = rows.len() as u64 > limit;
959        let kept = &rows[..rows.len().min(limit as usize)];
960        let items = kept
961            .iter()
962            .map(M::from_row)
963            .collect::<std::result::Result<Vec<_>, _>>()?;
964        let next = match kept.last() {
965            Some(last) if more => Some(Cursor::new(
966                (0..n)
967                    .map(|i| Value::from_row_column(last, &format!("__rok_o{i}")))
968                    .collect::<Result<Vec<_>>>()?,
969            )),
970            _ => None,
971        };
972        Ok(CursorPage { items, next })
973    }
974
975    fn cursor_select(self, after: Option<&Cursor>, limit: u64) -> Result<Self> {
976        let order = self.keyset_order();
977        let mut select = self;
978        select.order = order
979            .iter()
980            .map(|(c, d)| match d {
981                Direction::Asc => Column::new(c).asc(),
982                Direction::Desc => Column::new(c).desc(),
983            })
984            .collect();
985        select.offset = None;
986        select.limit = Some(limit + 1);
987        if let Some(cursor) = after {
988            if cursor.values.len() != order.len() {
989                return Err(Error::InvalidCursor(format!(
990                    "expected {} values, got {}",
991                    order.len(),
992                    cursor.values.len()
993                )));
994            }
995            // (a > x) OR (a = x AND b > y) OR … — works for mixed directions.
996            let branches = (0..order.len()).map(|i| {
997                let eqs = (0..i).map(|j| Column::<M>::new(order[j].0).eq(cursor.values[j].clone()));
998                let (column, direction) = order[i];
999                let value = cursor.values[i].clone();
1000                let step = match direction {
1001                    Direction::Asc => Column::<M>::new(column).gt(value),
1002                    Direction::Desc => Column::<M>::new(column).lt(value),
1003                };
1004                Expr::all_of(eqs.chain([step]))
1005            });
1006            select = select.filter(Expr::any_of(branches));
1007        }
1008        Ok(select)
1009    }
1010}
1011
1012impl<M> Clone for Select<M> {
1013    fn clone(&self) -> Self {
1014        Self {
1015            filters: self.filters.clone(),
1016            order: self.order.clone(),
1017            group_by: self.group_by.clone(),
1018            having: self.having.clone(),
1019            limit: self.limit,
1020            offset: self.offset,
1021            lock: self.lock,
1022            trashed: self.trashed,
1023            scoped: self.scoped,
1024            primary: self.primary,
1025            all_tenants: self.all_tenants,
1026            joins: self.joins.clone(),
1027            _model: PhantomData,
1028        }
1029    }
1030}
1031
1032impl<M: Model> fmt::Debug for Select<M> {
1033    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1034        f.debug_tuple("Select").field(&self.to_sql()).finish()
1035    }
1036}
1037
1038/// A `SELECT` of specific columns or aggregates, created with
1039/// [`Select::select`]. Decode rows into tuples or any `sqlx::FromRow` type.
1040pub struct Projected<M> {
1041    pub(crate) select: Select<M>,
1042    pub(crate) items: Vec<Projection<M>>,
1043}
1044
1045impl<M: Model> Projected<M> {
1046    /// Render the `SELECT` statement.
1047    pub fn to_sql(&self) -> Sql {
1048        let mut sql = self.select.new_sql();
1049        self.write_into(&mut sql);
1050        sql
1051    }
1052
1053    pub(crate) fn write_into(&self, sql: &mut Sql) {
1054        sql.push("SELECT ");
1055        sql.push_list(&self.items, ", ", |sql, p| p.write(sql));
1056        self.select.write_tail(sql);
1057    }
1058
1059    /// Fetch every row, decoded into `T` (a tuple or a `FromRow` type).
1060    pub async fn fetch_all<'e, T, E>(self, executor: E) -> Result<Vec<T>>
1061    where
1062        T: for<'r> FromRow<'r, PgRow> + Send + Unpin,
1063        E: Executor<'e>,
1064    {
1065        exec::fetch_all(executor, &self.to_sql(), &[], self.select.use_replica()).await
1066    }
1067
1068    /// Fetch the first row, or `None`.
1069    pub async fn fetch_optional<'e, T, E>(self, executor: E) -> Result<Option<T>>
1070    where
1071        T: for<'r> FromRow<'r, PgRow> + Send + Unpin,
1072        E: Executor<'e>,
1073    {
1074        let projected = Projected {
1075            select: self.select.limit(1),
1076            items: self.items,
1077        };
1078        let replica = projected.select.use_replica();
1079        exec::fetch_optional(executor, &projected.to_sql(), &[], replica).await
1080    }
1081
1082    /// Fetch the first row, failing with [`Error::NotFound`].
1083    pub async fn fetch_one<'e, T, E>(self, executor: E) -> Result<T>
1084    where
1085        T: for<'r> FromRow<'r, PgRow> + Send + Unpin,
1086        E: Executor<'e>,
1087    {
1088        self.fetch_optional(executor)
1089            .await?
1090            .ok_or_else(|| Error::not_found::<M>(None))
1091    }
1092
1093    /// Fetch the first column of the first row.
1094    pub async fn scalar<'e, T, E>(self, executor: E) -> Result<T>
1095    where
1096        T: Type<Postgres> + for<'r> Decode<'r, Postgres> + Send + Unpin,
1097        E: Executor<'e>,
1098    {
1099        exec::fetch_scalar(executor, &self.to_sql(), self.select.use_replica()).await
1100    }
1101
1102    /// Stream rows one at a time.
1103    pub fn stream<'e, T, E>(self, executor: E) -> BoxStream<'e, Result<T>>
1104    where
1105        T: for<'r> FromRow<'r, PgRow> + Send + Unpin + 'e,
1106        E: Executor<'e> + 'e,
1107    {
1108        let replica = self.select.use_replica();
1109        exec::stream(executor, self.to_sql(), replica)
1110    }
1111}
1112
1113impl<M> Clone for Projected<M> {
1114    fn clone(&self) -> Self {
1115        Self {
1116            select: self.select.clone(),
1117            items: self.items.clone(),
1118        }
1119    }
1120}
1121
1122impl<M: Model> fmt::Debug for Projected<M> {
1123    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1124        f.debug_tuple("Projected").field(&self.to_sql()).finish()
1125    }
1126}
1127
1128/// A [`Select`] whose results are cached, created with [`Select::memoize`].
1129///
1130/// Results are cloned out of the cache, so the model must be `Clone`.
1131pub struct Memoized<M> {
1132    select: Select<M>,
1133    ttl: Duration,
1134}
1135
1136impl<M: Model + Clone> Memoized<M> {
1137    async fn cached<'e, T, E, F, Fut>(&self, executor: E, op: &str, sql: &Sql, run: F) -> Result<T>
1138    where
1139        T: Clone + Send + Sync + 'static,
1140        E: Executor<'e>,
1141        F: FnOnce(E) -> Fut,
1142        Fut: Future<Output = Result<T>>,
1143    {
1144        let cache = executor.__context().and_then(|c| c.cache.clone());
1145        let Some(cache) = cache else {
1146            return run(executor).await;
1147        };
1148        let key = format!(
1149            "{op}\u{1f}{}\u{1f}{}\u{1f}{:?}",
1150            std::any::type_name::<M>(),
1151            sql.as_str(),
1152            sql.params()
1153        );
1154        if let Some(hit) = cache.get::<T>(&key) {
1155            tracing::debug!(target: "rok_db::cache", table = M::TABLE, op, "cache hit");
1156            return Ok(hit);
1157        }
1158        let stamp = cache.stamp(&self.select.tables());
1159        let value = run(executor).await?;
1160        cache.put(key, stamp, self.ttl, value.clone());
1161        Ok(value)
1162    }
1163
1164    /// Fetch every matching row, from the cache when possible.
1165    pub async fn all<'e, E: Executor<'e>>(self, executor: E) -> Result<Vec<M>> {
1166        let sql = self.select.to_sql();
1167        let replica = self.select.use_replica();
1168        self.cached(executor, "all", &sql, |e| {
1169            exec::fetch_all(e, &sql, &[], replica)
1170        })
1171        .await
1172    }
1173
1174    /// Fetch the first matching row, from the cache when possible.
1175    pub async fn first<'e, E: Executor<'e>>(self, executor: E) -> Result<Option<M>> {
1176        let sql = self.select.clone().limit(1).to_sql();
1177        self.cached(executor, "first", &sql, |e| {
1178            exec::fetch_optional(e, &sql, &[], self.select.use_replica())
1179        })
1180        .await
1181    }
1182
1183    /// Fetch the first matching row, failing with [`Error::NotFound`].
1184    pub async fn one<'e, E: Executor<'e>>(self, executor: E) -> Result<M> {
1185        self.first(executor)
1186            .await?
1187            .ok_or_else(|| Error::not_found::<M>(None))
1188    }
1189
1190    /// Count the matching rows, from the cache when possible.
1191    pub async fn count<'e, E: Executor<'e>>(self, executor: E) -> Result<i64> {
1192        let sql = self.select.count_sql();
1193        let replica = self.select.use_replica();
1194        self.cached(executor, "count", &sql, |e| {
1195            exec::fetch_scalar(e, &sql, replica)
1196        })
1197        .await
1198    }
1199
1200    /// `true` if at least one row matches, from the cache when possible.
1201    pub async fn exists<'e, E: Executor<'e>>(self, executor: E) -> Result<bool> {
1202        let sql = self.select.exists_sql();
1203        let replica = self.select.use_replica();
1204        self.cached(executor, "exists", &sql, |e| {
1205            exec::fetch_scalar(e, &sql, replica)
1206        })
1207        .await
1208    }
1209
1210    /// Fetch one page, from the cache when possible.
1211    pub async fn paginate<'e, E: Executor<'e>>(
1212        self,
1213        executor: E,
1214        page: u64,
1215        per_page: u64,
1216    ) -> Result<Page<M>> {
1217        let sql = self.select.paginate_sql(page.max(1), per_page);
1218        let select = self.select.clone();
1219        self.cached(executor, "paginate", &sql, |e| {
1220            select.paginate(e, page, per_page)
1221        })
1222        .await
1223    }
1224}
1225
1226impl<M> Clone for Memoized<M> {
1227    fn clone(&self) -> Self {
1228        Self {
1229            select: self.select.clone(),
1230            ttl: self.ttl,
1231        }
1232    }
1233}
1234
1235impl<M: Model> fmt::Debug for Memoized<M> {
1236    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1237        f.debug_struct("Memoized")
1238            .field("sql", &self.select.to_sql())
1239            .field("ttl", &self.ttl)
1240            .finish()
1241    }
1242}
1243
1244/// A bulk `UPDATE` over model `M`, created with [`Model::update_all`] or
1245/// [`Select::update`].
1246///
1247/// Managed columns are maintained automatically unless you set them
1248/// yourself: `updated_at = now()` for models with timestamps and
1249/// `version = version + 1` for models with optimistic locking. Soft-deleted
1250/// rows are skipped unless the query used `with_trashed`/`only_trashed`.
1251pub struct Update<M> {
1252    filters: Vec<Cond>,
1253    sets: Vec<(&'static str, Assign)>,
1254    trashed: Trashed,
1255    scoped: bool,
1256    all_tenants: bool,
1257    _model: PhantomData<fn() -> M>,
1258}
1259
1260#[derive(Debug, Clone)]
1261enum Assign {
1262    Value(Value),
1263    Raw(String, Vec<Value>),
1264}
1265
1266impl<M: Model> Update<M> {
1267    pub(crate) fn new() -> Self {
1268        Self {
1269            filters: Vec::new(),
1270            sets: Vec::new(),
1271            trashed: Trashed::Exclude,
1272            scoped: true,
1273            all_tenants: false,
1274            _model: PhantomData,
1275        }
1276    }
1277
1278    /// Update every tenant's rows, ignoring the current tenant scope.
1279    pub fn all_tenants(mut self) -> Self {
1280        self.all_tenants = true;
1281        self
1282    }
1283
1284    /// Ignore the model's default scope.
1285    pub fn unscoped(mut self) -> Self {
1286        self.scoped = false;
1287        self
1288    }
1289
1290    /// Add a `WHERE` condition. Multiple calls are combined with `AND`.
1291    pub fn filter(mut self, expr: Expr<M>) -> Self {
1292        self.filters.push(expr.cond);
1293        self
1294    }
1295
1296    /// Also update soft-deleted rows.
1297    pub fn with_trashed(mut self) -> Self {
1298        self.trashed = Trashed::Include;
1299        self
1300    }
1301
1302    /// `SET column = value`
1303    pub fn set(mut self, column: Column<M>, value: impl Into<Value>) -> Self {
1304        self.sets.push((column.name(), Assign::Value(value.into())));
1305        self
1306    }
1307
1308    /// `SET column = <raw sql>`, binding each `?` to the next parameter.
1309    ///
1310    /// ```ignore
1311    /// Post::update_all().set_raw(Post::PUBLISHED_AT, "now()", [] as [i32; 0])
1312    /// ```
1313    pub fn set_raw<V: Into<Value>>(
1314        mut self,
1315        column: Column<M>,
1316        sql: impl Into<String>,
1317        params: impl IntoIterator<Item = V>,
1318    ) -> Self {
1319        let params = params.into_iter().map(Into::into).collect();
1320        self.sets
1321            .push((column.name(), Assign::Raw(sql.into(), params)));
1322        self
1323    }
1324
1325    /// `SET column = column + by`
1326    pub fn increment(self, column: Column<M>, by: impl Into<Value>) -> Self {
1327        let raw = format!("{} + ?", quoted(column.name()));
1328        self.set_raw(column, raw, [by.into()])
1329    }
1330
1331    /// `SET column = column - by`
1332    pub fn decrement(self, column: Column<M>, by: impl Into<Value>) -> Self {
1333        let raw = format!("{} - ?", quoted(column.name()));
1334        self.set_raw(column, raw, [by.into()])
1335    }
1336
1337    pub(crate) fn has_sets(&self) -> bool {
1338        !self.sets.is_empty()
1339    }
1340
1341    pub(crate) fn into_select(self) -> Select<M> {
1342        Select {
1343            filters: self.filters,
1344            trashed: self.trashed,
1345            scoped: self.scoped,
1346            all_tenants: self.all_tenants,
1347            ..Select::new()
1348        }
1349    }
1350
1351    fn is_set(&self, column: &str) -> bool {
1352        self.sets.iter().any(|(c, _)| *c == column)
1353    }
1354
1355    /// Render the `UPDATE` statement.
1356    pub fn to_sql(&self) -> Sql {
1357        let mut sql = Sql::new();
1358        self.write_into(&mut sql);
1359        sql
1360    }
1361
1362    pub(crate) fn write_into(&self, sql: &mut Sql) {
1363        sql.push("UPDATE ").push_ident(M::TABLE).push(" SET ");
1364        sql.push_list(&self.sets, ", ", |sql, (column, assign)| {
1365            sql.push_ident(column).push(" = ");
1366            match assign {
1367                Assign::Value(v) => sql.bind(v.clone()),
1368                Assign::Raw(raw, params) => sql.push_raw(raw, params),
1369            };
1370        });
1371        if let Some(updated_at) = M::UPDATED_AT_COLUMN {
1372            if !self.is_set(updated_at) {
1373                sql.push(", ").push_ident(updated_at).push(" = now()");
1374            }
1375        }
1376        if let Some(version) = M::VERSION_COLUMN {
1377            if !self.is_set(version) {
1378                sql.push(", ")
1379                    .push_ident(version)
1380                    .push(" = ")
1381                    .push_ident(version)
1382                    .push(" + 1");
1383            }
1384        }
1385        let implicit = implicit::<M>(self.trashed, self.scoped, self.all_tenants);
1386        push_where(sql, " WHERE ", self.filters.iter().chain(&implicit));
1387    }
1388
1389    fn check(&self) -> Result<()> {
1390        if let Some(tenant) = M::TENANT_COLUMN {
1391            if self.is_set(tenant) && crate::tenant::current().is_some() {
1392                return Err(Error::InvalidQuery(format!(
1393                    "can't change the tenant column `{tenant}` inside a tenant scope"
1394                )));
1395            }
1396        }
1397        if self.sets.is_empty() {
1398            return Err(Error::InvalidQuery(format!(
1399                "UPDATE on `{}` has no columns to set",
1400                M::TABLE
1401            )));
1402        }
1403        Ok(())
1404    }
1405
1406    /// Run the update and return the number of affected rows.
1407    pub async fn exec<'e, E: Executor<'e>>(self, executor: E) -> Result<u64> {
1408        self.check()?;
1409        exec::execute(executor, &self.to_sql(), &[M::TABLE]).await
1410    }
1411
1412    /// Run the update and return the updated rows.
1413    pub async fn returning<'e, E: Executor<'e>>(self, executor: E) -> Result<Vec<M>> {
1414        self.check()?;
1415        let mut sql = self.to_sql();
1416        push_returning::<M>(&mut sql);
1417        exec::fetch_all(executor, &sql, &[M::TABLE], false).await
1418    }
1419
1420    pub(crate) async fn returning_one<'e, E: Executor<'e>>(self, executor: E) -> Result<Option<M>> {
1421        let mut sql = self.to_sql();
1422        push_returning::<M>(&mut sql);
1423        exec::fetch_optional(executor, &sql, &[M::TABLE], false).await
1424    }
1425}
1426
1427impl<M> Clone for Update<M> {
1428    fn clone(&self) -> Self {
1429        Self {
1430            filters: self.filters.clone(),
1431            sets: self.sets.clone(),
1432            trashed: self.trashed,
1433            scoped: self.scoped,
1434            all_tenants: self.all_tenants,
1435            _model: PhantomData,
1436        }
1437    }
1438}
1439
1440impl<M: Model> fmt::Debug for Update<M> {
1441    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1442        f.debug_tuple("Update").field(&self.to_sql()).finish()
1443    }
1444}
1445
1446fn quoted(ident: &str) -> String {
1447    let mut s = String::new();
1448    crate::sql::push_ident(&mut s, ident);
1449    s
1450}
1451
1452// ----- ON CONFLICT -------------------------------------------------------------
1453
1454#[derive(Debug, Clone)]
1455pub(crate) enum ConflictTarget {
1456    None,
1457    Columns(Vec<&'static str>),
1458    Constraint(&'static str),
1459}
1460
1461#[derive(Debug, Clone)]
1462pub(crate) enum ConflictAction {
1463    Nothing,
1464    Update(Vec<&'static str>),
1465    UpdateAll,
1466}
1467
1468/// An `ON CONFLICT` clause.
1469#[derive(Debug, Clone)]
1470pub(crate) struct Conflict {
1471    pub(crate) target: ConflictTarget,
1472    pub(crate) action: ConflictAction,
1473}
1474
1475impl Conflict {
1476    /// Write ` ON CONFLICT … DO …` for an insert of `inserted` columns.
1477    pub(crate) fn write<M: Model>(&self, sql: &mut Sql, inserted: &[&'static str]) -> Result<()> {
1478        sql.push(" ON CONFLICT");
1479        let target_columns: &[&str] = match &self.target {
1480            ConflictTarget::None => &[],
1481            ConflictTarget::Columns(columns) => {
1482                sql.push(" (")
1483                    .push_list(columns, ", ", |sql, c| {
1484                        sql.push_ident(c);
1485                    })
1486                    .push(")");
1487                columns
1488            }
1489            ConflictTarget::Constraint(name) => {
1490                sql.push(" ON CONSTRAINT ").push_ident(name);
1491                &[]
1492            }
1493        };
1494        let mut updates: Vec<&'static str> = match &self.action {
1495            ConflictAction::Nothing => {
1496                sql.push(" DO NOTHING");
1497                return Ok(());
1498            }
1499            ConflictAction::Update(columns) => columns.clone(),
1500            ConflictAction::UpdateAll => inserted
1501                .iter()
1502                .copied()
1503                .filter(|c| {
1504                    !target_columns.contains(c)
1505                        && !M::PRIMARY_KEYS.contains(c)
1506                        && M::CREATED_AT_COLUMN != Some(*c)
1507                })
1508                .collect(),
1509        };
1510        if matches!(self.target, ConflictTarget::None) {
1511            return Err(Error::InvalidQuery(
1512                "ON CONFLICT DO UPDATE needs conflict columns or a constraint".into(),
1513            ));
1514        }
1515        // Managed columns are maintained, never copied from the new row; the
1516        // tenant of an existing row never changes.
1517        updates.retain(|c| {
1518            M::VERSION_COLUMN != Some(*c)
1519                && M::UPDATED_AT_COLUMN != Some(*c)
1520                && M::TENANT_COLUMN != Some(*c)
1521        });
1522        sql.push(" DO UPDATE SET ");
1523        let mut first = true;
1524        let mut sep = |sql: &mut Sql| {
1525            if !std::mem::take(&mut first) {
1526                sql.push(", ");
1527            }
1528        };
1529        for column in &updates {
1530            sep(sql);
1531            sql.push_ident(column)
1532                .push(" = EXCLUDED.")
1533                .push_ident(column);
1534        }
1535        if let Some(updated_at) = M::UPDATED_AT_COLUMN {
1536            sep(sql);
1537            sql.push_ident(updated_at).push(" = now()");
1538        }
1539        if let Some(version) = M::VERSION_COLUMN {
1540            sep(sql);
1541            sql.push_ident(version)
1542                .push(" = ")
1543                .push_ident(M::TABLE)
1544                .push(".")
1545                .push_ident(version)
1546                .push(" + 1");
1547        }
1548        if first {
1549            // Nothing to change, but `DO UPDATE` still returns the row.
1550            sql.push_ident(M::PRIMARY_KEY)
1551                .push(" = ")
1552                .push_ident(M::TABLE)
1553                .push(".")
1554                .push_ident(M::PRIMARY_KEY);
1555        }
1556        // Never update another tenant's row: the conflicting row must belong
1557        // to the tenant being written (no row is returned otherwise).
1558        if let Some(tenant) = M::TENANT_COLUMN {
1559            if inserted.contains(&tenant) {
1560                sql.push(" WHERE ")
1561                    .push_ident(M::TABLE)
1562                    .push(".")
1563                    .push_ident(tenant)
1564                    .push(" = EXCLUDED.")
1565                    .push_ident(tenant);
1566            }
1567        }
1568        Ok(())
1569    }
1570}
1571
1572macro_rules! on_conflict_methods {
1573    () => {
1574        /// Handle conflicts on these columns (a unique index or the primary
1575        /// key). Follow with [`do_nothing`](Self::do_nothing),
1576        /// [`do_update`](Self::do_update) or
1577        /// [`do_update_all`](Self::do_update_all).
1578        pub fn on_conflict(mut self, columns: impl IntoIterator<Item = Column<M>>) -> Self {
1579            self.conflict_target = Some(ConflictTarget::Columns(
1580                columns.into_iter().map(|c| c.name()).collect(),
1581            ));
1582            self
1583        }
1584
1585        /// Handle conflicts on the named constraint.
1586        pub fn on_constraint(mut self, name: &'static str) -> Self {
1587            self.conflict_target = Some(ConflictTarget::Constraint(name));
1588            self
1589        }
1590
1591        /// `ON CONFLICT … DO NOTHING`: skip conflicting rows. Without
1592        /// [`on_conflict`](Self::on_conflict), any conflict is ignored.
1593        pub fn do_nothing(mut self) -> Self {
1594            self.conflict_action = Some(ConflictAction::Nothing);
1595            self
1596        }
1597
1598        /// `ON CONFLICT … DO UPDATE` setting these columns from the new row.
1599        pub fn do_update(mut self, columns: impl IntoIterator<Item = Column<M>>) -> Self {
1600            self.conflict_action = Some(ConflictAction::Update(
1601                columns.into_iter().map(|c| c.name()).collect(),
1602            ));
1603            self
1604        }
1605
1606        /// `ON CONFLICT … DO UPDATE` setting every inserted column except the
1607        /// conflict columns, the primary key and `created_at`. With
1608        /// [`on_constraint`](Self::on_constraint) the constraint's columns
1609        /// aren't known, so they are rewritten with their (equal) new values.
1610        pub fn do_update_all(mut self) -> Self {
1611            self.conflict_action = Some(ConflictAction::UpdateAll);
1612            self
1613        }
1614
1615        fn conflict(&self) -> Result<Option<Conflict>> {
1616            match (&self.conflict_target, &self.conflict_action) {
1617                (None, None) => Ok(None),
1618                (target, Some(action)) => Ok(Some(Conflict {
1619                    target: target.clone().unwrap_or(ConflictTarget::None),
1620                    action: action.clone(),
1621                })),
1622                (Some(_), None) => Err(Error::InvalidQuery(
1623                    "`on_conflict` needs `do_nothing`, `do_update` or `do_update_all`".into(),
1624                )),
1625            }
1626        }
1627    };
1628}
1629
1630/// An `INSERT` of a single row built column by column, created with
1631/// [`Model::create`]. Columns you don't set get their database default;
1632/// timestamp columns default to `now()`.
1633pub struct Insert<M> {
1634    /// `(column, raw sql, params)`; plain values are stored as `"?"`.
1635    values: Vec<(&'static str, String, Vec<Value>)>,
1636    conflict_target: Option<ConflictTarget>,
1637    conflict_action: Option<ConflictAction>,
1638    _model: PhantomData<fn() -> M>,
1639}
1640
1641impl<M: Model> Insert<M> {
1642    pub(crate) fn new() -> Self {
1643        Self {
1644            values: Vec::new(),
1645            conflict_target: None,
1646            conflict_action: None,
1647            _model: PhantomData,
1648        }
1649    }
1650
1651    /// Set `column` to `value`.
1652    pub fn set(mut self, column: Column<M>, value: impl Into<Value>) -> Self {
1653        self.values
1654            .push((column.name(), "?".into(), vec![value.into()]));
1655        self
1656    }
1657
1658    /// Set `column` to raw SQL, binding each `?` to the next parameter.
1659    pub fn set_raw<V: Into<Value>>(
1660        mut self,
1661        column: Column<M>,
1662        sql: impl Into<String>,
1663        params: impl IntoIterator<Item = V>,
1664    ) -> Self {
1665        let params = params.into_iter().map(Into::into).collect();
1666        self.values.push((column.name(), sql.into(), params));
1667        self
1668    }
1669
1670    on_conflict_methods!();
1671
1672    /// Render the `INSERT` statement. An incomplete `ON CONFLICT`
1673    /// configuration is left out here and reported by `exec`.
1674    pub fn to_sql(&self) -> Sql {
1675        self.build(false).expect("lenient rendering never fails")
1676    }
1677
1678    fn build(&self, strict: bool) -> Result<Sql> {
1679        let mut values = self.values.clone();
1680        if let Some(column) = M::TENANT_COLUMN {
1681            if let Some(tenant) = crate::model::tenant_override::<M>(column) {
1682                values.retain(|(c, _, _)| *c != column);
1683                values.push((column, "?".into(), vec![tenant]));
1684            }
1685        }
1686        for ts in [M::CREATED_AT_COLUMN, M::UPDATED_AT_COLUMN]
1687            .into_iter()
1688            .flatten()
1689        {
1690            if !values.iter().any(|(c, _, _)| *c == ts) {
1691                values.push((ts, "now()".into(), Vec::new()));
1692            }
1693        }
1694        let mut sql = Sql::new();
1695        sql.push("INSERT INTO ").push_ident(M::TABLE);
1696        if values.is_empty() {
1697            sql.push(" DEFAULT VALUES");
1698        } else {
1699            sql.push(" (")
1700                .push_list(&values, ", ", |sql, (c, _, _)| {
1701                    sql.push_ident(c);
1702                })
1703                .push(") VALUES (")
1704                .push_list(&values, ", ", |sql, (_, raw, params)| {
1705                    sql.push_raw(raw, params);
1706                })
1707                .push(")");
1708        }
1709        let conflict = match self.conflict() {
1710            Ok(conflict) => conflict,
1711            Err(e) if strict => return Err(e),
1712            Err(_) => None,
1713        };
1714        if let Some(conflict) = conflict {
1715            let inserted: Vec<_> = values.iter().map(|(c, _, _)| *c).collect();
1716            let mut with_conflict = sql.clone();
1717            match conflict.write::<M>(&mut with_conflict, &inserted) {
1718                Ok(()) => sql = with_conflict,
1719                Err(e) if strict => return Err(e),
1720                Err(_) => {}
1721            }
1722        }
1723        push_returning::<M>(&mut sql);
1724        Ok(sql)
1725    }
1726
1727    /// Run the insert and return the stored row. With
1728    /// [`do_nothing`](Self::do_nothing), a skipped row is an
1729    /// [`Error::NotFound`]; use [`exec_optional`](Self::exec_optional).
1730    pub async fn exec<'e, E: Executor<'e>>(self, executor: E) -> Result<M> {
1731        self.exec_optional(executor)
1732            .await?
1733            .ok_or_else(|| Error::not_found::<M>(None))
1734    }
1735
1736    /// Run the insert and return the stored row, or `None` if it was
1737    /// skipped by `ON CONFLICT … DO NOTHING`.
1738    pub async fn exec_optional<'e, E: Executor<'e>>(self, executor: E) -> Result<Option<M>> {
1739        exec::fetch_optional(executor, &self.build(true)?, &[M::TABLE], false).await
1740    }
1741}
1742
1743impl<M> Clone for Insert<M> {
1744    fn clone(&self) -> Self {
1745        Self {
1746            values: self.values.clone(),
1747            conflict_target: self.conflict_target.clone(),
1748            conflict_action: self.conflict_action.clone(),
1749            _model: PhantomData,
1750        }
1751    }
1752}
1753
1754impl<M: Model> fmt::Debug for Insert<M> {
1755    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1756        f.debug_tuple("Insert").field(&self.to_sql()).finish()
1757    }
1758}
1759
1760/// An `INSERT` of whole records, created with [`Model::insert_many`].
1761/// Generated columns are skipped and timestamps are set to `now()`.
1762pub struct InsertMany<'a, M> {
1763    records: &'a [M],
1764    conflict_target: Option<ConflictTarget>,
1765    conflict_action: Option<ConflictAction>,
1766}
1767
1768impl<'a, M: Model> InsertMany<'a, M> {
1769    pub(crate) fn new(records: &'a [M]) -> Self {
1770        Self {
1771            records,
1772            conflict_target: None,
1773            conflict_action: None,
1774        }
1775    }
1776
1777    on_conflict_methods!();
1778
1779    /// Render the `INSERT` statement. An incomplete `ON CONFLICT`
1780    /// configuration is left out here and reported by `exec`.
1781    pub fn to_sql(&self) -> Sql {
1782        let conflict = self.conflict().ok().flatten();
1783        crate::model::insert_sql(self.records, false, conflict.as_ref())
1784            .or_else(|_| crate::model::insert_sql(self.records, false, None))
1785            .expect("rendering without ON CONFLICT never fails")
1786    }
1787
1788    fn build(&self) -> Result<Sql> {
1789        crate::model::insert_sql(self.records, false, self.conflict()?.as_ref())
1790    }
1791
1792    /// Run the insert and return the stored rows. Rows skipped by
1793    /// `ON CONFLICT … DO NOTHING` are not returned.
1794    ///
1795    /// Every record is validated and passed to `before_insert` first; each
1796    /// returned row is passed to `after_insert`.
1797    pub async fn exec<'e, E: Executor<'e>>(self, executor: E) -> Result<Vec<M>> {
1798        if self.records.is_empty() {
1799            return Ok(Vec::new());
1800        }
1801        for record in self.records {
1802            crate::model::pre_insert(record)?;
1803        }
1804        let rows: Vec<M> = exec::fetch_all(executor, &self.build()?, &[M::TABLE], false).await?;
1805        for row in &rows {
1806            row.after_insert()?;
1807        }
1808        Ok(rows)
1809    }
1810}
1811
1812impl<M> Clone for InsertMany<'_, M> {
1813    fn clone(&self) -> Self {
1814        Self {
1815            records: self.records,
1816            conflict_target: self.conflict_target.clone(),
1817            conflict_action: self.conflict_action.clone(),
1818        }
1819    }
1820}
1821
1822impl<M: Model> fmt::Debug for InsertMany<'_, M> {
1823    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1824        f.debug_tuple("InsertMany").field(&self.to_sql()).finish()
1825    }
1826}
1827
1828#[doc(hidden)]
1829pub fn __paginate_sql<M: Model>(select: &Select<M>, page: u64, per_page: u64) -> Sql {
1830    select.paginate_sql(page, per_page)
1831}
1832
1833#[doc(hidden)]
1834pub fn __cursor_sql<M: Model>(
1835    select: Select<M>,
1836    after: Option<&Cursor>,
1837    limit: u64,
1838) -> Result<Sql> {
1839    if select.needs_wrapped_keyset() {
1840        return Ok(select.wrapped_cursor_sql(after, limit)?.0);
1841    }
1842    Ok(select.cursor_select(after, limit)?.to_sql())
1843}