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