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