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

cast_core/
query.rs
