prax_query/operations/
aggregate.rs

1//! Aggregation query operations.
2//!
3//! This module provides aggregate operations like `count`, `sum`, `avg`, `min`, `max`,
4//! and `groupBy` for performing statistical queries on the database.
5//!
6//! # Example
7//!
8//! ```rust,ignore
9//! // Count users
10//! let count = client
11//!     .user()
12//!     .aggregate()
13//!     .count()
14//!     .r#where(user::active::equals(true))
15//!     .exec()
16//!     .await?;
17//!
18//! // Get aggregated statistics
19//! let stats = client
20//!     .user()
21//!     .aggregate()
22//!     .count()
23//!     .avg(user::age())
24//!     .min(user::age())
25//!     .max(user::age())
26//!     .sum(user::age())
27//!     .r#where(user::active::equals(true))
28//!     .exec()
29//!     .await?;
30//!
31//! // Group by with aggregation
32//! let by_country = client
33//!     .user()
34//!     .group_by(user::country())
35//!     .count()
36//!     .avg(user::age())
37//!     .having(aggregate::count::gt(10))
38//!     .exec()
39//!     .await?;
40//! ```
41
42use std::marker::PhantomData;
43
44use crate::error::QueryResult;
45use crate::filter::Filter;
46use crate::sql::quote_identifier;
47use crate::traits::{Model, QueryEngine};
48use crate::types::OrderByField;
49
50/// An aggregation field specifier.
51#[derive(Debug, Clone)]
52pub enum AggregateField {
53    /// Count all rows.
54    CountAll,
55    /// Count non-null values in a column.
56    CountColumn(String),
57    /// Count distinct values in a column.
58    CountDistinct(String),
59    /// Sum of a numeric column.
60    Sum(String),
61    /// Average of a numeric column.
62    Avg(String),
63    /// Minimum value in a column.
64    Min(String),
65    /// Maximum value in a column.
66    Max(String),
67}
68
69impl AggregateField {
70    /// Build the SQL expression for this aggregate.
71    pub fn to_sql(&self) -> String {
72        match self {
73            Self::CountAll => "COUNT(*)".to_string(),
74            Self::CountColumn(col) => format!("COUNT({})", quote_identifier(col)),
75            Self::CountDistinct(col) => format!("COUNT(DISTINCT {})", quote_identifier(col)),
76            Self::Sum(col) => format!("SUM({})", quote_identifier(col)),
77            Self::Avg(col) => format!("AVG({})", quote_identifier(col)),
78            Self::Min(col) => format!("MIN({})", quote_identifier(col)),
79            Self::Max(col) => format!("MAX({})", quote_identifier(col)),
80        }
81    }
82
83    /// Get the alias for this aggregate.
84    pub fn alias(&self) -> String {
85        match self {
86            Self::CountAll => "_count".to_string(),
87            Self::CountColumn(col) => format!("_count_{}", col),
88            Self::CountDistinct(col) => format!("_count_distinct_{}", col),
89            Self::Sum(col) => format!("_sum_{}", col),
90            Self::Avg(col) => format!("_avg_{}", col),
91            Self::Min(col) => format!("_min_{}", col),
92            Self::Max(col) => format!("_max_{}", col),
93        }
94    }
95}
96
97/// Result of an aggregation query.
98///
99/// Populated from the single-row result of an aggregate query by
100/// [`AggregateOperation::exec`]. The keys in each map are the
101/// *column names* stripped of the `_sum_` / `_avg_` / `_min_` /
102/// `_max_` prefixes emitted by [`AggregateField::alias`], so callers
103/// index by the original column (e.g. `result.sum.get("views")`).
104#[derive(Debug, Clone, Default)]
105pub struct AggregateResult {
106    /// Total count (if requested).
107    pub count: Option<i64>,
108    /// Sum results keyed by column name.
109    pub sum: std::collections::HashMap<String, f64>,
110    /// Average results keyed by column name.
111    pub avg: std::collections::HashMap<String, f64>,
112    /// Minimum results keyed by column name.
113    pub min: std::collections::HashMap<String, serde_json::Value>,
114    /// Maximum results keyed by column name.
115    pub max: std::collections::HashMap<String, serde_json::Value>,
116}
117
118impl AggregateResult {
119    /// Build an [`AggregateResult`] from the single column-value map
120    /// returned by [`crate::traits::QueryEngine::aggregate_query`] for
121    /// a whole-table aggregate.
122    ///
123    /// The input map's keys are the dialect-emitted aliases
124    /// (`_count`, `_sum_<col>`, `_avg_<col>`, …). This method strips
125    /// the prefix and routes each entry into the right typed accessor
126    /// bucket. Values that don't parse as the expected numeric type
127    /// are dropped silently — aggregates against empty result sets
128    /// legitimately return NULL.
129    pub fn from_row(row: std::collections::HashMap<String, crate::filter::FilterValue>) -> Self {
130        use crate::filter::FilterValue;
131        let mut out = Self::default();
132        for (k, v) in row {
133            if k == "_count" {
134                if let FilterValue::Int(n) = v {
135                    out.count = Some(n);
136                }
137            } else if let Some(col) = k.strip_prefix("_sum_") {
138                if let Some(f) = value_to_f64(&v) {
139                    out.sum.insert(col.to_string(), f);
140                }
141            } else if let Some(col) = k.strip_prefix("_avg_") {
142                if let Some(f) = value_to_f64(&v) {
143                    out.avg.insert(col.to_string(), f);
144                }
145            } else if let Some(col) = k.strip_prefix("_min_") {
146                out.min.insert(col.to_string(), filter_value_to_json(&v));
147            } else if let Some(col) = k.strip_prefix("_max_") {
148                out.max.insert(col.to_string(), filter_value_to_json(&v));
149            }
150        }
151        out
152    }
153
154    /// Pull the sum of a column as `f64` if present.
155    pub fn sum_as_f64(&self, column: &str) -> Option<f64> {
156        self.sum.get(column).copied()
157    }
158
159    /// Pull the average of a column as `f64` if present.
160    pub fn avg_as_f64(&self, column: &str) -> Option<f64> {
161        self.avg.get(column).copied()
162    }
163
164    /// Pull the minimum of a column as `f64` if the stored JSON value
165    /// is numeric.
166    pub fn min_as_f64(&self, column: &str) -> Option<f64> {
167        self.min.get(column).and_then(|v| v.as_f64())
168    }
169
170    /// Pull the maximum of a column as `f64` if the stored JSON value
171    /// is numeric.
172    pub fn max_as_f64(&self, column: &str) -> Option<f64> {
173        self.max.get(column).and_then(|v| v.as_f64())
174    }
175}
176
177fn value_to_f64(v: &crate::filter::FilterValue) -> Option<f64> {
178    use crate::filter::FilterValue;
179    match v {
180        FilterValue::Int(n) => Some(*n as f64),
181        FilterValue::Float(f) => Some(*f),
182        FilterValue::String(s) => s.parse::<f64>().ok(),
183        _ => None,
184    }
185}
186
187fn filter_value_to_json(v: &crate::filter::FilterValue) -> serde_json::Value {
188    use crate::filter::FilterValue;
189    match v {
190        FilterValue::Null => serde_json::Value::Null,
191        FilterValue::Bool(b) => serde_json::Value::Bool(*b),
192        FilterValue::Int(n) => serde_json::Value::from(*n),
193        FilterValue::Float(f) => serde_json::Number::from_f64(*f)
194            .map(serde_json::Value::Number)
195            .unwrap_or(serde_json::Value::Null),
196        FilterValue::String(s) => serde_json::Value::String(s.clone()),
197        FilterValue::Json(j) => j.clone(),
198        FilterValue::List(_) => serde_json::Value::Null,
199    }
200}
201
202/// Aggregate operation builder.
203///
204/// # Engine ownership
205///
206/// The builder stores an `Option<E>` rather than the engine directly
207/// so existing unit tests that construct an `AggregateOperation` just
208/// to exercise SQL emission (`AggregateOperation::<Model,
209/// MockEngine>::new()`) keep working without a real engine.
210/// Production code always goes through [`AggregateOperation::with_engine`]
211/// (what the generated `Client<E>::aggregate()` accessor calls), and
212/// [`Self::exec`] refuses to run when the engine slot is empty.
213#[derive(Debug)]
214pub struct AggregateOperation<M: Model, E: QueryEngine> {
215    /// Phantom data for model type.
216    _model: PhantomData<M>,
217    /// Engine used by [`Self::exec`]. SQL-emission-only constructors
218    /// leave this `None`.
219    engine: Option<E>,
220    /// Aggregate fields to compute.
221    fields: Vec<AggregateField>,
222    /// Filter conditions.
223    filter: Option<Filter>,
224}
225
226impl<M: Model, E: QueryEngine> AggregateOperation<M, E> {
227    /// Create a new aggregate operation without an engine.
228    ///
229    /// Useful for unit tests that only exercise [`Self::build_sql`].
230    /// [`Self::exec`] will refuse to run on a builder created this way.
231    pub fn new() -> Self {
232        Self {
233            _model: PhantomData,
234            engine: None,
235            fields: Vec::new(),
236            filter: None,
237        }
238    }
239
240    /// Create a new aggregate operation bound to a concrete engine.
241    ///
242    /// This is what the generated `Client<E>::aggregate()` accessor
243    /// calls.
244    pub fn with_engine(engine: E) -> Self {
245        Self {
246            _model: PhantomData,
247            engine: Some(engine),
248            fields: Vec::new(),
249            filter: None,
250        }
251    }
252
253    /// Add a count of all rows.
254    pub fn count(mut self) -> Self {
255        self.fields.push(AggregateField::CountAll);
256        self
257    }
258
259    /// Add a count of non-null values in a column.
260    pub fn count_column(mut self, column: impl Into<String>) -> Self {
261        self.fields.push(AggregateField::CountColumn(column.into()));
262        self
263    }
264
265    /// Add a count of distinct values in a column.
266    pub fn count_distinct(mut self, column: impl Into<String>) -> Self {
267        self.fields
268            .push(AggregateField::CountDistinct(column.into()));
269        self
270    }
271
272    /// Add sum of a numeric column.
273    pub fn sum(mut self, column: impl Into<String>) -> Self {
274        self.fields.push(AggregateField::Sum(column.into()));
275        self
276    }
277
278    /// Add average of a numeric column.
279    pub fn avg(mut self, column: impl Into<String>) -> Self {
280        self.fields.push(AggregateField::Avg(column.into()));
281        self
282    }
283
284    /// Add minimum of a column.
285    pub fn min(mut self, column: impl Into<String>) -> Self {
286        self.fields.push(AggregateField::Min(column.into()));
287        self
288    }
289
290    /// Add maximum of a column.
291    pub fn max(mut self, column: impl Into<String>) -> Self {
292        self.fields.push(AggregateField::Max(column.into()));
293        self
294    }
295
296    /// Add a filter condition.
297    pub fn r#where(mut self, filter: impl Into<Filter>) -> Self {
298        self.filter = Some(filter.into());
299        self
300    }
301
302    /// Build the SQL for this operation.
303    pub fn build_sql(
304        &self,
305        dialect: &dyn crate::dialect::SqlDialect,
306    ) -> (String, Vec<crate::filter::FilterValue>) {
307        let mut params = Vec::new();
308
309        // If no fields specified, default to count
310        let fields = if self.fields.is_empty() {
311            vec![AggregateField::CountAll]
312        } else {
313            self.fields.clone()
314        };
315
316        let select_parts: Vec<String> = fields
317            .iter()
318            .map(|f| format!("{} AS {}", f.to_sql(), quote_identifier(&f.alias())))
319            .collect();
320
321        let mut sql = format!(
322            "SELECT {} FROM {}",
323            select_parts.join(", "),
324            quote_identifier(M::TABLE_NAME)
325        );
326
327        // Add WHERE clause
328        if let Some(filter) = &self.filter {
329            let (where_sql, where_params) = filter.to_sql(params.len() + 1, dialect);
330            sql.push_str(&format!(" WHERE {}", where_sql));
331            params.extend(where_params);
332        }
333
334        (sql, params)
335    }
336
337    /// Execute the aggregate operation.
338    ///
339    /// Routes the single-row aggregate result through
340    /// [`crate::traits::QueryEngine::aggregate_query`] and folds the
341    /// column→value map into an [`AggregateResult`]. Returns an empty
342    /// result (all fields `None`/empty) if the query yields zero rows
343    /// — aggregates on empty tables do this on Postgres/MySQL/SQLite.
344    ///
345    /// Errors with `QueryError::internal` if the builder was
346    /// constructed via [`Self::new`] without an engine.
347    pub async fn exec(self) -> QueryResult<AggregateResult> {
348        let engine = self.engine.as_ref().ok_or_else(|| {
349            crate::error::QueryError::internal(
350                "AggregateOperation::exec called on a builder without an engine; \
351                 use Client<E>::aggregate() (which calls with_engine) instead of \
352                 AggregateOperation::new()",
353            )
354        })?;
355        let dialect = engine.dialect();
356        let (sql, params) = self.build_sql(dialect);
357        let mut rows = engine.aggregate_query(&sql, params).await?;
358        Ok(AggregateResult::from_row(rows.pop().unwrap_or_default()))
359    }
360}
361
362impl<M: Model, E: QueryEngine> Default for AggregateOperation<M, E> {
363    fn default() -> Self {
364        Self::new()
365    }
366}
367
368/// Group by operation builder.
369///
370/// # Engine ownership
371///
372/// Like [`AggregateOperation`], holds an `Option<E>` so SQL-emission
373/// unit tests compile without a real engine. Production code uses
374/// [`GroupByOperation::with_engine`] via the generated
375/// `Client<E>::group_by` accessor.
376#[derive(Debug)]
377pub struct GroupByOperation<M: Model, E: QueryEngine> {
378    /// Phantom data for model type.
379    _model: PhantomData<M>,
380    /// Engine used by [`Self::exec`]; `None` for SQL-emission-only
381    /// unit-test constructors.
382    engine: Option<E>,
383    /// Columns to group by.
384    group_columns: Vec<String>,
385    /// Aggregate fields to compute.
386    agg_fields: Vec<AggregateField>,
387    /// Filter conditions (WHERE).
388    filter: Option<Filter>,
389    /// Having conditions.
390    having: Option<HavingCondition>,
391    /// Order by clauses.
392    order_by: Vec<OrderByField>,
393    /// Skip count.
394    skip: Option<usize>,
395    /// Take count.
396    take: Option<usize>,
397}
398
399/// A condition for the HAVING clause.
400#[derive(Debug, Clone)]
401pub struct HavingCondition {
402    /// The aggregate field to check.
403    pub field: AggregateField,
404    /// The comparison operator.
405    pub op: HavingOp,
406    /// The value to compare against.
407    pub value: f64,
408}
409
410/// Operators for HAVING conditions.
411#[derive(Debug, Clone, Copy)]
412pub enum HavingOp {
413    /// Greater than.
414    Gt,
415    /// Greater than or equal.
416    Gte,
417    /// Less than.
418    Lt,
419    /// Less than or equal.
420    Lte,
421    /// Equal.
422    Eq,
423    /// Not equal.
424    Ne,
425}
426
427impl HavingOp {
428    /// Get the SQL operator string.
429    pub fn as_str(&self) -> &'static str {
430        match self {
431            Self::Gt => ">",
432            Self::Gte => ">=",
433            Self::Lt => "<",
434            Self::Lte => "<=",
435            Self::Eq => "=",
436            Self::Ne => "<>",
437        }
438    }
439}
440
441impl<M: Model, E: QueryEngine> GroupByOperation<M, E> {
442    /// Create a new group-by operation without an engine.
443    ///
444    /// Useful for unit tests that only exercise [`Self::build_sql`].
445    /// [`Self::exec`] will refuse to run on a builder created this way.
446    pub fn new(columns: Vec<String>) -> Self {
447        Self {
448            _model: PhantomData,
449            engine: None,
450            group_columns: columns,
451            agg_fields: Vec::new(),
452            filter: None,
453            having: None,
454            order_by: Vec::new(),
455            skip: None,
456            take: None,
457        }
458    }
459
460    /// Create a new group-by operation bound to a concrete engine.
461    ///
462    /// This is what the generated `Client<E>::group_by(cols)` accessor
463    /// calls.
464    pub fn with_engine(engine: E, columns: Vec<String>) -> Self {
465        Self {
466            _model: PhantomData,
467            engine: Some(engine),
468            group_columns: columns,
469            agg_fields: Vec::new(),
470            filter: None,
471            having: None,
472            order_by: Vec::new(),
473            skip: None,
474            take: None,
475        }
476    }
477
478    /// Add a count aggregate.
479    pub fn count(mut self) -> Self {
480        self.agg_fields.push(AggregateField::CountAll);
481        self
482    }
483
484    /// Add sum of a column.
485    pub fn sum(mut self, column: impl Into<String>) -> Self {
486        self.agg_fields.push(AggregateField::Sum(column.into()));
487        self
488    }
489
490    /// Add average of a column.
491    pub fn avg(mut self, column: impl Into<String>) -> Self {
492        self.agg_fields.push(AggregateField::Avg(column.into()));
493        self
494    }
495
496    /// Add minimum of a column.
497    pub fn min(mut self, column: impl Into<String>) -> Self {
498        self.agg_fields.push(AggregateField::Min(column.into()));
499        self
500    }
501
502    /// Add maximum of a column.
503    pub fn max(mut self, column: impl Into<String>) -> Self {
504        self.agg_fields.push(AggregateField::Max(column.into()));
505        self
506    }
507
508    /// Add a filter condition.
509    pub fn r#where(mut self, filter: impl Into<Filter>) -> Self {
510        self.filter = Some(filter.into());
511        self
512    }
513
514    /// Add a having condition.
515    pub fn having(mut self, condition: HavingCondition) -> Self {
516        self.having = Some(condition);
517        self
518    }
519
520    /// Add ordering.
521    pub fn order_by(mut self, order: impl Into<OrderByField>) -> Self {
522        self.order_by.push(order.into());
523        self
524    }
525
526    /// Set skip count.
527    pub fn skip(mut self, count: usize) -> Self {
528        self.skip = Some(count);
529        self
530    }
531
532    /// Set take count.
533    pub fn take(mut self, count: usize) -> Self {
534        self.take = Some(count);
535        self
536    }
537
538    /// Build the SQL for this operation.
539    pub fn build_sql(
540        &self,
541        dialect: &dyn crate::dialect::SqlDialect,
542    ) -> (String, Vec<crate::filter::FilterValue>) {
543        let mut params = Vec::new();
544
545        // Build SELECT clause
546        let mut select_parts: Vec<String> = self
547            .group_columns
548            .iter()
549            .map(|c| quote_identifier(c))
550            .collect();
551
552        for field in &self.agg_fields {
553            select_parts.push(format!(
554                "{} AS {}",
555                field.to_sql(),
556                quote_identifier(&field.alias())
557            ));
558        }
559
560        let mut sql = format!(
561            "SELECT {} FROM {}",
562            select_parts.join(", "),
563            quote_identifier(M::TABLE_NAME)
564        );
565
566        // Add WHERE clause
567        if let Some(filter) = &self.filter {
568            let (where_sql, where_params) = filter.to_sql(params.len() + 1, dialect);
569            sql.push_str(&format!(" WHERE {}", where_sql));
570            params.extend(where_params);
571        }
572
573        // Add GROUP BY clause
574        if !self.group_columns.is_empty() {
575            let group_cols: Vec<String> = self
576                .group_columns
577                .iter()
578                .map(|c| quote_identifier(c))
579                .collect();
580            sql.push_str(&format!(" GROUP BY {}", group_cols.join(", ")));
581        }
582
583        // Add HAVING clause
584        if let Some(having) = &self.having {
585            sql.push_str(&format!(
586                " HAVING {} {} {}",
587                having.field.to_sql(),
588                having.op.as_str(),
589                having.value
590            ));
591        }
592
593        // Add ORDER BY clause
594        if !self.order_by.is_empty() {
595            let order_parts: Vec<String> = self
596                .order_by
597                .iter()
598                .map(|o| {
599                    let mut part = format!("{} {}", quote_identifier(&o.column), o.order.as_sql());
600                    if let Some(nulls) = o.nulls {
601                        part.push(' ');
602                        part.push_str(nulls.as_sql());
603                    }
604                    part
605                })
606                .collect();
607            sql.push_str(&format!(" ORDER BY {}", order_parts.join(", ")));
608        }
609
610        // Add LIMIT/OFFSET
611        if let Some(take) = self.take {
612            sql.push_str(&format!(" LIMIT {}", take));
613        }
614        if let Some(skip) = self.skip {
615            sql.push_str(&format!(" OFFSET {}", skip));
616        }
617
618        (sql, params)
619    }
620
621    /// Execute the group-by operation.
622    ///
623    /// Returns one [`GroupByResult`] per grouped row. Each result
624    /// splits the row map into two buckets:
625    /// - `group_values`: entries whose key matches a column named in
626    ///   `group_columns`.
627    /// - `aggregates`: everything else — parsed through
628    ///   [`AggregateResult::from_row`].
629    ///
630    /// Errors with `QueryError::internal` if the builder was
631    /// constructed via [`Self::new`] without an engine.
632    pub async fn exec(self) -> QueryResult<Vec<GroupByResult>> {
633        let engine = self.engine.as_ref().ok_or_else(|| {
634            crate::error::QueryError::internal(
635                "GroupByOperation::exec called on a builder without an engine; \
636                 use Client<E>::group_by() (which calls with_engine) instead of \
637                 GroupByOperation::new()",
638            )
639        })?;
640        let dialect = engine.dialect();
641        let group_columns = self.group_columns.clone();
642        let (sql, params) = self.build_sql(dialect);
643        let rows = engine.aggregate_query(&sql, params).await?;
644        Ok(rows
645            .into_iter()
646            .map(|row| {
647                let mut group_values = std::collections::HashMap::new();
648                let mut agg_map = std::collections::HashMap::new();
649                for (k, v) in row {
650                    if group_columns.iter().any(|c| c == &k) {
651                        group_values.insert(k, filter_value_to_json(&v));
652                    } else {
653                        agg_map.insert(k, v);
654                    }
655                }
656                GroupByResult {
657                    group_values,
658                    aggregates: AggregateResult::from_row(agg_map),
659                }
660            })
661            .collect())
662    }
663}
664
665/// Result of a group by query.
666#[derive(Debug, Clone)]
667pub struct GroupByResult {
668    /// The grouped column values.
669    pub group_values: std::collections::HashMap<String, serde_json::Value>,
670    /// The aggregate results.
671    pub aggregates: AggregateResult,
672}
673
674/// Helper for creating having conditions.
675pub mod having {
676    use super::*;
677
678    /// Create a having condition for count > value.
679    pub fn count_gt(value: f64) -> HavingCondition {
680        HavingCondition {
681            field: AggregateField::CountAll,
682            op: HavingOp::Gt,
683            value,
684        }
685    }
686
687    /// Create a having condition for count >= value.
688    pub fn count_gte(value: f64) -> HavingCondition {
689        HavingCondition {
690            field: AggregateField::CountAll,
691            op: HavingOp::Gte,
692            value,
693        }
694    }
695
696    /// Create a having condition for count < value.
697    pub fn count_lt(value: f64) -> HavingCondition {
698        HavingCondition {
699            field: AggregateField::CountAll,
700            op: HavingOp::Lt,
701            value,
702        }
703    }
704
705    /// Create a having condition for sum > value.
706    pub fn sum_gt(column: impl Into<String>, value: f64) -> HavingCondition {
707        HavingCondition {
708            field: AggregateField::Sum(column.into()),
709            op: HavingOp::Gt,
710            value,
711        }
712    }
713
714    /// Create a having condition for avg > value.
715    pub fn avg_gt(column: impl Into<String>, value: f64) -> HavingCondition {
716        HavingCondition {
717            field: AggregateField::Avg(column.into()),
718            op: HavingOp::Gt,
719            value,
720        }
721    }
722}
723
724#[cfg(test)]
725mod tests {
726    use super::*;
727    use crate::filter::{Filter, FilterValue};
728    use crate::types::NullsOrder;
729
730    // A simple test model
731    struct TestModel;
732
733    impl Model for TestModel {
734        const MODEL_NAME: &'static str = "TestModel";
735        const TABLE_NAME: &'static str = "test_models";
736        const PRIMARY_KEY: &'static [&'static str] = &["id"];
737        const COLUMNS: &'static [&'static str] = &["id", "name", "age", "score"];
738    }
739
740    impl crate::row::FromRow for TestModel {
741        fn from_row(_row: &impl crate::row::RowRef) -> Result<Self, crate::row::RowError> {
742            Ok(TestModel)
743        }
744    }
745
746    // A mock query engine
747    #[derive(Clone)]
748    struct MockEngine;
749
750    impl QueryEngine for MockEngine {
751        fn dialect(&self) -> &dyn crate::dialect::SqlDialect {
752            &crate::dialect::Postgres
753        }
754
755        fn query_many<T: Model + crate::row::FromRow + Send + 'static>(
756            &self,
757            _sql: &str,
758            _params: Vec<crate::filter::FilterValue>,
759        ) -> crate::traits::BoxFuture<'_, QueryResult<Vec<T>>> {
760            Box::pin(async { Ok(Vec::new()) })
761        }
762
763        fn query_one<T: Model + crate::row::FromRow + Send + 'static>(
764            &self,
765            _sql: &str,
766            _params: Vec<crate::filter::FilterValue>,
767        ) -> crate::traits::BoxFuture<'_, QueryResult<T>> {
768            Box::pin(async { Err(crate::error::QueryError::not_found("Not implemented")) })
769        }
770
771        fn query_optional<T: Model + crate::row::FromRow + Send + 'static>(
772            &self,
773            _sql: &str,
774            _params: Vec<crate::filter::FilterValue>,
775        ) -> crate::traits::BoxFuture<'_, QueryResult<Option<T>>> {
776            Box::pin(async { Ok(None) })
777        }
778
779        fn execute_insert<T: Model + crate::row::FromRow + Send + 'static>(
780            &self,
781            _sql: &str,
782            _params: Vec<crate::filter::FilterValue>,
783        ) -> crate::traits::BoxFuture<'_, QueryResult<T>> {
784            Box::pin(async { Err(crate::error::QueryError::not_found("Not implemented")) })
785        }
786
787        fn execute_update<T: Model + crate::row::FromRow + Send + 'static>(
788            &self,
789            _sql: &str,
790            _params: Vec<crate::filter::FilterValue>,
791        ) -> crate::traits::BoxFuture<'_, QueryResult<Vec<T>>> {
792            Box::pin(async { Ok(Vec::new()) })
793        }
794
795        fn execute_delete(
796            &self,
797            _sql: &str,
798            _params: Vec<crate::filter::FilterValue>,
799        ) -> crate::traits::BoxFuture<'_, QueryResult<u64>> {
800            Box::pin(async { Ok(0) })
801        }
802
803        fn execute_raw(
804            &self,
805            _sql: &str,
806            _params: Vec<crate::filter::FilterValue>,
807        ) -> crate::traits::BoxFuture<'_, QueryResult<u64>> {
808            Box::pin(async { Ok(0) })
809        }
810
811        fn count(
812            &self,
813            _sql: &str,
814            _params: Vec<crate::filter::FilterValue>,
815        ) -> crate::traits::BoxFuture<'_, QueryResult<u64>> {
816            Box::pin(async { Ok(0) })
817        }
818    }
819
820    // ========== AggregateField Tests ==========
821
822    #[test]
823    fn test_aggregate_field_sql() {
824        // Note: quote_identifier only quotes when needed (reserved words, special chars)
825        assert_eq!(AggregateField::CountAll.to_sql(), "COUNT(*)");
826        assert_eq!(
827            AggregateField::CountColumn("id".into()).to_sql(),
828            "COUNT(id)"
829        );
830        assert_eq!(
831            AggregateField::CountDistinct("email".into()).to_sql(),
832            "COUNT(DISTINCT email)"
833        );
834        assert_eq!(AggregateField::Sum("amount".into()).to_sql(), "SUM(amount)");
835        assert_eq!(
836            AggregateField::Avg("score".to_string()).to_sql(),
837            "AVG(score)"
838        );
839        assert_eq!(AggregateField::Min("age".into()).to_sql(), "MIN(age)");
840        assert_eq!(AggregateField::Max("age".into()).to_sql(), "MAX(age)");
841        // Test with reserved word - should be quoted
842        assert_eq!(
843            AggregateField::CountColumn("user".to_string()).to_sql(),
844            "COUNT(\"user\")"
845        );
846    }
847
848    #[test]
849    fn test_aggregate_field_alias() {
850        assert_eq!(AggregateField::CountAll.alias(), "_count");
851        assert_eq!(
852            AggregateField::CountColumn("id".into()).alias(),
853            "_count_id"
854        );
855        assert_eq!(
856            AggregateField::CountDistinct("email".into()).alias(),
857            "_count_distinct_email"
858        );
859        assert_eq!(AggregateField::Sum("amount".into()).alias(), "_sum_amount");
860        assert_eq!(
861            AggregateField::Avg("score".to_string()).alias(),
862            "_avg_score"
863        );
864        assert_eq!(AggregateField::Min("age".into()).alias(), "_min_age");
865        assert_eq!(
866            AggregateField::Max("salary".to_string()).alias(),
867            "_max_salary"
868        );
869    }
870
871    // ========== AggregateResult Tests ==========
872
873    #[test]
874    fn test_aggregate_result_default() {
875        let result = AggregateResult::default();
876        assert!(result.count.is_none());
877        assert!(result.sum.is_empty());
878        assert!(result.avg.is_empty());
879        assert!(result.min.is_empty());
880        assert!(result.max.is_empty());
881    }
882
883    #[test]
884    fn test_aggregate_result_debug() {
885        let result = AggregateResult::default();
886        let debug_str = format!("{:?}", result);
887        assert!(debug_str.contains("AggregateResult"));
888    }
889
890    #[test]
891    fn test_aggregate_result_clone() {
892        let mut result = AggregateResult::default();
893        result.count = Some(42);
894        result.sum.insert("amount".into(), 1000.0);
895
896        let cloned = result.clone();
897        assert_eq!(cloned.count, Some(42));
898        assert_eq!(cloned.sum.get("amount"), Some(&1000.0));
899    }
900
901    // ========== AggregateOperation Tests ==========
902
903    #[test]
904    fn test_aggregate_operation_new() {
905        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new();
906        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
907
908        // Default should be count all
909        assert!(sql.contains("COUNT(*)"));
910        assert!(params.is_empty());
911    }
912
913    #[test]
914    fn test_aggregate_operation_default() {
915        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::default();
916        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
917
918        assert!(sql.contains("COUNT(*)"));
919        assert!(params.is_empty());
920    }
921
922    #[test]
923    fn test_aggregate_operation_build_sql() {
924        let op: AggregateOperation<TestModel, MockEngine> =
925            AggregateOperation::new().count().sum("score").avg("age");
926
927        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
928
929        assert!(sql.contains("SELECT"));
930        assert!(sql.contains("COUNT(*)"));
931        assert!(sql.contains("SUM(score)"));
932        assert!(sql.contains("AVG(age)"));
933        assert!(sql.contains("FROM test_models"));
934        assert!(params.is_empty());
935    }
936
937    #[test]
938    fn test_aggregate_operation_count_column() {
939        let op: AggregateOperation<TestModel, MockEngine> =
940            AggregateOperation::new().count_column("email");
941
942        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
943
944        assert!(sql.contains("COUNT(email)"));
945    }
946
947    #[test]
948    fn test_aggregate_operation_count_distinct() {
949        let op: AggregateOperation<TestModel, MockEngine> =
950            AggregateOperation::new().count_distinct("email");
951
952        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
953
954        assert!(sql.contains("COUNT(DISTINCT email)"));
955    }
956
957    #[test]
958    fn test_aggregate_operation_min_max() {
959        let op: AggregateOperation<TestModel, MockEngine> =
960            AggregateOperation::new().min("age").max("age");
961
962        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
963
964        assert!(sql.contains("MIN(age)"));
965        assert!(sql.contains("MAX(age)"));
966    }
967
968    #[test]
969    fn test_aggregate_with_where() {
970        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new()
971            .count()
972            .r#where(Filter::Gt("age".into(), FilterValue::Int(18)));
973
974        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
975
976        assert!(sql.contains("WHERE"));
977        assert!(sql.contains("age")); // Not quoted since "age" is not a reserved word
978        assert!(sql.contains(">"));
979        assert!(!params.is_empty());
980    }
981
982    #[test]
983    fn test_aggregate_with_complex_filter() {
984        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new()
985            .sum("score")
986            .avg("age")
987            .r#where(Filter::and([
988                Filter::Gte("age".into(), FilterValue::Int(18)),
989                Filter::Equals("active".into(), FilterValue::Bool(true)),
990            ]));
991
992        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
993
994        assert!(sql.contains("WHERE"));
995        assert!(sql.contains("AND"));
996        assert_eq!(params.len(), 2);
997    }
998
999    #[test]
1000    fn test_aggregate_all_methods() {
1001        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new()
1002            .count()
1003            .count_column("name")
1004            .count_distinct("email")
1005            .sum("score")
1006            .avg("score")
1007            .min("age")
1008            .max("age");
1009
1010        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1011
1012        assert!(sql.contains("COUNT(*)"));
1013        assert!(sql.contains("COUNT(name)"));
1014        assert!(sql.contains("COUNT(DISTINCT email)"));
1015        assert!(sql.contains("SUM(score)"));
1016        assert!(sql.contains("AVG(score)"));
1017        assert!(sql.contains("MIN(age)"));
1018        assert!(sql.contains("MAX(age)"));
1019    }
1020
1021    #[tokio::test]
1022    async fn test_aggregate_exec_without_engine_errors() {
1023        // `new()` leaves engine = None; exec must refuse to run rather
1024        // than silently doing nothing or panicking.
1025        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new().count();
1026        let err = op.exec().await.unwrap_err();
1027        assert!(err.to_string().contains("without an engine"));
1028    }
1029
1030    #[tokio::test]
1031    async fn test_aggregate_exec_with_engine_ok() {
1032        // MockEngine doesn't override `aggregate_query`, so the default
1033        // impl returns `unsupported`. We just verify the engine-to-trait
1034        // wiring is intact.
1035        let op: AggregateOperation<TestModel, MockEngine> =
1036            AggregateOperation::with_engine(MockEngine).count();
1037        let err = op.exec().await.unwrap_err();
1038        assert!(err.to_string().contains("aggregate_query"));
1039    }
1040
1041    // ========== GroupByOperation Tests ==========
1042
1043    #[test]
1044    fn test_group_by_new() {
1045        let op: GroupByOperation<TestModel, MockEngine> =
1046            GroupByOperation::new(vec!["department".into()]);
1047
1048        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1049
1050        assert!(sql.contains("GROUP BY department"));
1051    }
1052
1053    #[test]
1054    fn test_group_by_build_sql() {
1055        let op: GroupByOperation<TestModel, MockEngine> =
1056            GroupByOperation::new(vec!["name".to_string()])
1057                .count()
1058                .avg("score");
1059
1060        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
1061
1062        assert!(sql.contains("SELECT"));
1063        assert!(sql.contains("name")); // Not quoted since "name" is not a reserved word
1064        assert!(sql.contains("COUNT(*)"));
1065        assert!(sql.contains("AVG(score)"));
1066        assert!(sql.contains("GROUP BY name"));
1067        assert!(params.is_empty());
1068    }
1069
1070    #[test]
1071    fn test_group_by_multiple_columns() {
1072        let op: GroupByOperation<TestModel, MockEngine> =
1073            GroupByOperation::new(vec!["department".into(), "role".into()]).count();
1074
1075        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1076
1077        assert!(sql.contains("GROUP BY department, role"));
1078    }
1079
1080    #[test]
1081    fn test_group_by_with_sum() {
1082        let op: GroupByOperation<TestModel, MockEngine> =
1083            GroupByOperation::new(vec!["category".into()]).sum("amount");
1084
1085        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1086
1087        assert!(sql.contains("SUM(amount)"));
1088    }
1089
1090    #[test]
1091    fn test_group_by_with_min_max() {
1092        let op: GroupByOperation<TestModel, MockEngine> =
1093            GroupByOperation::new(vec!["category".into()])
1094                .min("price")
1095                .max("price");
1096
1097        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1098
1099        assert!(sql.contains("MIN(price)"));
1100        assert!(sql.contains("MAX(price)"));
1101    }
1102
1103    #[test]
1104    fn test_group_by_with_where() {
1105        let op: GroupByOperation<TestModel, MockEngine> =
1106            GroupByOperation::new(vec!["department".into()])
1107                .count()
1108                .r#where(Filter::Equals("active".into(), FilterValue::Bool(true)));
1109
1110        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
1111
1112        assert!(sql.contains("WHERE"));
1113        assert!(sql.contains("GROUP BY"));
1114        assert_eq!(params.len(), 1);
1115    }
1116
1117    #[test]
1118    fn test_group_by_with_having() {
1119        let op: GroupByOperation<TestModel, MockEngine> =
1120            GroupByOperation::new(vec!["name".to_string()])
1121                .count()
1122                .having(having::count_gt(5.0));
1123
1124        let (sql, _params) = op.build_sql(&crate::dialect::Postgres);
1125
1126        assert!(sql.contains("HAVING COUNT(*) > 5"));
1127    }
1128
1129    #[test]
1130    fn test_group_by_with_order_and_limit() {
1131        let op: GroupByOperation<TestModel, MockEngine> =
1132            GroupByOperation::new(vec!["name".to_string()])
1133                .count()
1134                .order_by(OrderByField::desc("_count"))
1135                .take(10)
1136                .skip(5);
1137
1138        let (sql, _params) = op.build_sql(&crate::dialect::Postgres);
1139
1140        assert!(sql.contains("ORDER BY _count DESC")); // Not quoted since "_count" is not a reserved word
1141        assert!(sql.contains("LIMIT 10"));
1142        assert!(sql.contains("OFFSET 5"));
1143    }
1144
1145    #[test]
1146    fn test_group_by_order_with_nulls() {
1147        let op: GroupByOperation<TestModel, MockEngine> =
1148            GroupByOperation::new(vec!["department".into()])
1149                .count()
1150                .order_by(OrderByField::asc("name").nulls(NullsOrder::First));
1151
1152        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1153
1154        assert!(sql.contains("ORDER BY"));
1155        assert!(sql.contains("NULLS FIRST"));
1156    }
1157
1158    #[test]
1159    fn test_group_by_skip_only() {
1160        let op: GroupByOperation<TestModel, MockEngine> =
1161            GroupByOperation::new(vec!["department".into()])
1162                .count()
1163                .skip(20);
1164
1165        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1166
1167        assert!(sql.contains("OFFSET 20"));
1168        assert!(!sql.contains("LIMIT"));
1169    }
1170
1171    #[test]
1172    fn test_group_by_take_only() {
1173        let op: GroupByOperation<TestModel, MockEngine> =
1174            GroupByOperation::new(vec!["department".into()])
1175                .count()
1176                .take(50);
1177
1178        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1179
1180        assert!(sql.contains("LIMIT 50"));
1181        assert!(!sql.contains("OFFSET"));
1182    }
1183
1184    #[tokio::test]
1185    async fn test_group_by_exec_without_engine_errors() {
1186        let op: GroupByOperation<TestModel, MockEngine> =
1187            GroupByOperation::new(vec!["department".into()]).count();
1188        let err = op.exec().await.unwrap_err();
1189        assert!(err.to_string().contains("without an engine"));
1190    }
1191
1192    #[tokio::test]
1193    async fn test_group_by_exec_with_engine_ok() {
1194        let op: GroupByOperation<TestModel, MockEngine> =
1195            GroupByOperation::with_engine(MockEngine, vec!["department".into()]).count();
1196        let err = op.exec().await.unwrap_err();
1197        assert!(err.to_string().contains("aggregate_query"));
1198    }
1199
1200    // ========== HavingOp Tests ==========
1201
1202    #[test]
1203    fn test_having_op_as_str() {
1204        assert_eq!(HavingOp::Gt.as_str(), ">");
1205        assert_eq!(HavingOp::Gte.as_str(), ">=");
1206        assert_eq!(HavingOp::Lt.as_str(), "<");
1207        assert_eq!(HavingOp::Lte.as_str(), "<=");
1208        assert_eq!(HavingOp::Eq.as_str(), "=");
1209        assert_eq!(HavingOp::Ne.as_str(), "<>");
1210    }
1211
1212    // ========== HavingCondition Tests ==========
1213
1214    #[test]
1215    fn test_having_condition_debug() {
1216        let cond = HavingCondition {
1217            field: AggregateField::CountAll,
1218            op: HavingOp::Gt,
1219            value: 10.0,
1220        };
1221        let debug_str = format!("{:?}", cond);
1222        assert!(debug_str.contains("HavingCondition"));
1223    }
1224
1225    #[test]
1226    fn test_having_condition_clone() {
1227        let cond = HavingCondition {
1228            field: AggregateField::Sum("amount".into()),
1229            op: HavingOp::Gte,
1230            value: 1000.0,
1231        };
1232        let cloned = cond.clone();
1233        assert!((cloned.value - 1000.0).abs() < f64::EPSILON);
1234    }
1235
1236    // ========== Having Helper Tests ==========
1237
1238    #[test]
1239    fn test_having_helpers() {
1240        let cond = having::count_gt(10.0);
1241        assert!(matches!(cond.field, AggregateField::CountAll));
1242        assert!(matches!(cond.op, HavingOp::Gt));
1243        assert!((cond.value - 10.0).abs() < f64::EPSILON);
1244
1245        let cond = having::sum_gt("amount", 1000.0);
1246        if let AggregateField::Sum(col) = cond.field {
1247            assert_eq!(col, "amount");
1248        } else {
1249            panic!("Expected Sum");
1250        }
1251    }
1252
1253    #[test]
1254    fn test_having_count_gte() {
1255        let cond = having::count_gte(5.0);
1256        assert!(matches!(cond.field, AggregateField::CountAll));
1257        assert!(matches!(cond.op, HavingOp::Gte));
1258        assert!((cond.value - 5.0).abs() < f64::EPSILON);
1259    }
1260
1261    #[test]
1262    fn test_having_count_lt() {
1263        let cond = having::count_lt(100.0);
1264        assert!(matches!(cond.field, AggregateField::CountAll));
1265        assert!(matches!(cond.op, HavingOp::Lt));
1266        assert!((cond.value - 100.0).abs() < f64::EPSILON);
1267    }
1268
1269    #[test]
1270    fn test_having_avg_gt() {
1271        let cond = having::avg_gt("score", 75.5);
1272        assert!(matches!(cond.op, HavingOp::Gt));
1273        assert!((cond.value - 75.5).abs() < f64::EPSILON);
1274        if let AggregateField::Avg(col) = cond.field {
1275            assert_eq!(col, "score");
1276        } else {
1277            panic!("Expected Avg");
1278        }
1279    }
1280
1281    #[test]
1282    fn test_having_sum_gt_with_different_columns() {
1283        let cond1 = having::sum_gt("revenue", 50000.0);
1284        let cond2 = having::sum_gt("cost", 10000.0);
1285
1286        if let AggregateField::Sum(col) = &cond1.field {
1287            assert_eq!(col, "revenue");
1288        }
1289        if let AggregateField::Sum(col) = &cond2.field {
1290            assert_eq!(col, "cost");
1291        }
1292    }
1293
1294    // ========== GroupByResult Tests ==========
1295
1296    #[test]
1297    fn test_group_by_result_debug() {
1298        let result = GroupByResult {
1299            group_values: std::collections::HashMap::new(),
1300            aggregates: AggregateResult::default(),
1301        };
1302        let debug_str = format!("{:?}", result);
1303        assert!(debug_str.contains("GroupByResult"));
1304    }
1305
1306    #[test]
1307    fn test_group_by_result_clone() {
1308        let mut result = GroupByResult {
1309            group_values: std::collections::HashMap::new(),
1310            aggregates: AggregateResult::default(),
1311        };
1312        result
1313            .group_values
1314            .insert("category".into(), serde_json::json!("electronics"));
1315        result.aggregates.count = Some(50);
1316
1317        let cloned = result.clone();
1318        assert_eq!(cloned.aggregates.count, Some(50));
1319        assert!(cloned.group_values.contains_key("category"));
1320    }
1321
1322    // ========== SQL Structure Tests ==========
1323
1324    #[test]
1325    fn test_group_by_sql_structure() {
1326        let op: GroupByOperation<TestModel, MockEngine> =
1327            GroupByOperation::new(vec!["department".into()])
1328                .count()
1329                .r#where(Filter::Equals("active".into(), FilterValue::Bool(true)))
1330                .having(having::count_gt(5.0))
1331                .order_by(OrderByField::desc("_count"))
1332                .take(10)
1333                .skip(5);
1334
1335        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1336
1337        // Check SQL clause ordering: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET
1338        let select_pos = sql.find("SELECT").unwrap();
1339        let from_pos = sql.find("FROM").unwrap();
1340        let where_pos = sql.find("WHERE").unwrap();
1341        let group_pos = sql.find("GROUP BY").unwrap();
1342        let having_pos = sql.find("HAVING").unwrap();
1343        let order_pos = sql.find("ORDER BY").unwrap();
1344        let limit_pos = sql.find("LIMIT").unwrap();
1345        let offset_pos = sql.find("OFFSET").unwrap();
1346
1347        assert!(select_pos < from_pos);
1348        assert!(from_pos < where_pos);
1349        assert!(where_pos < group_pos);
1350        assert!(group_pos < having_pos);
1351        assert!(having_pos < order_pos);
1352        assert!(order_pos < limit_pos);
1353        assert!(limit_pos < offset_pos);
1354    }
1355
1356    #[test]
1357    fn test_aggregate_no_group_by() {
1358        let op: AggregateOperation<TestModel, MockEngine> =
1359            AggregateOperation::new().count().sum("score");
1360
1361        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1362
1363        assert!(!sql.contains("GROUP BY"));
1364    }
1365
1366    #[test]
1367    fn test_group_by_empty_columns() {
1368        let op: GroupByOperation<TestModel, MockEngine> = GroupByOperation::new(vec![]).count();
1369
1370        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1371
1372        // Empty group columns should not produce GROUP BY
1373        assert!(!sql.contains("GROUP BY"));
1374    }
1375}
prax_query/operations/aggregate.rs

prax_query/operations/
aggregate.rs
