sql/query/
select.rs

1use crate::query::{Cte, CteQuery};
2use crate::util::SqlExtension;
3use crate::{Dialect, ToSql};
4
5mod expr;
6mod join;
7
8pub use expr::*;
9pub use join::*;
10
11/// A SELECT query.
12#[derive(Debug, Clone, PartialEq, Eq)]
13pub struct Select {
14    pub ctes: Vec<Cte>,
15    pub distinct: bool,
16    pub columns: Vec<SelectColumn>,
17    pub from: Option<From>,
18    pub join: Vec<Join>,
19    pub where_: Where,
20    pub group: Vec<GroupBy>,
21    pub having: Where,
22    pub order: Vec<OrderBy>,
23    pub limit: Option<usize>,
24    pub offset: Option<usize>,
25}
26
27impl Default for Select {
28    fn default() -> Self {
29        Self {
30            ctes: vec![],
31            distinct: false,
32            columns: vec![],
33            from: None,
34            join: vec![],
35            where_: Where::And(vec![]),
36            group: vec![],
37            having: Where::And(vec![]),
38            order: vec![],
39            limit: None,
40            offset: None,
41        }
42    }
43}
44
45impl Select {
46    pub fn with_raw(mut self, name: &str, query: &str) -> Self {
47        self.ctes.push(Cte {
48            name: name.to_string(),
49            query: CteQuery::Raw(query.to_string()),
50        });
51        self
52    }
53
54    pub fn with(mut self, name: &str, query: Select) -> Self {
55        self.ctes.push(Cte {
56            name: name.to_string(),
57            query: CteQuery::Select(query),
58        });
59        self
60    }
61
62    pub fn distinct(mut self) -> Self {
63        self.distinct = true;
64        self
65    }
66
67    pub fn table_column(mut self, table: &str, column: &str) -> Self {
68        self.columns.push(SelectColumn::table_column(table, column));
69        self
70    }
71
72    pub fn select_raw(mut self, expression: impl Into<String>) -> Self {
73        self.columns.push(SelectColumn {
74            expression: SelectExpression::Raw(expression.into()),
75            alias: None,
76        });
77        self
78    }
79
80    pub fn from(mut self, table: &str) -> Self {
81        self.from = Some(From {
82            schema: None,
83            table: table.to_string(),
84            alias: None,
85        });
86        self
87    }
88
89    pub fn join(mut self, join: Join) -> Self {
90        self.join.push(join);
91        self
92    }
93
94    /// Assumes `AND`. Access the `.where_` field directly for more advanced operations.
95    pub fn where_(mut self, where_: Where) -> Self {
96        match self.where_ {
97            Where::And(ref mut v) => v.push(where_),
98            _ => self.where_ = Where::And(vec![self.where_, where_]),
99        }
100        self
101    }
102
103    pub fn where_raw(self, where_: impl Into<String>) -> Self {
104        self.where_(Where::raw(where_))
105    }
106
107    pub fn group_by(mut self, group: &str) -> Self {
108        self.group.push(GroupBy(group.to_string()));
109        self
110    }
111
112    pub fn having(mut self, having: Where) -> Self {
113        match self.having {
114            Where::And(ref mut v) => v.push(having),
115            _ => self.having = Where::And(vec![self.having, having]),
116        }
117        self
118    }
119
120    pub fn order_by(mut self, order: OrderBy) -> Self {
121        self.order.push(order);
122        self
123    }
124
125    pub fn order_asc(self, order: &str) -> Self {
126        self.order_by(OrderBy::new(order).asc())
127    }
128
129    pub fn order_desc(self, order: &str) -> Self {
130        self.order_by(OrderBy::new(order).desc())
131    }
132
133    pub fn limit(mut self, limit: usize) -> Self {
134        self.limit = Some(limit);
135        self
136    }
137
138    pub fn offset(mut self, offset: usize) -> Self {
139        self.offset = Some(offset);
140        self
141    }
142}
143
144/// Represents a select column value.
145#[derive(Debug, Clone, PartialEq, Eq)]
146pub enum SelectExpression {
147    Column {
148        schema: Option<String>,
149        table: Option<String>,
150        column: String,
151    },
152    Raw(String),
153}
154
155/// Represents a column of a SELECT statement.
156#[derive(Debug, Clone, PartialEq, Eq)]
157pub struct SelectColumn {
158    pub expression: SelectExpression,
159    pub alias: Option<String>,
160}
161
162impl SelectColumn {
163    pub fn column(&self) -> Option<&str> {
164        match &self.expression {
165            SelectExpression::Column { column, .. } => Some(column),
166            _ => None,
167        }
168    }
169
170    pub fn new(column: &str) -> Self {
171        Self {
172            expression: SelectExpression::Column {
173                schema: None,
174                table: None,
175                column: column.to_string(),
176            },
177            alias: None,
178        }
179    }
180
181    pub fn table_column(table: &str, column: &str) -> Self {
182        Self {
183            expression: SelectExpression::Column {
184                schema: None,
185                table: Some(table.to_string()),
186                column: column.to_string(),
187            },
188            alias: None,
189        }
190    }
191
192    pub fn raw(expression: &str) -> Self {
193        Self {
194            expression: SelectExpression::Raw(expression.to_string()),
195            alias: None,
196        }
197    }
198
199    pub fn alias(mut self, alias: impl Into<String>) -> Self {
200        self.alias = Some(alias.into());
201        self
202    }
203}
204
205impl<T: Into<String>> std::convert::From<T> for SelectColumn {
206    fn from(column: T) -> Self {
207        Self {
208            expression: SelectExpression::Column {
209                schema: None,
210                table: None,
211                column: column.into(),
212            },
213            alias: None,
214        }
215    }
216}
217
218impl ToSql for SelectColumn {
219    fn write_sql(&self, buf: &mut String, _: Dialect) {
220        use SelectExpression::*;
221        match &self.expression {
222            Column {
223                schema,
224                table,
225                column,
226            } => {
227                if let Some(schema) = schema {
228                    buf.push_quoted(schema);
229                    buf.push('.');
230                }
231                if let Some(table) = table {
232                    buf.push_quoted(table);
233                    buf.push('.');
234                }
235                buf.push_quoted(column);
236            }
237            Raw(raw) => {
238                buf.push_str(raw);
239            }
240        }
241        if let Some(alias) = &self.alias {
242            buf.push_str(" AS ");
243            buf.push_quoted(alias);
244        }
245    }
246}
247
248#[derive(Debug, Clone, PartialEq, Eq)]
249pub struct From {
250    pub schema: Option<String>,
251    pub table: String,
252    pub alias: Option<String>,
253}
254
255impl<T: Into<String>> std::convert::From<T> for From {
256    fn from(table: T) -> Self {
257        Self {
258            schema: None,
259            table: table.into(),
260            alias: None,
261        }
262    }
263}
264
265impl ToSql for From {
266    fn write_sql(&self, buf: &mut String, _: Dialect) {
267        buf.push_table_name(&self.schema, &self.table);
268        if let Some(alias) = &self.alias {
269            buf.push_str(" AS ");
270            buf.push_quoted(alias);
271        }
272    }
273}
274
275#[derive(Debug, Clone, PartialEq, Eq)]
276pub enum Where {
277    And(Vec<Where>),
278    Or(Vec<Where>),
279    #[deprecated]
280    Raw(String),
281    Expr(Expr),
282}
283
284impl std::convert::From<String> for Where {
285    fn from(s: String) -> Self {
286        Where::Expr(Expr::Raw(s))
287    }
288}
289
290impl Where {
291    pub fn is_empty(&self) -> bool {
292        use Where::*;
293        match self {
294            And(v) => v.is_empty(),
295            Or(v) => v.is_empty(),
296            #[allow(deprecated)]
297            Raw(s) => s.is_empty(),
298            Expr(_) => false,
299        }
300    }
301
302    pub fn raw(s: impl Into<String>) -> Self {
303        Where::Expr(Expr::Raw(s.into()))
304    }
305}
306
307impl ToSql for Where {
308    fn write_sql(&self, buf: &mut String, dialect: Dialect) {
309        match self {
310            Where::And(v) => {
311                buf.push_sql_sequence(&v, " AND ", dialect);
312            }
313            Where::Or(v) => {
314                buf.push('(');
315                buf.push_sql_sequence(&v, " OR ", dialect);
316                buf.push(')');
317            }
318            #[allow(deprecated)]
319            Where::Raw(s) => {
320                buf.push_str(s);
321            }
322            Where::Expr(expr) => {
323                buf.push_sql(expr, dialect);
324            }
325        }
326    }
327}
328
329/// The direction of a column in an ORDER BY clause.
330#[derive(Debug, Clone, Copy, PartialEq, Eq)]
331pub enum Direction {
332    Asc,
333    Desc,
334}
335
336#[derive(Debug, Clone, Copy, PartialEq, Eq)]
337pub enum NullsOrder {
338    First,
339    Last,
340}
341
342#[derive(Debug, Clone, PartialEq, Eq)]
343pub struct OrderBy {
344    pub column: String,
345    pub direction: Option<Direction>,
346    pub nulls: Option<NullsOrder>,
347}
348
349impl OrderBy {
350    pub fn new(column: &str) -> Self {
351        OrderBy {
352            column: column.to_string(),
353            direction: None,
354            nulls: None,
355        }
356    }
357
358    pub fn direction(mut self, direction: Direction) -> Self {
359        self.direction = Some(direction);
360        self
361    }
362
363    pub fn asc(mut self) -> Self {
364        self.direction = Some(Direction::Asc);
365        self
366    }
367
368    pub fn desc(mut self) -> Self {
369        self.direction = Some(Direction::Desc);
370        self
371    }
372
373    pub fn nulls(mut self, nulls: NullsOrder) -> Self {
374        self.nulls = Some(nulls);
375        self
376    }
377
378    pub fn nulls_first(mut self) -> Self {
379        self.nulls = Some(NullsOrder::First);
380        self
381    }
382
383    pub fn nulls_last(mut self) -> Self {
384        self.nulls = Some(NullsOrder::Last);
385        self
386    }
387}
388
389impl ToSql for OrderBy {
390    fn write_sql(&self, buf: &mut String, _: Dialect) {
391        use Direction::*;
392        buf.push_str(&self.column);
393        if let Some(direction) = self.direction {
394            match direction {
395                Asc => buf.push_str(" ASC"),
396                Desc => buf.push_str(" DESC"),
397            }
398        }
399        if let Some(nulls) = self.nulls {
400            match nulls {
401                NullsOrder::First => buf.push_str(" NULLS FIRST"),
402                NullsOrder::Last => buf.push_str(" NULLS LAST"),
403            }
404        }
405    }
406}
407
408impl Default for Direction {
409    fn default() -> Self {
410        Direction::Asc
411    }
412}
413
414#[derive(Debug, Clone, PartialEq, Eq)]
415pub struct GroupBy(String);
416
417impl ToSql for GroupBy {
418    fn write_sql(&self, buf: &mut String, _: Dialect) {
419        buf.push_str(&self.0)
420    }
421}
422
423impl ToSql for Select {
424    fn write_sql(&self, buf: &mut String, dialect: Dialect) {
425        if !self.ctes.is_empty() {
426            buf.push_str("WITH ");
427            buf.push_sql_sequence(&self.ctes, ", ", dialect);
428            buf.push(' ');
429        }
430        buf.push_str("SELECT ");
431        if self.distinct {
432            buf.push_str("DISTINCT ");
433        }
434        buf.push_sql_sequence(&self.columns, ", ", dialect);
435        if let Some(from) = &self.from {
436            buf.push_str(" FROM ");
437            buf.push_str(&from.to_sql(dialect));
438            buf.push(' ');
439        }
440        if !self.join.is_empty() {
441            buf.push_sql_sequence(&self.join, " ", dialect);
442        }
443        if !self.where_.is_empty() {
444            buf.push_str(" WHERE ");
445            buf.push_str(&self.where_.to_sql(dialect));
446        }
447        if !self.group.is_empty() {
448            buf.push_str(" GROUP BY ");
449            buf.push_sql_sequence(&self.group, ", ", dialect);
450        }
451        if !self.having.is_empty() {
452            buf.push_str(" HAVING ");
453            buf.push_str(&self.having.to_sql(dialect));
454        }
455        if !self.order.is_empty() {
456            buf.push_str(" ORDER BY ");
457            buf.push_sql_sequence(&self.order, ", ", dialect);
458        }
459        if let Some(limit) = self.limit {
460            buf.push_str(" LIMIT ");
461            buf.push_str(&limit.to_string());
462        }
463        if let Some(offset) = self.offset {
464            buf.push_str(" OFFSET ");
465            buf.push_str(&offset.to_string());
466        }
467    }
468}
469
470#[cfg(test)]
471mod tests {
472    use super::*;
473
474    #[test]
475    fn test_basic() {
476        let select = Select::default()
477            .with_raw("foo", "SELECT 1")
478            .with("bar", Select::default().select_raw("1"))
479            .select_raw("id")
480            .select_raw("name")
481            .from("users")
482            .join(Join::new("posts").on_raw("users.id = posts.user_id"))
483            .where_raw("1=1")
484            .order_asc("id")
485            .order_desc("name")
486            .limit(10)
487            .offset(5);
488        assert_eq!(
489            select.to_sql(Dialect::Postgres),
490            r#"WITH foo AS (SELECT 1), bar AS (SELECT 1) SELECT id, name FROM "users" JOIN "posts" ON users.id = posts.user_id WHERE 1=1 ORDER BY id ASC, name DESC LIMIT 10 OFFSET 5"#
491        );
492    }
493}
sql/query/select.rs

sql/query/
select.rs
