sea_query/expr/enum.rs
1use std::borrow::Cow;
2
3use crate::*;
4
5/// An arbitrary, dynamically-typed SQL expression.
6///
7/// It can be used in select fields, where clauses and many other places.
8///
9/// More concreterly, under the hood [`Expr`]s can be:
10///
11/// - Rust values
12/// - SQL identifiers
13/// - SQL function calls
14/// - various operators and sub-queries
15///
16/// If something is not supported here, look into [`BinOper::Custom`],
17/// [`Func::cust`], or [`Expr::cust*`][`Expr::cust_with_values`] as a
18/// workaround, and consider reporting your issue.
19#[derive(Debug, Clone, PartialEq)]
20#[non_exhaustive]
21pub enum Expr {
22 Column(ColumnRef),
23 Tuple(Vec<Expr>),
24 Unary(UnOper, Box<Expr>),
25 FunctionCall(FunctionCall),
26 Binary(Box<Expr>, BinOper, Box<Expr>),
27 SubQuery(Option<SubQueryOper>, Box<SubQueryStatement>),
28 Value(Value),
29 Values(Vec<Value>),
30 Custom(Cow<'static, str>),
31 CustomWithExpr(Cow<'static, str>, Vec<Expr>),
32 Keyword(Keyword),
33 AsEnum(DynIden, Box<Expr>),
34 Case(Box<CaseStatement>),
35 Constant(Value),
36 TypeName(TypeRef),
37}
38
39impl Expr {
40 #[deprecated(since = "0.29.0", note = "Please use the [`Asterisk`]")]
41 pub fn asterisk() -> Self {
42 Self::col(Asterisk)
43 }
44
45 /// Express the target column without table prefix.
46 ///
47 /// # Examples
48 ///
49 /// ```
50 /// use sea_query::{tests_cfg::*, *};
51 ///
52 /// let query = Query::select()
53 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
54 /// .from(Char::Table)
55 /// .and_where(Expr::col(Char::SizeW).eq(1))
56 /// .to_owned();
57 ///
58 /// assert_eq!(
59 /// query.to_string(MysqlQueryBuilder),
60 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE `size_w` = 1"#
61 /// );
62 /// assert_eq!(
63 /// query.to_string(PostgresQueryBuilder),
64 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "size_w" = 1"#
65 /// );
66 /// assert_eq!(
67 /// query.to_string(SqliteQueryBuilder),
68 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "size_w" = 1"#
69 /// );
70 /// ```
71 ///
72 /// ```
73 /// use sea_query::{tests_cfg::*, *};
74 ///
75 /// let query = Query::select()
76 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
77 /// .from(Char::Table)
78 /// .and_where(Expr::col((Char::Table, Char::SizeW)).eq(1))
79 /// .to_owned();
80 ///
81 /// assert_eq!(
82 /// query.to_string(MysqlQueryBuilder),
83 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE `character`.`size_w` = 1"#
84 /// );
85 /// assert_eq!(
86 /// query.to_string(PostgresQueryBuilder),
87 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "character"."size_w" = 1"#
88 /// );
89 /// assert_eq!(
90 /// query.to_string(SqliteQueryBuilder),
91 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "character"."size_w" = 1"#
92 /// );
93 /// ```
94 pub fn col<T>(n: T) -> Self
95 where
96 T: IntoColumnRef,
97 {
98 Self::Column(n.into_column_ref())
99 }
100
101 /// Express the target column without table prefix, returning a [`Expr`].
102 ///
103 /// # Examples
104 ///
105 /// ```
106 /// use sea_query::{tests_cfg::*, *};
107 ///
108 /// let query = Query::select()
109 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
110 /// .from(Char::Table)
111 /// .and_where(Expr::column(Char::SizeW).eq(1))
112 /// .to_owned();
113 ///
114 /// assert_eq!(
115 /// query.to_string(MysqlQueryBuilder),
116 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE `size_w` = 1"#
117 /// );
118 /// assert_eq!(
119 /// query.to_string(PostgresQueryBuilder),
120 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "size_w" = 1"#
121 /// );
122 /// assert_eq!(
123 /// query.to_string(SqliteQueryBuilder),
124 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "size_w" = 1"#
125 /// );
126 /// ```
127 ///
128 /// ```
129 /// use sea_query::{tests_cfg::*, *};
130 ///
131 /// let query = Query::select()
132 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
133 /// .from(Char::Table)
134 /// .and_where(Expr::column((Char::Table, Char::SizeW)).eq(1))
135 /// .to_owned();
136 ///
137 /// assert_eq!(
138 /// query.to_string(MysqlQueryBuilder),
139 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE `character`.`size_w` = 1"#
140 /// );
141 /// assert_eq!(
142 /// query.to_string(PostgresQueryBuilder),
143 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "character"."size_w" = 1"#
144 /// );
145 /// assert_eq!(
146 /// query.to_string(SqliteQueryBuilder),
147 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "character"."size_w" = 1"#
148 /// );
149 /// ```
150 pub fn column<T>(n: T) -> Self
151 where
152 T: IntoColumnRef,
153 {
154 Self::Column(n.into_column_ref())
155 }
156
157 /// Wraps tuple of `Expr`, can be used for tuple comparison
158 ///
159 /// # Examples
160 ///
161 /// ```
162 /// use sea_query::{tests_cfg::*, *};
163 ///
164 /// let query = Query::select()
165 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
166 /// .from(Char::Table)
167 /// .and_where(
168 /// Expr::tuple([Expr::col(Char::SizeW).into(), Expr::value(100)])
169 /// .lt(Expr::tuple([Expr::value(500), Expr::value(100)])))
170 /// .to_owned();
171 ///
172 /// assert_eq!(
173 /// query.to_string(MysqlQueryBuilder),
174 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE (`size_w`, 100) < (500, 100)"#
175 /// );
176 /// assert_eq!(
177 /// query.to_string(PostgresQueryBuilder),
178 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE ("size_w", 100) < (500, 100)"#
179 /// );
180 /// assert_eq!(
181 /// query.to_string(SqliteQueryBuilder),
182 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE ("size_w", 100) < (500, 100)"#
183 /// );
184 /// ```
185 pub fn tuple<I>(n: I) -> Self
186 where
187 I: IntoIterator<Item = Self>,
188 {
189 Self::Tuple(n.into_iter().collect::<Vec<Self>>())
190 }
191
192 #[deprecated(since = "0.29.0", note = "Please use the [`Asterisk`]")]
193 pub fn table_asterisk<T>(t: T) -> Self
194 where
195 T: IntoIden,
196 {
197 Self::col((t.into_iden(), Asterisk))
198 }
199
200 /// Express a [`Value`], returning a [`Expr`].
201 ///
202 /// # Examples
203 ///
204 /// ```
205 /// use sea_query::{tests_cfg::*, *};
206 ///
207 /// let query = Query::select()
208 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
209 /// .from(Char::Table)
210 /// .and_where(Expr::val(1).into())
211 /// .and_where(Expr::val(2.5).into())
212 /// .and_where(Expr::val("3").into())
213 /// .to_owned();
214 ///
215 /// assert_eq!(
216 /// query.to_string(MysqlQueryBuilder),
217 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE 1 AND 2.5 AND '3'"#
218 /// );
219 /// assert_eq!(
220 /// query.to_string(PostgresQueryBuilder),
221 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE 1 AND 2.5 AND '3'"#
222 /// );
223 /// assert_eq!(
224 /// query.to_string(SqliteQueryBuilder),
225 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE 1 AND 2.5 AND '3'"#
226 /// );
227 /// ```
228 pub fn val<V>(v: V) -> Self
229 where
230 V: Into<Value>,
231 {
232 Self::from(v)
233 }
234
235 /// Wrap an expression to perform some operation on it later.
236 ///
237 /// Since `sea_query` 0.32.0 (`sea_orm` 1.1.1), **this is not necessary** in most cases!
238 ///
239 /// Some SQL operations used to be defined only as inherent methods on [`Expr`].
240 /// Thus, to use them, you needed to manually convert from other types to [`Expr`].
241 /// But now these operations are also defined as [`ExprTrait`] methods
242 /// that can be called directly on any expression type,
243 ///
244 /// # Examples
245 ///
246 /// ```
247 /// use sea_query::{tests_cfg::*, *};
248 ///
249 /// let query = Query::select()
250 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
251 /// .from(Char::Table)
252 /// // This is the old style, when `Expr::expr` was necessary:
253 /// .and_where(Expr::expr(Expr::col(Char::SizeW).if_null(0)).gt(2))
254 /// .to_owned();
255 ///
256 /// // But since 0.32.0, this compiles too:
257 /// let _ = Expr::col(Char::SizeW).if_null(0).gt(2);
258 ///
259 /// assert_eq!(
260 /// query.to_string(MysqlQueryBuilder),
261 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE IFNULL(`size_w`, 0) > 2"#
262 /// );
263 /// assert_eq!(
264 /// query.to_string(PostgresQueryBuilder),
265 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE COALESCE("size_w", 0) > 2"#
266 /// );
267 /// assert_eq!(
268 /// query.to_string(SqliteQueryBuilder),
269 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE IFNULL("size_w", 0) > 2"#
270 /// );
271 /// ```
272 ///
273 /// ```
274 /// use sea_query::{tests_cfg::*, *};
275 ///
276 /// let query = Query::select()
277 /// .column(Char::Character)
278 /// .from(Char::Table)
279 /// // This is the old style, when `Expr::expr` was necessary:
280 /// .and_where(Expr::expr(Func::lower(Expr::col(Char::Character))).is_in(["a", "b"]))
281 /// .to_owned();
282 ///
283 /// // But since 0.32.0, this compiles too:
284 /// let _ = Func::lower(Expr::col(Char::Character)).is_in(["a", "b"]);
285 ///
286 /// assert_eq!(
287 /// query.to_string(MysqlQueryBuilder),
288 /// r#"SELECT `character` FROM `character` WHERE LOWER(`character`) IN ('a', 'b')"#
289 /// );
290 /// assert_eq!(
291 /// query.to_string(PostgresQueryBuilder),
292 /// r#"SELECT "character" FROM "character" WHERE LOWER("character") IN ('a', 'b')"#
293 /// );
294 /// assert_eq!(
295 /// query.to_string(SqliteQueryBuilder),
296 /// r#"SELECT "character" FROM "character" WHERE LOWER("character") IN ('a', 'b')"#
297 /// );
298 /// ```
299 #[allow(clippy::self_named_constructors)]
300 pub fn expr<T>(expr: T) -> Self
301 where
302 T: Into<Self>,
303 {
304 expr.into()
305 }
306
307 /// Express a [`Value`], returning a [`Expr`].
308 ///
309 /// # Examples
310 ///
311 /// ```
312 /// use sea_query::{tests_cfg::*, *};
313 ///
314 /// let query = Query::select()
315 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
316 /// .from(Char::Table)
317 /// .and_where(Expr::value(1))
318 /// .and_where(Expr::value(2.5))
319 /// .and_where(Expr::value("3"))
320 /// .to_owned();
321 ///
322 /// assert_eq!(
323 /// query.to_string(MysqlQueryBuilder),
324 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE 1 AND 2.5 AND '3'"#
325 /// );
326 /// assert_eq!(
327 /// query.to_string(PostgresQueryBuilder),
328 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE 1 AND 2.5 AND '3'"#
329 /// );
330 /// assert_eq!(
331 /// query.to_string(SqliteQueryBuilder),
332 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE 1 AND 2.5 AND '3'"#
333 /// );
334 /// ```
335 pub fn value<V>(v: V) -> Self
336 where
337 V: Into<Self>,
338 {
339 v.into()
340 }
341
342 /// Express any custom expression in [`&str`].
343 ///
344 /// # Examples
345 ///
346 /// ```
347 /// use sea_query::{tests_cfg::*, *};
348 ///
349 /// let query = Query::select()
350 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
351 /// .from(Char::Table)
352 /// .and_where(Expr::cust("1 = 1"))
353 /// .to_owned();
354 ///
355 /// assert_eq!(
356 /// query.to_string(MysqlQueryBuilder),
357 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE 1 = 1"#
358 /// );
359 /// assert_eq!(
360 /// query.to_string(PostgresQueryBuilder),
361 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE 1 = 1"#
362 /// );
363 /// assert_eq!(
364 /// query.to_string(SqliteQueryBuilder),
365 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE 1 = 1"#
366 /// );
367 /// ```
368 pub fn cust<T>(s: T) -> Self
369 where
370 T: Into<Cow<'static, str>>,
371 {
372 Self::Custom(s.into())
373 }
374
375 /// Express any custom expression with [`Value`]. Use this if your expression needs variables.
376 ///
377 /// # Examples
378 ///
379 /// ```
380 /// use sea_query::{tests_cfg::*, *};
381 ///
382 /// let query = Query::select()
383 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
384 /// .from(Char::Table)
385 /// .and_where(Expr::col(Char::Id).eq(1))
386 /// .and_where(Expr::cust_with_values("6 = ? * ?", [2, 3]))
387 /// .to_owned();
388 ///
389 /// assert_eq!(
390 /// query.to_string(MysqlQueryBuilder),
391 /// r#"SELECT `character`, `size_w`, `size_h` FROM `character` WHERE `id` = 1 AND (6 = 2 * 3)"#
392 /// );
393 /// assert_eq!(
394 /// query.to_string(SqliteQueryBuilder),
395 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "id" = 1 AND (6 = 2 * 3)"#
396 /// );
397 /// ```
398 /// ```
399 /// use sea_query::{tests_cfg::*, *};
400 ///
401 /// let query = Query::select()
402 /// .columns([Char::Character, Char::SizeW, Char::SizeH])
403 /// .from(Char::Table)
404 /// .and_where(Expr::col(Char::Id).eq(1))
405 /// .and_where(Expr::cust_with_values("6 = $2 * $1", [3, 2]))
406 /// .to_owned();
407 ///
408 /// assert_eq!(
409 /// query.to_string(PostgresQueryBuilder),
410 /// r#"SELECT "character", "size_w", "size_h" FROM "character" WHERE "id" = 1 AND (6 = 2 * 3)"#
411 /// );
412 /// ```
413 /// ```
414 /// use sea_query::{tests_cfg::*, *};
415 ///
416 /// let query = Query::select()
417 /// .expr(Expr::cust_with_values("6 = ? * ?", [2, 3]))
418 /// .to_owned();
419 ///
420 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT 6 = 2 * 3"#);
421 /// assert_eq!(query.to_string(SqliteQueryBuilder), r#"SELECT 6 = 2 * 3"#);
422 /// ```
423 /// Postgres only: use `$$` to escape `$`
424 /// ```
425 /// use sea_query::{tests_cfg::*, *};
426 ///
427 /// let query = Query::select()
428 /// .expr(Expr::cust_with_values("$1 $$ $2", ["a", "b"]))
429 /// .to_owned();
430 ///
431 /// assert_eq!(query.to_string(PostgresQueryBuilder), r#"SELECT 'a' $ 'b'"#);
432 /// ```
433 /// ```
434 /// use sea_query::{tests_cfg::*, *};
435 ///
436 /// let query = Query::select()
437 /// .expr(Expr::cust_with_values("data @? ($1::JSONPATH)", ["hello"]))
438 /// .to_owned();
439 ///
440 /// assert_eq!(
441 /// query.to_string(PostgresQueryBuilder),
442 /// r#"SELECT data @? ('hello'::JSONPATH)"#
443 /// );
444 /// ```
445 pub fn cust_with_values<T, V, I>(s: T, v: I) -> Self
446 where
447 T: Into<Cow<'static, str>>,
448 V: Into<Value>,
449 I: IntoIterator<Item = V>,
450 {
451 Self::CustomWithExpr(
452 s.into(),
453 v.into_iter()
454 .map(|v| Into::<Value>::into(v).into())
455 .collect(),
456 )
457 }
458
459 /// Express any custom expression with [`Expr`]. Use this if your expression needs other expression.
460 ///
461 /// # Examples
462 ///
463 /// ```
464 /// use sea_query::{tests_cfg::*, *};
465 ///
466 /// let query = Query::select()
467 /// .expr(Expr::val(1).add(2))
468 /// .expr(Expr::cust_with_expr("data @? ($1::JSONPATH)", "hello"))
469 /// .to_owned();
470 /// let (sql, values) = query.build(PostgresQueryBuilder);
471 ///
472 /// assert_eq!(sql, r#"SELECT $1 + $2, data @? ($3::JSONPATH)"#);
473 /// assert_eq!(
474 /// values,
475 /// Values(vec![1i32.into(), 2i32.into(), "hello".into()])
476 /// );
477 /// ```
478 /// ```
479 /// use sea_query::{tests_cfg::*, *};
480 ///
481 /// let query = Query::select()
482 /// .expr(Expr::cust_with_expr(
483 /// "json_agg(DISTINCT $1)",
484 /// Expr::col(Char::Character),
485 /// ))
486 /// .to_owned();
487 ///
488 /// assert_eq!(
489 /// query.to_string(PostgresQueryBuilder),
490 /// r#"SELECT json_agg(DISTINCT "character")"#
491 /// );
492 /// ```
493 pub fn cust_with_expr<T, E>(s: T, expr: E) -> Self
494 where
495 T: Into<Cow<'static, str>>,
496 E: Into<Self>,
497 {
498 Self::CustomWithExpr(s.into(), vec![expr.into()])
499 }
500
501 /// Express any custom expression with [`Expr`]. Use this if your expression needs other expressions.
502 pub fn cust_with_exprs<T, I>(s: T, v: I) -> Self
503 where
504 T: Into<Cow<'static, str>>,
505 I: IntoIterator<Item = Expr>,
506 {
507 Self::CustomWithExpr(s.into(), v.into_iter().collect())
508 }
509
510 /// Express a `MAX` function.
511 ///
512 /// # Examples
513 ///
514 /// ```
515 /// use sea_query::{tests_cfg::*, *};
516 ///
517 /// let query = Query::select()
518 /// .expr(Expr::col((Char::Table, Char::SizeW)).max())
519 /// .from(Char::Table)
520 /// .to_owned();
521 ///
522 /// assert_eq!(
523 /// query.to_string(MysqlQueryBuilder),
524 /// r#"SELECT MAX(`character`.`size_w`) FROM `character`"#
525 /// );
526 /// assert_eq!(
527 /// query.to_string(PostgresQueryBuilder),
528 /// r#"SELECT MAX("character"."size_w") FROM "character""#
529 /// );
530 /// assert_eq!(
531 /// query.to_string(SqliteQueryBuilder),
532 /// r#"SELECT MAX("character"."size_w") FROM "character""#
533 /// );
534 /// ```
535 pub fn max(self) -> Self {
536 Func::max(self).into()
537 }
538
539 /// Express a `MIN` function.
540 ///
541 /// # Examples
542 ///
543 /// ```
544 /// use sea_query::{tests_cfg::*, *};
545 ///
546 /// let query = Query::select()
547 /// .expr(Expr::col((Char::Table, Char::SizeW)).min())
548 /// .from(Char::Table)
549 /// .to_owned();
550 ///
551 /// assert_eq!(
552 /// query.to_string(MysqlQueryBuilder),
553 /// r#"SELECT MIN(`character`.`size_w`) FROM `character`"#
554 /// );
555 /// assert_eq!(
556 /// query.to_string(PostgresQueryBuilder),
557 /// r#"SELECT MIN("character"."size_w") FROM "character""#
558 /// );
559 /// assert_eq!(
560 /// query.to_string(SqliteQueryBuilder),
561 /// r#"SELECT MIN("character"."size_w") FROM "character""#
562 /// );
563 /// ```
564 pub fn min(self) -> Self {
565 Func::min(self).into()
566 }
567
568 /// Express a `SUM` function.
569 ///
570 /// # Examples
571 ///
572 /// ```
573 /// use sea_query::{tests_cfg::*, *};
574 ///
575 /// let query = Query::select()
576 /// .expr(Expr::col((Char::Table, Char::SizeW)).sum())
577 /// .from(Char::Table)
578 /// .to_owned();
579 ///
580 /// assert_eq!(
581 /// query.to_string(MysqlQueryBuilder),
582 /// r#"SELECT SUM(`character`.`size_w`) FROM `character`"#
583 /// );
584 /// assert_eq!(
585 /// query.to_string(PostgresQueryBuilder),
586 /// r#"SELECT SUM("character"."size_w") FROM "character""#
587 /// );
588 /// assert_eq!(
589 /// query.to_string(SqliteQueryBuilder),
590 /// r#"SELECT SUM("character"."size_w") FROM "character""#
591 /// );
592 /// ```
593 pub fn sum(self) -> Self {
594 Func::sum(self).into()
595 }
596
597 /// Express a `COUNT` function.
598 ///
599 /// # Examples
600 ///
601 /// ```
602 /// use sea_query::{tests_cfg::*, *};
603 ///
604 /// let query = Query::select()
605 /// .expr(Expr::col((Char::Table, Char::SizeW)).count())
606 /// .from(Char::Table)
607 /// .to_owned();
608 ///
609 /// assert_eq!(
610 /// query.to_string(MysqlQueryBuilder),
611 /// r#"SELECT COUNT(`character`.`size_w`) FROM `character`"#
612 /// );
613 /// assert_eq!(
614 /// query.to_string(PostgresQueryBuilder),
615 /// r#"SELECT COUNT("character"."size_w") FROM "character""#
616 /// );
617 /// assert_eq!(
618 /// query.to_string(SqliteQueryBuilder),
619 /// r#"SELECT COUNT("character"."size_w") FROM "character""#
620 /// );
621 /// ```
622 pub fn count(self) -> Self {
623 Func::count(self).into()
624 }
625
626 /// Express a `COUNT` function with the DISTINCT modifier.
627 ///
628 /// # Examples
629 ///
630 /// ```
631 /// use sea_query::{tests_cfg::*, *};
632 ///
633 /// let query = Query::select()
634 /// .expr(Expr::col((Char::Table, Char::SizeW)).count_distinct())
635 /// .from(Char::Table)
636 /// .to_owned();
637 ///
638 /// assert_eq!(
639 /// query.to_string(MysqlQueryBuilder),
640 /// r#"SELECT COUNT(DISTINCT `character`.`size_w`) FROM `character`"#
641 /// );
642 /// assert_eq!(
643 /// query.to_string(PostgresQueryBuilder),
644 /// r#"SELECT COUNT(DISTINCT "character"."size_w") FROM "character""#
645 /// );
646 /// assert_eq!(
647 /// query.to_string(SqliteQueryBuilder),
648 /// r#"SELECT COUNT(DISTINCT "character"."size_w") FROM "character""#
649 /// );
650 /// ```
651 pub fn count_distinct(self) -> Self {
652 Func::count_distinct(self).into()
653 }
654
655 /// Express a `IF NULL` function.
656 ///
657 /// # Examples
658 ///
659 /// ```
660 /// use sea_query::{tests_cfg::*, *};
661 ///
662 /// let query = Query::select()
663 /// .expr(Expr::col((Char::Table, Char::SizeW)).if_null(0))
664 /// .from(Char::Table)
665 /// .to_owned();
666 ///
667 /// assert_eq!(
668 /// query.to_string(MysqlQueryBuilder),
669 /// r#"SELECT IFNULL(`character`.`size_w`, 0) FROM `character`"#
670 /// );
671 /// assert_eq!(
672 /// query.to_string(PostgresQueryBuilder),
673 /// r#"SELECT COALESCE("character"."size_w", 0) FROM "character""#
674 /// );
675 /// assert_eq!(
676 /// query.to_string(SqliteQueryBuilder),
677 /// r#"SELECT IFNULL("character"."size_w", 0) FROM "character""#
678 /// );
679 /// ```
680 pub fn if_null<V>(self, v: V) -> Self
681 where
682 V: Into<Self>,
683 {
684 Func::if_null(self, v).into()
685 }
686
687 /// Express a `EXISTS` sub-query expression.
688 ///
689 /// # Examples
690 ///
691 /// ```
692 /// use sea_query::{*, tests_cfg::*};
693 ///
694 /// let query = Query::select()
695 /// .expr_as(Expr::exists(Query::select().column(Char::Id).from(Char::Table).take()), "character_exists")
696 /// .expr_as(Expr::exists(Query::select().column(Glyph::Id).from(Glyph::Table).take()), "glyph_exists")
697 /// .to_owned();
698 ///
699 /// assert_eq!(
700 /// query.to_string(MysqlQueryBuilder),
701 /// r#"SELECT EXISTS(SELECT `id` FROM `character`) AS `character_exists`, EXISTS(SELECT `id` FROM `glyph`) AS `glyph_exists`"#
702 /// );
703 /// assert_eq!(
704 /// query.to_string(PostgresQueryBuilder),
705 /// r#"SELECT EXISTS(SELECT "id" FROM "character") AS "character_exists", EXISTS(SELECT "id" FROM "glyph") AS "glyph_exists""#
706 /// );
707 /// assert_eq!(
708 /// query.to_string(SqliteQueryBuilder),
709 /// r#"SELECT EXISTS(SELECT "id" FROM "character") AS "character_exists", EXISTS(SELECT "id" FROM "glyph") AS "glyph_exists""#
710 /// );
711 /// ```
712 pub fn exists(sel: SelectStatement) -> Self {
713 Self::SubQuery(Some(SubQueryOper::Exists), Box::new(sel.into()))
714 }
715
716 /// Express a `NOT EXISTS` sub-query expression.
717 /// ```
718 /// use sea_query::{*, tests_cfg::*};
719 ///
720 /// let query = Query::select()
721 /// .expr_as(Expr::not_exists(Query::select().column(Char::Id).from(Char::Table).take()), "character_exists")
722 /// .expr_as(Expr::not_exists(Query::select().column(Glyph::Id).from(Glyph::Table).take()), "glyph_exists")
723 /// .to_owned();
724 ///
725 /// assert_eq!(
726 /// query.to_string(MysqlQueryBuilder),
727 /// r#"SELECT NOT EXISTS(SELECT `id` FROM `character`) AS `character_exists`, NOT EXISTS(SELECT `id` FROM `glyph`) AS `glyph_exists`"#
728 /// );
729 /// assert_eq!(
730 /// query.to_string(PostgresQueryBuilder),
731 /// r#"SELECT NOT EXISTS(SELECT "id" FROM "character") AS "character_exists", NOT EXISTS(SELECT "id" FROM "glyph") AS "glyph_exists""#
732 /// );
733 /// assert_eq!(
734 /// query.to_string(SqliteQueryBuilder),
735 /// r#"SELECT NOT EXISTS(SELECT "id" FROM "character") AS "character_exists", NOT EXISTS(SELECT "id" FROM "glyph") AS "glyph_exists""#
736 /// );
737 /// ```
738 pub fn not_exists(sel: SelectStatement) -> Self {
739 Self::exists(sel).not()
740 }
741
742 /// Express a `ANY` sub-query expression.
743 ///
744 /// # Examples
745 ///
746 /// ```
747 /// use sea_query::{tests_cfg::*, *};
748 ///
749 /// let query = Query::select()
750 /// .column(Char::Id)
751 /// .from(Char::Table)
752 /// .and_where(Expr::col(Char::Id).eq(Expr::any(
753 /// Query::select().column(Char::Id).from(Char::Table).take(),
754 /// )))
755 /// .to_owned();
756 ///
757 /// assert_eq!(
758 /// query.to_string(MysqlQueryBuilder),
759 /// r#"SELECT `id` FROM `character` WHERE `id` = ANY(SELECT `id` FROM `character`)"#
760 /// );
761 /// assert_eq!(
762 /// query.to_string(PostgresQueryBuilder),
763 /// r#"SELECT "id" FROM "character" WHERE "id" = ANY(SELECT "id" FROM "character")"#
764 /// );
765 /// ```
766 pub fn any(sel: SelectStatement) -> Self {
767 Self::SubQuery(Some(SubQueryOper::Any), Box::new(sel.into()))
768 }
769
770 /// Express a `SOME` sub-query expression.
771 ///
772 /// # Examples
773 ///
774 /// ```
775 /// use sea_query::{tests_cfg::*, *};
776 ///
777 /// let query = Query::select()
778 /// .column(Char::Id)
779 /// .from(Char::Table)
780 /// .and_where(Expr::col(Char::Id).ne(Expr::some(
781 /// Query::select().column(Char::Id).from(Char::Table).take(),
782 /// )))
783 /// .to_owned();
784 ///
785 /// assert_eq!(
786 /// query.to_string(MysqlQueryBuilder),
787 /// r#"SELECT `id` FROM `character` WHERE `id` <> SOME(SELECT `id` FROM `character`)"#
788 /// );
789 /// assert_eq!(
790 /// query.to_string(PostgresQueryBuilder),
791 /// r#"SELECT "id" FROM "character" WHERE "id" <> SOME(SELECT "id" FROM "character")"#
792 /// );
793 /// ```
794 pub fn some(sel: SelectStatement) -> Self {
795 Self::SubQuery(Some(SubQueryOper::Some), Box::new(sel.into()))
796 }
797
798 /// Express a `ALL` sub-query expression.
799 pub fn all(sel: SelectStatement) -> Self {
800 Self::SubQuery(Some(SubQueryOper::All), Box::new(sel.into()))
801 }
802
803 /// Adds new `CASE WHEN` to existing case statement.
804 ///
805 /// # Examples
806 ///
807 /// ```
808 /// use sea_query::{*, tests_cfg::*};
809 ///
810 /// let query = Query::select()
811 /// .expr_as(
812 /// Expr::case(
813 /// Expr::col((Glyph::Table, Glyph::Aspect)).is_in([2, 4]),
814 /// true
815 /// )
816 /// .finally(false),
817 /// "is_even"
818 /// )
819 /// .from(Glyph::Table)
820 /// .to_owned();
821 ///
822 /// assert_eq!(
823 /// query.to_string(PostgresQueryBuilder),
824 /// r#"SELECT (CASE WHEN ("glyph"."aspect" IN (2, 4)) THEN TRUE ELSE FALSE END) AS "is_even" FROM "glyph""#
825 /// );
826 /// ```
827 pub fn case<C, T>(cond: C, then: T) -> CaseStatement
828 where
829 C: IntoCondition,
830 T: Into<Self>,
831 {
832 CaseStatement::new().case(cond, then)
833 }
834
835 /// Keyword `NULL`.
836 ///
837 /// # Examples
838 ///
839 /// ```
840 /// use sea_query::*;
841 ///
842 /// let query = Query::select().expr(Expr::null()).to_owned();
843 ///
844 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT NULL"#);
845 /// assert_eq!(query.to_string(PostgresQueryBuilder), r#"SELECT NULL"#);
846 /// assert_eq!(query.to_string(SqliteQueryBuilder), r#"SELECT NULL"#);
847 /// ```
848 pub fn null() -> Self {
849 Self::Keyword(Keyword::Null)
850 }
851
852 /// Keyword `CURRENT_DATE`.
853 ///
854 /// # Examples
855 ///
856 /// ```
857 /// use sea_query::*;
858 ///
859 /// let query = Query::select().expr(Expr::current_date()).to_owned();
860 ///
861 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT CURRENT_DATE"#);
862 /// assert_eq!(
863 /// query.to_string(PostgresQueryBuilder),
864 /// r#"SELECT CURRENT_DATE"#
865 /// );
866 /// assert_eq!(
867 /// query.to_string(SqliteQueryBuilder),
868 /// r#"SELECT CURRENT_DATE"#
869 /// );
870 /// ```
871 pub fn current_date() -> Self {
872 Self::Keyword(Keyword::CurrentDate)
873 }
874
875 /// Keyword `CURRENT_TIMESTAMP`.
876 ///
877 /// # Examples
878 ///
879 /// ```
880 /// use sea_query::*;
881 ///
882 /// let query = Query::select().expr(Expr::current_time()).to_owned();
883 ///
884 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT CURRENT_TIME"#);
885 /// assert_eq!(
886 /// query.to_string(PostgresQueryBuilder),
887 /// r#"SELECT CURRENT_TIME"#
888 /// );
889 /// assert_eq!(
890 /// query.to_string(SqliteQueryBuilder),
891 /// r#"SELECT CURRENT_TIME"#
892 /// );
893 /// ```
894 pub fn current_time() -> Self {
895 Self::Keyword(Keyword::CurrentTime)
896 }
897
898 /// Keyword `CURRENT_TIMESTAMP`.
899 ///
900 /// # Examples
901 ///
902 /// ```
903 /// use sea_query::{Expr, MysqlQueryBuilder, PostgresQueryBuilder, Query, SqliteQueryBuilder};
904 ///
905 /// let query = Query::select().expr(Expr::current_timestamp()).to_owned();
906 ///
907 /// assert_eq!(
908 /// query.to_string(MysqlQueryBuilder),
909 /// r#"SELECT CURRENT_TIMESTAMP"#
910 /// );
911 /// assert_eq!(
912 /// query.to_string(PostgresQueryBuilder),
913 /// r#"SELECT CURRENT_TIMESTAMP"#
914 /// );
915 /// assert_eq!(
916 /// query.to_string(SqliteQueryBuilder),
917 /// r#"SELECT CURRENT_TIMESTAMP"#
918 /// );
919 /// ```
920 pub fn current_timestamp() -> Self {
921 Self::Keyword(Keyword::CurrentTimestamp)
922 }
923
924 /// Keyword `DEFAULT`.
925 ///
926 /// SQLite does not support VALUES ​​(DEFAULT).
927 ///
928 /// # Examples
929 ///
930 /// ```
931 /// use sea_query::{
932 /// Expr, MysqlQueryBuilder, PostgresQueryBuilder, Query, SqliteQueryBuilder, tests_cfg::*,
933 /// };
934 ///
935 /// let query = Query::insert()
936 /// .columns([Char::Id])
937 /// .values_panic([Expr::keyword_default()])
938 /// .to_owned();
939 ///
940 /// assert_eq!(
941 /// query.to_string(MysqlQueryBuilder),
942 /// r#"INSERT (`id`) VALUES (DEFAULT)"#
943 /// );
944 /// assert_eq!(
945 /// query.to_string(PostgresQueryBuilder),
946 /// r#"INSERT ("id") VALUES (DEFAULT)"#
947 /// );
948 /// ```
949 pub fn keyword_default() -> Self {
950 Self::Keyword(Keyword::Default)
951 }
952
953 /// Custom keyword.
954 ///
955 /// # Examples
956 ///
957 /// ```
958 /// use sea_query::*;
959 ///
960 /// let query = Query::select()
961 /// .expr(Expr::custom_keyword("test"))
962 /// .to_owned();
963 ///
964 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT test"#);
965 /// assert_eq!(query.to_string(PostgresQueryBuilder), r#"SELECT test"#);
966 /// assert_eq!(query.to_string(SqliteQueryBuilder), r#"SELECT test"#);
967 /// ```
968 pub fn custom_keyword<T>(i: T) -> Self
969 where
970 T: IntoIden,
971 {
972 Self::Keyword(Keyword::Custom(i.into_iden()))
973 }
974
975 pub(crate) fn is_binary(&self) -> bool {
976 matches!(self, Self::Binary(_, _, _))
977 }
978
979 pub(crate) fn get_bin_oper(&self) -> Option<&BinOper> {
980 match self {
981 Self::Binary(_, oper, _) => Some(oper),
982 _ => None,
983 }
984 }
985}
986
987impl<T> From<T> for Expr
988where
989 T: Into<Value>,
990{
991 fn from(v: T) -> Self {
992 Self::Value(v.into())
993 }
994}
995
996impl From<Vec<Value>> for Expr {
997 fn from(v: Vec<Value>) -> Self {
998 Self::Values(v)
999 }
1000}
1001
1002impl From<SubQueryStatement> for Expr {
1003 fn from(v: SubQueryStatement) -> Self {
1004 Self::SubQuery(None, Box::new(v))
1005 }
1006}
1007
1008macro_rules! from_into_subquery_expr {
1009 ($($ty:ty),+) => {
1010 $(
1011 impl From<$ty> for Expr {
1012 fn from(v: $ty) -> Self {
1013 Self::SubQuery(None, Box::new(v.into()))
1014 }
1015 }
1016 )+
1017 };
1018}
1019
1020from_into_subquery_expr!(
1021 WithQuery,
1022 DeleteStatement,
1023 UpdateStatement,
1024 InsertStatement,
1025 SelectStatement
1026);
1027
1028impl From<FunctionCall> for Expr {
1029 fn from(func: FunctionCall) -> Self {
1030 Self::FunctionCall(func)
1031 }
1032}
1033
1034impl From<ColumnRef> for Expr {
1035 fn from(col: ColumnRef) -> Self {
1036 Self::Column(col)
1037 }
1038}
1039
1040impl From<Keyword> for Expr {
1041 fn from(k: Keyword) -> Self {
1042 Self::Keyword(k)
1043 }
1044}
1045
1046impl From<LikeExpr> for Expr {
1047 fn from(like: LikeExpr) -> Self {
1048 match like.escape {
1049 Some(escape) => Self::Binary(
1050 Box::new(like.pattern.into()),
1051 BinOper::Escape,
1052 Box::new(Expr::Constant(escape.into())),
1053 ),
1054 None => like.pattern.into(),
1055 }
1056 }
1057}
1058
1059impl From<TypeRef> for Expr {
1060 fn from(type_name: TypeRef) -> Self {
1061 Self::TypeName(type_name)
1062 }
1063}