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