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