floz_orm/query/
select.rs

1//! SELECT query builder.
2//!
3//! ```ignore
4//! let (sql, params) = SelectQuery::new("users")
5//!     .where_(col_age.gt(25))
6//!     .order_by(col_name.asc())
7//!     .limit(10)
8//!     .to_sql();
9//! // "SELECT * FROM users WHERE age > $1 ORDER BY name ASC LIMIT 10"
10//! ```
11
12use std::fmt::Write;
13
14use crate::expr::{Expr, OrderExpr};
15use crate::value::Value;
16use super::quote_table;
17
18/// A SELECT query builder.
19///
20/// Supports: WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT, column selection.
21#[derive(Debug, Clone)]
22pub struct SelectQuery {
23    table: String,
24    columns: Option<Vec<String>>,
25    where_clause: Option<Expr>,
26    order_by: Vec<OrderExpr>,
27    limit: Option<u64>,
28    offset: Option<u64>,
29    distinct: bool,
30}
31
32impl SelectQuery {
33    /// Create a new SELECT query for the given table.
34    pub fn new(table: &str) -> Self {
35        Self {
36            table: table.to_string(),
37            columns: None,
38            where_clause: None,
39            order_by: Vec::new(),
40            limit: None,
41            offset: None,
42            distinct: false,
43        }
44    }
45
46    /// Select specific columns instead of `*`.
47    pub fn cols(mut self, columns: &[&str]) -> Self {
48        self.columns = Some(columns.iter().map(|c| c.to_string()).collect());
49        self
50    }
51
52    /// Add a WHERE clause. Multiple calls replace (not append).
53    /// Use `.and()` / `.or()` on `Expr` to compose filters.
54    pub fn where_(mut self, expr: Expr) -> Self {
55        self.where_clause = Some(expr);
56        self
57    }
58
59    /// Add an ORDER BY clause. Multiple calls append ordering.
60    pub fn order_by(mut self, order: OrderExpr) -> Self {
61        self.order_by.push(order);
62        self
63    }
64
65    /// Set the LIMIT.
66    pub fn limit(mut self, n: u64) -> Self {
67        self.limit = Some(n);
68        self
69    }
70
71    /// Set the OFFSET.
72    pub fn offset(mut self, n: u64) -> Self {
73        self.offset = Some(n);
74        self
75    }
76
77    /// Add DISTINCT.
78    pub fn distinct(mut self) -> Self {
79        self.distinct = true;
80        self
81    }
82
83    /// Render to SQL string + parameters.
84    pub fn to_sql(&self) -> (String, Vec<Value>) {
85        let mut sql = String::with_capacity(128);
86        let mut params = Vec::new();
87        let mut idx = 0usize;
88
89        // SELECT [DISTINCT]
90        sql.push_str("SELECT ");
91        if self.distinct {
92            sql.push_str("DISTINCT ");
93        }
94
95        // Columns
96        match &self.columns {
97            Some(cols) => {
98                for (i, col) in cols.iter().enumerate() {
99                    if i > 0 { sql.push_str(", "); }
100                    sql.push_str(col);
101                }
102            }
103            None => sql.push('*'),
104        }
105
106        // FROM
107        write!(sql, " FROM {}", quote_table(&self.table)).unwrap();
108
109        // WHERE
110        if let Some(expr) = &self.where_clause {
111            sql.push_str(" WHERE ");
112            expr.to_sql(&mut sql, &mut params, &mut idx);
113        }
114
115        // ORDER BY
116        if !self.order_by.is_empty() {
117            sql.push_str(" ORDER BY ");
118            for (i, order) in self.order_by.iter().enumerate() {
119                if i > 0 { sql.push_str(", "); }
120                order.to_sql(&mut sql);
121            }
122        }
123
124        // LIMIT
125        if let Some(n) = self.limit {
126            write!(sql, " LIMIT {}", n).unwrap();
127        }
128
129        // OFFSET
130        if let Some(n) = self.offset {
131            write!(sql, " OFFSET {}", n).unwrap();
132        }
133
134        (sql, params)
135    }
136}
137
138#[cfg(test)]
139mod tests {
140    use super::*;
141    use crate::column::Column;
142
143    fn col_id() -> Column<i32> { Column::new("id", "users") }
144    fn col_age() -> Column<i32> { Column::new("age", "users") }
145    fn col_name() -> Column<String> { Column::new("name", "users") }
146    fn col_email() -> Column<String> { Column::new("email", "users") }
147
148    #[test]
149    fn select_star() {
150        let (sql, params) = SelectQuery::new("users").to_sql();
151        assert_eq!(sql, "SELECT * FROM users");
152        assert!(params.is_empty());
153    }
154
155    #[test]
156    fn select_columns() {
157        let (sql, _) = SelectQuery::new("users").cols(&["name", "age"]).to_sql();
158        assert_eq!(sql, "SELECT name, age FROM users");
159    }
160
161    #[test]
162    fn select_where() {
163        let (sql, params) = SelectQuery::new("users")
164            .where_(col_age().gt(25))
165            .to_sql();
166        assert_eq!(sql, "SELECT * FROM users WHERE age > $1");
167        assert_eq!(params, vec![Value::Int(25)]);
168    }
169
170    #[test]
171    fn select_where_complex() {
172        let (sql, params) = SelectQuery::new("users")
173            .where_(
174                col_age().between(18, 65)
175                    .and(col_email().is_not_null())
176                    .or(col_name().eq("admin")),
177            )
178            .to_sql();
179        assert_eq!(
180            sql,
181            "SELECT * FROM users WHERE ((age BETWEEN $1 AND $2 AND email IS NOT NULL) OR name = $3)"
182        );
183        assert_eq!(params.len(), 3);
184    }
185
186    #[test]
187    fn select_order_by() {
188        let (sql, _) = SelectQuery::new("users")
189            .order_by(col_name().asc())
190            .to_sql();
191        assert_eq!(sql, "SELECT * FROM users ORDER BY name ASC");
192    }
193
194    #[test]
195    fn select_multiple_order() {
196        let (sql, _) = SelectQuery::new("users")
197            .order_by(col_name().asc())
198            .order_by(col_age().desc())
199            .to_sql();
200        assert_eq!(sql, "SELECT * FROM users ORDER BY name ASC, age DESC");
201    }
202
203    #[test]
204    fn select_limit() {
205        let (sql, _) = SelectQuery::new("users").limit(10).to_sql();
206        assert_eq!(sql, "SELECT * FROM users LIMIT 10");
207    }
208
209    #[test]
210    fn select_offset() {
211        let (sql, _) = SelectQuery::new("users").offset(20).to_sql();
212        assert_eq!(sql, "SELECT * FROM users OFFSET 20");
213    }
214
215    #[test]
216    fn select_limit_offset() {
217        let (sql, _) = SelectQuery::new("users")
218            .order_by(col_id().asc())
219            .limit(20)
220            .offset(40)
221            .to_sql();
222        assert_eq!(sql, "SELECT * FROM users ORDER BY id ASC LIMIT 20 OFFSET 40");
223    }
224
225    #[test]
226    fn select_distinct() {
227        let (sql, _) = SelectQuery::new("users")
228            .cols(&["name"])
229            .distinct()
230            .to_sql();
231        assert_eq!(sql, "SELECT DISTINCT name FROM users");
232    }
233
234    #[test]
235    fn select_full_query() {
236        let (sql, params) = SelectQuery::new("users")
237            .cols(&["id", "name", "age"])
238            .where_(col_age().gt(18).and(col_email().is_not_null()))
239            .order_by(col_name().asc())
240            .limit(50)
241            .offset(100)
242            .distinct()
243            .to_sql();
244        assert_eq!(
245            sql,
246            "SELECT DISTINCT id, name, age FROM users WHERE (age > $1 AND email IS NOT NULL) ORDER BY name ASC LIMIT 50 OFFSET 100"
247        );
248        assert_eq!(params, vec![Value::Int(18)]);
249    }
250
251    #[test]
252    fn select_schema_qualified() {
253        let (sql, _) = SelectQuery::new("auth.users").to_sql();
254        assert_eq!(sql, r#"SELECT * FROM "auth"."users""#);
255    }
256}
floz_orm/query/select.rs

floz_orm/query/
select.rs
