cast_core/
query.rs

1//! Query builder. Typed where clauses on top of sea-query + sqlx.
2//!
3//! Mirrors Eloquent's full query-builder surface: `where`/`orWhere` family,
4//! `whereIn`/`whereNull`/`whereBetween`/`whereLike`, aggregates
5//! (`sum`/`avg`/`min`/`max`/`exists`), sorting (`latest`/`oldest`/`inRandomOrder`),
6//! terminals (`pluck`/`value`/`firstOrFail`), joins, group-by/having, and
7//! soft-delete scopes (`withTrashed`/`onlyTrashed`/`withoutTrashed`).
8//!
9//! WHERE conditions are tracked as a single `Option<SimpleExpr>` and folded
10//! left-to-right with AND/OR junctions — so `where_eq.where_eq.or_where_eq`
11//! becomes `((a AND b) OR c)`. To group explicitly, build the SimpleExpr by hand
12//! and pass to `where_raw`.
13
14use std::marker::PhantomData;
15
16use sea_query::{Expr, Order, PostgresQueryBuilder, Query, SimpleExpr};
17use sea_query_binder::SqlxBinder;
18use sqlx::{postgres::PgRow, FromRow};
19
20use crate::column::Column;
21use crate::model::Model;
22use crate::Error;
23
24#[derive(Clone, Copy, PartialEq, Eq)]
25enum SoftDeleteMode {
26    /// Apply the deleted_at IS NULL filter if the model derives `#[soft_deletes]`.
27    Default,
28    /// `.with_trashed()` — include soft-deleted rows.
29    WithTrashed,
30    /// `.only_trashed()` — only soft-deleted rows.
31    OnlyTrashed,
32    /// `.without_trashed()` — explicitly exclude soft-deleted rows.
33    WithoutTrashed,
34}
35
36pub struct QueryBuilder<M: Model> {
37    select: sea_query::SelectStatement,
38    where_clause: Option<SimpleExpr>,
39    soft_delete_mode: SoftDeleteMode,
40    _marker: PhantomData<M>,
41}
42
43impl<M: Model> Default for QueryBuilder<M> {
44    fn default() -> Self {
45        Self::new()
46    }
47}
48
49impl<M: Model> Clone for QueryBuilder<M> {
50    fn clone(&self) -> Self {
51        Self {
52            select: self.select.clone(),
53            where_clause: self.where_clause.clone(),
54            soft_delete_mode: self.soft_delete_mode,
55            _marker: PhantomData,
56        }
57    }
58}
59
60impl<M: Model> QueryBuilder<M>
61where
62    for<'r> M: FromRow<'r, PgRow>,
63{
64    pub fn new() -> Self {
65        let mut select = Query::select();
66        select.from(sea_query::Alias::new(M::TABLE));
67        // Fully-qualify every column with the table name so joins disambiguate
68        // shared column names (`id`, `created_at`, etc.).
69        for c in M::COLUMNS {
70            select.column((sea_query::Alias::new(M::TABLE), sea_query::Alias::new(*c)));
71        }
72        Self {
73            select,
74            where_clause: None,
75            soft_delete_mode: SoftDeleteMode::Default,
76            _marker: PhantomData,
77        }
78    }
79
80    /// Build a fully-qualified `table.column` expression so joins disambiguate.
81    fn col_of(name: &str) -> sea_query::Expr {
82        sea_query::Expr::col((sea_query::Alias::new(M::TABLE), sea_query::Alias::new(name)))
83    }
84
85    fn add_and(mut self, expr: SimpleExpr) -> Self {
86        self.where_clause = Some(match self.where_clause.take() {
87            None => expr,
88            Some(prev) => prev.and(expr),
89        });
90        self
91    }
92
93    fn add_or(mut self, expr: SimpleExpr) -> Self {
94        self.where_clause = Some(match self.where_clause.take() {
95            None => expr,
96            Some(prev) => prev.or(expr),
97        });
98        self
99    }
100
101    // ── Selection / projection ───────────────────────────────────────────────
102
103    pub fn select_only(mut self, columns: &[&str]) -> Self {
104        self.select.clear_selects();
105        for c in columns {
106            self.select.column(sea_query::Alias::new(*c));
107        }
108        self
109    }
110
111    pub fn distinct(mut self) -> Self {
112        self.select.distinct();
113        self
114    }
115
116    // ── Equality / comparison ────────────────────────────────────────────────
117
118    pub fn where_eq<T>(self, column: Column<M, T>, value: T) -> Self
119    where
120        T: Into<sea_query::Value>,
121    {
122        self.add_and(Self::col_of(column.name()).eq(value))
123    }
124
125    pub fn or_where_eq<T>(self, column: Column<M, T>, value: T) -> Self
126    where
127        T: Into<sea_query::Value>,
128    {
129        self.add_or(Self::col_of(column.name()).eq(value))
130    }
131
132    pub fn where_ne<T>(self, column: Column<M, T>, value: T) -> Self
133    where
134        T: Into<sea_query::Value>,
135    {
136        self.add_and(Self::col_of(column.name()).ne(value))
137    }
138
139    pub fn or_where_ne<T>(self, column: Column<M, T>, value: T) -> Self
140    where
141        T: Into<sea_query::Value>,
142    {
143        self.add_or(Self::col_of(column.name()).ne(value))
144    }
145
146    pub fn where_gt<T>(self, column: Column<M, T>, value: T) -> Self
147    where
148        T: Into<sea_query::Value>,
149    {
150        self.add_and(Self::col_of(column.name()).gt(value))
151    }
152
153    pub fn or_where_gt<T>(self, column: Column<M, T>, value: T) -> Self
154    where
155        T: Into<sea_query::Value>,
156    {
157        self.add_or(Self::col_of(column.name()).gt(value))
158    }
159
160    pub fn where_gte<T>(self, column: Column<M, T>, value: T) -> Self
161    where
162        T: Into<sea_query::Value>,
163    {
164        self.add_and(Self::col_of(column.name()).gte(value))
165    }
166
167    pub fn or_where_gte<T>(self, column: Column<M, T>, value: T) -> Self
168    where
169        T: Into<sea_query::Value>,
170    {
171        self.add_or(Self::col_of(column.name()).gte(value))
172    }
173
174    pub fn where_lt<T>(self, column: Column<M, T>, value: T) -> Self
175    where
176        T: Into<sea_query::Value>,
177    {
178        self.add_and(Self::col_of(column.name()).lt(value))
179    }
180
181    pub fn or_where_lt<T>(self, column: Column<M, T>, value: T) -> Self
182    where
183        T: Into<sea_query::Value>,
184    {
185        self.add_or(Self::col_of(column.name()).lt(value))
186    }
187
188    pub fn where_lte<T>(self, column: Column<M, T>, value: T) -> Self
189    where
190        T: Into<sea_query::Value>,
191    {
192        self.add_and(Self::col_of(column.name()).lte(value))
193    }
194
195    pub fn or_where_lte<T>(self, column: Column<M, T>, value: T) -> Self
196    where
197        T: Into<sea_query::Value>,
198    {
199        self.add_or(Self::col_of(column.name()).lte(value))
200    }
201
202    // ── IN / NOT IN ──────────────────────────────────────────────────────────
203
204    pub fn where_in<T, I>(self, column: Column<M, T>, values: I) -> Self
205    where
206        T: Into<sea_query::Value>,
207        I: IntoIterator<Item = T>,
208    {
209        self.add_and(Self::col_of(column.name()).is_in(values))
210    }
211
212    pub fn or_where_in<T, I>(self, column: Column<M, T>, values: I) -> Self
213    where
214        T: Into<sea_query::Value>,
215        I: IntoIterator<Item = T>,
216    {
217        self.add_or(Self::col_of(column.name()).is_in(values))
218    }
219
220    pub fn where_not_in<T, I>(self, column: Column<M, T>, values: I) -> Self
221    where
222        T: Into<sea_query::Value>,
223        I: IntoIterator<Item = T>,
224    {
225        self.add_and(Self::col_of(column.name()).is_not_in(values))
226    }
227
228    pub fn or_where_not_in<T, I>(self, column: Column<M, T>, values: I) -> Self
229    where
230        T: Into<sea_query::Value>,
231        I: IntoIterator<Item = T>,
232    {
233        self.add_or(Self::col_of(column.name()).is_not_in(values))
234    }
235
236    // ── NULL / NOT NULL ──────────────────────────────────────────────────────
237
238    pub fn where_null<T>(self, column: Column<M, T>) -> Self {
239        self.add_and(Self::col_of(column.name()).is_null())
240    }
241
242    pub fn or_where_null<T>(self, column: Column<M, T>) -> Self {
243        self.add_or(Self::col_of(column.name()).is_null())
244    }
245
246    pub fn where_not_null<T>(self, column: Column<M, T>) -> Self {
247        self.add_and(Self::col_of(column.name()).is_not_null())
248    }
249
250    pub fn or_where_not_null<T>(self, column: Column<M, T>) -> Self {
251        self.add_or(Self::col_of(column.name()).is_not_null())
252    }
253
254    // ── BETWEEN ──────────────────────────────────────────────────────────────
255
256    pub fn where_between<T>(self, column: Column<M, T>, low: T, high: T) -> Self
257    where
258        T: Into<sea_query::Value>,
259    {
260        self.add_and(Self::col_of(column.name()).between(low, high))
261    }
262
263    pub fn or_where_between<T>(self, column: Column<M, T>, low: T, high: T) -> Self
264    where
265        T: Into<sea_query::Value>,
266    {
267        self.add_or(Self::col_of(column.name()).between(low, high))
268    }
269
270    pub fn where_not_between<T>(self, column: Column<M, T>, low: T, high: T) -> Self
271    where
272        T: Into<sea_query::Value>,
273    {
274        self.add_and(Self::col_of(column.name()).not_between(low, high))
275    }
276
277    // ── LIKE ─────────────────────────────────────────────────────────────────
278
279    pub fn where_like(self, column: Column<M, String>, pattern: impl Into<String>) -> Self {
280        self.add_and(Self::col_of(column.name()).like(pattern.into()))
281    }
282
283    pub fn or_where_like(self, column: Column<M, String>, pattern: impl Into<String>) -> Self {
284        self.add_or(Self::col_of(column.name()).like(pattern.into()))
285    }
286
287    pub fn where_not_like(self, column: Column<M, String>, pattern: impl Into<String>) -> Self {
288        self.add_and(Self::col_of(column.name()).not_like(pattern.into()))
289    }
290
291    // ── Column comparison ────────────────────────────────────────────────────
292
293    pub fn where_column<T>(self, a: Column<M, T>, b: Column<M, T>) -> Self {
294        self.add_and(Self::col_of(a.name()).equals((
295            sea_query::Alias::new(M::TABLE),
296            sea_query::Alias::new(b.name()),
297        )))
298    }
299
300    // ── Raw escape hatches ───────────────────────────────────────────────────
301
302    pub fn where_raw(self, raw: SimpleExpr) -> Self {
303        self.add_and(raw)
304    }
305
306    pub fn or_where_raw(self, raw: SimpleExpr) -> Self {
307        self.add_or(raw)
308    }
309
310    pub fn where_sql(self, sql: impl Into<String>) -> Self {
311        self.add_and(Expr::cust(sql.into()))
312    }
313
314    pub fn or_where_sql(self, sql: impl Into<String>) -> Self {
315        self.add_or(Expr::cust(sql.into()))
316    }
317
318    // ── Joins ────────────────────────────────────────────────────────────────
319
320    /// `INNER JOIN table ON left_column = right_column`. Mirrors Eloquent's `join`.
321    /// Columns are passed as fully-qualified strings (e.g. `"users.id"`).
322    pub fn join(mut self, table: &str, left_column: &str, right_column: &str) -> Self {
323        self.select.inner_join(
324            sea_query::Alias::new(table),
325            Expr::cust(&format!("{left_column} = {right_column}")),
326        );
327        self
328    }
329
330    /// `LEFT JOIN table ON ...`.
331    pub fn left_join(mut self, table: &str, left_column: &str, right_column: &str) -> Self {
332        self.select.left_join(
333            sea_query::Alias::new(table),
334            Expr::cust(&format!("{left_column} = {right_column}")),
335        );
336        self
337    }
338
339    /// `RIGHT JOIN table ON ...`.
340    pub fn right_join(mut self, table: &str, left_column: &str, right_column: &str) -> Self {
341        self.select.right_join(
342            sea_query::Alias::new(table),
343            Expr::cust(&format!("{left_column} = {right_column}")),
344        );
345        self
346    }
347
348    /// `CROSS JOIN table` (no ON clause).
349    pub fn cross_join(mut self, table: &str) -> Self {
350        self.select
351            .cross_join(sea_query::Alias::new(table), Expr::cust("TRUE"));
352        self
353    }
354
355    // ── Group / Having ───────────────────────────────────────────────────────
356
357    pub fn group_by<T>(mut self, column: Column<M, T>) -> Self {
358        self.select
359            .add_group_by([Self::col_of(column.name()).into()]);
360        self
361    }
362
363    /// `GROUP BY raw_sql`.
364    pub fn group_by_raw(mut self, raw: impl Into<String>) -> Self {
365        self.select.add_group_by([Expr::cust(&raw.into())]);
366        self
367    }
368
369    pub fn having(mut self, expr: SimpleExpr) -> Self {
370        self.select.and_having(expr);
371        self
372    }
373
374    pub fn having_raw(mut self, sql: impl Into<String>) -> Self {
375        self.select.and_having(Expr::cust(&sql.into()));
376        self
377    }
378
379    // ── Soft deletes ─────────────────────────────────────────────────────────
380
381    /// Include soft-deleted rows. Eloquent's `->withTrashed()`.
382    pub fn with_trashed(mut self) -> Self {
383        self.soft_delete_mode = SoftDeleteMode::WithTrashed;
384        self
385    }
386
387    /// Only soft-deleted rows. Eloquent's `->onlyTrashed()`.
388    pub fn only_trashed(mut self) -> Self {
389        self.soft_delete_mode = SoftDeleteMode::OnlyTrashed;
390        self
391    }
392
393    /// Explicitly exclude soft-deleted rows (this is the default for models
394    /// with `#[soft_deletes]`). Eloquent's `->withoutTrashed()`.
395    pub fn without_trashed(mut self) -> Self {
396        self.soft_delete_mode = SoftDeleteMode::WithoutTrashed;
397        self
398    }
399
400    // ── whereHas / withCount (relationship-aware subqueries) ─────────────────
401
402    /// Filter parent rows by the existence of related child rows. Mirrors
403    /// Eloquent's `->whereHas('posts', fn ($q) => $q->where(...))`.
404    ///
405    /// Emits `WHERE EXISTS (SELECT 1 FROM child WHERE child.fk = parent.lk AND <closure conditions>)`.
406    ///
407    /// ```ignore
408    /// User::query()
409    ///     .where_has(User::posts_rel(), |q| q.where_eq(Post::columns().published(), true))
410    ///     .get(pool).await?;
411    /// ```
412    pub fn where_has<R, F>(self, _rel: R, f: F) -> Self
413    where
414        R: crate::relation::RelationDef<Parent = M>,
415        R::Child: Model,
416        for<'r> R::Child: FromRow<'r, PgRow>,
417        F: FnOnce(QueryBuilder<R::Child>) -> QueryBuilder<R::Child>,
418    {
419        let exists_expr = build_exists_subquery::<M, R, F>(f, false);
420        self.add_and(exists_expr)
421    }
422
423    /// Negated form of `where_has`. Mirrors Eloquent's `->whereDoesntHave(...)`.
424    pub fn where_doesnt_have<R, F>(self, _rel: R, f: F) -> Self
425    where
426        R: crate::relation::RelationDef<Parent = M>,
427        R::Child: Model,
428        for<'r> R::Child: FromRow<'r, PgRow>,
429        F: FnOnce(QueryBuilder<R::Child>) -> QueryBuilder<R::Child>,
430    {
431        let exists_expr = build_exists_subquery::<M, R, F>(f, true);
432        self.add_and(exists_expr)
433    }
434
435    /// OR-combined `where_has`.
436    pub fn or_where_has<R, F>(self, _rel: R, f: F) -> Self
437    where
438        R: crate::relation::RelationDef<Parent = M>,
439        R::Child: Model,
440        for<'r> R::Child: FromRow<'r, PgRow>,
441        F: FnOnce(QueryBuilder<R::Child>) -> QueryBuilder<R::Child>,
442    {
443        let exists_expr = build_exists_subquery::<M, R, F>(f, false);
444        self.add_or(exists_expr)
445    }
446
447    /// Pagination. Mirrors Eloquent's `->paginate($perPage, ['*'], 'page', $page)`.
448    pub async fn paginate(
449        self,
450        per_page: u64,
451        page: u64,
452        pool: &sqlx::PgPool,
453    ) -> Result<crate::paginator::Paginator<M>, Error> {
454        let total = self.clone().count(pool).await?;
455        let page = page.max(1);
456        let per_page = per_page.max(1);
457        let items = self
458            .skip((page - 1) * per_page)
459            .take(per_page)
460            .get(pool)
461            .await?;
462        Ok(crate::paginator::Paginator::new(
463            items, total, per_page, page,
464        ))
465    }
466
467    /// Fetch the parent rows along with a related-row count. Mirrors Eloquent's
468    /// `->withCount('posts')`. Returns `Vec<(M, i64)>` instead of dynamic attributes.
469    ///
470    /// ```ignore
471    /// let users_with_counts: Vec<(User, i64)> = User::query()
472    ///     .with_count_of(User::posts_rel(), pool)
473    ///     .await?;
474    /// ```
475    pub async fn with_count_of<R>(
476        self,
477        _rel: R,
478        pool: &sqlx::PgPool,
479    ) -> Result<Vec<(M, i64)>, Error>
480    where
481        R: crate::relation::RelationDef<Parent = M>,
482        R::Child: Model,
483    {
484        use sqlx::Row as _;
485        const COUNT_ALIAS: &str = "__related_count";
486        // Build the existing SELECT, then add a correlated subquery column.
487        let mut select = self.prepare();
488        let subquery_sql = format!(
489            "(SELECT COUNT(*) FROM {child} WHERE {child}.{fk} = {parent}.{lk})",
490            child = R::Child::TABLE,
491            fk = R::foreign_key(),
492            parent = M::TABLE,
493            lk = R::local_key(),
494        );
495        select.expr_as(
496            Expr::cust(&subquery_sql),
497            sea_query::Alias::new(COUNT_ALIAS),
498        );
499        let (sql, values) = select.build_sqlx(PostgresQueryBuilder);
500        let rows = sqlx::query_with(&sql, values).fetch_all(pool).await?;
501        let mut out = Vec::with_capacity(rows.len());
502        for row in &rows {
503            let model = <M as FromRow<PgRow>>::from_row(row)?;
504            let count: i64 = row.try_get(COUNT_ALIAS)?;
505            out.push((model, count));
506        }
507        Ok(out)
508    }
509
510    // ── Order / pagination ───────────────────────────────────────────────────
511
512    pub fn order_by<T>(mut self, column: Column<M, T>, ascending: bool) -> Self {
513        self.select.order_by(
514            sea_query::Alias::new(column.name()),
515            if ascending { Order::Asc } else { Order::Desc },
516        );
517        self
518    }
519
520    pub fn order_by_asc<T>(self, column: Column<M, T>) -> Self {
521        self.order_by(column, true)
522    }
523
524    pub fn order_by_desc<T>(self, column: Column<M, T>) -> Self {
525        self.order_by(column, false)
526    }
527
528    pub fn latest(mut self) -> Self {
529        self.select
530            .order_by(sea_query::Alias::new("created_at"), Order::Desc);
531        self
532    }
533
534    pub fn oldest(mut self) -> Self {
535        self.select
536            .order_by(sea_query::Alias::new("created_at"), Order::Asc);
537        self
538    }
539
540    pub fn latest_by<T>(self, column: Column<M, T>) -> Self {
541        self.order_by_desc(column)
542    }
543
544    pub fn oldest_by<T>(self, column: Column<M, T>) -> Self {
545        self.order_by_asc(column)
546    }
547
548    pub fn in_random_order(mut self) -> Self {
549        self.select
550            .order_by_expr(Expr::cust("RANDOM()"), Order::Asc);
551        self
552    }
553
554    pub fn reorder(mut self) -> Self {
555        self.select.clear_order_by();
556        self
557    }
558
559    pub fn limit(mut self, n: u64) -> Self {
560        self.select.limit(n);
561        self
562    }
563
564    pub fn take(self, n: u64) -> Self {
565        self.limit(n)
566    }
567
568    pub fn offset(mut self, n: u64) -> Self {
569        self.select.offset(n);
570        self
571    }
572
573    pub fn skip(self, n: u64) -> Self {
574        self.offset(n)
575    }
576
577    // ── Internal: prepare the SelectStatement for execution ──────────────────
578
579    /// Apply the accumulated `where_clause` + the soft-delete filter to a clone
580    /// of `select`, ready to be passed to sea-query.
581    fn prepare(&self) -> sea_query::SelectStatement {
582        let mut select = self.select.clone();
583        let mut combined = self.where_clause.clone();
584
585        // Apply the soft-delete filter if the model opted in.
586        if M::SOFT_DELETES {
587            let deleted_at = Expr::col(sea_query::Alias::new("deleted_at"));
588            let filter = match self.soft_delete_mode {
589                SoftDeleteMode::Default | SoftDeleteMode::WithoutTrashed => {
590                    Some(deleted_at.is_null())
591                }
592                SoftDeleteMode::OnlyTrashed => Some(deleted_at.is_not_null()),
593                SoftDeleteMode::WithTrashed => None,
594            };
595            if let Some(f) = filter {
596                combined = Some(match combined {
597                    None => f,
598                    Some(prev) => prev.and(f),
599                });
600            }
601        }
602
603        if let Some(w) = combined {
604            select.and_where(w);
605        }
606        select
607    }
608
609    // ── Terminals: rows ──────────────────────────────────────────────────────
610
611    pub async fn get(self, pool: &sqlx::PgPool) -> Result<Vec<M>, Error> {
612        let select = self.prepare();
613        let (sql, values) = select.build_sqlx(PostgresQueryBuilder);
614        let rows = sqlx::query_as_with::<_, M, _>(&sql, values)
615            .fetch_all(pool)
616            .await?;
617        Ok(rows)
618    }
619
620    pub async fn first(self, pool: &sqlx::PgPool) -> Result<Option<M>, Error> {
621        let mut select = self.prepare();
622        select.limit(1);
623        let (sql, values) = select.build_sqlx(PostgresQueryBuilder);
624        let row = sqlx::query_as_with::<_, M, _>(&sql, values)
625            .fetch_optional(pool)
626            .await?;
627        Ok(row)
628    }
629
630    pub async fn first_or_fail(self, pool: &sqlx::PgPool) -> Result<M, Error> {
631        self.first(pool).await?.ok_or(Error::NotFound)
632    }
633
634    pub async fn pluck<T>(self, column: Column<M, T>, pool: &sqlx::PgPool) -> Result<Vec<T>, Error>
635    where
636        T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
637    {
638        let mut select = self.prepare();
639        select.clear_selects();
640        select.column(sea_query::Alias::new(column.name()));
641        let (sql, values) = select.build_sqlx(PostgresQueryBuilder);
642        let rows: Vec<(T,)> = sqlx::query_as_with(&sql, values).fetch_all(pool).await?;
643        Ok(rows.into_iter().map(|(v,)| v).collect())
644    }
645
646    pub async fn value<T>(
647        self,
648        column: Column<M, T>,
649        pool: &sqlx::PgPool,
650    ) -> Result<Option<T>, Error>
651    where
652        T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
653    {
654        let mut select = self.prepare();
655        select.clear_selects();
656        select.column(sea_query::Alias::new(column.name()));
657        select.limit(1);
658        let (sql, values) = select.build_sqlx(PostgresQueryBuilder);
659        let row: Option<(T,)> = sqlx::query_as_with(&sql, values)
660            .fetch_optional(pool)
661            .await?;
662        Ok(row.map(|(v,)| v))
663    }
664
665    // ── Terminals: aggregates ────────────────────────────────────────────────
666
667    pub async fn count(self, pool: &sqlx::PgPool) -> Result<i64, Error> {
668        self.aggregate_i64(pool, "COUNT(*)").await
669    }
670
671    pub async fn min<T>(self, column: Column<M, T>, pool: &sqlx::PgPool) -> Result<Option<T>, Error>
672    where
673        T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
674    {
675        self.aggregate_one_value(pool, &format!("MIN({})", column.name()))
676            .await
677    }
678
679    pub async fn max<T>(self, column: Column<M, T>, pool: &sqlx::PgPool) -> Result<Option<T>, Error>
680    where
681        T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
682    {
683        self.aggregate_one_value(pool, &format!("MAX({})", column.name()))
684            .await
685    }
686
687    pub async fn sum<T>(self, column: Column<M, T>, pool: &sqlx::PgPool) -> Result<i64, Error> {
688        self.aggregate_i64(
689            pool,
690            &format!("COALESCE(SUM({})::BIGINT, 0)", column.name()),
691        )
692        .await
693    }
694
695    pub async fn avg<T>(
696        self,
697        column: Column<M, T>,
698        pool: &sqlx::PgPool,
699    ) -> Result<Option<f64>, Error> {
700        self.aggregate_one_value(pool, &format!("AVG({})::float8", column.name()))
701            .await
702    }
703
704    pub async fn exists(self, pool: &sqlx::PgPool) -> Result<bool, Error> {
705        Ok(self.count(pool).await? > 0)
706    }
707
708    pub async fn doesnt_exist(self, pool: &sqlx::PgPool) -> Result<bool, Error> {
709        Ok(self.count(pool).await? == 0)
710    }
711
712    // ── helpers ──────────────────────────────────────────────────────────────
713
714    async fn aggregate_i64(self, pool: &sqlx::PgPool, expr: &str) -> Result<i64, Error> {
715        let mut q = self.prepare();
716        q.clear_selects();
717        // Drop ORDER BY / LIMIT / OFFSET on aggregates — Postgres rejects them
718        // alongside a bare COUNT(*).
719        q.clear_order_by();
720        q.reset_limit();
721        q.reset_offset();
722        q.expr(Expr::cust(expr));
723        let (sql, values) = q.build_sqlx(PostgresQueryBuilder);
724        let (v,): (i64,) = sqlx::query_as_with(&sql, values).fetch_one(pool).await?;
725        Ok(v)
726    }
727
728    async fn aggregate_one_value<T>(
729        self,
730        pool: &sqlx::PgPool,
731        expr: &str,
732    ) -> Result<Option<T>, Error>
733    where
734        T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
735    {
736        let mut q = self.prepare();
737        q.clear_selects();
738        q.clear_order_by();
739        q.reset_limit();
740        q.reset_offset();
741        q.expr(Expr::cust(expr));
742        let (sql, values) = q.build_sqlx(PostgresQueryBuilder);
743        let row: Option<(Option<T>,)> = sqlx::query_as_with(&sql, values)
744            .fetch_optional(pool)
745            .await?;
746        Ok(row.and_then(|(v,)| v))
747    }
748}
749
750// ─── whereHas / whereDoesntHave subquery builder ───────────────────────────
751
752fn build_exists_subquery<M, R, F>(f: F, negate: bool) -> SimpleExpr
753where
754    M: Model,
755    for<'r> M: FromRow<'r, PgRow>,
756    R: crate::relation::RelationDef<Parent = M>,
757    R::Child: Model,
758    for<'r> R::Child: FromRow<'r, PgRow>,
759    F: FnOnce(QueryBuilder<R::Child>) -> QueryBuilder<R::Child>,
760{
761    // Build the inner SELECT against the child model, then apply user filters
762    // and the correlation `child.fk = parent.lk`.
763    let inner = f(QueryBuilder::<R::Child>::new());
764    let mut child_select = inner.prepare();
765    // Drop the FROM-clauses sea-query computed and replace with just the child
766    // table + a SELECT 1 — we only need EXISTS semantics, not the columns.
767    child_select.clear_selects();
768    child_select.expr(Expr::cust("1"));
769    // Add the correlation predicate.
770    let correlate = Expr::cust(&format!(
771        "{child}.{fk} = {parent}.{lk}",
772        child = R::Child::TABLE,
773        fk = R::foreign_key(),
774        parent = M::TABLE,
775        lk = R::local_key(),
776    ));
777    child_select.and_where(correlate);
778
779    // sea-query's `Expr::exists` takes a SubQueryStatement.
780    let exists = sea_query::Expr::exists(child_select);
781    if negate {
782        exists.not()
783    } else {
784        exists
785    }
786}
cast_core/query.rs

cast_core/
query.rs
