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 {
893            count: Some(42),
894            ..AggregateResult::default()
895        };
896        result.sum.insert("amount".into(), 1000.0);
897
898        let cloned = result.clone();
899        assert_eq!(cloned.count, Some(42));
900        assert_eq!(cloned.sum.get("amount"), Some(&1000.0));
901    }
902
903    // ========== AggregateOperation Tests ==========
904
905    #[test]
906    fn test_aggregate_operation_new() {
907        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new();
908        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
909
910        // Default should be count all
911        assert!(sql.contains("COUNT(*)"));
912        assert!(params.is_empty());
913    }
914
915    #[test]
916    fn test_aggregate_operation_default() {
917        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::default();
918        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
919
920        assert!(sql.contains("COUNT(*)"));
921        assert!(params.is_empty());
922    }
923
924    #[test]
925    fn test_aggregate_operation_build_sql() {
926        let op: AggregateOperation<TestModel, MockEngine> =
927            AggregateOperation::new().count().sum("score").avg("age");
928
929        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
930
931        assert!(sql.contains("SELECT"));
932        assert!(sql.contains("COUNT(*)"));
933        assert!(sql.contains("SUM(score)"));
934        assert!(sql.contains("AVG(age)"));
935        assert!(sql.contains("FROM test_models"));
936        assert!(params.is_empty());
937    }
938
939    #[test]
940    fn test_aggregate_operation_count_column() {
941        let op: AggregateOperation<TestModel, MockEngine> =
942            AggregateOperation::new().count_column("email");
943
944        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
945
946        assert!(sql.contains("COUNT(email)"));
947    }
948
949    #[test]
950    fn test_aggregate_operation_count_distinct() {
951        let op: AggregateOperation<TestModel, MockEngine> =
952            AggregateOperation::new().count_distinct("email");
953
954        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
955
956        assert!(sql.contains("COUNT(DISTINCT email)"));
957    }
958
959    #[test]
960    fn test_aggregate_operation_min_max() {
961        let op: AggregateOperation<TestModel, MockEngine> =
962            AggregateOperation::new().min("age").max("age");
963
964        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
965
966        assert!(sql.contains("MIN(age)"));
967        assert!(sql.contains("MAX(age)"));
968    }
969
970    #[test]
971    fn test_aggregate_with_where() {
972        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new()
973            .count()
974            .r#where(Filter::Gt("age".into(), FilterValue::Int(18)));
975
976        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
977
978        assert!(sql.contains("WHERE"));
979        assert!(sql.contains("age")); // Not quoted since "age" is not a reserved word
980        assert!(sql.contains(">"));
981        assert!(!params.is_empty());
982    }
983
984    #[test]
985    fn test_aggregate_with_complex_filter() {
986        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new()
987            .sum("score")
988            .avg("age")
989            .r#where(Filter::and([
990                Filter::Gte("age".into(), FilterValue::Int(18)),
991                Filter::Equals("active".into(), FilterValue::Bool(true)),
992            ]));
993
994        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
995
996        assert!(sql.contains("WHERE"));
997        assert!(sql.contains("AND"));
998        assert_eq!(params.len(), 2);
999    }
1000
1001    #[test]
1002    fn test_aggregate_all_methods() {
1003        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new()
1004            .count()
1005            .count_column("name")
1006            .count_distinct("email")
1007            .sum("score")
1008            .avg("score")
1009            .min("age")
1010            .max("age");
1011
1012        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1013
1014        assert!(sql.contains("COUNT(*)"));
1015        assert!(sql.contains("COUNT(name)"));
1016        assert!(sql.contains("COUNT(DISTINCT email)"));
1017        assert!(sql.contains("SUM(score)"));
1018        assert!(sql.contains("AVG(score)"));
1019        assert!(sql.contains("MIN(age)"));
1020        assert!(sql.contains("MAX(age)"));
1021    }
1022
1023    #[tokio::test]
1024    async fn test_aggregate_exec_without_engine_errors() {
1025        // `new()` leaves engine = None; exec must refuse to run rather
1026        // than silently doing nothing or panicking.
1027        let op: AggregateOperation<TestModel, MockEngine> = AggregateOperation::new().count();
1028        let err = op.exec().await.unwrap_err();
1029        assert!(err.to_string().contains("without an engine"));
1030    }
1031
1032    #[tokio::test]
1033    async fn test_aggregate_exec_with_engine_ok() {
1034        // MockEngine doesn't override `aggregate_query`, so the default
1035        // impl returns `unsupported`. We just verify the engine-to-trait
1036        // wiring is intact.
1037        let op: AggregateOperation<TestModel, MockEngine> =
1038            AggregateOperation::with_engine(MockEngine).count();
1039        let err = op.exec().await.unwrap_err();
1040        assert!(err.to_string().contains("aggregate_query"));
1041    }
1042
1043    // ========== GroupByOperation Tests ==========
1044
1045    #[test]
1046    fn test_group_by_new() {
1047        let op: GroupByOperation<TestModel, MockEngine> =
1048            GroupByOperation::new(vec!["department".into()]);
1049
1050        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1051
1052        assert!(sql.contains("GROUP BY department"));
1053    }
1054
1055    #[test]
1056    fn test_group_by_build_sql() {
1057        let op: GroupByOperation<TestModel, MockEngine> =
1058            GroupByOperation::new(vec!["name".to_string()])
1059                .count()
1060                .avg("score");
1061
1062        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
1063
1064        assert!(sql.contains("SELECT"));
1065        assert!(sql.contains("name")); // Not quoted since "name" is not a reserved word
1066        assert!(sql.contains("COUNT(*)"));
1067        assert!(sql.contains("AVG(score)"));
1068        assert!(sql.contains("GROUP BY name"));
1069        assert!(params.is_empty());
1070    }
1071
1072    #[test]
1073    fn test_group_by_multiple_columns() {
1074        let op: GroupByOperation<TestModel, MockEngine> =
1075            GroupByOperation::new(vec!["department".into(), "role".into()]).count();
1076
1077        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1078
1079        assert!(sql.contains("GROUP BY department, role"));
1080    }
1081
1082    #[test]
1083    fn test_group_by_with_sum() {
1084        let op: GroupByOperation<TestModel, MockEngine> =
1085            GroupByOperation::new(vec!["category".into()]).sum("amount");
1086
1087        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1088
1089        assert!(sql.contains("SUM(amount)"));
1090    }
1091
1092    #[test]
1093    fn test_group_by_with_min_max() {
1094        let op: GroupByOperation<TestModel, MockEngine> =
1095            GroupByOperation::new(vec!["category".into()])
1096                .min("price")
1097                .max("price");
1098
1099        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1100
1101        assert!(sql.contains("MIN(price)"));
1102        assert!(sql.contains("MAX(price)"));
1103    }
1104
1105    #[test]
1106    fn test_group_by_with_where() {
1107        let op: GroupByOperation<TestModel, MockEngine> =
1108            GroupByOperation::new(vec!["department".into()])
1109                .count()
1110                .r#where(Filter::Equals("active".into(), FilterValue::Bool(true)));
1111
1112        let (sql, params) = op.build_sql(&crate::dialect::Postgres);
1113
1114        assert!(sql.contains("WHERE"));
1115        assert!(sql.contains("GROUP BY"));
1116        assert_eq!(params.len(), 1);
1117    }
1118
1119    #[test]
1120    fn test_group_by_with_having() {
1121        let op: GroupByOperation<TestModel, MockEngine> =
1122            GroupByOperation::new(vec!["name".to_string()])
1123                .count()
1124                .having(having::count_gt(5.0));
1125
1126        let (sql, _params) = op.build_sql(&crate::dialect::Postgres);
1127
1128        assert!(sql.contains("HAVING COUNT(*) > 5"));
1129    }
1130
1131    #[test]
1132    fn test_group_by_with_order_and_limit() {
1133        let op: GroupByOperation<TestModel, MockEngine> =
1134            GroupByOperation::new(vec!["name".to_string()])
1135                .count()
1136                .order_by(OrderByField::desc("_count"))
1137                .take(10)
1138                .skip(5);
1139
1140        let (sql, _params) = op.build_sql(&crate::dialect::Postgres);
1141
1142        assert!(sql.contains("ORDER BY _count DESC")); // Not quoted since "_count" is not a reserved word
1143        assert!(sql.contains("LIMIT 10"));
1144        assert!(sql.contains("OFFSET 5"));
1145    }
1146
1147    #[test]
1148    fn test_group_by_order_with_nulls() {
1149        let op: GroupByOperation<TestModel, MockEngine> =
1150            GroupByOperation::new(vec!["department".into()])
1151                .count()
1152                .order_by(OrderByField::asc("name").nulls(NullsOrder::First));
1153
1154        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1155
1156        assert!(sql.contains("ORDER BY"));
1157        assert!(sql.contains("NULLS FIRST"));
1158    }
1159
1160    #[test]
1161    fn test_group_by_skip_only() {
1162        let op: GroupByOperation<TestModel, MockEngine> =
1163            GroupByOperation::new(vec!["department".into()])
1164                .count()
1165                .skip(20);
1166
1167        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1168
1169        assert!(sql.contains("OFFSET 20"));
1170        assert!(!sql.contains("LIMIT"));
1171    }
1172
1173    #[test]
1174    fn test_group_by_take_only() {
1175        let op: GroupByOperation<TestModel, MockEngine> =
1176            GroupByOperation::new(vec!["department".into()])
1177                .count()
1178                .take(50);
1179
1180        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1181
1182        assert!(sql.contains("LIMIT 50"));
1183        assert!(!sql.contains("OFFSET"));
1184    }
1185
1186    #[tokio::test]
1187    async fn test_group_by_exec_without_engine_errors() {
1188        let op: GroupByOperation<TestModel, MockEngine> =
1189            GroupByOperation::new(vec!["department".into()]).count();
1190        let err = op.exec().await.unwrap_err();
1191        assert!(err.to_string().contains("without an engine"));
1192    }
1193
1194    #[tokio::test]
1195    async fn test_group_by_exec_with_engine_ok() {
1196        let op: GroupByOperation<TestModel, MockEngine> =
1197            GroupByOperation::with_engine(MockEngine, vec!["department".into()]).count();
1198        let err = op.exec().await.unwrap_err();
1199        assert!(err.to_string().contains("aggregate_query"));
1200    }
1201
1202    // ========== HavingOp Tests ==========
1203
1204    #[test]
1205    fn test_having_op_as_str() {
1206        assert_eq!(HavingOp::Gt.as_str(), ">");
1207        assert_eq!(HavingOp::Gte.as_str(), ">=");
1208        assert_eq!(HavingOp::Lt.as_str(), "<");
1209        assert_eq!(HavingOp::Lte.as_str(), "<=");
1210        assert_eq!(HavingOp::Eq.as_str(), "=");
1211        assert_eq!(HavingOp::Ne.as_str(), "<>");
1212    }
1213
1214    // ========== HavingCondition Tests ==========
1215
1216    #[test]
1217    fn test_having_condition_debug() {
1218        let cond = HavingCondition {
1219            field: AggregateField::CountAll,
1220            op: HavingOp::Gt,
1221            value: 10.0,
1222        };
1223        let debug_str = format!("{:?}", cond);
1224        assert!(debug_str.contains("HavingCondition"));
1225    }
1226
1227    #[test]
1228    fn test_having_condition_clone() {
1229        let cond = HavingCondition {
1230            field: AggregateField::Sum("amount".into()),
1231            op: HavingOp::Gte,
1232            value: 1000.0,
1233        };
1234        let cloned = cond.clone();
1235        assert!((cloned.value - 1000.0).abs() < f64::EPSILON);
1236    }
1237
1238    // ========== Having Helper Tests ==========
1239
1240    #[test]
1241    fn test_having_helpers() {
1242        let cond = having::count_gt(10.0);
1243        assert!(matches!(cond.field, AggregateField::CountAll));
1244        assert!(matches!(cond.op, HavingOp::Gt));
1245        assert!((cond.value - 10.0).abs() < f64::EPSILON);
1246
1247        let cond = having::sum_gt("amount", 1000.0);
1248        if let AggregateField::Sum(col) = cond.field {
1249            assert_eq!(col, "amount");
1250        } else {
1251            panic!("Expected Sum");
1252        }
1253    }
1254
1255    #[test]
1256    fn test_having_count_gte() {
1257        let cond = having::count_gte(5.0);
1258        assert!(matches!(cond.field, AggregateField::CountAll));
1259        assert!(matches!(cond.op, HavingOp::Gte));
1260        assert!((cond.value - 5.0).abs() < f64::EPSILON);
1261    }
1262
1263    #[test]
1264    fn test_having_count_lt() {
1265        let cond = having::count_lt(100.0);
1266        assert!(matches!(cond.field, AggregateField::CountAll));
1267        assert!(matches!(cond.op, HavingOp::Lt));
1268        assert!((cond.value - 100.0).abs() < f64::EPSILON);
1269    }
1270
1271    #[test]
1272    fn test_having_avg_gt() {
1273        let cond = having::avg_gt("score", 75.5);
1274        assert!(matches!(cond.op, HavingOp::Gt));
1275        assert!((cond.value - 75.5).abs() < f64::EPSILON);
1276        if let AggregateField::Avg(col) = cond.field {
1277            assert_eq!(col, "score");
1278        } else {
1279            panic!("Expected Avg");
1280        }
1281    }
1282
1283    #[test]
1284    fn test_having_sum_gt_with_different_columns() {
1285        let cond1 = having::sum_gt("revenue", 50000.0);
1286        let cond2 = having::sum_gt("cost", 10000.0);
1287
1288        if let AggregateField::Sum(col) = &cond1.field {
1289            assert_eq!(col, "revenue");
1290        }
1291        if let AggregateField::Sum(col) = &cond2.field {
1292            assert_eq!(col, "cost");
1293        }
1294    }
1295
1296    // ========== GroupByResult Tests ==========
1297
1298    #[test]
1299    fn test_group_by_result_debug() {
1300        let result = GroupByResult {
1301            group_values: std::collections::HashMap::new(),
1302            aggregates: AggregateResult::default(),
1303        };
1304        let debug_str = format!("{:?}", result);
1305        assert!(debug_str.contains("GroupByResult"));
1306    }
1307
1308    #[test]
1309    fn test_group_by_result_clone() {
1310        let mut result = GroupByResult {
1311            group_values: std::collections::HashMap::new(),
1312            aggregates: AggregateResult::default(),
1313        };
1314        result
1315            .group_values
1316            .insert("category".into(), serde_json::json!("electronics"));
1317        result.aggregates.count = Some(50);
1318
1319        let cloned = result.clone();
1320        assert_eq!(cloned.aggregates.count, Some(50));
1321        assert!(cloned.group_values.contains_key("category"));
1322    }
1323
1324    // ========== SQL Structure Tests ==========
1325
1326    #[test]
1327    fn test_group_by_sql_structure() {
1328        let op: GroupByOperation<TestModel, MockEngine> =
1329            GroupByOperation::new(vec!["department".into()])
1330                .count()
1331                .r#where(Filter::Equals("active".into(), FilterValue::Bool(true)))
1332                .having(having::count_gt(5.0))
1333                .order_by(OrderByField::desc("_count"))
1334                .take(10)
1335                .skip(5);
1336
1337        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1338
1339        // Check SQL clause ordering: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET
1340        let select_pos = sql.find("SELECT").unwrap();
1341        let from_pos = sql.find("FROM").unwrap();
1342        let where_pos = sql.find("WHERE").unwrap();
1343        let group_pos = sql.find("GROUP BY").unwrap();
1344        let having_pos = sql.find("HAVING").unwrap();
1345        let order_pos = sql.find("ORDER BY").unwrap();
1346        let limit_pos = sql.find("LIMIT").unwrap();
1347        let offset_pos = sql.find("OFFSET").unwrap();
1348
1349        assert!(select_pos < from_pos);
1350        assert!(from_pos < where_pos);
1351        assert!(where_pos < group_pos);
1352        assert!(group_pos < having_pos);
1353        assert!(having_pos < order_pos);
1354        assert!(order_pos < limit_pos);
1355        assert!(limit_pos < offset_pos);
1356    }
1357
1358    #[test]
1359    fn test_aggregate_no_group_by() {
1360        let op: AggregateOperation<TestModel, MockEngine> =
1361            AggregateOperation::new().count().sum("score");
1362
1363        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1364
1365        assert!(!sql.contains("GROUP BY"));
1366    }
1367
1368    #[test]
1369    fn test_group_by_empty_columns() {
1370        let op: GroupByOperation<TestModel, MockEngine> = GroupByOperation::new(vec![]).count();
1371
1372        let (sql, _) = op.build_sql(&crate::dialect::Postgres);
1373
1374        // Empty group columns should not produce GROUP BY
1375        assert!(!sql.contains("GROUP BY"));
1376    }
1377}
prax_query/operations/aggregate.rs

prax_query/operations/
aggregate.rs
