sqlmodel_query/
select.rs

1//! SELECT query builder.
2
3use crate::clause::{Limit, Offset, OrderBy, Where};
4use crate::eager::{EagerLoader, IncludePath, build_join_clause, find_relationship};
5use crate::expr::{Dialect, Expr};
6use crate::join::Join;
7use asupersync::{Cx, Outcome};
8use sqlmodel_core::{Connection, Model, RelationshipKind, Value};
9use std::marker::PhantomData;
10
11/// Information about a JOIN for eager loading.
12///
13/// Used internally to track which relationships are being eagerly loaded
14/// and how to hydrate them from the query results.
15#[derive(Debug, Clone)]
16#[allow(dead_code)] // Fields used for full hydration (future implementation)
17struct EagerJoinInfo {
18    /// Name of the relationship field.
19    relationship_name: &'static str,
20    /// Table name of the related model.
21    related_table: &'static str,
22    /// Kind of relationship.
23    kind: RelationshipKind,
24    /// Nested relationships to load.
25    nested: Vec<IncludePath>,
26}
27
28/// A SELECT query builder.
29///
30/// Provides a fluent API for building SELECT queries with
31/// type-safe column references and conditions.
32#[derive(Debug, Clone)]
33pub struct Select<M: Model> {
34    /// Columns to select (empty = all)
35    columns: Vec<String>,
36    /// WHERE clause conditions
37    where_clause: Option<Where>,
38    /// ORDER BY clauses
39    order_by: Vec<OrderBy>,
40    /// JOIN clauses
41    joins: Vec<Join>,
42    /// LIMIT clause
43    limit: Option<Limit>,
44    /// OFFSET clause
45    offset: Option<Offset>,
46    /// GROUP BY columns
47    group_by: Vec<String>,
48    /// HAVING clause
49    having: Option<Where>,
50    /// DISTINCT flag
51    distinct: bool,
52    /// FOR UPDATE flag
53    for_update: bool,
54    /// Eager loading configuration
55    eager_loader: Option<EagerLoader<M>>,
56    /// Model type marker
57    _marker: PhantomData<M>,
58}
59
60impl<M: Model> Select<M> {
61    /// Create a new SELECT query for the model's table.
62    pub fn new() -> Self {
63        Self {
64            columns: Vec::new(),
65            where_clause: None,
66            order_by: Vec::new(),
67            joins: Vec::new(),
68            limit: None,
69            offset: None,
70            group_by: Vec::new(),
71            having: None,
72            distinct: false,
73            for_update: false,
74            eager_loader: None,
75            _marker: PhantomData,
76        }
77    }
78
79    /// Select specific columns.
80    pub fn columns(mut self, cols: &[&str]) -> Self {
81        self.columns = cols.iter().map(|&s| s.to_string()).collect();
82        self
83    }
84
85    /// Add a WHERE condition.
86    pub fn filter(mut self, expr: Expr) -> Self {
87        self.where_clause = Some(match self.where_clause {
88            Some(existing) => existing.and(expr),
89            None => Where::new(expr),
90        });
91        self
92    }
93
94    /// Add an OR WHERE condition.
95    pub fn or_filter(mut self, expr: Expr) -> Self {
96        self.where_clause = Some(match self.where_clause {
97            Some(existing) => existing.or(expr),
98            None => Where::new(expr),
99        });
100        self
101    }
102
103    /// Add ORDER BY clause.
104    pub fn order_by(mut self, order: OrderBy) -> Self {
105        self.order_by.push(order);
106        self
107    }
108
109    /// Add a JOIN clause.
110    pub fn join(mut self, join: Join) -> Self {
111        self.joins.push(join);
112        self
113    }
114
115    /// Set LIMIT.
116    pub fn limit(mut self, n: u64) -> Self {
117        self.limit = Some(Limit(n));
118        self
119    }
120
121    /// Set OFFSET.
122    pub fn offset(mut self, n: u64) -> Self {
123        self.offset = Some(Offset(n));
124        self
125    }
126
127    /// Add GROUP BY columns.
128    pub fn group_by(mut self, cols: &[&str]) -> Self {
129        self.group_by.extend(cols.iter().map(|&s| s.to_string()));
130        self
131    }
132
133    /// Add HAVING condition.
134    pub fn having(mut self, expr: Expr) -> Self {
135        self.having = Some(match self.having {
136            Some(existing) => existing.and(expr),
137            None => Where::new(expr),
138        });
139        self
140    }
141
142    /// Make this a DISTINCT query.
143    pub fn distinct(mut self) -> Self {
144        self.distinct = true;
145        self
146    }
147
148    /// Add FOR UPDATE lock.
149    pub fn for_update(mut self) -> Self {
150        self.for_update = true;
151        self
152    }
153
154    /// Configure eager loading for relationships.
155    ///
156    /// # Example
157    ///
158    /// ```ignore
159    /// let heroes = select!(Hero)
160    ///     .eager(EagerLoader::new().include("team"))
161    ///     .all_eager(cx, &conn)
162    ///     .await?;
163    /// ```
164    pub fn eager(mut self, loader: EagerLoader<M>) -> Self {
165        self.eager_loader = Some(loader);
166        self
167    }
168
169    /// Build SQL for eager loading with JOINs.
170    ///
171    /// Generates SELECT with aliased columns and LEFT JOINs for included relationships.
172    #[tracing::instrument(level = "trace", skip(self))]
173    fn build_eager(&self) -> (String, Vec<Value>, Vec<EagerJoinInfo>) {
174        let mut sql = String::new();
175        let mut params = Vec::new();
176        let mut join_info = Vec::new();
177
178        // Collect parent table columns
179        let parent_cols: Vec<&str> = M::fields().iter().map(|f| f.name).collect();
180
181        // Start with SELECT DISTINCT to avoid duplicates from JOINs
182        sql.push_str("SELECT ");
183        if self.distinct {
184            sql.push_str("DISTINCT ");
185        }
186
187        // Build column list with parent table aliased
188        let mut col_parts = Vec::new();
189        for col in &parent_cols {
190            col_parts.push(format!(
191                "{}.{} AS {}__{}",
192                M::TABLE_NAME,
193                col,
194                M::TABLE_NAME,
195                col
196            ));
197        }
198
199        // Add columns for each eagerly loaded relationship
200        if let Some(loader) = &self.eager_loader {
201            for include in loader.includes() {
202                if let Some(rel) = find_relationship::<M>(include.relationship) {
203                    // For now, we assume related model has same column structure
204                    // In practice, we'd need to look up the related Model's fields
205                    join_info.push(EagerJoinInfo {
206                        relationship_name: include.relationship,
207                        related_table: rel.related_table,
208                        kind: rel.kind,
209                        nested: include.nested.clone(),
210                    });
211
212                    // Add aliased columns for related table
213                    // We select all columns and alias them
214                    col_parts.push(format!("{}.*", rel.related_table));
215                }
216            }
217        }
218
219        sql.push_str(&col_parts.join(", "));
220
221        // FROM
222        sql.push_str(" FROM ");
223        sql.push_str(M::TABLE_NAME);
224
225        // Add JOINs for eager loading
226        if let Some(loader) = &self.eager_loader {
227            for include in loader.includes() {
228                if let Some(rel) = find_relationship::<M>(include.relationship) {
229                    let (join_sql, join_params) =
230                        build_join_clause(M::TABLE_NAME, rel, params.len());
231                    sql.push_str(&join_sql);
232                    params.extend(join_params);
233                }
234            }
235        }
236
237        // Additional explicit JOINs
238        for join in &self.joins {
239            sql.push_str(&join.build(&mut params, 0));
240        }
241
242        // WHERE
243        if let Some(where_clause) = &self.where_clause {
244            let (where_sql, where_params) = where_clause.build_with_offset(params.len());
245            sql.push_str(" WHERE ");
246            sql.push_str(&where_sql);
247            params.extend(where_params);
248        }
249
250        // GROUP BY
251        if !self.group_by.is_empty() {
252            sql.push_str(" GROUP BY ");
253            sql.push_str(&self.group_by.join(", "));
254        }
255
256        // HAVING
257        if let Some(having) = &self.having {
258            let (having_sql, having_params) = having.build_with_offset(params.len());
259            sql.push_str(" HAVING ");
260            sql.push_str(&having_sql);
261            params.extend(having_params);
262        }
263
264        // ORDER BY
265        if !self.order_by.is_empty() {
266            sql.push_str(" ORDER BY ");
267            let order_strs: Vec<_> = self
268                .order_by
269                .iter()
270                .map(|o| o.build(Dialect::default(), &mut params, 0))
271                .collect();
272            sql.push_str(&order_strs.join(", "));
273        }
274
275        // LIMIT
276        if let Some(Limit(n)) = self.limit {
277            sql.push_str(&format!(" LIMIT {}", n));
278        }
279
280        // OFFSET
281        if let Some(Offset(n)) = self.offset {
282            sql.push_str(&format!(" OFFSET {}", n));
283        }
284
285        (sql, params, join_info)
286    }
287
288    /// Execute the query with eager loading and return hydrated models.
289    ///
290    /// This method fetches the parent models along with their eagerly loaded
291    /// relationships in a single query using JOINs. Results are deduplicated
292    /// by primary key to handle one-to-many JOINs.
293    ///
294    /// # Note
295    ///
296    /// Currently, this method parses parent models from aliased columns and
297    /// deduplicates by primary key. Full hydration of `Related<T>` and
298    /// `RelatedMany<T>` fields requires macro support and is tracked
299    /// separately. The JOIN query is still valuable as it:
300    /// - Fetches all data in a single query (avoiding N+1)
301    /// - Returns related data that can be accessed via `row.subset_by_prefix()`
302    ///
303    /// # Example
304    ///
305    /// ```ignore
306    /// let heroes = select!(Hero)
307    ///     .eager(EagerLoader::new().include("team"))
308    ///     .all_eager(cx, &conn)
309    ///     .await?;
310    /// ```
311    #[tracing::instrument(level = "debug", skip(self, cx, conn))]
312    pub async fn all_eager<C: Connection>(
313        self,
314        cx: &Cx,
315        conn: &C,
316    ) -> Outcome<Vec<M>, sqlmodel_core::Error> {
317        // If no eager loading configured, fall back to regular all()
318        if !self.eager_loader.as_ref().is_some_and(|e| e.has_includes()) {
319            tracing::trace!("No eager loading configured, falling back to regular all()");
320            return self.all(cx, conn).await;
321        }
322
323        let (sql, params, join_info) = self.build_eager();
324
325        tracing::debug!(
326            table = M::TABLE_NAME,
327            includes = join_info.len(),
328            "Executing eager loading query"
329        );
330        tracing::trace!(sql = %sql, "Eager SQL");
331
332        let rows = conn.query(cx, &sql, &params).await;
333
334        rows.and_then(|rows| {
335            tracing::debug!(row_count = rows.len(), "Processing eager query results");
336
337            // Use a map to deduplicate by primary key (JOINs can duplicate parent rows)
338            let mut seen_pks = std::collections::HashSet::new();
339            let mut models = Vec::with_capacity(rows.len());
340
341            for row in &rows {
342                // Extract parent columns using table prefix
343                let parent_row = row.subset_by_prefix(M::TABLE_NAME);
344
345                // Skip if we can't parse (shouldn't happen with well-formed query)
346                if parent_row.is_empty() {
347                    tracing::warn!(
348                        table = M::TABLE_NAME,
349                        "Row has no columns with parent table prefix"
350                    );
351                    // Fall back to trying the row as-is (backwards compatibility)
352                    match M::from_row(row) {
353                        Ok(model) => {
354                            models.push(model);
355                        }
356                        Err(e) => {
357                            tracing::debug!(error = %e, "Failed to parse model from row");
358                            return Outcome::Err(e);
359                        }
360                    }
361                    continue;
362                }
363
364                // Parse the parent model from extracted columns
365                match M::from_row(&parent_row) {
366                    Ok(model) => {
367                        // Deduplicate by primary key
368                        let pk = model.primary_key_value();
369                        let pk_hash = {
370                            use std::hash::{Hash, Hasher};
371                            let mut hasher = std::collections::hash_map::DefaultHasher::new();
372                            // Hash the debug representation as a simple PK identifier
373                            format!("{:?}", pk).hash(&mut hasher);
374                            hasher.finish()
375                        };
376
377                        if seen_pks.insert(pk_hash) {
378                            models.push(model);
379                        }
380                    }
381                    Err(e) => {
382                        tracing::debug!(error = %e, "Failed to parse model from prefixed row");
383                        return Outcome::Err(e);
384                    }
385                }
386            }
387
388            tracing::debug!(
389                unique_models = models.len(),
390                "Eager loading complete (deduplicated)"
391            );
392            Outcome::Ok(models)
393        })
394    }
395
396    /// Build the SQL query and parameters.
397    pub fn build(&self) -> (String, Vec<Value>) {
398        let mut sql = String::new();
399        let mut params = Vec::new();
400
401        // SELECT
402        sql.push_str("SELECT ");
403        if self.distinct {
404            sql.push_str("DISTINCT ");
405        }
406
407        if self.columns.is_empty() {
408            sql.push('*');
409        } else {
410            sql.push_str(&self.columns.join(", "));
411        }
412
413        // FROM
414        sql.push_str(" FROM ");
415        sql.push_str(M::TABLE_NAME);
416
417        // JOINs
418        for join in &self.joins {
419            sql.push_str(&join.build(&mut params, 0));
420        }
421
422        // WHERE
423        if let Some(where_clause) = &self.where_clause {
424            let (where_sql, where_params) = where_clause.build_with_offset(params.len());
425            sql.push_str(" WHERE ");
426            sql.push_str(&where_sql);
427            params.extend(where_params);
428        }
429
430        // GROUP BY
431        if !self.group_by.is_empty() {
432            sql.push_str(" GROUP BY ");
433            sql.push_str(&self.group_by.join(", "));
434        }
435
436        // HAVING
437        if let Some(having) = &self.having {
438            let (having_sql, having_params) = having.build_with_offset(params.len());
439            sql.push_str(" HAVING ");
440            sql.push_str(&having_sql);
441            params.extend(having_params);
442        }
443
444        // ORDER BY
445        if !self.order_by.is_empty() {
446            sql.push_str(" ORDER BY ");
447            let order_strs: Vec<_> = self
448                .order_by
449                .iter()
450                .map(|o| o.build(Dialect::default(), &mut params, 0))
451                .collect();
452            sql.push_str(&order_strs.join(", "));
453        }
454
455        // LIMIT
456        if let Some(Limit(n)) = self.limit {
457            sql.push_str(&format!(" LIMIT {}", n));
458        }
459
460        // OFFSET
461        if let Some(Offset(n)) = self.offset {
462            sql.push_str(&format!(" OFFSET {}", n));
463        }
464
465        // FOR UPDATE
466        if self.for_update {
467            sql.push_str(" FOR UPDATE");
468        }
469
470        (sql, params)
471    }
472
473    /// Convert this SELECT query to an EXISTS expression.
474    ///
475    /// Creates an `Expr::Exists` that can be used in WHERE clauses of other queries.
476    /// For performance, the SELECT is automatically optimized to `SELECT 1` when
477    /// generating the EXISTS subquery.
478    ///
479    /// # Example
480    ///
481    /// ```ignore
482    /// // Find customers who have at least one order
483    /// let has_orders = Select::<Order>::new()
484    ///     .filter(Expr::raw("orders.customer_id = customers.id"))
485    ///     .into_exists();
486    ///
487    /// let customers = Select::<Customer>::new()
488    ///     .filter(has_orders)
489    ///     .all(cx, &conn)
490    ///     .await?;
491    ///
492    /// // Generates: SELECT * FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id)
493    /// ```
494    pub fn into_exists(self) -> Expr {
495        // Build the SELECT 1 version for optimal EXISTS
496        let (sql, params) = self.build_exists_subquery();
497        Expr::exists(sql, params)
498    }
499
500    /// Convert this SELECT query to a NOT EXISTS expression.
501    ///
502    /// Creates an `Expr::Exists` (negated) that can be used in WHERE clauses.
503    /// For performance, the SELECT is automatically optimized to `SELECT 1`.
504    ///
505    /// # Example
506    ///
507    /// ```ignore
508    /// // Find customers with no orders
509    /// let has_no_orders = Select::<Order>::new()
510    ///     .filter(Expr::raw("orders.customer_id = customers.id"))
511    ///     .into_not_exists();
512    ///
513    /// let customers = Select::<Customer>::new()
514    ///     .filter(has_no_orders)
515    ///     .all(cx, &conn)
516    ///     .await?;
517    ///
518    /// // Generates: SELECT * FROM customers WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id)
519    /// ```
520    pub fn into_not_exists(self) -> Expr {
521        let (sql, params) = self.build_exists_subquery();
522        Expr::not_exists(sql, params)
523    }
524
525    /// Convert this SELECT into a LATERAL JOIN.
526    ///
527    /// Creates a `Join` with `lateral: true` that can be added to another query.
528    /// The subquery can reference columns from the outer query.
529    ///
530    /// Supported by PostgreSQL (9.3+) and MySQL (8.0.14+). Not supported by SQLite.
531    ///
532    /// # Arguments
533    ///
534    /// * `alias` - Required alias for the lateral subquery
535    /// * `join_type` - The join type (typically `Left` or `Inner`)
536    /// * `on` - ON condition (use `Expr::raw("TRUE")` for implicit TRUE)
537    ///
538    /// # Example
539    ///
540    /// ```ignore
541    /// // Get top 3 recent orders per customer
542    /// let recent_orders = Select::<Order>::new()
543    ///     .filter(Expr::raw("orders.customer_id = customers.id"))
544    ///     .order_by(OrderBy::desc("date"))
545    ///     .limit(3)
546    ///     .into_lateral_join("recent_orders", JoinType::Left, Expr::raw("TRUE"));
547    ///
548    /// let query = Select::<Customer>::new().join(recent_orders);
549    /// ```
550    pub fn into_lateral_join(
551        self,
552        alias: impl Into<String>,
553        join_type: crate::JoinType,
554        on: Expr,
555    ) -> crate::Join {
556        let (sql, params) = self.build();
557        crate::Join::lateral(join_type, sql, alias, on, params)
558    }
559
560    /// Build an optimized EXISTS subquery (SELECT 1 instead of SELECT *).
561    fn build_exists_subquery(&self) -> (String, Vec<Value>) {
562        let mut sql = String::new();
563        let mut params = Vec::new();
564
565        // SELECT 1 for optimal EXISTS performance
566        sql.push_str("SELECT 1 FROM ");
567        sql.push_str(M::TABLE_NAME);
568
569        // JOINs (if any)
570        for join in &self.joins {
571            sql.push_str(&join.build(&mut params, 0));
572        }
573
574        // WHERE
575        if let Some(where_clause) = &self.where_clause {
576            let (where_sql, where_params) = where_clause.build_with_offset(params.len());
577            sql.push_str(" WHERE ");
578            sql.push_str(&where_sql);
579            params.extend(where_params);
580        }
581
582        // GROUP BY (rare in EXISTS but supported)
583        if !self.group_by.is_empty() {
584            sql.push_str(" GROUP BY ");
585            sql.push_str(&self.group_by.join(", "));
586        }
587
588        // HAVING (rare in EXISTS but supported)
589        if let Some(having) = &self.having {
590            let (having_sql, having_params) = having.build_with_offset(params.len());
591            sql.push_str(" HAVING ");
592            sql.push_str(&having_sql);
593            params.extend(having_params);
594        }
595
596        // Note: ORDER BY, LIMIT, OFFSET are omitted in EXISTS subquery as they have no effect
597
598        (sql, params)
599    }
600
601    /// Execute the query and return all matching rows as models.
602    pub async fn all<C: Connection>(
603        self,
604        cx: &Cx,
605        conn: &C,
606    ) -> Outcome<Vec<M>, sqlmodel_core::Error> {
607        let (sql, params) = self.build();
608        let rows = conn.query(cx, &sql, &params).await;
609
610        rows.and_then(|rows| {
611            let mut models = Vec::with_capacity(rows.len());
612            for row in &rows {
613                match M::from_row(row) {
614                    Ok(model) => models.push(model),
615                    Err(e) => return Outcome::Err(e),
616                }
617            }
618            Outcome::Ok(models)
619        })
620    }
621
622    /// Execute the query and return the first matching row.
623    pub async fn first<C: Connection>(
624        self,
625        cx: &Cx,
626        conn: &C,
627    ) -> Outcome<Option<M>, sqlmodel_core::Error> {
628        let query = self.limit(1);
629        let (sql, params) = query.build();
630        let row = conn.query_one(cx, &sql, &params).await;
631
632        row.and_then(|opt_row| match opt_row {
633            Some(row) => match M::from_row(&row) {
634                Ok(model) => Outcome::Ok(Some(model)),
635                Err(e) => Outcome::Err(e),
636            },
637            None => Outcome::Ok(None),
638        })
639    }
640
641    /// Execute the query and return exactly one row, or error.
642    pub async fn one<C: Connection>(self, cx: &Cx, conn: &C) -> Outcome<M, sqlmodel_core::Error> {
643        match self.first(cx, conn).await {
644            Outcome::Ok(Some(model)) => Outcome::Ok(model),
645            Outcome::Ok(None) => Outcome::Err(sqlmodel_core::Error::Custom(
646                "Expected one row, found none".to_string(),
647            )),
648            Outcome::Err(e) => Outcome::Err(e),
649            Outcome::Cancelled(r) => Outcome::Cancelled(r),
650            Outcome::Panicked(p) => Outcome::Panicked(p),
651        }
652    }
653
654    /// Execute the query and return the count of matching rows.
655    pub async fn count<C: Connection>(
656        self,
657        cx: &Cx,
658        conn: &C,
659    ) -> Outcome<u64, sqlmodel_core::Error> {
660        let mut count_query = self;
661        count_query.columns = vec!["COUNT(*) as count".to_string()];
662        count_query.order_by.clear();
663        count_query.limit = None;
664        count_query.offset = None;
665
666        let (sql, params) = count_query.build();
667        let row = conn.query_one(cx, &sql, &params).await;
668
669        row.and_then(|opt_row| match opt_row {
670            Some(row) => match row.get_named::<i64>("count") {
671                Ok(count) => Outcome::Ok(count as u64),
672                Err(e) => Outcome::Err(e),
673            },
674            None => Outcome::Ok(0),
675        })
676    }
677
678    /// Check if any rows match the query.
679    pub async fn exists<C: Connection>(
680        self,
681        cx: &Cx,
682        conn: &C,
683    ) -> Outcome<bool, sqlmodel_core::Error> {
684        let count = self.count(cx, conn).await;
685        count.map(|n| n > 0)
686    }
687}
688
689impl<M: Model> Default for Select<M> {
690    fn default() -> Self {
691        Self::new()
692    }
693}
694
695#[cfg(test)]
696mod tests {
697    use super::*;
698    use sqlmodel_core::{Error, FieldInfo, Result, Row, Value};
699
700    #[derive(Debug, Clone)]
701    struct Hero;
702
703    impl Model for Hero {
704        const TABLE_NAME: &'static str = "heroes";
705        const PRIMARY_KEY: &'static [&'static str] = &["id"];
706
707        fn fields() -> &'static [FieldInfo] {
708            &[]
709        }
710
711        fn to_row(&self) -> Vec<(&'static str, Value)> {
712            Vec::new()
713        }
714
715        fn from_row(_row: &Row) -> Result<Self> {
716            Err(Error::Custom("not used in tests".to_string()))
717        }
718
719        fn primary_key_value(&self) -> Vec<Value> {
720            Vec::new()
721        }
722
723        fn is_new(&self) -> bool {
724            true
725        }
726    }
727
728    #[test]
729    fn build_collects_params_across_joins_where_having() {
730        let query = Select::<Hero>::new()
731            .join(Join::inner(
732                "teams",
733                Expr::qualified("teams", "active").eq(true),
734            ))
735            .filter(Expr::col("age").gt(18))
736            .group_by(&["team_id"])
737            .having(Expr::col("count").gt(1));
738
739        let (sql, params) = query.build();
740
741        assert_eq!(
742            sql,
743            "SELECT * FROM heroes INNER JOIN teams ON \"teams\".\"active\" = $1 WHERE \"age\" > $2 GROUP BY team_id HAVING \"count\" > $3"
744        );
745        assert_eq!(
746            params,
747            vec![Value::Bool(true), Value::Int(18), Value::Int(1)]
748        );
749    }
750
751    #[test]
752    fn test_select_all_columns() {
753        let query = Select::<Hero>::new();
754        let (sql, params) = query.build();
755
756        assert_eq!(sql, "SELECT * FROM heroes");
757        assert!(params.is_empty());
758    }
759
760    #[test]
761    fn test_select_specific_columns() {
762        let query = Select::<Hero>::new().columns(&["id", "name", "power"]);
763        let (sql, params) = query.build();
764
765        assert_eq!(sql, "SELECT id, name, power FROM heroes");
766        assert!(params.is_empty());
767    }
768
769    #[test]
770    fn test_select_distinct() {
771        let query = Select::<Hero>::new().columns(&["team_id"]).distinct();
772        let (sql, params) = query.build();
773
774        assert_eq!(sql, "SELECT DISTINCT team_id FROM heroes");
775        assert!(params.is_empty());
776    }
777
778    #[test]
779    fn test_select_with_simple_filter() {
780        let query = Select::<Hero>::new().filter(Expr::col("active").eq(true));
781        let (sql, params) = query.build();
782
783        assert_eq!(sql, "SELECT * FROM heroes WHERE \"active\" = $1");
784        assert_eq!(params, vec![Value::Bool(true)]);
785    }
786
787    #[test]
788    fn test_select_with_multiple_and_filters() {
789        let query = Select::<Hero>::new()
790            .filter(Expr::col("active").eq(true))
791            .filter(Expr::col("age").gt(18));
792        let (sql, params) = query.build();
793
794        assert_eq!(
795            sql,
796            "SELECT * FROM heroes WHERE \"active\" = $1 AND \"age\" > $2"
797        );
798        assert_eq!(params, vec![Value::Bool(true), Value::Int(18)]);
799    }
800
801    #[test]
802    fn test_select_with_or_filter() {
803        let query = Select::<Hero>::new()
804            .filter(Expr::col("role").eq("warrior"))
805            .or_filter(Expr::col("role").eq("mage"));
806        let (sql, params) = query.build();
807
808        assert_eq!(
809            sql,
810            "SELECT * FROM heroes WHERE \"role\" = $1 OR \"role\" = $2"
811        );
812        assert_eq!(
813            params,
814            vec![
815                Value::Text("warrior".to_string()),
816                Value::Text("mage".to_string())
817            ]
818        );
819    }
820
821    #[test]
822    fn test_select_with_order_by_asc() {
823        let query = Select::<Hero>::new().order_by(OrderBy::asc(Expr::col("name")));
824        let (sql, params) = query.build();
825
826        assert_eq!(sql, "SELECT * FROM heroes ORDER BY \"name\" ASC");
827        assert!(params.is_empty());
828    }
829
830    #[test]
831    fn test_select_with_order_by_desc() {
832        let query = Select::<Hero>::new().order_by(OrderBy::desc(Expr::col("created_at")));
833        let (sql, params) = query.build();
834
835        assert_eq!(sql, "SELECT * FROM heroes ORDER BY \"created_at\" DESC");
836        assert!(params.is_empty());
837    }
838
839    #[test]
840    fn test_select_with_multiple_order_by() {
841        let query = Select::<Hero>::new()
842            .order_by(OrderBy::asc(Expr::col("team_id")))
843            .order_by(OrderBy::asc(Expr::col("name")));
844        let (sql, params) = query.build();
845
846        assert_eq!(
847            sql,
848            "SELECT * FROM heroes ORDER BY \"team_id\" ASC, \"name\" ASC"
849        );
850        assert!(params.is_empty());
851    }
852
853    #[test]
854    fn test_select_with_limit() {
855        let query = Select::<Hero>::new().limit(10);
856        let (sql, params) = query.build();
857
858        assert_eq!(sql, "SELECT * FROM heroes LIMIT 10");
859        assert!(params.is_empty());
860    }
861
862    #[test]
863    fn test_select_with_offset() {
864        let query = Select::<Hero>::new().offset(20);
865        let (sql, params) = query.build();
866
867        assert_eq!(sql, "SELECT * FROM heroes OFFSET 20");
868        assert!(params.is_empty());
869    }
870
871    #[test]
872    fn test_select_with_limit_and_offset() {
873        let query = Select::<Hero>::new().limit(10).offset(20);
874        let (sql, params) = query.build();
875
876        assert_eq!(sql, "SELECT * FROM heroes LIMIT 10 OFFSET 20");
877        assert!(params.is_empty());
878    }
879
880    #[test]
881    fn test_select_with_group_by() {
882        let query = Select::<Hero>::new()
883            .columns(&["team_id", "COUNT(*) as count"])
884            .group_by(&["team_id"]);
885        let (sql, params) = query.build();
886
887        assert_eq!(
888            sql,
889            "SELECT team_id, COUNT(*) as count FROM heroes GROUP BY team_id"
890        );
891        assert!(params.is_empty());
892    }
893
894    #[test]
895    fn test_select_with_multiple_group_by() {
896        let query = Select::<Hero>::new()
897            .columns(&["team_id", "role", "COUNT(*) as count"])
898            .group_by(&["team_id", "role"]);
899        let (sql, params) = query.build();
900
901        assert_eq!(
902            sql,
903            "SELECT team_id, role, COUNT(*) as count FROM heroes GROUP BY team_id, role"
904        );
905        assert!(params.is_empty());
906    }
907
908    #[test]
909    fn test_select_with_for_update() {
910        let query = Select::<Hero>::new()
911            .filter(Expr::col("id").eq(1))
912            .for_update();
913        let (sql, params) = query.build();
914
915        assert_eq!(sql, "SELECT * FROM heroes WHERE \"id\" = $1 FOR UPDATE");
916        assert_eq!(params, vec![Value::Int(1)]);
917    }
918
919    #[test]
920    fn test_select_inner_join() {
921        let query = Select::<Hero>::new().join(Join::inner(
922            "teams",
923            Expr::qualified("heroes", "team_id").eq(Expr::qualified("teams", "id")),
924        ));
925        let (sql, _) = query.build();
926
927        assert!(sql.contains("INNER JOIN teams ON"));
928    }
929
930    #[test]
931    fn test_select_left_join() {
932        let query = Select::<Hero>::new().join(Join::left(
933            "teams",
934            Expr::qualified("heroes", "team_id").eq(Expr::qualified("teams", "id")),
935        ));
936        let (sql, _) = query.build();
937
938        assert!(sql.contains("LEFT JOIN teams ON"));
939    }
940
941    #[test]
942    fn test_select_right_join() {
943        let query = Select::<Hero>::new().join(Join::right(
944            "teams",
945            Expr::qualified("heroes", "team_id").eq(Expr::qualified("teams", "id")),
946        ));
947        let (sql, _) = query.build();
948
949        assert!(sql.contains("RIGHT JOIN teams ON"));
950    }
951
952    #[test]
953    fn test_select_multiple_joins() {
954        let query = Select::<Hero>::new()
955            .join(Join::inner(
956                "teams",
957                Expr::qualified("heroes", "team_id").eq(Expr::qualified("teams", "id")),
958            ))
959            .join(Join::left(
960                "powers",
961                Expr::qualified("heroes", "id").eq(Expr::qualified("powers", "hero_id")),
962            ));
963        let (sql, _) = query.build();
964
965        assert!(sql.contains("INNER JOIN teams ON"));
966        assert!(sql.contains("LEFT JOIN powers ON"));
967    }
968
969    #[test]
970    fn test_select_complex_query() {
971        let query = Select::<Hero>::new()
972            .columns(&["heroes.id", "heroes.name", "teams.name as team_name"])
973            .distinct()
974            .join(Join::inner(
975                "teams",
976                Expr::qualified("heroes", "team_id").eq(Expr::qualified("teams", "id")),
977            ))
978            .filter(Expr::col("active").eq(true))
979            .filter(Expr::col("level").gt(10))
980            .group_by(&["heroes.id", "heroes.name", "teams.name"])
981            .having(Expr::col("score").gt(100))
982            .order_by(OrderBy::desc(Expr::col("level")))
983            .limit(50)
984            .offset(0);
985        let (sql, params) = query.build();
986
987        assert!(sql.starts_with(
988            "SELECT DISTINCT heroes.id, heroes.name, teams.name as team_name FROM heroes"
989        ));
990        assert!(sql.contains("INNER JOIN teams ON"));
991        assert!(sql.contains("WHERE"));
992        assert!(sql.contains("GROUP BY"));
993        assert!(sql.contains("HAVING"));
994        assert!(sql.contains("ORDER BY"));
995        assert!(sql.contains("LIMIT 50"));
996        assert!(sql.contains("OFFSET 0"));
997
998        // Params: true (active), 10 (level), 100 (score)
999        // Note: join condition uses column comparison, not value param
1000        assert_eq!(params.len(), 3);
1001    }
1002
1003    #[test]
1004    fn test_select_default() {
1005        let query = Select::<Hero>::default();
1006        let (sql, _) = query.build();
1007        assert_eq!(sql, "SELECT * FROM heroes");
1008    }
1009
1010    #[test]
1011    fn test_select_clone() {
1012        let query = Select::<Hero>::new()
1013            .filter(Expr::col("id").eq(1))
1014            .limit(10);
1015        let cloned = query.clone();
1016
1017        let (sql1, params1) = query.build();
1018        let (sql2, params2) = cloned.build();
1019
1020        assert_eq!(sql1, sql2);
1021        assert_eq!(params1, params2);
1022    }
1023
1024    // ========================================================================
1025    // Eager Loading Tests
1026    // ========================================================================
1027
1028    use sqlmodel_core::RelationshipInfo;
1029
1030    /// A test hero model with relationships defined.
1031    #[derive(Debug, Clone)]
1032    struct EagerHero;
1033
1034    impl Model for EagerHero {
1035        const TABLE_NAME: &'static str = "heroes";
1036        const PRIMARY_KEY: &'static [&'static str] = &["id"];
1037        const RELATIONSHIPS: &'static [RelationshipInfo] =
1038            &[
1039                RelationshipInfo::new("team", "teams", RelationshipKind::ManyToOne)
1040                    .local_key("team_id"),
1041            ];
1042
1043        fn fields() -> &'static [FieldInfo] {
1044            static FIELDS: &[FieldInfo] = &[
1045                FieldInfo::new("id", "id", sqlmodel_core::SqlType::BigInt),
1046                FieldInfo::new("name", "name", sqlmodel_core::SqlType::Text),
1047                FieldInfo::new("team_id", "team_id", sqlmodel_core::SqlType::BigInt),
1048            ];
1049            FIELDS
1050        }
1051
1052        fn to_row(&self) -> Vec<(&'static str, Value)> {
1053            Vec::new()
1054        }
1055
1056        fn from_row(_row: &Row) -> Result<Self> {
1057            Err(Error::Custom("not used in tests".to_string()))
1058        }
1059
1060        fn primary_key_value(&self) -> Vec<Value> {
1061            Vec::new()
1062        }
1063
1064        fn is_new(&self) -> bool {
1065            true
1066        }
1067    }
1068
1069    #[test]
1070    fn test_select_with_eager_loader() {
1071        let loader = EagerLoader::<EagerHero>::new().include("team");
1072        let query = Select::<EagerHero>::new().eager(loader);
1073
1074        // Verify eager_loader is set
1075        assert!(query.eager_loader.is_some());
1076        assert!(query.eager_loader.as_ref().unwrap().has_includes());
1077    }
1078
1079    #[test]
1080    fn test_select_eager_generates_join() {
1081        let loader = EagerLoader::<EagerHero>::new().include("team");
1082        let query = Select::<EagerHero>::new().eager(loader);
1083
1084        let (sql, params, join_info) = query.build_eager();
1085
1086        // Should have LEFT JOIN for team relationship
1087        assert!(sql.contains("LEFT JOIN teams"));
1088        assert!(sql.contains("heroes.team_id = teams.id"));
1089
1090        // Should have aliased columns for parent table
1091        assert!(sql.contains("heroes.id AS heroes__id"));
1092        assert!(sql.contains("heroes.name AS heroes__name"));
1093        assert!(sql.contains("heroes.team_id AS heroes__team_id"));
1094
1095        // Should have join info
1096        assert_eq!(join_info.len(), 1);
1097        assert!(params.is_empty());
1098    }
1099
1100    #[test]
1101    fn test_select_eager_with_filter() {
1102        let loader = EagerLoader::<EagerHero>::new().include("team");
1103        let query = Select::<EagerHero>::new()
1104            .eager(loader)
1105            .filter(Expr::col("active").eq(true));
1106
1107        let (sql, params, _) = query.build_eager();
1108
1109        assert!(sql.contains("LEFT JOIN teams"));
1110        assert!(sql.contains("WHERE"));
1111        assert!(sql.contains("\"active\" = $1"));
1112        assert_eq!(params, vec![Value::Bool(true)]);
1113    }
1114
1115    #[test]
1116    fn test_select_eager_with_order_and_limit() {
1117        let loader = EagerLoader::<EagerHero>::new().include("team");
1118        let query = Select::<EagerHero>::new()
1119            .eager(loader)
1120            .order_by(OrderBy::asc(Expr::col("name")))
1121            .limit(10)
1122            .offset(5);
1123
1124        let (sql, _, _) = query.build_eager();
1125
1126        assert!(sql.contains("LEFT JOIN teams"));
1127        assert!(sql.contains("ORDER BY"));
1128        assert!(sql.contains("LIMIT 10"));
1129        assert!(sql.contains("OFFSET 5"));
1130    }
1131
1132    #[test]
1133    fn test_select_eager_no_includes_fallback() {
1134        // Eager loader with no includes
1135        let loader = EagerLoader::<EagerHero>::new();
1136        let query = Select::<EagerHero>::new().eager(loader);
1137
1138        // all_eager should fall back to regular all() when no includes
1139        // We can't test async execution here, but we can verify the state
1140        assert!(query.eager_loader.is_some());
1141        assert!(!query.eager_loader.as_ref().unwrap().has_includes());
1142    }
1143
1144    #[test]
1145    fn test_select_eager_distinct() {
1146        let loader = EagerLoader::<EagerHero>::new().include("team");
1147        let query = Select::<EagerHero>::new().eager(loader).distinct();
1148
1149        let (sql, _, _) = query.build_eager();
1150
1151        assert!(sql.starts_with("SELECT DISTINCT"));
1152    }
1153
1154    // ==================== EXISTS Tests ====================
1155
1156    #[test]
1157    fn test_select_into_exists() {
1158        // Convert a SELECT query into an EXISTS expression
1159        let exists_expr = Select::<Hero>::new()
1160            .filter(Expr::raw("orders.customer_id = customers.id"))
1161            .into_exists();
1162
1163        let mut params = Vec::new();
1164        let sql = exists_expr.build(&mut params, 0);
1165
1166        // Should generate EXISTS (SELECT 1 FROM heroes WHERE ...)
1167        assert_eq!(
1168            sql,
1169            "EXISTS (SELECT 1 FROM heroes WHERE orders.customer_id = customers.id)"
1170        );
1171    }
1172
1173    #[test]
1174    fn test_select_into_not_exists() {
1175        // Convert a SELECT query into a NOT EXISTS expression
1176        let not_exists_expr = Select::<Hero>::new()
1177            .filter(Expr::raw("orders.customer_id = customers.id"))
1178            .into_not_exists();
1179
1180        let mut params = Vec::new();
1181        let sql = not_exists_expr.build(&mut params, 0);
1182
1183        assert_eq!(
1184            sql,
1185            "NOT EXISTS (SELECT 1 FROM heroes WHERE orders.customer_id = customers.id)"
1186        );
1187    }
1188
1189    #[test]
1190    fn test_select_into_exists_with_params() {
1191        // EXISTS subquery with bound parameters
1192        let exists_expr = Select::<Hero>::new()
1193            .filter(Expr::col("status").eq("active"))
1194            .into_exists();
1195
1196        let mut params = Vec::new();
1197        let sql = exists_expr.build(&mut params, 0);
1198
1199        assert_eq!(sql, "EXISTS (SELECT 1 FROM heroes WHERE \"status\" = $1)");
1200        assert_eq!(params.len(), 1);
1201        assert_eq!(params[0], Value::Text("active".to_string()));
1202    }
1203
1204    #[test]
1205    fn test_select_into_exists_with_join() {
1206        // EXISTS subquery with JOIN
1207        let exists_expr = Select::<Hero>::new()
1208            .join(Join::inner(
1209                "teams",
1210                Expr::qualified("heroes", "team_id").eq(Expr::qualified("teams", "id")),
1211            ))
1212            .filter(Expr::col("active").eq(true))
1213            .into_exists();
1214
1215        let mut params = Vec::new();
1216        let sql = exists_expr.build(&mut params, 0);
1217
1218        assert!(sql.starts_with("EXISTS (SELECT 1 FROM heroes"));
1219        assert!(sql.contains("INNER JOIN teams ON"));
1220        assert!(sql.contains("WHERE"));
1221    }
1222
1223    #[test]
1224    fn test_select_into_exists_omits_order_by_limit() {
1225        // ORDER BY, LIMIT, OFFSET should be omitted from EXISTS subquery
1226        // as they have no effect and add unnecessary overhead
1227        let exists_expr = Select::<Hero>::new()
1228            .filter(Expr::col("active").eq(true))
1229            .order_by(OrderBy::asc(Expr::col("name")))
1230            .limit(10)
1231            .offset(5)
1232            .into_exists();
1233
1234        let mut params = Vec::new();
1235        let sql = exists_expr.build(&mut params, 0);
1236
1237        // Should NOT contain ORDER BY, LIMIT, OFFSET
1238        assert!(!sql.contains("ORDER BY"));
1239        assert!(!sql.contains("LIMIT"));
1240        assert!(!sql.contains("OFFSET"));
1241        assert_eq!(sql, "EXISTS (SELECT 1 FROM heroes WHERE \"active\" = $1)");
1242    }
1243
1244    #[test]
1245    fn test_exists_in_outer_query() {
1246        // Use EXISTS expression in a WHERE clause of another query
1247        let has_heroes = Select::<Hero>::new()
1248            .filter(Expr::raw("heroes.team_id = teams.id"))
1249            .into_exists();
1250
1251        // Note: We'd need a Team model to properly test this,
1252        // but we can test the expr combination manually
1253        let outer_expr = Expr::col("active").eq(true).and(has_heroes);
1254
1255        let mut params = Vec::new();
1256        let sql = outer_expr.build(&mut params, 0);
1257
1258        assert_eq!(
1259            sql,
1260            "\"active\" = $1 AND EXISTS (SELECT 1 FROM heroes WHERE heroes.team_id = teams.id)"
1261        );
1262    }
1263}
sqlmodel_query/select.rs

sqlmodel_query/
select.rs
