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