grafbase_sql_ast/ast/
select.rs

1use std::borrow::Cow;
2
3use super::{
4    Column, CommonTableExpression, ConditionTree, Expression, ExpressionKind, Grouping,
5    IntoGroupByDefinition, IntoOrderDefinition, Join, JoinData, Ordering, Query, Table,
6};
7
8type Type<'a> = ConditionTree<'a>;
9
10/// A builder for a `SELECT` statement.
11#[derive(Debug, PartialEq, Clone, Default)]
12pub struct Select<'a> {
13    pub(crate) ctes: Vec<CommonTableExpression<'a>>,
14    pub(crate) distinct: bool,
15    pub(crate) tables: Vec<Table<'a>>,
16    pub(crate) columns: Vec<Expression<'a>>,
17    pub(crate) conditions: Option<ConditionTree<'a>>,
18    pub(crate) ordering: Ordering<'a>,
19    pub(crate) grouping: Grouping<'a>,
20    pub(crate) having: Option<Type<'a>>,
21    pub(crate) limit: Option<u32>,
22    pub(crate) offset: Option<u32>,
23    pub(crate) joins: Vec<Join<'a>>,
24    pub(crate) comment: Option<Cow<'a, str>>,
25}
26
27impl<'a> From<Select<'a>> for Expression<'a> {
28    fn from(sel: Select<'a>) -> Expression<'a> {
29        Expression {
30            kind: ExpressionKind::Selection(Box::new(sel)),
31            alias: None,
32        }
33    }
34}
35
36impl<'a> From<Select<'a>> for Query<'a> {
37    fn from(sel: Select<'a>) -> Query<'a> {
38        Query::Select(Box::new(sel))
39    }
40}
41
42impl<'a> Select<'a> {
43    /// Creates a new `SELECT` statement for the given table.
44    ///
45    /// ```rust
46    /// # use grafbase_sql_ast::{ast::*, renderer::{self, Renderer}};
47    /// # fn main() {
48    /// let query = Select::from_table("users");
49    /// let (sql, _) = renderer::Postgres::build(query);
50    ///
51    /// assert_eq!(r#"SELECT "users".* FROM "users""#, sql);
52    /// # }
53    /// ```
54    ///
55    /// The table can be in multiple parts, defining the schema.
56    ///
57    /// ```rust
58    /// # use grafbase_sql_ast::{ast::*, renderer::{self, Renderer}};
59    /// # fn main() {
60    /// let query = Select::from_table(("crm", "users"));
61    /// let (sql, _) = renderer::Postgres::build(query);
62    ///
63    /// assert_eq!(r#"SELECT "crm"."users".* FROM "crm"."users""#, sql);
64    /// # }
65    /// ```
66    ///
67    /// Selecting from a nested `SELECT`.
68    ///
69    /// ```rust
70    /// # use grafbase_sql_ast::{ast::*, renderer::{self, Renderer}};
71    /// # fn main() {
72    /// let mut inner_select = Select::default();
73    /// inner_select.value(1);
74    ///
75    /// let select = Table::from(inner_select).alias("num");
76    /// let query = Select::from_table(select.alias("num"));
77    /// let (sql, params) = renderer::Postgres::build(query);
78    ///
79    /// assert_eq!(r#"SELECT "num".* FROM (SELECT $1) AS "num""#, sql);
80    /// assert_eq!(vec![Value::from(1)], params);
81    /// # }
82    /// ```
83    pub fn from_table<T>(table: T) -> Self
84    where
85        T: Into<Table<'a>>,
86    {
87        Select {
88            tables: vec![table.into()],
89            ..Select::default()
90        }
91    }
92
93    /// Adds a table to be selected.
94    ///
95    /// ```rust
96    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
97    /// # fn main() {
98    /// let mut query = Select::from_table("users");
99    ///
100    /// let mut inner_select = Select::default();
101    /// inner_select.value(1);
102    ///
103    /// query.and_from(Table::from(inner_select).alias("num"));
104    /// query.column(("users", "name"));
105    /// query.value(Table::from("num").asterisk());
106    ///
107    /// let (sql, _) = renderer::Postgres::build(query);
108    ///
109    /// assert_eq!(r#"SELECT "users"."name", "num".* FROM "users", (SELECT $1) AS "num""#, sql);
110    /// # }
111    /// ```
112    pub fn and_from<T>(&mut self, table: T)
113    where
114        T: Into<Table<'a>>,
115    {
116        self.tables.push(table.into());
117    }
118
119    /// Selects a static value as the column.
120    ///
121    /// ```rust
122    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
123    /// # fn main() {
124    /// let mut query = Select::default();
125    /// query.value(1);
126    ///
127    /// let (sql, params) = renderer::Postgres::build(query);
128    ///
129    /// assert_eq!("SELECT $1", sql);
130    /// assert_eq!(vec![Value::from(1)], params);
131    /// # }
132    /// ```
133    pub fn value<T>(&mut self, value: T)
134    where
135        T: Into<Expression<'a>>,
136    {
137        self.columns.push(value.into());
138    }
139
140    /// Adds a column to be selected.
141    ///
142    /// ```rust
143    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
144    /// # fn main() {
145    /// let mut query = Select::from_table("users");
146    ///
147    /// query.column("name");
148    /// query.column(("users", "id"));
149    /// query.column((("crm", "users"), "foo"));
150    ///
151    /// let (sql, _) = renderer::Postgres::build(query);
152    ///
153    /// assert_eq!(r#"SELECT "name", "users"."id", "crm"."users"."foo" FROM "users""#, sql);
154    /// # }
155    /// ```
156    pub fn column<T>(&mut self, column: T)
157    where
158        T: Into<Column<'a>>,
159    {
160        self.columns.push(column.into().into());
161    }
162
163    /// A bulk method to select multiple values.
164    ///
165    /// ```rust
166    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
167    /// # fn main() {
168    /// let mut query = Select::from_table("users");
169    /// query.columns(["foo", "bar"]);
170    ///
171    /// let (sql, _) = renderer::Postgres::build(query);
172    ///
173    /// assert_eq!(r#"SELECT "foo", "bar" FROM "users""#, sql);
174    /// # }
175    /// ```
176    pub fn columns<T, C>(&mut self, columns: T)
177    where
178        T: IntoIterator<Item = C>,
179        C: Into<Column<'a>>,
180    {
181        self.columns = columns.into_iter().map(|c| c.into().into()).collect();
182    }
183
184    /// Adds `DISTINCT` to the select query.
185    ///
186    /// ```rust
187    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
188    /// # fn main() {
189    /// let mut query = Select::from_table("users");
190    ///
191    /// query.column("foo");
192    /// query.column("bar");
193    /// query.distinct();
194    ///
195    /// let (sql, _) = renderer::Postgres::build(query);
196    ///
197    /// assert_eq!(r#"SELECT DISTINCT "foo", "bar" FROM "users""#, sql);
198    /// # }
199    /// ```
200    pub fn distinct(&mut self) {
201        self.distinct = true;
202    }
203
204    /// Adds `WHERE` conditions to the query, replacing the previous conditions.
205    /// See [Comparable](trait.Comparable.html#required-methods) for more
206    /// examples.
207    ///
208    /// ```rust
209    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
210    /// # fn main() {
211    /// let mut query = Select::from_table("users");
212    /// query.so_that("foo".equals("bar"));
213    ///
214    /// let (sql, params) = renderer::Postgres::build(query);
215    ///
216    /// assert_eq!(r#"SELECT "users".* FROM "users" WHERE "foo" = $1"#, sql);
217    ///
218    /// assert_eq!(vec![
219    ///    Value::from("bar"),
220    /// ], params);
221    /// # }
222    /// ```
223    pub fn so_that<T>(&mut self, conditions: T)
224    where
225        T: Into<ConditionTree<'a>>,
226    {
227        self.conditions = Some(conditions.into());
228    }
229
230    /// Adds an additional `WHERE` condition to the query combining the possible
231    /// previous condition with `AND`. See
232    /// [Comparable](trait.Comparable.html#required-methods) for more examples.
233    ///
234    /// ```rust
235    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
236    /// # fn main() {
237    /// let mut query = Select::from_table("users");
238    ///
239    /// query.so_that("foo".equals("bar"));
240    /// query.and_where("lol".equals("wtf"));
241    ///
242    /// let (sql, params) = renderer::Postgres::build(query);
243    ///
244    /// assert_eq!(r#"SELECT "users".* FROM "users" WHERE ("foo" = $1 AND "lol" = $2)"#, sql);
245    ///
246    /// assert_eq!(vec![
247    ///    Value::from("bar"),
248    ///    Value::from("wtf"),
249    /// ], params);
250    /// # }
251    /// ```
252    pub fn and_where<T>(&mut self, conditions: T)
253    where
254        T: Into<ConditionTree<'a>>,
255    {
256        match self.conditions.take() {
257            Some(previous) => {
258                self.conditions = Some(previous.and(conditions.into()));
259            }
260            None => self.so_that(conditions),
261        }
262    }
263
264    /// Adds an additional `WHERE` condition to the query combining the possible
265    /// previous condition with `OR`. See
266    /// [Comparable](trait.Comparable.html#required-methods) for more examples.
267    ///
268    /// ```rust
269    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
270    /// # fn main() {
271    /// let mut query = Select::from_table("users");
272    ///
273    /// query.so_that("foo".equals("bar"));
274    /// query.or_where("lol".equals("wtf"));
275    ///
276    /// let (sql, params) = renderer::Postgres::build(query);
277    ///
278    /// assert_eq!(r#"SELECT "users".* FROM "users" WHERE ("foo" = $1 OR "lol" = $2)"#, sql);
279    ///
280    /// assert_eq!(vec![
281    ///    Value::from("bar"),
282    ///    Value::from("wtf"),
283    /// ], params);
284    /// # }
285    /// ```
286    pub fn or_where<T>(&mut self, conditions: T)
287    where
288        T: Into<ConditionTree<'a>>,
289    {
290        match self.conditions.take() {
291            Some(previous) => {
292                self.conditions = Some(previous.or(conditions.into()));
293            }
294            None => self.so_that(conditions),
295        }
296    }
297
298    /// Adds `INNER JOIN` clause to the query.
299    ///
300    /// ```rust
301    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
302    /// # fn main() {
303    /// let join = Table::from("posts")
304    ///     .alias("p")
305    ///     .on(("p", "user_id").equals(Column::from(("users", "id"))));
306    ///
307    /// let mut query = Select::from_table("users");
308    /// query.inner_join(join);
309    ///
310    /// let (sql, _) = renderer::Postgres::build(query);
311    ///
312    /// assert_eq!(
313    ///     r#"SELECT "users".* FROM "users" INNER JOIN "posts" AS "p" ON "p"."user_id" = "users"."id""#,
314    ///     sql
315    /// );
316    /// # }
317    /// ```
318    pub fn inner_join<J>(&mut self, join: J)
319    where
320        J: Into<JoinData<'a>>,
321    {
322        self.joins.push(Join::Inner(join.into()));
323    }
324
325    /// Adds `LEFT JOIN` clause to the query.
326    ///
327    /// ```rust
328    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
329    /// # fn main() {
330    /// let join = Table::from("posts")
331    ///    .alias("p")
332    ///    .on(("p", "visible").equals(true));
333    ///
334    /// let mut query = Select::from_table("users");
335    /// query.left_join(join);
336    ///
337    /// let (sql, params) = renderer::Postgres::build(query);
338    ///
339    /// assert_eq!(
340    ///     r#"SELECT "users".* FROM "users" LEFT JOIN "posts" AS "p" ON "p"."visible" = $1"#,
341    ///     sql
342    /// );
343    ///
344    /// assert_eq!(
345    ///     vec![
346    ///         Value::from(true),
347    ///     ],
348    ///     params
349    /// );
350    /// # }
351    /// ```
352    pub fn left_join<J>(&mut self, join: J)
353    where
354        J: Into<JoinData<'a>>,
355    {
356        self.joins.push(Join::Left(join.into()));
357    }
358
359    /// Adds `RIGHT JOIN` clause to the query.
360    ///
361    /// ```rust
362    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
363    /// # fn main() {
364    /// let join = Table::from("posts")
365    ///    .alias("p")
366    ///    .on(("p", "visible").equals(true));
367    ///
368    ///
369    /// let mut query = Select::from_table("users");
370    /// query.right_join(join);
371    ///
372    /// let (sql, params) = renderer::Postgres::build(query);
373    ///
374    /// assert_eq!(
375    ///     r#"SELECT "users".* FROM "users" RIGHT JOIN "posts" AS "p" ON "p"."visible" = $1"#,
376    ///     sql
377    /// );
378    ///
379    /// assert_eq!(
380    ///     vec![
381    ///         Value::from(true),
382    ///     ],
383    ///     params
384    /// );
385    /// # }
386    /// ```
387    pub fn right_join<J>(&mut self, join: J)
388    where
389        J: Into<JoinData<'a>>,
390    {
391        self.joins.push(Join::Right(join.into()));
392    }
393
394    /// Adds `FULL JOIN` clause to the query.
395    ///
396    /// ```rust
397    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
398    /// # fn main() {
399    /// let join = Table::from("posts")
400    ///    .alias("p")
401    ///    .on(("p", "visible").equals(true));
402    ///
403    /// let mut query = Select::from_table("users");
404    /// query.full_join(join);
405    ///
406    /// let (sql, params) = renderer::Postgres::build(query);
407    ///
408    /// assert_eq!(
409    ///     r#"SELECT "users".* FROM "users" FULL JOIN "posts" AS "p" ON "p"."visible" = $1"#,
410    ///     sql
411    /// );
412    ///
413    /// assert_eq!(
414    ///     vec![
415    ///         Value::from(true),
416    ///     ],
417    ///     params
418    /// );
419    /// # }
420    /// ```
421    pub fn full_join<J>(&mut self, join: J)
422    where
423        J: Into<JoinData<'a>>,
424    {
425        self.joins.push(Join::Full(join.into()));
426    }
427
428    /// Adds an ordering to the `ORDER BY` section.
429    ///
430    /// ```rust
431    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
432    /// # fn main() {
433    /// let mut query = Select::from_table("users");
434    /// query.order_by("foo");
435    /// query.order_by("baz".ascend());
436    /// query.order_by("bar".descend());
437    ///
438    /// let (sql, _) = renderer::Postgres::build(query);
439    ///
440    /// assert_eq!(r#"SELECT "users".* FROM "users" ORDER BY "foo", "baz" ASC, "bar" DESC"#, sql);
441    /// # }
442    pub fn order_by<T>(&mut self, value: T)
443    where
444        T: IntoOrderDefinition<'a>,
445    {
446        self.ordering.append(value.into_order_definition());
447    }
448
449    /// Adds a grouping to the `GROUP BY` section.
450    ///
451    /// This does not check if the grouping is actually valid in respect to aggregated columns.
452    ///
453    /// ```rust
454    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
455    /// # fn main() {
456    /// let mut query = Select::from_table("users");
457    ///
458    /// query.column("foo");
459    /// query.column("bar");
460    /// query.group_by("foo");
461    /// query.group_by("bar");
462    ///
463    /// let (sql, _) = renderer::Postgres::build(query);
464    ///
465    /// assert_eq!(r#"SELECT "foo", "bar" FROM "users" GROUP BY "foo", "bar""#, sql);
466    /// # }
467    pub fn group_by<T>(&mut self, value: T)
468    where
469        T: IntoGroupByDefinition<'a>,
470    {
471        self.grouping.append(value.into_group_by_definition());
472    }
473
474    /// Adds group conditions to a query. Should be combined together with a
475    /// [group_by](struct.Select.html#method.group_by) statement.
476    ///
477    /// ```rust
478    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
479    /// # fn main() {
480    /// let mut query = Select::from_table("users");
481    ///
482    /// query.column("foo");
483    /// query.column("bar");
484    /// query.group_by("foo");
485    /// query.having("foo".greater_than(100));
486    ///
487    /// let (sql, params) = renderer::Postgres::build(query);
488    ///
489    /// assert_eq!(r#"SELECT "foo", "bar" FROM "users" GROUP BY "foo" HAVING "foo" > $1"#, sql);
490    /// assert_eq!(vec![Value::from(100)], params);
491    /// # }
492    pub fn having<T>(&mut self, conditions: T)
493    where
494        T: Into<ConditionTree<'a>>,
495    {
496        self.having = Some(conditions.into());
497    }
498
499    /// Sets the `LIMIT` value.
500    ///
501    /// ```rust
502    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
503    /// # fn main() {
504    /// let mut query = Select::from_table("users");
505    /// query.limit(10);
506    ///
507    /// let (sql, params) = renderer::Postgres::build(query);
508    ///
509    /// assert_eq!(r#"SELECT "users".* FROM "users" LIMIT $1"#, sql);
510    /// assert_eq!(vec![Value::from(10_i64)], params);
511    /// # }
512    pub fn limit(&mut self, limit: u32) {
513        self.limit = Some(limit);
514    }
515
516    /// Sets the `OFFSET` value.
517    ///
518    /// ```rust
519    /// # use grafbase_sql_ast::{ast::*, renderer::{Renderer, self}};
520    /// # fn main() {
521    /// let mut query = Select::from_table("users");
522    /// query.offset(10);
523    ///
524    /// let (sql, params) = renderer::Postgres::build(query);
525    ///
526    /// assert_eq!(r#"SELECT "users".* FROM "users" OFFSET $1"#, sql);
527    /// assert_eq!(vec![Value::from(10_i64)], params);
528    /// # }
529    pub fn offset(&mut self, offset: u32) {
530        self.offset = Some(offset);
531    }
532
533    /// Adds a common table expression to the select.
534    pub fn with(&mut self, cte: CommonTableExpression<'a>) {
535        self.ctes.push(cte);
536    }
537}
grafbase_sql_ast/ast/select.rs

grafbase_sql_ast/ast/
select.rs
