sea_query/query/select.rs
1use std::borrow::Cow;
2#[cfg(feature = "backend-mysql")]
3use std::collections::HashMap;
4
5use crate::{
6 FunctionCall, QueryStatement, QueryStatementBuilder, QueryStatementWriter, SubQueryStatement,
7 WindowStatement, WithClause, WithQuery,
8 backend::QueryBuilder,
9 expr::*,
10 prepare::*,
11 query::{OrderedStatement, condition::*},
12 types::*,
13 value::*,
14};
15use inherent::inherent;
16
17/// Select rows from an existing table
18///
19/// # Examples
20///
21/// ```
22/// use sea_query::{*, tests_cfg::*};
23///
24/// let query = Query::select()
25/// .column(Char::Character)
26/// .column((Font::Table, Font::Name))
27/// .from(Char::Table)
28/// .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
29/// .and_where(Expr::col(Char::SizeW).is_in([3, 4]))
30/// .and_where(Expr::col(Char::Character).like("A%"))
31/// .to_owned();
32///
33/// assert_eq!(
34/// query.to_string(MysqlQueryBuilder),
35/// r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id` WHERE `size_w` IN (3, 4) AND `character` LIKE 'A%'"#
36/// );
37/// assert_eq!(
38/// query.to_string(PostgresQueryBuilder),
39/// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" WHERE "size_w" IN (3, 4) AND "character" LIKE 'A%'"#
40/// );
41/// assert_eq!(
42/// query.to_string(SqliteQueryBuilder),
43/// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" WHERE "size_w" IN (3, 4) AND "character" LIKE 'A%'"#
44/// );
45/// ```
46#[derive(Default, Debug, Clone, PartialEq)]
47pub struct SelectStatement {
48 pub(crate) distinct: Option<SelectDistinct>,
49 pub(crate) selects: Vec<SelectExpr>,
50 pub(crate) from: Vec<TableRef>,
51 pub(crate) join: Vec<JoinExpr>,
52 pub(crate) r#where: ConditionHolder,
53 pub(crate) groups: Vec<Expr>,
54 pub(crate) having: ConditionHolder,
55 pub(crate) unions: Vec<(UnionType, SelectStatement)>,
56 pub(crate) orders: Vec<OrderExpr>,
57 pub(crate) limit: Option<Value>,
58 pub(crate) offset: Option<Value>,
59 pub(crate) lock: Option<LockClause>,
60 pub(crate) window: Option<(DynIden, WindowStatement)>,
61 pub(crate) with: Option<Box<WithClause>>,
62 #[cfg(feature = "backend-postgres")]
63 pub(crate) table_sample: Option<crate::extension::postgres::TableSample>,
64 #[cfg(feature = "backend-mysql")]
65 pub(crate) index_hints:
66 HashMap<index_hint::IndexHintKey, Vec<crate::extension::mysql::IndexHint>>,
67}
68
69#[cfg(feature = "backend-mysql")]
70mod index_hint {
71 use crate::*;
72 #[derive(Debug, Clone, PartialEq, Eq, Hash)]
73 pub(crate) enum IndexHintKey {
74 Table(TableName, Option<DynIden>),
75 Alias(DynIden),
76 }
77
78 impl From<TableRef> for IndexHintKey {
79 fn from(value: TableRef) -> Self {
80 match value {
81 TableRef::Table(table_name, alias) => Self::Table(table_name, alias),
82 TableRef::SubQuery(_, alias)
83 | TableRef::ValuesList(_, alias)
84 | TableRef::FunctionCall(_, alias) => Self::Alias(alias),
85 }
86 }
87 }
88
89 impl From<&TableRef> for IndexHintKey {
90 fn from(value: &TableRef) -> Self {
91 match value {
92 TableRef::Table(table_name, alias) => {
93 Self::Table(table_name.clone(), alias.clone())
94 }
95 TableRef::SubQuery(_, alias)
96 | TableRef::ValuesList(_, alias)
97 | TableRef::FunctionCall(_, alias) => Self::Alias(alias.clone()),
98 }
99 }
100 }
101
102 impl PartialEq<&TableRef> for IndexHintKey {
103 fn eq(&self, other: &&TableRef) -> bool {
104 match (self, other) {
105 (Self::Table(table_name, alias), TableRef::Table(table_name2, alias2)) => {
106 table_name == table_name2 && alias == alias2
107 }
108 (
109 Self::Alias(alias),
110 TableRef::SubQuery(_, alias2)
111 | TableRef::ValuesList(_, alias2)
112 | TableRef::FunctionCall(_, alias2),
113 ) => alias == alias2,
114 _ => false,
115 }
116 }
117 }
118}
119
120/// List of distinct keywords that can be used in select statement
121#[derive(Debug, Clone, PartialEq)]
122#[non_exhaustive]
123pub enum SelectDistinct {
124 All,
125 Distinct,
126 DistinctRow,
127 DistinctOn(Vec<ColumnRef>),
128}
129
130/// Window type in [`SelectExpr`]
131#[derive(Debug, Clone, PartialEq)]
132#[non_exhaustive]
133pub enum WindowSelectType {
134 /// Name in [`SelectStatement`]
135 Name(DynIden),
136 /// Inline query in [`SelectExpr`]
137 Query(WindowStatement),
138}
139
140/// Select expression used in select statement
141#[derive(Debug, Clone, PartialEq)]
142pub struct SelectExpr {
143 pub expr: Expr,
144 pub alias: Option<DynIden>,
145 pub window: Option<WindowSelectType>,
146}
147
148/// Join expression used in select statement
149#[derive(Debug, Clone, PartialEq)]
150pub struct JoinExpr {
151 pub join: JoinType,
152 pub table: Box<TableRef>,
153 pub on: Option<JoinOn>,
154 pub lateral: bool,
155}
156
157/// List of lock types that can be used in select statement
158#[derive(Debug, Clone, Copy, PartialEq, Eq)]
159#[non_exhaustive]
160pub enum LockType {
161 /// Exclusive lock
162 Update,
163 NoKeyUpdate,
164 /// Shared lock
165 Share,
166 KeyShare,
167}
168
169/// List of lock behavior can be used in select statement
170#[derive(Debug, Clone, Copy, PartialEq, Eq)]
171#[non_exhaustive]
172pub enum LockBehavior {
173 Nowait,
174 SkipLocked,
175}
176
177#[derive(Debug, Clone, PartialEq)]
178pub struct LockClause {
179 pub(crate) r#type: LockType,
180 pub(crate) tables: Vec<TableRef>,
181 pub(crate) behavior: Option<LockBehavior>,
182}
183
184/// List of union types that can be used in union clause
185#[derive(Debug, Clone, Copy, PartialEq, Eq)]
186#[non_exhaustive]
187pub enum UnionType {
188 Intersect,
189 Distinct,
190 Except,
191 All,
192}
193
194impl<T> From<T> for SelectExpr
195where
196 T: Into<Expr>,
197{
198 fn from(expr: T) -> Self {
199 SelectExpr {
200 expr: expr.into(),
201 alias: None,
202 window: None,
203 }
204 }
205}
206
207impl SelectStatement {
208 /// Construct a new [`SelectStatement`]
209 pub fn new() -> Self {
210 Self::default()
211 }
212
213 /// Take the ownership of data in the current [`SelectStatement`]
214 pub fn take(&mut self) -> Self {
215 Self {
216 distinct: self.distinct.take(),
217 selects: std::mem::take(&mut self.selects),
218 from: std::mem::take(&mut self.from),
219 join: std::mem::take(&mut self.join),
220 r#where: std::mem::replace(&mut self.r#where, ConditionHolder::new()),
221 groups: std::mem::take(&mut self.groups),
222 having: std::mem::replace(&mut self.having, ConditionHolder::new()),
223 unions: std::mem::take(&mut self.unions),
224 orders: std::mem::take(&mut self.orders),
225 limit: self.limit.take(),
226 offset: self.offset.take(),
227 lock: self.lock.take(),
228 window: self.window.take(),
229 with: self.with.take(),
230 #[cfg(feature = "backend-postgres")]
231 table_sample: std::mem::take(&mut self.table_sample),
232 #[cfg(feature = "backend-mysql")]
233 index_hints: std::mem::take(&mut self.index_hints),
234 }
235 }
236
237 /// A shorthand to express if ... else ... when constructing the select statement.
238 ///
239 /// # Examples
240 ///
241 /// ```
242 /// use sea_query::{tests_cfg::*, *};
243 ///
244 /// let query = Query::select()
245 /// .column(Char::Character)
246 /// .from(Char::Table)
247 /// .conditions(
248 /// true,
249 /// |x| {
250 /// x.and_where(Expr::col(Char::FontId).eq(5));
251 /// },
252 /// |x| {
253 /// x.and_where(Expr::col(Char::FontId).eq(10));
254 /// },
255 /// )
256 /// .to_owned();
257 ///
258 /// assert_eq!(
259 /// query.to_string(MysqlQueryBuilder),
260 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
261 /// );
262 /// assert_eq!(
263 /// query.to_string(PostgresQueryBuilder),
264 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
265 /// );
266 /// assert_eq!(
267 /// query.to_string(SqliteQueryBuilder),
268 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
269 /// );
270 /// ```
271 pub fn conditions<T, F>(&mut self, b: bool, if_true: T, if_false: F) -> &mut Self
272 where
273 T: FnOnce(&mut Self),
274 F: FnOnce(&mut Self),
275 {
276 if b {
277 if_true(self)
278 } else {
279 if_false(self)
280 }
281 self
282 }
283
284 /// A shorthand to express if ... else ... when constructing the select statement.
285 ///
286 /// # Examples
287 ///
288 /// ```
289 /// use sea_query::{tests_cfg::*, *};
290 ///
291 /// let query = Query::select()
292 /// .column(Char::Character)
293 /// .from(Char::Table)
294 /// .apply_if(Some(5), |q, v| {
295 /// q.and_where(Expr::col(Char::FontId).eq(v));
296 /// })
297 /// .to_owned();
298 ///
299 /// assert_eq!(
300 /// query.to_string(MysqlQueryBuilder),
301 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
302 /// );
303 /// ```
304 pub fn apply_if<T, F>(&mut self, val: Option<T>, if_some: F) -> &mut Self
305 where
306 Self: Sized,
307 F: FnOnce(&mut Self, T),
308 {
309 if let Some(val) = val {
310 if_some(self, val);
311 }
312 self
313 }
314
315 /// Construct part of the select statement in another function.
316 ///
317 /// # Examples
318 ///
319 /// ```
320 /// use sea_query::{tests_cfg::*, *};
321 ///
322 /// let common_expr = |q: &mut SelectStatement| {
323 /// q.and_where(Expr::col(Char::FontId).eq(5));
324 /// };
325 ///
326 /// let query = Query::select()
327 /// .column(Char::Character)
328 /// .from(Char::Table)
329 /// .apply(common_expr)
330 /// .to_owned();
331 ///
332 /// assert_eq!(
333 /// query.to_string(MysqlQueryBuilder),
334 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
335 /// );
336 /// assert_eq!(
337 /// query.to_string(PostgresQueryBuilder),
338 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
339 /// );
340 /// assert_eq!(
341 /// query.to_string(SqliteQueryBuilder),
342 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
343 /// );
344 /// ```
345 pub fn apply<F>(&mut self, func: F) -> &mut Self
346 where
347 F: FnOnce(&mut Self),
348 {
349 func(self);
350 self
351 }
352
353 /// Clear the select list
354 pub fn clear_selects(&mut self) -> &mut Self {
355 self.selects = Vec::new();
356 self
357 }
358
359 /// Add an expression to the select expression list.
360 ///
361 /// # Examples
362 ///
363 /// ```
364 /// use sea_query::{tests_cfg::*, *};
365 ///
366 /// let query = Query::select()
367 /// .from(Char::Table)
368 /// .expr(Expr::val(42))
369 /// .expr(Expr::col(Char::Id).max())
370 /// .expr((1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)))
371 /// .to_owned();
372 ///
373 /// assert_eq!(
374 /// query.to_string(MysqlQueryBuilder),
375 /// r#"SELECT 42, MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
376 /// );
377 /// assert_eq!(
378 /// query.to_string(PostgresQueryBuilder),
379 /// r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
380 /// );
381 /// assert_eq!(
382 /// query.to_string(SqliteQueryBuilder),
383 /// r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
384 /// );
385 /// ```
386 pub fn expr<T>(&mut self, expr: T) -> &mut Self
387 where
388 T: Into<SelectExpr>,
389 {
390 self.selects.push(expr.into());
391 self
392 }
393
394 /// Add select expressions from vector of [`SelectExpr`].
395 ///
396 /// # Examples
397 ///
398 /// ```
399 /// use sea_query::{tests_cfg::*, *};
400 ///
401 /// let query = Query::select()
402 /// .from(Char::Table)
403 /// .exprs([
404 /// Expr::col(Char::Id).max(),
405 /// (1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)),
406 /// ])
407 /// .to_owned();
408 ///
409 /// assert_eq!(
410 /// query.to_string(MysqlQueryBuilder),
411 /// r#"SELECT MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
412 /// );
413 /// assert_eq!(
414 /// query.to_string(PostgresQueryBuilder),
415 /// r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
416 /// );
417 /// assert_eq!(
418 /// query.to_string(SqliteQueryBuilder),
419 /// r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
420 /// );
421 /// ```
422 pub fn exprs<T, I>(&mut self, exprs: I) -> &mut Self
423 where
424 T: Into<SelectExpr>,
425 I: IntoIterator<Item = T>,
426 {
427 self.selects
428 .append(&mut exprs.into_iter().map(|c| c.into()).collect());
429 self
430 }
431
432 pub fn exprs_mut_for_each<F>(&mut self, func: F)
433 where
434 F: FnMut(&mut SelectExpr),
435 {
436 self.selects.iter_mut().for_each(func);
437 }
438
439 /// Select distinct
440 pub fn distinct(&mut self) -> &mut Self {
441 self.distinct = Some(SelectDistinct::Distinct);
442 self
443 }
444
445 /// Select distinct on for *POSTGRES ONLY*
446 ///
447 /// # Examples
448 ///
449 /// ```
450 /// use sea_query::{tests_cfg::*, *};
451 ///
452 /// let query = Query::select()
453 /// .from(Char::Table)
454 /// .distinct_on([Char::Character])
455 /// .column(Char::Character)
456 /// .column(Char::SizeW)
457 /// .column(Char::SizeH)
458 /// .to_owned();
459 ///
460 /// assert_eq!(
461 /// query.to_string(PostgresQueryBuilder),
462 /// r#"SELECT DISTINCT ON ("character") "character", "size_w", "size_h" FROM "character""#
463 /// )
464 /// ```
465 ///
466 /// ```
467 /// use sea_query::{tests_cfg::*, *};
468 ///
469 /// let query = Query::select()
470 /// .from(Char::Table)
471 /// .distinct_on(vec![(Char::Table, Char::Character)])
472 /// .column(Char::Character)
473 /// .column(Char::SizeW)
474 /// .column(Char::SizeH)
475 /// .to_owned();
476 ///
477 /// assert_eq!(
478 /// query.to_string(PostgresQueryBuilder),
479 /// r#"SELECT DISTINCT ON ("character"."character") "character", "size_w", "size_h" FROM "character""#
480 /// )
481 /// ```
482 ///
483 /// ```
484 /// use sea_query::{tests_cfg::*, *};
485 ///
486 /// let distinct_cols: Vec<Character> = vec![];
487 /// let query = Query::select()
488 /// .from(Char::Table)
489 /// .distinct_on(distinct_cols)
490 /// .column(Char::Character)
491 /// .column(Char::SizeW)
492 /// .column(Char::SizeH)
493 /// .to_owned();
494 ///
495 /// assert_eq!(
496 /// query.to_string(PostgresQueryBuilder),
497 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
498 /// )
499 /// ```
500 pub fn distinct_on<T, I>(&mut self, cols: I) -> &mut Self
501 where
502 T: IntoColumnRef,
503 I: IntoIterator<Item = T>,
504 {
505 let cols = cols
506 .into_iter()
507 .map(|col| col.into_column_ref())
508 .collect::<Vec<ColumnRef>>();
509 self.distinct = if !cols.is_empty() {
510 Some(SelectDistinct::DistinctOn(cols))
511 } else {
512 None
513 };
514 self
515 }
516
517 /// Add a column to the select expression list.
518 ///
519 /// # Examples
520 ///
521 /// ```
522 /// use sea_query::{tests_cfg::*, *};
523 ///
524 /// let query = Query::select()
525 /// .from(Char::Table)
526 /// .column(Char::Character)
527 /// .column(Char::SizeW)
528 /// .column(Char::SizeH)
529 /// .to_owned();
530 ///
531 /// assert_eq!(
532 /// query.to_string(MysqlQueryBuilder),
533 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
534 /// );
535 /// assert_eq!(
536 /// query.to_string(PostgresQueryBuilder),
537 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
538 /// );
539 /// assert_eq!(
540 /// query.to_string(SqliteQueryBuilder),
541 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
542 /// );
543 /// ```
544 ///
545 /// ```
546 /// use sea_query::{tests_cfg::*, *};
547 ///
548 /// let query = Query::select()
549 /// .from(Char::Table)
550 /// .column((Char::Table, Char::Character))
551 /// .to_owned();
552 ///
553 /// assert_eq!(
554 /// query.to_string(MysqlQueryBuilder),
555 /// r#"SELECT `character`.`character` FROM `character`"#
556 /// );
557 /// assert_eq!(
558 /// query.to_string(PostgresQueryBuilder),
559 /// r#"SELECT "character"."character" FROM "character""#
560 /// );
561 /// assert_eq!(
562 /// query.to_string(SqliteQueryBuilder),
563 /// r#"SELECT "character"."character" FROM "character""#
564 /// );
565 /// ```
566 ///
567 /// ```
568 /// use sea_query::{tests_cfg::*, *};
569 ///
570 /// let query = Query::select()
571 /// .from(Char::Table)
572 /// .column(("schema", Char::Table, Char::Character))
573 /// .to_owned();
574 ///
575 /// assert_eq!(
576 /// query.to_string(MysqlQueryBuilder),
577 /// r#"SELECT `schema`.`character`.`character` FROM `character`"#
578 /// );
579 /// assert_eq!(
580 /// query.to_string(PostgresQueryBuilder),
581 /// r#"SELECT "schema"."character"."character" FROM "character""#
582 /// );
583 /// assert_eq!(
584 /// query.to_string(SqliteQueryBuilder),
585 /// r#"SELECT "schema"."character"."character" FROM "character""#
586 /// );
587 /// ```
588 pub fn column<C>(&mut self, col: C) -> &mut Self
589 where
590 C: IntoColumnRef,
591 {
592 self.expr(Expr::Column(col.into_column_ref()))
593 }
594
595 /// Select columns.
596 ///
597 /// # Examples
598 ///
599 /// ```
600 /// use sea_query::{tests_cfg::*, *};
601 ///
602 /// let query = Query::select()
603 /// .from(Char::Table)
604 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
605 /// .to_owned();
606 ///
607 /// assert_eq!(
608 /// query.to_string(MysqlQueryBuilder),
609 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
610 /// );
611 /// assert_eq!(
612 /// query.to_string(PostgresQueryBuilder),
613 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
614 /// );
615 /// assert_eq!(
616 /// query.to_string(SqliteQueryBuilder),
617 /// r#"SELECT "character", "size_w", "size_h" FROM "character""#
618 /// );
619 /// ```
620 ///
621 /// ```
622 /// use sea_query::{*, tests_cfg::*};
623 ///
624 /// let query = Query::select()
625 /// .from(Char::Table)
626 /// .columns([
627 /// (Char::Table, Char::Character),
628 /// (Char::Table, Char::SizeW),
629 /// (Char::Table, Char::SizeH),
630 /// ])
631 /// .to_owned();
632 ///
633 /// assert_eq!(
634 /// query.to_string(MysqlQueryBuilder),
635 /// r#"SELECT `character`.`character`, `character`.`size_w`, `character`.`size_h` FROM `character`"#
636 /// );
637 /// assert_eq!(
638 /// query.to_string(PostgresQueryBuilder),
639 /// r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
640 /// );
641 /// assert_eq!(
642 /// query.to_string(SqliteQueryBuilder),
643 /// r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
644 /// );
645 /// ```
646 pub fn columns<T, I>(&mut self, cols: I) -> &mut Self
647 where
648 T: IntoColumnRef,
649 I: IntoIterator<Item = T>,
650 {
651 self.exprs(cols.into_iter().map(|c| Expr::Column(c.into_column_ref())))
652 }
653
654 /// Select column.
655 ///
656 /// # Examples
657 ///
658 /// ```
659 /// use sea_query::{tests_cfg::*, *};
660 ///
661 /// let query = Query::select()
662 /// .from(Char::Table)
663 /// .expr_as(Expr::col(Char::Character), "C")
664 /// .to_owned();
665 ///
666 /// assert_eq!(
667 /// query.to_string(MysqlQueryBuilder),
668 /// r#"SELECT `character` AS `C` FROM `character`"#
669 /// );
670 /// assert_eq!(
671 /// query.to_string(PostgresQueryBuilder),
672 /// r#"SELECT "character" AS "C" FROM "character""#
673 /// );
674 /// assert_eq!(
675 /// query.to_string(SqliteQueryBuilder),
676 /// r#"SELECT "character" AS "C" FROM "character""#
677 /// );
678 /// ```
679 pub fn expr_as<T, A>(&mut self, expr: T, alias: A) -> &mut Self
680 where
681 T: Into<Expr>,
682 A: IntoIden,
683 {
684 self.expr(SelectExpr {
685 expr: expr.into(),
686 alias: Some(alias.into_iden()),
687 window: None,
688 });
689 self
690 }
691
692 /// Select column with window function.
693 ///
694 /// # Examples
695 ///
696 /// ```
697 /// use sea_query::{tests_cfg::*, *};
698 ///
699 /// let query = Query::select()
700 /// .from(Char::Table)
701 /// .expr_window(
702 /// Expr::col(Char::Character),
703 /// WindowStatement::partition_by(Char::FontSize),
704 /// )
705 /// .to_owned();
706 ///
707 /// assert_eq!(
708 /// query.to_string(MysqlQueryBuilder),
709 /// r#"SELECT `character` OVER ( PARTITION BY `font_size` ) FROM `character`"#
710 /// );
711 /// assert_eq!(
712 /// query.to_string(PostgresQueryBuilder),
713 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) FROM "character""#
714 /// );
715 /// assert_eq!(
716 /// query.to_string(SqliteQueryBuilder),
717 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) FROM "character""#
718 /// );
719 /// ```
720 pub fn expr_window<T>(&mut self, expr: T, window: WindowStatement) -> &mut Self
721 where
722 T: Into<Expr>,
723 {
724 self.expr(SelectExpr {
725 expr: expr.into(),
726 alias: None,
727 window: Some(WindowSelectType::Query(window)),
728 });
729 self
730 }
731
732 /// Select column with window function and label.
733 ///
734 /// # Examples
735 ///
736 /// ```
737 /// use sea_query::{tests_cfg::*, *};
738 ///
739 /// let query = Query::select()
740 /// .from(Char::Table)
741 /// .expr_window_as(
742 /// Expr::col(Char::Character),
743 /// WindowStatement::partition_by(Char::FontSize),
744 /// "C",
745 /// )
746 /// .to_owned();
747 ///
748 /// assert_eq!(
749 /// query.to_string(MysqlQueryBuilder),
750 /// r#"SELECT `character` OVER ( PARTITION BY `font_size` ) AS `C` FROM `character`"#
751 /// );
752 /// assert_eq!(
753 /// query.to_string(PostgresQueryBuilder),
754 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
755 /// );
756 /// assert_eq!(
757 /// query.to_string(SqliteQueryBuilder),
758 /// r#"SELECT "character" OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
759 /// );
760 /// ```
761 pub fn expr_window_as<T, A>(&mut self, expr: T, window: WindowStatement, alias: A) -> &mut Self
762 where
763 T: Into<Expr>,
764 A: IntoIden,
765 {
766 self.expr(SelectExpr {
767 expr: expr.into(),
768 alias: Some(alias.into_iden()),
769 window: Some(WindowSelectType::Query(window)),
770 });
771 self
772 }
773
774 /// Select column with window name.
775 ///
776 /// # Examples
777 ///
778 /// ```
779 /// use sea_query::{tests_cfg::*, *};
780 ///
781 /// let query = Query::select()
782 /// .from(Char::Table)
783 /// .expr_window_name(Expr::col(Char::Character), "w")
784 /// .window("w", WindowStatement::partition_by(Char::FontSize))
785 /// .to_owned();
786 ///
787 /// assert_eq!(
788 /// query.to_string(MysqlQueryBuilder),
789 /// r#"SELECT `character` OVER `w` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
790 /// );
791 /// assert_eq!(
792 /// query.to_string(PostgresQueryBuilder),
793 /// r#"SELECT "character" OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
794 /// );
795 /// assert_eq!(
796 /// query.to_string(SqliteQueryBuilder),
797 /// r#"SELECT "character" OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
798 /// );
799 /// ```
800 pub fn expr_window_name<T, W>(&mut self, expr: T, window: W) -> &mut Self
801 where
802 T: Into<Expr>,
803 W: IntoIden,
804 {
805 self.expr(SelectExpr {
806 expr: expr.into(),
807 alias: None,
808 window: Some(WindowSelectType::Name(window.into_iden())),
809 });
810 self
811 }
812
813 /// Select column with window name and label.
814 ///
815 /// # Examples
816 ///
817 /// ```
818 /// use sea_query::{tests_cfg::*, *};
819 ///
820 /// let query = Query::select()
821 /// .from(Char::Table)
822 /// .expr_window_name_as(Expr::col(Char::Character), "w", "C")
823 /// .window("w", WindowStatement::partition_by(Char::FontSize))
824 /// .to_owned();
825 ///
826 /// assert_eq!(
827 /// query.to_string(MysqlQueryBuilder),
828 /// r#"SELECT `character` OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
829 /// );
830 /// assert_eq!(
831 /// query.to_string(PostgresQueryBuilder),
832 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
833 /// );
834 /// assert_eq!(
835 /// query.to_string(SqliteQueryBuilder),
836 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
837 /// );
838 /// ```
839 pub fn expr_window_name_as<T, W, A>(&mut self, expr: T, window: W, alias: A) -> &mut Self
840 where
841 T: Into<Expr>,
842 A: IntoIden,
843 W: IntoIden,
844 {
845 self.expr(SelectExpr {
846 expr: expr.into(),
847 alias: Some(alias.into_iden()),
848 window: Some(WindowSelectType::Name(window.into_iden())),
849 });
850 self
851 }
852
853 /// From table.
854 ///
855 /// # Examples
856 ///
857 /// ```
858 /// use sea_query::{tests_cfg::*, *};
859 ///
860 /// let query = Query::select()
861 /// .column(Char::FontSize)
862 /// .from(Char::Table)
863 /// .to_owned();
864 ///
865 /// assert_eq!(
866 /// query.to_string(MysqlQueryBuilder),
867 /// r#"SELECT `font_size` FROM `character`"#
868 /// );
869 /// assert_eq!(
870 /// query.to_string(PostgresQueryBuilder),
871 /// r#"SELECT "font_size" FROM "character""#
872 /// );
873 /// assert_eq!(
874 /// query.to_string(SqliteQueryBuilder),
875 /// r#"SELECT "font_size" FROM "character""#
876 /// );
877 /// ```
878 ///
879 /// ```
880 /// use sea_query::{tests_cfg::*, *};
881 ///
882 /// let query = Query::select()
883 /// .column(Char::FontSize)
884 /// .from((Char::Table, Glyph::Table))
885 /// .to_owned();
886 ///
887 /// assert_eq!(
888 /// query.to_string(MysqlQueryBuilder),
889 /// r#"SELECT `font_size` FROM `character`.`glyph`"#
890 /// );
891 /// assert_eq!(
892 /// query.to_string(PostgresQueryBuilder),
893 /// r#"SELECT "font_size" FROM "character"."glyph""#
894 /// );
895 /// assert_eq!(
896 /// query.to_string(SqliteQueryBuilder),
897 /// r#"SELECT "font_size" FROM "character"."glyph""#
898 /// );
899 /// ```
900 ///
901 /// ```
902 /// use sea_query::{tests_cfg::*, *};
903 ///
904 /// let query = Query::select()
905 /// .column(Char::FontSize)
906 /// .from(("database", Char::Table, Glyph::Table))
907 /// .to_owned();
908 ///
909 /// assert_eq!(
910 /// query.to_string(MysqlQueryBuilder),
911 /// r#"SELECT `font_size` FROM `database`.`character`.`glyph`"#
912 /// );
913 /// assert_eq!(
914 /// query.to_string(PostgresQueryBuilder),
915 /// r#"SELECT "font_size" FROM "database"."character"."glyph""#
916 /// );
917 /// assert_eq!(
918 /// query.to_string(SqliteQueryBuilder),
919 /// r#"SELECT "font_size" FROM "database"."character"."glyph""#
920 /// );
921 /// ```
922 ///
923 /// If you specify `from` multiple times, the resulting query will have multiple from clauses.
924 /// You can perform an 'old-school' join this way.
925 ///
926 /// ```
927 /// use sea_query::{tests_cfg::*, *};
928 ///
929 /// let query = Query::select()
930 /// .expr(Expr::asterisk())
931 /// .from(Char::Table)
932 /// .from(Font::Table)
933 /// .and_where(Expr::col((Font::Table, Font::Id)).equals((Char::Table, Char::FontId)))
934 /// .to_owned();
935 ///
936 /// assert_eq!(
937 /// query.to_string(MysqlQueryBuilder),
938 /// r#"SELECT * FROM `character`, `font` WHERE `font`.`id` = `character`.`font_id`"#
939 /// );
940 /// assert_eq!(
941 /// query.to_string(PostgresQueryBuilder),
942 /// r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
943 /// );
944 /// assert_eq!(
945 /// query.to_string(SqliteQueryBuilder),
946 /// r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
947 /// );
948 /// ```
949 pub fn from<R>(&mut self, tbl_ref: R) -> &mut Self
950 where
951 R: IntoTableRef,
952 {
953 self.from_from(tbl_ref.into_table_ref())
954 }
955
956 /// Shorthand for selecting from a constant value list.
957 ///
958 /// # Panics
959 ///
960 /// Panics on an empty values list.
961 ///
962 /// ```
963 /// use sea_query::{tests_cfg::*, *};
964 ///
965 /// let query = Query::select()
966 /// .expr(Expr::asterisk())
967 /// .from_values([(1, "hello"), (2, "world")], "x")
968 /// .to_owned();
969 ///
970 /// assert_eq!(
971 /// query.to_string(MysqlQueryBuilder),
972 /// r#"SELECT * FROM (VALUES ROW(1, 'hello'), ROW(2, 'world')) AS `x`"#
973 /// );
974 /// assert_eq!(
975 /// query.to_string(PostgresQueryBuilder),
976 /// r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS "x""#
977 /// );
978 /// assert_eq!(
979 /// query.to_string(SqliteQueryBuilder),
980 /// r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS "x""#
981 /// );
982 /// ```
983 pub fn from_values<I, V, A>(&mut self, value_tuples: I, alias: A) -> &mut Self
984 where
985 I: IntoIterator<Item = V>,
986 V: IntoValueTuple,
987 A: IntoIden,
988 {
989 let value_tuples: Vec<ValueTuple> = value_tuples
990 .into_iter()
991 .map(|vt| vt.into_value_tuple())
992 .collect();
993 assert!(!value_tuples.is_empty());
994 self.from_from(TableRef::ValuesList(value_tuples, alias.into_iden()))
995 }
996
997 /// From table with alias.
998 ///
999 /// # Examples
1000 ///
1001 /// ```
1002 /// use sea_query::{tests_cfg::*, *};
1003 ///
1004 /// let query = Query::select()
1005 /// .from_as(Char::Table, "char")
1006 /// .column(("char", Char::Character))
1007 /// .to_owned();
1008 ///
1009 /// assert_eq!(
1010 /// query.to_string(MysqlQueryBuilder),
1011 /// r#"SELECT `char`.`character` FROM `character` AS `char`"#
1012 /// );
1013 /// assert_eq!(
1014 /// query.to_string(PostgresQueryBuilder),
1015 /// r#"SELECT "char"."character" FROM "character" AS "char""#
1016 /// );
1017 /// assert_eq!(
1018 /// query.to_string(SqliteQueryBuilder),
1019 /// r#"SELECT "char"."character" FROM "character" AS "char""#
1020 /// );
1021 /// ```
1022 ///
1023 /// ```
1024 /// use sea_query::{audit::*, tests_cfg::*, *};
1025 ///
1026 /// let query = Query::select()
1027 /// .from_as((Font::Table, Char::Table), "alias")
1028 /// .column(("alias", Char::Character))
1029 /// .to_owned();
1030 ///
1031 /// assert_eq!(
1032 /// query.to_string(MysqlQueryBuilder),
1033 /// r#"SELECT `alias`.`character` FROM `font`.`character` AS `alias`"#
1034 /// );
1035 /// assert_eq!(
1036 /// query.to_string(PostgresQueryBuilder),
1037 /// r#"SELECT "alias"."character" FROM "font"."character" AS "alias""#
1038 /// );
1039 /// assert_eq!(
1040 /// query.to_string(SqliteQueryBuilder),
1041 /// r#"SELECT "alias"."character" FROM "font"."character" AS "alias""#
1042 /// );
1043 /// assert_eq!(
1044 /// query.audit().unwrap().selects(),
1045 /// [TableName(Some(Font::Table.into()), Char::Table.into_iden())]
1046 /// );
1047 /// ```
1048 pub fn from_as<R, A>(&mut self, tbl_ref: R, alias: A) -> &mut Self
1049 where
1050 R: IntoTableRef,
1051 A: IntoIden,
1052 {
1053 self.from_from(tbl_ref.into_table_ref().alias(alias.into_iden()))
1054 }
1055
1056 /// From sub-query.
1057 ///
1058 /// # Examples
1059 ///
1060 /// ```
1061 /// use sea_query::{audit::*, tests_cfg::*, *};
1062 ///
1063 /// let query = Query::select()
1064 /// .columns([Glyph::Image])
1065 /// .from_subquery(
1066 /// Query::select()
1067 /// .columns([Glyph::Image, Glyph::Aspect])
1068 /// .from(Glyph::Table)
1069 /// .take(),
1070 /// "subglyph",
1071 /// )
1072 /// .to_owned();
1073 ///
1074 /// assert_eq!(
1075 /// query.to_string(MysqlQueryBuilder),
1076 /// r#"SELECT `image` FROM (SELECT `image`, `aspect` FROM `glyph`) AS `subglyph`"#
1077 /// );
1078 /// assert_eq!(
1079 /// query.to_string(PostgresQueryBuilder),
1080 /// r#"SELECT "image" FROM (SELECT "image", "aspect" FROM "glyph") AS "subglyph""#
1081 /// );
1082 /// assert_eq!(
1083 /// query.to_string(SqliteQueryBuilder),
1084 /// r#"SELECT "image" FROM (SELECT "image", "aspect" FROM "glyph") AS "subglyph""#
1085 /// );
1086 /// assert_eq!(
1087 /// query.audit().unwrap().selected_tables(),
1088 /// [Glyph::Table.into_iden()]
1089 /// );
1090 /// ```
1091 pub fn from_subquery<T>(&mut self, query: SelectStatement, alias: T) -> &mut Self
1092 where
1093 T: IntoIden,
1094 {
1095 self.from_from(TableRef::SubQuery(query.into(), alias.into_iden()))
1096 }
1097
1098 /// From function call.
1099 ///
1100 /// # Examples
1101 ///
1102 /// ```
1103 /// use sea_query::{tests_cfg::*, *};
1104 ///
1105 /// let query = Query::select()
1106 /// .column(Asterisk)
1107 /// .from_function(Func::random(), "func")
1108 /// .to_owned();
1109 ///
1110 /// assert_eq!(
1111 /// query.to_string(MysqlQueryBuilder),
1112 /// r#"SELECT * FROM RAND() AS `func`"#
1113 /// );
1114 /// assert_eq!(
1115 /// query.to_string(PostgresQueryBuilder),
1116 /// r#"SELECT * FROM RANDOM() AS "func""#
1117 /// );
1118 /// assert_eq!(
1119 /// query.to_string(SqliteQueryBuilder),
1120 /// r#"SELECT * FROM RANDOM() AS "func""#
1121 /// );
1122 /// ```
1123 pub fn from_function<T>(&mut self, func: FunctionCall, alias: T) -> &mut Self
1124 where
1125 T: IntoIden,
1126 {
1127 self.from_from(TableRef::FunctionCall(func, alias.into_iden()))
1128 }
1129
1130 /// Clears all current from clauses.
1131 ///
1132 /// # Examples
1133 ///
1134 /// ```
1135 /// use sea_query::{tests_cfg::*, *};
1136 ///
1137 /// let query = Query::select()
1138 /// .column(Asterisk)
1139 /// .from(Char::Table)
1140 /// .from_clear()
1141 /// .from(Font::Table)
1142 /// .to_owned();
1143 ///
1144 /// assert_eq!(
1145 /// query.to_string(MysqlQueryBuilder),
1146 /// r#"SELECT * FROM `font`"#
1147 /// );
1148 /// assert_eq!(
1149 /// query.to_string(PostgresQueryBuilder),
1150 /// r#"SELECT * FROM "font""#
1151 /// );
1152 /// assert_eq!(
1153 /// query.to_string(SqliteQueryBuilder),
1154 /// r#"SELECT * FROM "font""#
1155 /// );
1156 /// ```
1157 pub fn from_clear(&mut self) -> &mut Self {
1158 self.from.clear();
1159 self
1160 }
1161
1162 #[allow(clippy::wrong_self_convention)]
1163 fn from_from(&mut self, select: TableRef) -> &mut Self {
1164 self.from.push(select);
1165 self
1166 }
1167
1168 /// Cross join.
1169 ///
1170 /// # Examples
1171 ///
1172 /// ```
1173 /// use sea_query::{audit::*, tests_cfg::*, *};
1174 ///
1175 /// let query = Query::select()
1176 /// .column(Char::Character)
1177 /// .column((Font::Table, Font::Name))
1178 /// .from(Char::Table)
1179 /// .cross_join(Font::Table)
1180 /// .to_owned();
1181 ///
1182 /// assert_eq!(
1183 /// query.to_string(MysqlQueryBuilder),
1184 /// r#"SELECT `character`, `font`.`name` FROM `character` CROSS JOIN `font`"#
1185 /// );
1186 /// assert_eq!(
1187 /// query.to_string(PostgresQueryBuilder),
1188 /// r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font""#
1189 /// );
1190 /// assert_eq!(
1191 /// query.to_string(SqliteQueryBuilder),
1192 /// r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font""#
1193 /// );
1194 /// assert_eq!(
1195 /// query.audit().unwrap().selected_tables(),
1196 /// [Char::Table.into_iden(), Font::Table.into_iden()]
1197 /// );
1198 /// ```
1199 pub fn cross_join<R>(&mut self, tbl_ref: R) -> &mut Self
1200 where
1201 R: IntoTableRef,
1202 {
1203 self.push_join(JoinType::CrossJoin, tbl_ref.into_table_ref(), None, false);
1204 self
1205 }
1206
1207 /// Left join.
1208 ///
1209 /// # Examples
1210 ///
1211 /// ```
1212 /// use sea_query::{audit::*, tests_cfg::*, *};
1213 ///
1214 /// let query = Query::select()
1215 /// .column(Char::Character)
1216 /// .column((Font::Table, Font::Name))
1217 /// .from(Char::Table)
1218 /// .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1219 /// .to_owned();
1220 ///
1221 /// assert_eq!(
1222 /// query.to_string(MysqlQueryBuilder),
1223 /// r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1224 /// );
1225 /// assert_eq!(
1226 /// query.to_string(PostgresQueryBuilder),
1227 /// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id""#
1228 /// );
1229 /// assert_eq!(
1230 /// query.to_string(SqliteQueryBuilder),
1231 /// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id""#
1232 /// );
1233 /// assert_eq!(
1234 /// query.audit().unwrap().selected_tables(),
1235 /// [Char::Table.into_iden(), Font::Table.into_iden()]
1236 /// );
1237 ///
1238 /// // Constructing chained join conditions
1239 /// let query = Query::select()
1240 /// .column(Char::Character)
1241 /// .column((Font::Table, Font::Name))
1242 /// .from(Char::Table)
1243 /// .left_join(
1244 /// Font::Table,
1245 /// all![
1246 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1247 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1248 /// ]
1249 /// )
1250 /// .to_owned();
1251 ///
1252 /// assert_eq!(
1253 /// query.to_string(MysqlQueryBuilder),
1254 /// r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1255 /// );
1256 /// assert_eq!(
1257 /// query.to_string(PostgresQueryBuilder),
1258 /// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1259 /// );
1260 /// assert_eq!(
1261 /// query.to_string(SqliteQueryBuilder),
1262 /// r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1263 /// );
1264 /// ```
1265 pub fn left_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1266 where
1267 R: IntoTableRef,
1268 C: IntoCondition,
1269 {
1270 self.join(JoinType::LeftJoin, tbl_ref, condition)
1271 }
1272
1273 /// Right join.
1274 ///
1275 /// # Examples
1276 ///
1277 /// ```
1278 /// use sea_query::{*, tests_cfg::*};
1279 ///
1280 /// let query = Query::select()
1281 /// .column(Char::Character)
1282 /// .column((Font::Table, Font::Name))
1283 /// .from(Char::Table)
1284 /// .right_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1285 /// .to_owned();
1286 ///
1287 /// assert_eq!(
1288 /// query.to_string(MysqlQueryBuilder),
1289 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1290 /// );
1291 /// assert_eq!(
1292 /// query.to_string(PostgresQueryBuilder),
1293 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
1294 /// );
1295 /// assert_eq!(
1296 /// query.to_string(SqliteQueryBuilder),
1297 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
1298 /// );
1299 ///
1300 /// // Constructing chained join conditions
1301 /// let query = Query::select()
1302 /// .column(Char::Character)
1303 /// .column((Font::Table, Font::Name))
1304 /// .from(Char::Table)
1305 /// .right_join(
1306 /// Font::Table,
1307 /// all![
1308 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1309 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1310 /// ]
1311 /// )
1312 /// .to_owned();
1313 ///
1314 /// assert_eq!(
1315 /// query.to_string(MysqlQueryBuilder),
1316 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1317 /// );
1318 /// assert_eq!(
1319 /// query.to_string(PostgresQueryBuilder),
1320 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1321 /// );
1322 /// assert_eq!(
1323 /// query.to_string(SqliteQueryBuilder),
1324 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1325 /// );
1326 /// ```
1327 pub fn right_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1328 where
1329 R: IntoTableRef,
1330 C: IntoCondition,
1331 {
1332 self.join(JoinType::RightJoin, tbl_ref, condition)
1333 }
1334
1335 /// Inner join.
1336 ///
1337 /// # Examples
1338 ///
1339 /// ```
1340 /// use sea_query::{*, tests_cfg::*};
1341 ///
1342 /// let query = Query::select()
1343 /// .column(Char::Character)
1344 /// .column((Font::Table, Font::Name))
1345 /// .from(Char::Table)
1346 /// .inner_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1347 /// .to_owned();
1348 ///
1349 /// assert_eq!(
1350 /// query.to_string(MysqlQueryBuilder),
1351 /// r#"SELECT `character`, `font`.`name` FROM `character` INNER JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1352 /// );
1353 /// assert_eq!(
1354 /// query.to_string(PostgresQueryBuilder),
1355 /// r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id""#
1356 /// );
1357 /// assert_eq!(
1358 /// query.to_string(SqliteQueryBuilder),
1359 /// r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id""#
1360 /// );
1361 ///
1362 /// // Constructing chained join conditions
1363 /// let query = Query::select()
1364 /// .column(Char::Character)
1365 /// .column((Font::Table, Font::Name))
1366 /// .from(Char::Table)
1367 /// .inner_join(
1368 /// Font::Table,
1369 /// all![
1370 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1371 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1372 /// ]
1373 /// )
1374 /// .to_owned();
1375 ///
1376 /// assert_eq!(
1377 /// query.to_string(MysqlQueryBuilder),
1378 /// r#"SELECT `character`, `font`.`name` FROM `character` INNER JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1379 /// );
1380 /// assert_eq!(
1381 /// query.to_string(PostgresQueryBuilder),
1382 /// r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1383 /// );
1384 /// assert_eq!(
1385 /// query.to_string(SqliteQueryBuilder),
1386 /// r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1387 /// );
1388 /// ```
1389 pub fn inner_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1390 where
1391 R: IntoTableRef,
1392 C: IntoCondition,
1393 {
1394 self.join(JoinType::InnerJoin, tbl_ref, condition)
1395 }
1396
1397 /// Full outer join.
1398 ///
1399 /// # Examples
1400 ///
1401 /// ```
1402 /// use sea_query::{*, tests_cfg::*};
1403 ///
1404 /// let query = Query::select()
1405 /// .column(Char::Character)
1406 /// .column((Font::Table, Font::Name))
1407 /// .from(Char::Table)
1408 /// .full_outer_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1409 /// .to_owned();
1410 ///
1411 /// assert_eq!(
1412 /// query.to_string(PostgresQueryBuilder),
1413 /// r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id""#
1414 /// );
1415 /// assert_eq!(
1416 /// query.to_string(SqliteQueryBuilder),
1417 /// r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id""#
1418 /// );
1419 ///
1420 /// // Constructing chained join conditions
1421 /// let query = Query::select()
1422 /// .column(Char::Character)
1423 /// .column((Font::Table, Font::Name))
1424 /// .from(Char::Table)
1425 /// .full_outer_join(
1426 /// Font::Table,
1427 /// all![
1428 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1429 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1430 /// ]
1431 /// )
1432 /// .to_owned();
1433 ///
1434 /// assert_eq!(
1435 /// query.to_string(PostgresQueryBuilder),
1436 /// r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1437 /// );
1438 /// assert_eq!(
1439 /// query.to_string(SqliteQueryBuilder),
1440 /// r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1441 /// );
1442 /// ```
1443 pub fn full_outer_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1444 where
1445 R: IntoTableRef,
1446 C: IntoCondition,
1447 {
1448 self.join(JoinType::FullOuterJoin, tbl_ref, condition)
1449 }
1450
1451 /// Straight join. MySQL only.
1452 ///
1453 /// # Examples
1454 ///
1455 /// ```
1456 /// use sea_query::{*, tests_cfg::*};
1457 ///
1458 /// let query = Query::select()
1459 /// .column(Char::Character)
1460 /// .column((Font::Table, Font::Name))
1461 /// .from(Char::Table)
1462 /// .straight_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1463 /// .to_owned();
1464 ///
1465 /// assert_eq!(
1466 /// query.to_string(MysqlQueryBuilder),
1467 /// r#"SELECT `character`, `font`.`name` FROM `character` STRAIGHT_JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1468 /// );
1469 ///
1470 /// // Constructing chained join conditions
1471 /// let query = Query::select()
1472 /// .column(Char::Character)
1473 /// .column((Font::Table, Font::Name))
1474 /// .from(Char::Table)
1475 /// .straight_join(
1476 /// Font::Table,
1477 /// all![
1478 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1479 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1480 /// ]
1481 /// )
1482 /// .to_owned();
1483 ///
1484 /// assert_eq!(
1485 /// query.to_string(MysqlQueryBuilder),
1486 /// r#"SELECT `character`, `font`.`name` FROM `character` STRAIGHT_JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1487 /// );
1488 /// ```
1489 pub fn straight_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
1490 where
1491 R: IntoTableRef,
1492 C: IntoCondition,
1493 {
1494 self.join(JoinType::StraightJoin, tbl_ref, condition)
1495 }
1496
1497 /// Join with other table by [`JoinType`].
1498 ///
1499 /// If [`JoinType`] is [`CrossJoin`](JoinType::CrossJoin), the condition will be ignored.
1500 ///
1501 /// # Examples
1502 ///
1503 /// ```
1504 /// use sea_query::{audit::*, tests_cfg::*, *};
1505 ///
1506 /// let query = Query::select()
1507 /// .column(Char::Character)
1508 /// .column((Font::Table, Font::Name))
1509 /// .from(Char::Table)
1510 /// .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1511 /// .to_owned();
1512 ///
1513 /// assert_eq!(
1514 /// query.to_string(MysqlQueryBuilder),
1515 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
1516 /// );
1517 /// assert_eq!(
1518 /// query.to_string(PostgresQueryBuilder),
1519 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
1520 /// );
1521 /// assert_eq!(
1522 /// query.to_string(SqliteQueryBuilder),
1523 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
1524 /// );
1525 /// assert_eq!(
1526 /// query.audit().unwrap().selected_tables(),
1527 /// [Char::Table.into_iden(), Font::Table.into_iden()]
1528 /// );
1529 ///
1530 /// // Constructing chained join conditions
1531 /// let query = Query::select()
1532 /// .column(Char::Character)
1533 /// .column((Font::Table, Font::Name))
1534 /// .from(Char::Table)
1535 /// .join(
1536 /// JoinType::RightJoin,
1537 /// Font::Table,
1538 /// all![
1539 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1540 /// Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
1541 /// ]
1542 /// )
1543 /// .to_owned();
1544 ///
1545 /// assert_eq!(
1546 /// query.to_string(MysqlQueryBuilder),
1547 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
1548 /// );
1549 /// assert_eq!(
1550 /// query.to_string(PostgresQueryBuilder),
1551 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1552 /// );
1553 /// assert_eq!(
1554 /// query.to_string(SqliteQueryBuilder),
1555 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
1556 /// );
1557 /// assert_eq!(
1558 /// query.audit().unwrap().selected_tables(),
1559 /// [Char::Table.into_iden(), Font::Table.into_iden()]
1560 /// );
1561 /// ```
1562 pub fn join<R, C>(&mut self, join: JoinType, tbl_ref: R, condition: C) -> &mut Self
1563 where
1564 R: IntoTableRef,
1565 C: IntoCondition,
1566 {
1567 let on = match join {
1568 JoinType::CrossJoin => None,
1569 _ => Some(JoinOn::Condition(Box::new(
1570 ConditionHolder::new_with_condition(condition.into_condition()),
1571 ))),
1572 };
1573 self.push_join(join, tbl_ref.into_table_ref(), on, false)
1574 }
1575
1576 /// Join with other table by [`JoinType`], assigning an alias to the joined table.
1577 ///
1578 /// If [`JoinType`] is [`CrossJoin`](JoinType::CrossJoin), the condition will be ignored.
1579 ///
1580 /// # Examples
1581 ///
1582 /// ```
1583 /// use sea_query::{*, tests_cfg::*};
1584 ///
1585 /// let query = Query::select()
1586 /// .column(Char::Character)
1587 /// .column(("f", Font::Name))
1588 /// .from(Char::Table)
1589 /// .join_as(
1590 /// JoinType::RightJoin,
1591 /// Font::Table,
1592 /// "f",
1593 /// Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id))
1594 /// )
1595 /// .to_owned();
1596 ///
1597 /// assert_eq!(
1598 /// query.to_string(MysqlQueryBuilder),
1599 /// r#"SELECT `character`, `f`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `f`.`id`"#
1600 /// );
1601 /// assert_eq!(
1602 /// query.to_string(PostgresQueryBuilder),
1603 /// r#"SELECT "character", "f"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "f"."id""#
1604 /// );
1605 /// assert_eq!(
1606 /// query.to_string(SqliteQueryBuilder),
1607 /// r#"SELECT "character", "f"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "f"."id""#
1608 /// );
1609 ///
1610 /// // Constructing chained join conditions
1611 /// assert_eq!(
1612 /// Query::select()
1613 /// .column(Char::Character)
1614 /// .column(("f", Font::Name))
1615 /// .from(Char::Table)
1616 /// .join_as(
1617 /// JoinType::RightJoin,
1618 /// Font::Table,
1619 /// "f",
1620 /// Condition::all()
1621 /// .add(Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id)))
1622 /// .add(Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id)))
1623 /// )
1624 /// .to_string(MysqlQueryBuilder),
1625 /// r#"SELECT `character`, `f`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `f`.`id` AND `character`.`font_id` = `f`.`id`"#
1626 /// );
1627 /// ```
1628 pub fn join_as<R, A, C>(
1629 &mut self,
1630 join: JoinType,
1631 tbl_ref: R,
1632 alias: A,
1633 condition: C,
1634 ) -> &mut Self
1635 where
1636 R: IntoTableRef,
1637 A: IntoIden,
1638 C: IntoCondition,
1639 {
1640 let on = match join {
1641 JoinType::CrossJoin => None,
1642 _ => Some(JoinOn::Condition(Box::new(
1643 ConditionHolder::new_with_condition(condition.into_condition()),
1644 ))),
1645 };
1646 self.push_join(
1647 join,
1648 tbl_ref.into_table_ref().alias(alias.into_iden()),
1649 on,
1650 false,
1651 )
1652 }
1653
1654 /// Join with sub-query.
1655 ///
1656 /// # Examples
1657 ///
1658 /// ```
1659 /// use sea_query::{tests_cfg::*, audit::*, *};
1660 ///
1661 /// let query = Query::select()
1662 /// .column(Font::Name)
1663 /// .from(Font::Table)
1664 /// .join_subquery(
1665 /// JoinType::LeftJoin,
1666 /// Query::select().column(Glyph::Id).from(Glyph::Table).take(),
1667 /// "sub_glyph",
1668 /// Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id))
1669 /// )
1670 /// .to_owned();
1671 ///
1672 /// assert_eq!(
1673 /// query.to_string(MysqlQueryBuilder),
1674 /// r#"SELECT `name` FROM `font` LEFT JOIN (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id`"#
1675 /// );
1676 /// assert_eq!(
1677 /// query.to_string(PostgresQueryBuilder),
1678 /// r#"SELECT "name" FROM "font" LEFT JOIN (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
1679 /// );
1680 /// assert_eq!(
1681 /// query.to_string(SqliteQueryBuilder),
1682 /// r#"SELECT "name" FROM "font" LEFT JOIN (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
1683 /// );
1684 /// assert_eq!(
1685 /// query.audit().unwrap().selected_tables(),
1686 /// [Font::Table.into_iden(), Glyph::Table.into_iden()]
1687 /// );
1688 ///
1689 /// // Constructing chained join conditions
1690 /// assert_eq!(
1691 /// Query::select()
1692 /// .column(Font::Name)
1693 /// .from(Font::Table)
1694 /// .join_subquery(
1695 /// JoinType::LeftJoin,
1696 /// Query::select().column(Glyph::Id).from(Glyph::Table).take(),
1697 /// "sub_glyph",
1698 /// Condition::all()
1699 /// .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
1700 /// .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
1701 /// )
1702 /// .to_string(MysqlQueryBuilder),
1703 /// r#"SELECT `name` FROM `font` LEFT JOIN (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id` AND `font`.`id` = `sub_glyph`.`id`"#
1704 /// );
1705 /// assert_eq!(
1706 /// query.audit().unwrap().selected_tables(),
1707 /// [Font::Table.into_iden(), Glyph::Table.into_iden()]
1708 /// );
1709 /// ```
1710 pub fn join_subquery<T, C>(
1711 &mut self,
1712 join: JoinType,
1713 query: SelectStatement,
1714 alias: T,
1715 condition: C,
1716 ) -> &mut Self
1717 where
1718 T: IntoIden,
1719 C: IntoCondition,
1720 {
1721 self.push_join(
1722 join,
1723 TableRef::SubQuery(query.into(), alias.into_iden()),
1724 JoinOn::Condition(Box::new(ConditionHolder::new_with_condition(
1725 condition.into_condition(),
1726 ))),
1727 false,
1728 )
1729 }
1730
1731 /// Join Lateral with sub-query. Not supported by SQLite.
1732 ///
1733 /// # Examples
1734 ///
1735 /// ```
1736 /// use sea_query::{audit::*, tests_cfg::*, *};
1737 ///
1738 /// let query = Query::select()
1739 /// .column(Font::Name)
1740 /// .from(Font::Table)
1741 /// .join_lateral(
1742 /// JoinType::LeftJoin,
1743 /// Query::select().column(Glyph::Id).from(Glyph::Table).take(),
1744 /// "sub_glyph",
1745 /// Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id))
1746 /// )
1747 /// .to_owned();
1748 ///
1749 /// assert_eq!(
1750 /// query.to_string(MysqlQueryBuilder),
1751 /// r#"SELECT `name` FROM `font` LEFT JOIN LATERAL (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id`"#
1752 /// );
1753 /// assert_eq!(
1754 /// query.to_string(PostgresQueryBuilder),
1755 /// r#"SELECT "name" FROM "font" LEFT JOIN LATERAL (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
1756 /// );
1757 /// assert_eq!(
1758 /// query.audit().unwrap().selected_tables(),
1759 /// [Font::Table.into_iden(), Glyph::Table.into_iden()]
1760 /// );
1761 ///
1762 /// // Constructing chained join conditions
1763 /// assert_eq!(
1764 /// Query::select()
1765 /// .column(Font::Name)
1766 /// .from(Font::Table)
1767 /// .join_lateral(
1768 /// JoinType::LeftJoin,
1769 /// Query::select().column(Glyph::Id).from(Glyph::Table).take(),
1770 /// "sub_glyph",
1771 /// Condition::all()
1772 /// .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
1773 /// .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
1774 /// )
1775 /// .to_string(MysqlQueryBuilder),
1776 /// r#"SELECT `name` FROM `font` LEFT JOIN LATERAL (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id` AND `font`.`id` = `sub_glyph`.`id`"#
1777 /// );
1778 /// ```
1779 pub fn join_lateral<T, C>(
1780 &mut self,
1781 join: JoinType,
1782 query: SelectStatement,
1783 alias: T,
1784 condition: C,
1785 ) -> &mut Self
1786 where
1787 T: IntoIden,
1788 C: IntoCondition,
1789 {
1790 self.push_join(
1791 join,
1792 TableRef::SubQuery(query.into(), alias.into_iden()),
1793 JoinOn::Condition(Box::new(ConditionHolder::new_with_condition(
1794 condition.into_condition(),
1795 ))),
1796 true,
1797 )
1798 }
1799
1800 fn push_join(
1801 &mut self,
1802 join: JoinType,
1803 table: TableRef,
1804 on: impl Into<Option<JoinOn>>,
1805 lateral: bool,
1806 ) -> &mut Self {
1807 self.join.push(JoinExpr {
1808 join,
1809 table: Box::new(table),
1810 on: on.into(),
1811 lateral,
1812 });
1813 self
1814 }
1815
1816 /// Group by columns.
1817 ///
1818 /// # Examples
1819 ///
1820 /// ```
1821 /// use sea_query::{*, tests_cfg::*};
1822 ///
1823 /// let query = Query::select()
1824 /// .column(Char::Character)
1825 /// .column((Font::Table, Font::Name))
1826 /// .from(Char::Table)
1827 /// .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1828 /// .group_by_columns([
1829 /// Char::Character,
1830 /// ])
1831 /// .to_owned();
1832 ///
1833 /// assert_eq!(
1834 /// query.to_string(MysqlQueryBuilder),
1835 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`"#
1836 /// );
1837 /// assert_eq!(
1838 /// query.to_string(PostgresQueryBuilder),
1839 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character""#
1840 /// );
1841 /// assert_eq!(
1842 /// query.to_string(SqliteQueryBuilder),
1843 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character""#
1844 /// );
1845 /// ```
1846 ///
1847 /// ```
1848 /// use sea_query::{*, tests_cfg::*};
1849 ///
1850 /// let query = Query::select()
1851 /// .column(Char::Character)
1852 /// .column((Font::Table, Font::Name))
1853 /// .from(Char::Table)
1854 /// .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1855 /// .group_by_columns([
1856 /// (Char::Table, Char::Character),
1857 /// ])
1858 /// .to_owned();
1859 ///
1860 /// assert_eq!(
1861 /// query.to_string(MysqlQueryBuilder),
1862 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`.`character`"#
1863 /// );
1864 /// assert_eq!(
1865 /// query.to_string(PostgresQueryBuilder),
1866 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
1867 /// );
1868 /// assert_eq!(
1869 /// query.to_string(SqliteQueryBuilder),
1870 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
1871 /// );
1872 /// ```
1873 pub fn group_by_columns<T, I>(&mut self, cols: I) -> &mut Self
1874 where
1875 T: IntoColumnRef,
1876 I: IntoIterator<Item = T>,
1877 {
1878 self.add_group_by(cols.into_iter().map(|c| Expr::Column(c.into_column_ref())))
1879 }
1880
1881 /// Add a group by column.
1882 ///
1883 /// ```
1884 /// use sea_query::{*, tests_cfg::*};
1885 ///
1886 /// let query = Query::select()
1887 /// .column(Char::Character)
1888 /// .column((Font::Table, Font::Name))
1889 /// .from(Char::Table)
1890 /// .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
1891 /// .group_by_col((Char::Table, Char::Character))
1892 /// .to_owned();
1893 ///
1894 /// assert_eq!(
1895 /// query.to_string(MysqlQueryBuilder),
1896 /// r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`.`character`"#
1897 /// );
1898 /// assert_eq!(
1899 /// query.to_string(PostgresQueryBuilder),
1900 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
1901 /// );
1902 /// assert_eq!(
1903 /// query.to_string(SqliteQueryBuilder),
1904 /// r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
1905 /// );
1906 /// ```
1907 pub fn group_by_col<T>(&mut self, col: T) -> &mut Self
1908 where
1909 T: IntoColumnRef,
1910 {
1911 self.group_by_columns([col])
1912 }
1913
1914 /// Add group by expressions from vector of [`SelectExpr`].
1915 ///
1916 /// # Examples
1917 ///
1918 /// ```
1919 /// use sea_query::{tests_cfg::*, *};
1920 ///
1921 /// let query = Query::select()
1922 /// .from(Char::Table)
1923 /// .column(Char::Character)
1924 /// .add_group_by([Expr::col(Char::SizeW).into(), Expr::col(Char::SizeH).into()])
1925 /// .to_owned();
1926 ///
1927 /// assert_eq!(
1928 /// query.to_string(MysqlQueryBuilder),
1929 /// r#"SELECT `character` FROM `character` GROUP BY `size_w`, `size_h`"#
1930 /// );
1931 /// assert_eq!(
1932 /// query.to_string(PostgresQueryBuilder),
1933 /// r#"SELECT "character" FROM "character" GROUP BY "size_w", "size_h""#
1934 /// );
1935 /// assert_eq!(
1936 /// query.to_string(SqliteQueryBuilder),
1937 /// r#"SELECT "character" FROM "character" GROUP BY "size_w", "size_h""#
1938 /// );
1939 /// ```
1940 pub fn add_group_by<I>(&mut self, expr: I) -> &mut Self
1941 where
1942 I: IntoIterator<Item = Expr>,
1943 {
1944 self.groups.append(&mut expr.into_iter().collect());
1945 self
1946 }
1947
1948 /// Having condition, expressed with [`any!`](crate::any) and [`all!`](crate::all).
1949 ///
1950 /// # Examples
1951 ///
1952 /// ```
1953 /// use sea_query::{*, tests_cfg::*};
1954 ///
1955 /// let query = Query::select()
1956 /// .column(Glyph::Aspect)
1957 /// .expr(Expr::col(Glyph::Image).max())
1958 /// .from(Glyph::Table)
1959 /// .group_by_columns([
1960 /// Glyph::Aspect,
1961 /// ])
1962 /// .cond_having(
1963 /// all![
1964 /// Expr::col((Glyph::Table, Glyph::Aspect)).is_in([3, 4]),
1965 /// any![
1966 /// Expr::col((Glyph::Table, Glyph::Image)).like("A%"),
1967 /// Expr::col((Glyph::Table, Glyph::Image)).like("B%")
1968 /// ]
1969 /// ]
1970 /// )
1971 /// .to_owned();
1972 ///
1973 /// assert_eq!(
1974 /// query.to_string(MysqlQueryBuilder),
1975 /// r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `glyph`.`aspect` IN (3, 4) AND (`glyph`.`image` LIKE 'A%' OR `glyph`.`image` LIKE 'B%')"#
1976 /// );
1977 /// ```
1978 pub fn cond_having<C>(&mut self, condition: C) -> &mut Self
1979 where
1980 C: IntoCondition,
1981 {
1982 self.having.add_condition(condition.into_condition());
1983 self
1984 }
1985
1986 /// And having condition.
1987 ///
1988 /// # Examples
1989 ///
1990 /// ```
1991 /// use sea_query::{*, tests_cfg::*};
1992 ///
1993 /// let query = Query::select()
1994 /// .column(Glyph::Aspect)
1995 /// .expr(Expr::col(Glyph::Image).max())
1996 /// .from(Glyph::Table)
1997 /// .group_by_columns([
1998 /// Glyph::Aspect,
1999 /// ])
2000 /// .and_having(Expr::col(Glyph::Aspect).gt(2))
2001 /// .cond_having(Expr::col(Glyph::Aspect).lt(8))
2002 /// .to_owned();
2003 ///
2004 /// assert_eq!(
2005 /// query.to_string(MysqlQueryBuilder),
2006 /// r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `aspect` > 2 AND `aspect` < 8"#
2007 /// );
2008 /// assert_eq!(
2009 /// query.to_string(PostgresQueryBuilder),
2010 /// r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 AND "aspect" < 8"#
2011 /// );
2012 /// assert_eq!(
2013 /// query.to_string(SqliteQueryBuilder),
2014 /// r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 AND "aspect" < 8"#
2015 /// );
2016 /// ```
2017 pub fn and_having(&mut self, other: Expr) -> &mut Self {
2018 self.cond_having(other)
2019 }
2020
2021 /// Limit the number of returned rows.
2022 ///
2023 /// # Examples
2024 ///
2025 /// ```
2026 /// use sea_query::{tests_cfg::*, *};
2027 ///
2028 /// let query = Query::select()
2029 /// .column(Glyph::Aspect)
2030 /// .from(Glyph::Table)
2031 /// .limit(10)
2032 /// .to_owned();
2033 ///
2034 /// assert_eq!(
2035 /// query.to_string(MysqlQueryBuilder),
2036 /// r#"SELECT `aspect` FROM `glyph` LIMIT 10"#
2037 /// );
2038 /// assert_eq!(
2039 /// query.to_string(PostgresQueryBuilder),
2040 /// r#"SELECT "aspect" FROM "glyph" LIMIT 10"#
2041 /// );
2042 /// assert_eq!(
2043 /// query.to_string(SqliteQueryBuilder),
2044 /// r#"SELECT "aspect" FROM "glyph" LIMIT 10"#
2045 /// );
2046 /// ```
2047 pub fn limit(&mut self, limit: u64) -> &mut Self {
2048 self.limit = Some(limit.into());
2049 self
2050 }
2051
2052 /// Reset limit
2053 pub fn reset_limit(&mut self) -> &mut Self {
2054 self.limit = None;
2055 self
2056 }
2057
2058 /// Offset number of returned rows.
2059 ///
2060 /// # Examples
2061 ///
2062 /// ```
2063 /// use sea_query::{tests_cfg::*, *};
2064 ///
2065 /// let query = Query::select()
2066 /// .column(Glyph::Aspect)
2067 /// .from(Glyph::Table)
2068 /// .limit(10)
2069 /// .offset(10)
2070 /// .to_owned();
2071 ///
2072 /// assert_eq!(
2073 /// query.to_string(MysqlQueryBuilder),
2074 /// r#"SELECT `aspect` FROM `glyph` LIMIT 10 OFFSET 10"#
2075 /// );
2076 /// assert_eq!(
2077 /// query.to_string(PostgresQueryBuilder),
2078 /// r#"SELECT "aspect" FROM "glyph" LIMIT 10 OFFSET 10"#
2079 /// );
2080 /// assert_eq!(
2081 /// query.to_string(SqliteQueryBuilder),
2082 /// r#"SELECT "aspect" FROM "glyph" LIMIT 10 OFFSET 10"#
2083 /// );
2084 /// ```
2085 pub fn offset(&mut self, offset: u64) -> &mut Self {
2086 self.offset = Some(offset.into());
2087 self
2088 }
2089
2090 /// Reset offset
2091 pub fn reset_offset(&mut self) -> &mut Self {
2092 self.offset = None;
2093 self
2094 }
2095
2096 /// Row locking (if supported).
2097 ///
2098 /// # Examples
2099 ///
2100 /// ```
2101 /// use sea_query::{tests_cfg::*, *};
2102 ///
2103 /// let query = Query::select()
2104 /// .column(Char::Character)
2105 /// .from(Char::Table)
2106 /// .and_where(Expr::col(Char::FontId).eq(5))
2107 /// .lock(LockType::Update)
2108 /// .to_owned();
2109 ///
2110 /// assert_eq!(
2111 /// query.to_string(MysqlQueryBuilder),
2112 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE"#
2113 /// );
2114 /// assert_eq!(
2115 /// query.to_string(PostgresQueryBuilder),
2116 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE"#
2117 /// );
2118 /// assert_eq!(
2119 /// query.to_string(SqliteQueryBuilder),
2120 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2121 /// );
2122 /// ```
2123 pub fn lock(&mut self, r#type: LockType) -> &mut Self {
2124 self.lock = Some(LockClause {
2125 r#type,
2126 tables: Vec::new(),
2127 behavior: None,
2128 });
2129 self
2130 }
2131
2132 /// Row locking with tables (if supported).
2133 ///
2134 /// # Examples
2135 ///
2136 /// ```
2137 /// use sea_query::{tests_cfg::*, *};
2138 ///
2139 /// let query = Query::select()
2140 /// .column(Char::Character)
2141 /// .from(Char::Table)
2142 /// .and_where(Expr::col(Char::FontId).eq(5))
2143 /// .lock_with_tables(LockType::Update, [Glyph::Table])
2144 /// .to_owned();
2145 ///
2146 /// assert_eq!(
2147 /// query.to_string(MysqlQueryBuilder),
2148 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE OF `glyph`"#
2149 /// );
2150 /// assert_eq!(
2151 /// query.to_string(PostgresQueryBuilder),
2152 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE OF "glyph""#
2153 /// );
2154 /// assert_eq!(
2155 /// query.to_string(SqliteQueryBuilder),
2156 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2157 /// );
2158 /// ```
2159 pub fn lock_with_tables<T, I>(&mut self, r#type: LockType, tables: I) -> &mut Self
2160 where
2161 T: IntoTableRef,
2162 I: IntoIterator<Item = T>,
2163 {
2164 self.lock = Some(LockClause {
2165 r#type,
2166 tables: tables.into_iter().map(|t| t.into_table_ref()).collect(),
2167 behavior: None,
2168 });
2169 self
2170 }
2171
2172 /// Row locking with behavior (if supported).
2173 ///
2174 /// # Examples
2175 ///
2176 /// ```
2177 /// use sea_query::{tests_cfg::*, *};
2178 ///
2179 /// let query = Query::select()
2180 /// .column(Char::Character)
2181 /// .from(Char::Table)
2182 /// .and_where(Expr::col(Char::FontId).eq(5))
2183 /// .lock_with_behavior(LockType::Update, LockBehavior::Nowait)
2184 /// .to_owned();
2185 ///
2186 /// assert_eq!(
2187 /// query.to_string(MysqlQueryBuilder),
2188 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE NOWAIT"#
2189 /// );
2190 /// assert_eq!(
2191 /// query.to_string(PostgresQueryBuilder),
2192 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE NOWAIT"#
2193 /// );
2194 /// assert_eq!(
2195 /// query.to_string(SqliteQueryBuilder),
2196 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2197 /// );
2198 /// ```
2199 pub fn lock_with_behavior(&mut self, r#type: LockType, behavior: LockBehavior) -> &mut Self {
2200 self.lock = Some(LockClause {
2201 r#type,
2202 tables: Vec::new(),
2203 behavior: Some(behavior),
2204 });
2205 self
2206 }
2207
2208 /// Row locking with tables and behavior (if supported).
2209 ///
2210 /// # Examples
2211 ///
2212 /// ```
2213 /// use sea_query::{tests_cfg::*, *};
2214 ///
2215 /// let query = Query::select()
2216 /// .column(Char::Character)
2217 /// .from(Char::Table)
2218 /// .and_where(Expr::col(Char::FontId).eq(5))
2219 /// .lock_with_tables_behavior(LockType::Update, [Glyph::Table], LockBehavior::Nowait)
2220 /// .to_owned();
2221 ///
2222 /// assert_eq!(
2223 /// query.to_string(MysqlQueryBuilder),
2224 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE OF `glyph` NOWAIT"#
2225 /// );
2226 /// assert_eq!(
2227 /// query.to_string(PostgresQueryBuilder),
2228 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE OF "glyph" NOWAIT"#
2229 /// );
2230 /// assert_eq!(
2231 /// query.to_string(SqliteQueryBuilder),
2232 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2233 /// );
2234 /// ```
2235 pub fn lock_with_tables_behavior<T, I>(
2236 &mut self,
2237 r#type: LockType,
2238 tables: I,
2239 behavior: LockBehavior,
2240 ) -> &mut Self
2241 where
2242 T: IntoTableRef,
2243 I: IntoIterator<Item = T>,
2244 {
2245 self.lock = Some(LockClause {
2246 r#type,
2247 tables: tables.into_iter().map(|t| t.into_table_ref()).collect(),
2248 behavior: Some(behavior),
2249 });
2250 self
2251 }
2252
2253 /// Shared row locking (if supported).
2254 ///
2255 /// # Examples
2256 ///
2257 /// ```
2258 /// use sea_query::{tests_cfg::*, *};
2259 ///
2260 /// let query = Query::select()
2261 /// .column(Char::Character)
2262 /// .from(Char::Table)
2263 /// .and_where(Expr::col(Char::FontId).eq(5))
2264 /// .lock_shared()
2265 /// .to_owned();
2266 ///
2267 /// assert_eq!(
2268 /// query.to_string(MysqlQueryBuilder),
2269 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 LOCK IN SHARE MODE"#
2270 /// );
2271 /// assert_eq!(
2272 /// query.to_string(PostgresQueryBuilder),
2273 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR SHARE"#
2274 /// );
2275 /// assert_eq!(
2276 /// query.to_string(SqliteQueryBuilder),
2277 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2278 /// );
2279 /// ```
2280 pub fn lock_shared(&mut self) -> &mut Self {
2281 self.lock(LockType::Share)
2282 }
2283
2284 /// Exclusive row locking (if supported).
2285 ///
2286 /// # Examples
2287 ///
2288 /// ```
2289 /// use sea_query::{tests_cfg::*, *};
2290 ///
2291 /// let query = Query::select()
2292 /// .column(Char::Character)
2293 /// .from(Char::Table)
2294 /// .and_where(Expr::col(Char::FontId).eq(5))
2295 /// .lock_exclusive()
2296 /// .to_owned();
2297 ///
2298 /// assert_eq!(
2299 /// query.to_string(MysqlQueryBuilder),
2300 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE"#
2301 /// );
2302 /// assert_eq!(
2303 /// query.to_string(PostgresQueryBuilder),
2304 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE"#
2305 /// );
2306 /// assert_eq!(
2307 /// query.to_string(SqliteQueryBuilder),
2308 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
2309 /// );
2310 /// ```
2311 pub fn lock_exclusive(&mut self) -> &mut Self {
2312 self.lock(LockType::Update)
2313 }
2314
2315 /// Union with another SelectStatement that must have the same selected fields.
2316 ///
2317 /// # Examples
2318 ///
2319 /// ```
2320 /// use sea_query::{audit::*, tests_cfg::*, *};
2321 ///
2322 /// let query = Query::select()
2323 /// .column(Char::Character)
2324 /// .from(Char::Table)
2325 /// .and_where(Expr::col(Char::FontId).eq(5))
2326 /// .union(UnionType::All, Query::select()
2327 /// .column(Char::Character)
2328 /// .from(Char::Table)
2329 /// .and_where(Expr::col(Char::FontId).eq(4))
2330 /// .to_owned()
2331 /// )
2332 /// .to_owned();
2333 ///
2334 /// assert_eq!(
2335 /// query.to_string(MysqlQueryBuilder),
2336 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 UNION ALL (SELECT `character` FROM `character` WHERE `font_id` = 4)"#
2337 /// );
2338 /// assert_eq!(
2339 /// query.to_string(PostgresQueryBuilder),
2340 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL (SELECT "character" FROM "character" WHERE "font_id" = 4)"#
2341 /// );
2342 /// assert_eq!(
2343 /// query.to_string(SqliteQueryBuilder),
2344 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL SELECT "character" FROM "character" WHERE "font_id" = 4"#
2345 /// );
2346 /// assert_eq!(
2347 /// query.audit().unwrap().selected_tables(),
2348 /// [Char::Table.into_iden()]
2349 /// );
2350 /// ```
2351 pub fn union(&mut self, union_type: UnionType, query: SelectStatement) -> &mut Self {
2352 self.unions.push((union_type, query));
2353 self
2354 }
2355
2356 /// Union with multiple SelectStatement that must have the same selected fields.
2357 ///
2358 /// # Examples
2359 ///
2360 /// ```
2361 /// use sea_query::{audit::*, tests_cfg::*, *};
2362 ///
2363 /// let query = Query::select()
2364 /// .column(Char::Character)
2365 /// .from(Char::Table)
2366 /// .and_where(Expr::col(Char::FontId).eq(5))
2367 /// .unions([
2368 /// (UnionType::All, Query::select()
2369 /// .column(Char::Character)
2370 /// .from(Char::Table)
2371 /// .and_where(Expr::col(Char::FontId).eq(4))
2372 /// .to_owned()),
2373 /// (UnionType::Distinct, Query::select()
2374 /// .column(Glyph::Image)
2375 /// .from(Glyph::Table)
2376 /// .to_owned()),
2377 /// ])
2378 /// .to_owned();
2379 ///
2380 /// assert_eq!(
2381 /// query.to_string(MysqlQueryBuilder),
2382 /// r#"SELECT `character` FROM `character` WHERE `font_id` = 5 UNION ALL (SELECT `character` FROM `character` WHERE `font_id` = 4) UNION (SELECT `image` FROM `glyph`)"#
2383 /// );
2384 /// assert_eq!(
2385 /// query.to_string(PostgresQueryBuilder),
2386 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL (SELECT "character" FROM "character" WHERE "font_id" = 4) UNION (SELECT "image" FROM "glyph")"#
2387 /// );
2388 /// assert_eq!(
2389 /// query.to_string(SqliteQueryBuilder),
2390 /// r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL SELECT "character" FROM "character" WHERE "font_id" = 4 UNION SELECT "image" FROM "glyph""#
2391 /// );
2392 /// assert_eq!(
2393 /// query.audit().unwrap().selected_tables(),
2394 /// [Char::Table.into_iden(), Glyph::Table.into_iden()]
2395 /// );
2396 /// ```
2397 pub fn unions<T: IntoIterator<Item = (UnionType, SelectStatement)>>(
2398 &mut self,
2399 unions: T,
2400 ) -> &mut Self {
2401 self.unions.extend(unions);
2402 self
2403 }
2404
2405 /// Create a [WithQuery] by specifying a [WithClause] to execute this query with.
2406 ///
2407 /// # Examples
2408 ///
2409 /// ```
2410 /// use sea_query::{*, IntoCondition, IntoIden, audit::AuditTrait, tests_cfg::*};
2411 ///
2412 /// let base_query = SelectStatement::new()
2413 /// .column("id")
2414 /// .expr(1i32)
2415 /// .column("next")
2416 /// .column("value")
2417 /// .from(Task::Table)
2418 /// .to_owned();
2419 ///
2420 /// let cte_referencing = SelectStatement::new()
2421 /// .column("id")
2422 /// .expr(Expr::col("depth").add(1i32))
2423 /// .column("next")
2424 /// .column("value")
2425 /// .from(Task::Table)
2426 /// .join(
2427 /// JoinType::InnerJoin,
2428 /// "cte_traversal",
2429 /// Expr::col(("cte_traversal", "next")).equals((Task::Table, "id"))
2430 /// )
2431 /// .to_owned();
2432 ///
2433 /// let common_table_expression = CommonTableExpression::new()
2434 /// .query(
2435 /// base_query.clone().union(UnionType::All, cte_referencing).to_owned()
2436 /// )
2437 /// .columns(["id", "depth", "next", "value"])
2438 /// .table_name("cte_traversal")
2439 /// .to_owned();
2440 ///
2441 /// let select = SelectStatement::new()
2442 /// .column(Asterisk)
2443 /// .from("cte_traversal")
2444 /// .to_owned();
2445 ///
2446 /// let with_clause = WithClause::new()
2447 /// .recursive(true)
2448 /// .cte(common_table_expression)
2449 /// .cycle(Cycle::new_from_expr_set_using(Expr::Column("id".into_column_ref()), "looped", "traversal_path"))
2450 /// .to_owned();
2451 ///
2452 /// let query = select.with(with_clause).to_owned();
2453 ///
2454 /// assert_eq!(
2455 /// query.to_string(MysqlQueryBuilder),
2456 /// r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, 1, `next`, `value` FROM `task` UNION ALL (SELECT `id`, `depth` + 1, `next`, `value` FROM `task` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `task`.`id`)) SELECT * FROM `cte_traversal`"#
2457 /// );
2458 /// assert_eq!(
2459 /// query.to_string(PostgresQueryBuilder),
2460 /// r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL (SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id")) CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
2461 /// );
2462 /// assert_eq!(
2463 /// query.to_string(SqliteQueryBuilder),
2464 /// r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id") SELECT * FROM "cte_traversal""#
2465 /// );
2466 /// assert_eq!(
2467 /// query.audit().unwrap().selected_tables(),
2468 /// [Task::Table.into_iden()]
2469 /// );
2470 /// ```
2471 pub fn with(self, clause: WithClause) -> WithQuery {
2472 clause.query(self)
2473 }
2474
2475 /// Create a Common Table Expression by specifying a [CommonTableExpression] or [WithClause] to execute this query with.
2476 ///
2477 /// # Examples
2478 ///
2479 /// ```
2480 /// use sea_query::{*, IntoCondition, IntoIden, audit::AuditTrait, tests_cfg::*};
2481 ///
2482 /// let base_query = SelectStatement::new()
2483 /// .column("id")
2484 /// .expr(1i32)
2485 /// .column("next")
2486 /// .column("value")
2487 /// .from(Task::Table)
2488 /// .to_owned();
2489 ///
2490 /// let cte_referencing = SelectStatement::new()
2491 /// .column("id")
2492 /// .expr(Expr::col("depth").add(1i32))
2493 /// .column("next")
2494 /// .column("value")
2495 /// .from(Task::Table)
2496 /// .join(
2497 /// JoinType::InnerJoin,
2498 /// "cte_traversal",
2499 /// Expr::col(("cte_traversal", "next")).equals((Task::Table, "id"))
2500 /// )
2501 /// .to_owned();
2502 ///
2503 /// let common_table_expression = CommonTableExpression::new()
2504 /// .query(
2505 /// base_query.clone().union(UnionType::All, cte_referencing).to_owned()
2506 /// )
2507 /// .columns(["id", "depth", "next", "value"])
2508 /// .table_name("cte_traversal")
2509 /// .to_owned();
2510 ///
2511 /// let with_clause = WithClause::new()
2512 /// .recursive(true)
2513 /// .cte(common_table_expression)
2514 /// .cycle(Cycle::new_from_expr_set_using(Expr::Column("id".into_column_ref()), "looped", "traversal_path"))
2515 /// .to_owned();
2516 ///
2517 /// let query = SelectStatement::new()
2518 /// .column(Asterisk)
2519 /// .from("cte_traversal")
2520 /// .with_cte(with_clause)
2521 /// .to_owned();
2522 ///
2523 /// assert_eq!(
2524 /// query.to_string(MysqlQueryBuilder),
2525 /// r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, 1, `next`, `value` FROM `task` UNION ALL (SELECT `id`, `depth` + 1, `next`, `value` FROM `task` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `task`.`id`)) SELECT * FROM `cte_traversal`"#
2526 /// );
2527 /// assert_eq!(
2528 /// query.to_string(PostgresQueryBuilder),
2529 /// r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL (SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id")) CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
2530 /// );
2531 /// assert_eq!(
2532 /// query.to_string(SqliteQueryBuilder),
2533 /// r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id") SELECT * FROM "cte_traversal""#
2534 /// );
2535 /// assert_eq!(
2536 /// query.audit().unwrap().selected_tables(),
2537 /// [Task::Table.into_iden()]
2538 /// );
2539 /// ```
2540 pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self {
2541 self.with = Some(Box::new(clause.into()));
2542 self
2543 }
2544
2545 /// WINDOW
2546 ///
2547 /// # Examples:
2548 ///
2549 /// ```
2550 /// use sea_query::{tests_cfg::*, *};
2551 ///
2552 /// let query = Query::select()
2553 /// .from(Char::Table)
2554 /// .expr_window_name_as(Expr::col(Char::Character), "w", "C")
2555 /// .window("w", WindowStatement::partition_by(Char::FontSize))
2556 /// .to_owned();
2557 ///
2558 /// assert_eq!(
2559 /// query.to_string(MysqlQueryBuilder),
2560 /// r#"SELECT `character` OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
2561 /// );
2562 /// assert_eq!(
2563 /// query.to_string(PostgresQueryBuilder),
2564 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
2565 /// );
2566 /// assert_eq!(
2567 /// query.to_string(SqliteQueryBuilder),
2568 /// r#"SELECT "character" OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
2569 /// );
2570 /// ```
2571 pub fn window<A>(&mut self, name: A, window: WindowStatement) -> &mut Self
2572 where
2573 A: IntoIden,
2574 {
2575 self.window = Some((name.into_iden(), window));
2576 self
2577 }
2578}
2579
2580#[inherent]
2581impl QueryStatementBuilder for SelectStatement {
2582 pub fn build_collect_any_into(
2583 &self,
2584 query_builder: &impl QueryBuilder,
2585 sql: &mut impl SqlWriter,
2586 ) {
2587 query_builder.prepare_select_statement(self, sql);
2588 }
2589
2590 pub fn build_any(&self, query_builder: &impl QueryBuilder) -> (String, Values);
2591 pub fn build_collect_any(
2592 &self,
2593 query_builder: &impl QueryBuilder,
2594 sql: &mut impl SqlWriter,
2595 ) -> String;
2596}
2597
2598impl From<SelectStatement> for QueryStatement {
2599 fn from(s: SelectStatement) -> Self {
2600 Self::Select(s)
2601 }
2602}
2603
2604impl From<SelectStatement> for SubQueryStatement {
2605 fn from(s: SelectStatement) -> Self {
2606 Self::SelectStatement(s)
2607 }
2608}
2609
2610#[inherent]
2611impl QueryStatementWriter for SelectStatement {
2612 pub fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut impl SqlWriter) {
2613 query_builder.prepare_select_statement(self, sql);
2614 }
2615
2616 pub fn build_collect<T: QueryBuilder>(
2617 &self,
2618 query_builder: T,
2619 sql: &mut impl SqlWriter,
2620 ) -> String;
2621 pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values);
2622 pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String;
2623}
2624
2625#[inherent]
2626impl OrderedStatement for SelectStatement {
2627 pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
2628 self.orders.push(order);
2629 self
2630 }
2631
2632 pub fn clear_order_by(&mut self) -> &mut Self {
2633 self.orders = Vec::new();
2634 self
2635 }
2636
2637 pub fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self
2638 where
2639 T: IntoColumnRef;
2640
2641 pub fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self;
2642 pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
2643 where
2644 T: Into<Cow<'static, str>>,
2645 I: IntoIterator<Item = (T, Order)>;
2646 pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self
2647 where
2648 T: IntoColumnRef,
2649 I: IntoIterator<Item = (T, Order)>;
2650 pub fn order_by_with_nulls<T>(
2651 &mut self,
2652 col: T,
2653 order: Order,
2654 nulls: NullOrdering,
2655 ) -> &mut Self
2656 where
2657 T: IntoColumnRef;
2658 pub fn order_by_expr_with_nulls(
2659 &mut self,
2660 expr: Expr,
2661 order: Order,
2662 nulls: NullOrdering,
2663 ) -> &mut Self;
2664 pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
2665 where
2666 T: Into<Cow<'static, str>>,
2667 I: IntoIterator<Item = (T, Order, NullOrdering)>;
2668 pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
2669 where
2670 T: IntoColumnRef,
2671 I: IntoIterator<Item = (T, Order, NullOrdering)>;
2672}
2673
2674#[inherent]
2675impl ConditionalStatement for SelectStatement {
2676 pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
2677 self.r#where.add_and_or(condition);
2678 self
2679 }
2680
2681 pub fn cond_where<C>(&mut self, condition: C) -> &mut Self
2682 where
2683 C: IntoCondition,
2684 {
2685 self.r#where.add_condition(condition.into_condition());
2686 self
2687 }
2688
2689 pub fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self;
2690 pub fn and_where(&mut self, other: Expr) -> &mut Self;
2691}