nautilus_core/
select.rs

1//! SELECT query AST and builder.
2
3use crate::column::ColumnMarker;
4use crate::error::{Error, Result};
5use crate::expr::Expr;
6
7/// A select list item that can be either a simple column or a computed expression.
8#[derive(Debug, Clone, PartialEq)]
9pub enum SelectItem {
10    /// A simple column reference.
11    Column(ColumnMarker),
12    /// A computed expression with an alias.
13    Computed {
14        /// The expression to compute.
15        expr: Expr,
16        /// The alias for the computed expression.
17        alias: String,
18    },
19}
20
21impl SelectItem {
22    /// Creates a SelectItem from a ColumnMarker.
23    pub fn column(marker: ColumnMarker) -> Self {
24        SelectItem::Column(marker)
25    }
26
27    /// Creates a computed SelectItem with an expression and alias.
28    pub fn computed(expr: Expr, alias: impl Into<String>) -> Self {
29        SelectItem::Computed {
30            expr,
31            alias: alias.into(),
32        }
33    }
34}
35
36impl From<ColumnMarker> for SelectItem {
37    fn from(marker: ColumnMarker) -> Self {
38        SelectItem::Column(marker)
39    }
40}
41
42/// Sort direction for ORDER BY clauses.
43#[derive(Debug, Clone, Copy, PartialEq, Eq)]
44pub enum OrderDir {
45    /// Ascending.
46    Asc,
47    /// Descending.
48    Desc,
49}
50
51/// ORDER BY clause item.
52#[derive(Debug, Clone, PartialEq, Eq)]
53pub struct OrderBy {
54    /// Column name.
55    pub column: String,
56    /// Sort direction.
57    pub direction: OrderDir,
58}
59
60impl OrderBy {
61    /// Creates a new ORDER BY clause.
62    pub fn new(column: impl Into<String>, direction: OrderDir) -> Self {
63        OrderBy {
64            column: column.into(),
65            direction,
66        }
67    }
68
69    /// Creates an ascending ORDER BY clause.
70    pub fn asc(column: impl Into<String>) -> Self {
71        OrderBy::new(column, OrderDir::Asc)
72    }
73
74    /// Creates a descending ORDER BY clause.
75    pub fn desc(column: impl Into<String>) -> Self {
76        OrderBy::new(column, OrderDir::Desc)
77    }
78}
79
80/// One ORDER BY item in the original user-specified sequence.
81#[derive(Debug, Clone, PartialEq)]
82pub enum OrderByItem {
83    /// An ORDER BY over a plain column reference.
84    Column(OrderBy),
85    /// An ORDER BY over an arbitrary expression, such as an aggregate function.
86    Expr(Expr, OrderDir),
87}
88
89/// Type of JOIN operation.
90#[derive(Debug, Clone, Copy, PartialEq, Eq)]
91pub enum JoinType {
92    /// INNER JOIN — only matching rows from both tables.
93    Inner,
94    /// LEFT JOIN — all rows from the left table, matching rows from the right.
95    Left,
96}
97
98/// A JOIN clause attached to a SELECT query.
99#[derive(Debug, Clone, PartialEq)]
100pub struct JoinClause {
101    /// The type of join (INNER, LEFT).
102    pub join_type: JoinType,
103    /// The table to join.
104    pub table: String,
105    /// The ON condition expression.
106    pub on: Expr,
107    /// Select items (columns or computed expressions) from the joined table.
108    pub items: Vec<SelectItem>,
109}
110
111impl JoinClause {
112    /// Creates a new JOIN clause.
113    pub fn new(
114        join_type: JoinType,
115        table: impl Into<String>,
116        on: Expr,
117        items: Vec<SelectItem>,
118    ) -> Self {
119        Self {
120            join_type,
121            table: table.into(),
122            on,
123            items,
124        }
125    }
126}
127
128/// SELECT query AST node.
129#[derive(Debug, Clone, PartialEq)]
130pub struct Select {
131    /// Table name.
132    pub table: String,
133    /// Select items (columns or computed expressions).
134    pub items: Vec<SelectItem>,
135    /// JOIN clauses.
136    pub joins: Vec<JoinClause>,
137    /// WHERE clause.
138    pub filter: Option<Expr>,
139    /// ORDER BY clauses.
140    pub order_by: Vec<OrderBy>,
141    /// Row count to return (maps to SQL LIMIT).
142    ///
143    /// Positive values limit forward; negative values signal backward pagination
144    /// (callers must negate and reverse `ORDER BY` in application code).
145    /// The absolute value is used when building the SQL `LIMIT` clause.
146    pub take: Option<i32>,
147    /// Row offset to skip (maps to SQL OFFSET).
148    pub skip: Option<u32>,
149    /// GROUP BY columns.
150    pub group_by: Vec<ColumnMarker>,
151    /// Columns to deduplicate on.
152    ///
153    /// Non-empty activates deduplication:
154    /// - Postgres: `SELECT DISTINCT ON (cols)`
155    /// - SQLite / MySQL: plain `SELECT DISTINCT`
156    pub distinct: Vec<String>,
157    /// HAVING clause (post-GROUP BY filter).
158    pub having: Option<Expr>,
159    /// ORDER BY items preserved in their original mixed column/expression order.
160    pub order_by_items: Vec<OrderByItem>,
161    /// ORDER BY expression items (for aggregate functions, e.g. `COUNT(*) DESC`).
162    pub order_by_exprs: Vec<(Expr, OrderDir)>,
163}
164
165impl Select {
166    /// Creates a new SELECT query builder for the given table.
167    pub fn from_table(table: impl Into<String>) -> SelectBuilder {
168        SelectBuilder {
169            table: table.into(),
170            items: Vec::new(),
171            joins: Vec::new(),
172            filter: None,
173            order_by: Vec::new(),
174            take: None,
175            skip: None,
176            group_by: Vec::new(),
177            distinct: Vec::new(),
178            having: None,
179            order_by_items: Vec::new(),
180            order_by_exprs: Vec::new(),
181        }
182    }
183}
184
185/// Builder for SELECT queries.
186#[derive(Debug, Clone)]
187pub struct SelectBuilder {
188    table: String,
189    items: Vec<SelectItem>,
190    joins: Vec<JoinClause>,
191    filter: Option<Expr>,
192    order_by: Vec<OrderBy>,
193    take: Option<i32>,
194    skip: Option<u32>,
195    group_by: Vec<ColumnMarker>,
196    distinct: Vec<String>,
197    having: Option<Expr>,
198    order_by_items: Vec<OrderByItem>,
199    order_by_exprs: Vec<(Expr, OrderDir)>,
200}
201
202impl SelectBuilder {
203    /// Sets the select items.
204    #[must_use]
205    pub fn items(mut self, items: Vec<SelectItem>) -> Self {
206        self.items = items;
207        self
208    }
209
210    /// Adds a select item.
211    #[must_use]
212    pub fn item(mut self, item: SelectItem) -> Self {
213        self.items.push(item);
214        self
215    }
216
217    /// Adds a computed expression with an alias.
218    #[must_use]
219    pub fn computed(mut self, expr: Expr, alias: impl Into<String>) -> Self {
220        self.items.push(SelectItem::computed(expr, alias));
221        self
222    }
223
224    /// Adds a WHERE clause filter.
225    #[must_use]
226    pub fn filter(mut self, expr: Expr) -> Self {
227        self.filter = Some(expr);
228        self
229    }
230
231    /// Adds an ORDER BY clause.
232    #[must_use]
233    pub fn order_by(mut self, column: impl Into<String>, direction: OrderDir) -> Self {
234        let order = OrderBy::new(column, direction);
235        self.order_by.push(order.clone());
236        self.order_by_items.push(OrderByItem::Column(order));
237        self
238    }
239
240    /// Adds an ORDER BY ASC clause.
241    #[must_use]
242    pub fn order_by_asc(mut self, column: impl Into<String>) -> Self {
243        let order = OrderBy::asc(column);
244        self.order_by.push(order.clone());
245        self.order_by_items.push(OrderByItem::Column(order));
246        self
247    }
248
249    /// Adds an ORDER BY DESC clause.
250    #[must_use]
251    pub fn order_by_desc(mut self, column: impl Into<String>) -> Self {
252        let order = OrderBy::desc(column);
253        self.order_by.push(order.clone());
254        self.order_by_items.push(OrderByItem::Column(order));
255        self
256    }
257
258    /// Sets the row count (maps to SQL LIMIT).
259    ///
260    /// Pass a positive value for forward pagination. Negative values signal
261    /// backward pagination to callers; the dialect renders the absolute value.
262    #[must_use]
263    pub fn take(mut self, n: i32) -> Self {
264        self.take = Some(n);
265        self
266    }
267
268    /// Sets the row offset (maps to SQL OFFSET).
269    #[must_use]
270    pub fn skip(mut self, n: u32) -> Self {
271        self.skip = Some(n);
272        self
273    }
274
275    /// Adds a JOIN clause.
276    #[must_use]
277    pub fn join(mut self, clause: JoinClause) -> Self {
278        self.joins.push(clause);
279        self
280    }
281
282    /// Adds an INNER JOIN clause.
283    #[must_use]
284    pub fn inner_join(self, table: impl Into<String>, on: Expr, items: Vec<SelectItem>) -> Self {
285        self.join(JoinClause::new(JoinType::Inner, table, on, items))
286    }
287
288    /// Adds a LEFT JOIN clause.
289    #[must_use]
290    pub fn left_join(self, table: impl Into<String>, on: Expr, items: Vec<SelectItem>) -> Self {
291        self.join(JoinClause::new(JoinType::Left, table, on, items))
292    }
293
294    /// Adds a GROUP BY clause.
295    #[must_use]
296    pub fn group_by_column(mut self, column: ColumnMarker) -> Self {
297        self.group_by.push(column);
298        self
299    }
300
301    /// Adds multiple columns to the GROUP BY clause.
302    #[must_use]
303    pub fn group_by(mut self, columns: Vec<ColumnMarker>) -> Self {
304        self.group_by.extend(columns);
305        self
306    }
307
308    /// Sets the HAVING clause (post-GROUP BY filter).
309    #[must_use]
310    pub fn having(mut self, expr: Expr) -> Self {
311        self.having = Some(expr);
312        self
313    }
314
315    /// Adds an ORDER BY clause using an arbitrary expression (e.g. an aggregate function).
316    #[must_use]
317    pub fn order_by_expr(mut self, expr: Expr, direction: OrderDir) -> Self {
318        self.order_by_exprs.push((expr.clone(), direction));
319        self.order_by_items.push(OrderByItem::Expr(expr, direction));
320        self
321    }
322
323    /// Sets the columns to deduplicate on (SELECT DISTINCT / DISTINCT ON).
324    ///
325    /// - **Postgres**: emits `SELECT DISTINCT ON (col, ...)` and requires those
326    ///   columns to appear first in `ORDER BY` (callers should prepend them).
327    /// - **SQLite / MySQL**: emits plain `SELECT DISTINCT`.
328    #[must_use]
329    pub fn distinct(mut self, columns: Vec<String>) -> Self {
330        self.distinct = columns;
331        self
332    }
333
334    /// Builds the final SELECT query.
335    pub fn build(self) -> Result<Select> {
336        if self.table.is_empty() {
337            return Err(Error::MissingField("table".to_string()));
338        }
339
340        Ok(Select {
341            table: self.table,
342            items: self.items,
343            joins: self.joins,
344            filter: self.filter,
345            order_by: self.order_by,
346            take: self.take,
347            skip: self.skip,
348            group_by: self.group_by,
349            distinct: self.distinct,
350            having: self.having,
351            order_by_items: self.order_by_items,
352            order_by_exprs: self.order_by_exprs,
353        })
354    }
355}
356
357#[cfg(test)]
358mod tests {
359    use super::*;
360    use crate::expr::Expr;
361
362    #[test]
363    fn test_order_by() {
364        let asc = OrderBy::asc("id");
365        assert_eq!(asc.column, "id");
366        assert_eq!(asc.direction, OrderDir::Asc);
367
368        let desc = OrderBy::desc("created_at");
369        assert_eq!(desc.column, "created_at");
370        assert_eq!(desc.direction, OrderDir::Desc);
371    }
372
373    #[test]
374    fn test_simple_select() {
375        let query = Select::from_table("users").build().unwrap();
376
377        assert_eq!(query.table, "users");
378        assert!(query.items.is_empty());
379        assert!(query.joins.is_empty());
380        assert!(query.filter.is_none());
381        assert!(query.order_by.is_empty());
382        assert!(query.take.is_none());
383        assert!(query.skip.is_none());
384    }
385
386    #[test]
387    fn test_select_with_columns() {
388        let query = Select::from_table("users")
389            .item(SelectItem::from(ColumnMarker::new("users", "id")))
390            .item(SelectItem::from(ColumnMarker::new("users", "email")))
391            .build()
392            .unwrap();
393
394        assert_eq!(query.items.len(), 2);
395        if let SelectItem::Column(col) = &query.items[0] {
396            assert_eq!(col.table, "users");
397            assert_eq!(col.name, "id");
398        }
399        if let SelectItem::Column(col) = &query.items[1] {
400            assert_eq!(col.table, "users");
401            assert_eq!(col.name, "email");
402        }
403    }
404
405    #[test]
406    fn test_select_with_filter() {
407        let filter = Expr::column("age").ge(Expr::param(18i64));
408        let query = Select::from_table("users")
409            .filter(filter.clone())
410            .build()
411            .unwrap();
412
413        assert_eq!(query.filter, Some(filter));
414    }
415
416    #[test]
417    fn test_select_with_order_by() {
418        let query = Select::from_table("users")
419            .order_by_desc("created_at")
420            .order_by_asc("email")
421            .build()
422            .unwrap();
423
424        assert_eq!(query.order_by.len(), 2);
425        assert_eq!(query.order_by[0].column, "created_at");
426        assert_eq!(query.order_by[0].direction, OrderDir::Desc);
427        assert_eq!(query.order_by[1].column, "email");
428        assert_eq!(query.order_by[1].direction, OrderDir::Asc);
429    }
430
431    #[test]
432    fn test_select_with_take_and_skip() {
433        let query = Select::from_table("users")
434            .take(10)
435            .skip(20)
436            .build()
437            .unwrap();
438
439        assert_eq!(query.take, Some(10));
440        assert_eq!(query.skip, Some(20));
441    }
442
443    #[test]
444    fn test_complex_select() {
445        let filter = Expr::column("age")
446            .ge(Expr::param(18i64))
447            .and(Expr::column("email").like(Expr::param("%@gmail.com")));
448
449        let query = Select::from_table("users")
450            .items(vec![
451                SelectItem::from(ColumnMarker::new("users", "id")),
452                SelectItem::from(ColumnMarker::new("users", "email")),
453                SelectItem::from(ColumnMarker::new("users", "age")),
454            ])
455            .filter(filter)
456            .order_by_desc("id")
457            .take(10)
458            .build()
459            .unwrap();
460
461        assert_eq!(query.table, "users");
462        assert_eq!(query.items.len(), 3);
463        assert!(query.filter.is_some());
464        assert_eq!(query.order_by.len(), 1);
465        assert_eq!(query.take, Some(10));
466    }
467
468    #[test]
469    fn test_select_with_inner_join() {
470        let on = Expr::column("users__id").eq(Expr::column("posts__user_id"));
471        let query = Select::from_table("users")
472            .item(SelectItem::from(ColumnMarker::new("users", "id")))
473            .inner_join(
474                "posts",
475                on.clone(),
476                vec![
477                    SelectItem::from(ColumnMarker::new("posts", "id")),
478                    SelectItem::from(ColumnMarker::new("posts", "title")),
479                ],
480            )
481            .build()
482            .unwrap();
483
484        assert_eq!(query.joins.len(), 1);
485        assert_eq!(query.joins[0].join_type, JoinType::Inner);
486        assert_eq!(query.joins[0].table, "posts");
487        assert_eq!(query.joins[0].on, on);
488        assert_eq!(query.joins[0].items.len(), 2);
489    }
490
491    #[test]
492    fn test_select_with_left_join() {
493        let on = Expr::column("users__id").eq(Expr::column("posts__user_id"));
494        let query = Select::from_table("users")
495            .item(SelectItem::from(ColumnMarker::new("users", "id")))
496            .left_join(
497                "posts",
498                on,
499                vec![SelectItem::from(ColumnMarker::new("posts", "title"))],
500            )
501            .build()
502            .unwrap();
503
504        assert_eq!(query.joins.len(), 1);
505        assert_eq!(query.joins[0].join_type, JoinType::Left);
506        assert_eq!(query.joins[0].table, "posts");
507        assert_eq!(query.joins[0].items.len(), 1);
508    }
509
510    #[test]
511    fn test_select_with_multiple_joins() {
512        let query = Select::from_table("users")
513            .inner_join(
514                "posts",
515                Expr::column("users__id").eq(Expr::column("posts__user_id")),
516                vec![SelectItem::from(ColumnMarker::new("posts", "title"))],
517            )
518            .left_join(
519                "comments",
520                Expr::column("posts__id").eq(Expr::column("comments__post_id")),
521                vec![SelectItem::from(ColumnMarker::new("comments", "body"))],
522            )
523            .build()
524            .unwrap();
525
526        assert_eq!(query.joins.len(), 2);
527        assert_eq!(query.joins[0].join_type, JoinType::Inner);
528        assert_eq!(query.joins[0].table, "posts");
529        assert_eq!(query.joins[1].join_type, JoinType::Left);
530        assert_eq!(query.joins[1].table, "comments");
531    }
532}
nautilus_core/select.rs

nautilus_core/
select.rs
