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 `AVG` (average) 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)).avg())
606 /// .from(Char::Table)
607 /// .to_owned();
608 ///
609 /// assert_eq!(
610 /// query.to_string(MysqlQueryBuilder),
611 /// r#"SELECT AVG(`character`.`size_w`) FROM `character`"#
612 /// );
613 /// assert_eq!(
614 /// query.to_string(PostgresQueryBuilder),
615 /// r#"SELECT AVG("character"."size_w") FROM "character""#
616 /// );
617 /// assert_eq!(
618 /// query.to_string(SqliteQueryBuilder),
619 /// r#"SELECT AVG("character"."size_w") FROM "character""#
620 /// );
621 /// ```
622 pub fn avg(self) -> Self {
623 Func::avg(self).into()
624 }
625
626 /// Express a `COUNT` function.
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())
635 /// .from(Char::Table)
636 /// .to_owned();
637 ///
638 /// assert_eq!(
639 /// query.to_string(MysqlQueryBuilder),
640 /// r#"SELECT COUNT(`character`.`size_w`) FROM `character`"#
641 /// );
642 /// assert_eq!(
643 /// query.to_string(PostgresQueryBuilder),
644 /// r#"SELECT COUNT("character"."size_w") FROM "character""#
645 /// );
646 /// assert_eq!(
647 /// query.to_string(SqliteQueryBuilder),
648 /// r#"SELECT COUNT("character"."size_w") FROM "character""#
649 /// );
650 /// ```
651 pub fn count(self) -> Self {
652 Func::count(self).into()
653 }
654
655 /// Express a `COUNT` function with the DISTINCT modifier.
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)).count_distinct())
664 /// .from(Char::Table)
665 /// .to_owned();
666 ///
667 /// assert_eq!(
668 /// query.to_string(MysqlQueryBuilder),
669 /// r#"SELECT COUNT(DISTINCT `character`.`size_w`) FROM `character`"#
670 /// );
671 /// assert_eq!(
672 /// query.to_string(PostgresQueryBuilder),
673 /// r#"SELECT COUNT(DISTINCT "character"."size_w") FROM "character""#
674 /// );
675 /// assert_eq!(
676 /// query.to_string(SqliteQueryBuilder),
677 /// r#"SELECT COUNT(DISTINCT "character"."size_w") FROM "character""#
678 /// );
679 /// ```
680 pub fn count_distinct(self) -> Self {
681 Func::count_distinct(self).into()
682 }
683
684 /// Express a `IF NULL` function.
685 ///
686 /// # Examples
687 ///
688 /// ```
689 /// use sea_query::{tests_cfg::*, *};
690 ///
691 /// let query = Query::select()
692 /// .expr(Expr::col((Char::Table, Char::SizeW)).if_null(0))
693 /// .from(Char::Table)
694 /// .to_owned();
695 ///
696 /// assert_eq!(
697 /// query.to_string(MysqlQueryBuilder),
698 /// r#"SELECT IFNULL(`character`.`size_w`, 0) FROM `character`"#
699 /// );
700 /// assert_eq!(
701 /// query.to_string(PostgresQueryBuilder),
702 /// r#"SELECT COALESCE("character"."size_w", 0) FROM "character""#
703 /// );
704 /// assert_eq!(
705 /// query.to_string(SqliteQueryBuilder),
706 /// r#"SELECT IFNULL("character"."size_w", 0) FROM "character""#
707 /// );
708 /// ```
709 pub fn if_null<V>(self, v: V) -> Self
710 where
711 V: Into<Self>,
712 {
713 Func::if_null(self, v).into()
714 }
715
716 /// Express a `EXISTS` sub-query expression.
717 ///
718 /// # Examples
719 ///
720 /// ```
721 /// use sea_query::{*, tests_cfg::*};
722 ///
723 /// let query = Query::select()
724 /// .expr_as(Expr::exists(Query::select().column(Char::Id).from(Char::Table).take()), "character_exists")
725 /// .expr_as(Expr::exists(Query::select().column(Glyph::Id).from(Glyph::Table).take()), "glyph_exists")
726 /// .to_owned();
727 ///
728 /// assert_eq!(
729 /// query.to_string(MysqlQueryBuilder),
730 /// r#"SELECT EXISTS(SELECT `id` FROM `character`) AS `character_exists`, EXISTS(SELECT `id` FROM `glyph`) AS `glyph_exists`"#
731 /// );
732 /// assert_eq!(
733 /// query.to_string(PostgresQueryBuilder),
734 /// r#"SELECT EXISTS(SELECT "id" FROM "character") AS "character_exists", EXISTS(SELECT "id" FROM "glyph") AS "glyph_exists""#
735 /// );
736 /// assert_eq!(
737 /// query.to_string(SqliteQueryBuilder),
738 /// r#"SELECT EXISTS(SELECT "id" FROM "character") AS "character_exists", EXISTS(SELECT "id" FROM "glyph") AS "glyph_exists""#
739 /// );
740 /// ```
741 pub fn exists(sel: SelectStatement) -> Self {
742 Self::SubQuery(Some(SubQueryOper::Exists), Box::new(sel.into()))
743 }
744
745 /// Express a `NOT EXISTS` sub-query expression.
746 /// ```
747 /// use sea_query::{*, tests_cfg::*};
748 ///
749 /// let query = Query::select()
750 /// .expr_as(Expr::not_exists(Query::select().column(Char::Id).from(Char::Table).take()), "character_exists")
751 /// .expr_as(Expr::not_exists(Query::select().column(Glyph::Id).from(Glyph::Table).take()), "glyph_exists")
752 /// .to_owned();
753 ///
754 /// assert_eq!(
755 /// query.to_string(MysqlQueryBuilder),
756 /// r#"SELECT NOT EXISTS(SELECT `id` FROM `character`) AS `character_exists`, NOT EXISTS(SELECT `id` FROM `glyph`) AS `glyph_exists`"#
757 /// );
758 /// assert_eq!(
759 /// query.to_string(PostgresQueryBuilder),
760 /// r#"SELECT NOT EXISTS(SELECT "id" FROM "character") AS "character_exists", NOT EXISTS(SELECT "id" FROM "glyph") AS "glyph_exists""#
761 /// );
762 /// assert_eq!(
763 /// query.to_string(SqliteQueryBuilder),
764 /// r#"SELECT NOT EXISTS(SELECT "id" FROM "character") AS "character_exists", NOT EXISTS(SELECT "id" FROM "glyph") AS "glyph_exists""#
765 /// );
766 /// ```
767 pub fn not_exists(sel: SelectStatement) -> Self {
768 Self::exists(sel).not()
769 }
770
771 /// Express a `ANY` sub-query expression.
772 ///
773 /// # Examples
774 ///
775 /// ```
776 /// use sea_query::{tests_cfg::*, *};
777 ///
778 /// let query = Query::select()
779 /// .column(Char::Id)
780 /// .from(Char::Table)
781 /// .and_where(Expr::col(Char::Id).eq(Expr::any(
782 /// Query::select().column(Char::Id).from(Char::Table).take(),
783 /// )))
784 /// .to_owned();
785 ///
786 /// assert_eq!(
787 /// query.to_string(MysqlQueryBuilder),
788 /// r#"SELECT `id` FROM `character` WHERE `id` = ANY(SELECT `id` FROM `character`)"#
789 /// );
790 /// assert_eq!(
791 /// query.to_string(PostgresQueryBuilder),
792 /// r#"SELECT "id" FROM "character" WHERE "id" = ANY(SELECT "id" FROM "character")"#
793 /// );
794 /// ```
795 pub fn any(sel: SelectStatement) -> Self {
796 Self::SubQuery(Some(SubQueryOper::Any), Box::new(sel.into()))
797 }
798
799 /// Express a `SOME` sub-query expression.
800 ///
801 /// # Examples
802 ///
803 /// ```
804 /// use sea_query::{tests_cfg::*, *};
805 ///
806 /// let query = Query::select()
807 /// .column(Char::Id)
808 /// .from(Char::Table)
809 /// .and_where(Expr::col(Char::Id).ne(Expr::some(
810 /// Query::select().column(Char::Id).from(Char::Table).take(),
811 /// )))
812 /// .to_owned();
813 ///
814 /// assert_eq!(
815 /// query.to_string(MysqlQueryBuilder),
816 /// r#"SELECT `id` FROM `character` WHERE `id` <> SOME(SELECT `id` FROM `character`)"#
817 /// );
818 /// assert_eq!(
819 /// query.to_string(PostgresQueryBuilder),
820 /// r#"SELECT "id" FROM "character" WHERE "id" <> SOME(SELECT "id" FROM "character")"#
821 /// );
822 /// ```
823 pub fn some(sel: SelectStatement) -> Self {
824 Self::SubQuery(Some(SubQueryOper::Some), Box::new(sel.into()))
825 }
826
827 /// Express a `ALL` sub-query expression.
828 pub fn all(sel: SelectStatement) -> Self {
829 Self::SubQuery(Some(SubQueryOper::All), Box::new(sel.into()))
830 }
831
832 /// Adds new `CASE WHEN` to existing case statement.
833 ///
834 /// # Examples
835 ///
836 /// ```
837 /// use sea_query::{*, tests_cfg::*};
838 ///
839 /// let query = Query::select()
840 /// .expr_as(
841 /// Expr::case(
842 /// Expr::col((Glyph::Table, Glyph::Aspect)).is_in([2, 4]),
843 /// true
844 /// )
845 /// .finally(false),
846 /// "is_even"
847 /// )
848 /// .from(Glyph::Table)
849 /// .to_owned();
850 ///
851 /// assert_eq!(
852 /// query.to_string(PostgresQueryBuilder),
853 /// r#"SELECT (CASE WHEN ("glyph"."aspect" IN (2, 4)) THEN TRUE ELSE FALSE END) AS "is_even" FROM "glyph""#
854 /// );
855 /// ```
856 pub fn case<C, T>(cond: C, then: T) -> CaseStatement
857 where
858 C: IntoCondition,
859 T: Into<Self>,
860 {
861 CaseStatement::new().case(cond, then)
862 }
863
864 /// Keyword `NULL`.
865 ///
866 /// # Examples
867 ///
868 /// ```
869 /// use sea_query::*;
870 ///
871 /// let query = Query::select().expr(Expr::null()).to_owned();
872 ///
873 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT NULL"#);
874 /// assert_eq!(query.to_string(PostgresQueryBuilder), r#"SELECT NULL"#);
875 /// assert_eq!(query.to_string(SqliteQueryBuilder), r#"SELECT NULL"#);
876 /// ```
877 pub fn null() -> Self {
878 Self::Keyword(Keyword::Null)
879 }
880
881 /// Keyword `CURRENT_DATE`.
882 ///
883 /// # Examples
884 ///
885 /// ```
886 /// use sea_query::*;
887 ///
888 /// let query = Query::select().expr(Expr::current_date()).to_owned();
889 ///
890 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT CURRENT_DATE"#);
891 /// assert_eq!(
892 /// query.to_string(PostgresQueryBuilder),
893 /// r#"SELECT CURRENT_DATE"#
894 /// );
895 /// assert_eq!(
896 /// query.to_string(SqliteQueryBuilder),
897 /// r#"SELECT CURRENT_DATE"#
898 /// );
899 /// ```
900 pub fn current_date() -> Self {
901 Self::Keyword(Keyword::CurrentDate)
902 }
903
904 /// Keyword `CURRENT_TIMESTAMP`.
905 ///
906 /// # Examples
907 ///
908 /// ```
909 /// use sea_query::*;
910 ///
911 /// let query = Query::select().expr(Expr::current_time()).to_owned();
912 ///
913 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT CURRENT_TIME"#);
914 /// assert_eq!(
915 /// query.to_string(PostgresQueryBuilder),
916 /// r#"SELECT CURRENT_TIME"#
917 /// );
918 /// assert_eq!(
919 /// query.to_string(SqliteQueryBuilder),
920 /// r#"SELECT CURRENT_TIME"#
921 /// );
922 /// ```
923 pub fn current_time() -> Self {
924 Self::Keyword(Keyword::CurrentTime)
925 }
926
927 /// Keyword `CURRENT_TIMESTAMP`.
928 ///
929 /// # Examples
930 ///
931 /// ```
932 /// use sea_query::{Expr, MysqlQueryBuilder, PostgresQueryBuilder, Query, SqliteQueryBuilder};
933 ///
934 /// let query = Query::select().expr(Expr::current_timestamp()).to_owned();
935 ///
936 /// assert_eq!(
937 /// query.to_string(MysqlQueryBuilder),
938 /// r#"SELECT CURRENT_TIMESTAMP"#
939 /// );
940 /// assert_eq!(
941 /// query.to_string(PostgresQueryBuilder),
942 /// r#"SELECT CURRENT_TIMESTAMP"#
943 /// );
944 /// assert_eq!(
945 /// query.to_string(SqliteQueryBuilder),
946 /// r#"SELECT CURRENT_TIMESTAMP"#
947 /// );
948 /// ```
949 pub fn current_timestamp() -> Self {
950 Self::Keyword(Keyword::CurrentTimestamp)
951 }
952
953 /// Keyword `DEFAULT`.
954 ///
955 /// SQLite does not support VALUES (DEFAULT).
956 ///
957 /// # Examples
958 ///
959 /// ```
960 /// use sea_query::{
961 /// Expr, MysqlQueryBuilder, PostgresQueryBuilder, Query, SqliteQueryBuilder, tests_cfg::*,
962 /// };
963 ///
964 /// let query = Query::insert()
965 /// .columns([Char::Id])
966 /// .values_panic([Expr::keyword_default()])
967 /// .to_owned();
968 ///
969 /// assert_eq!(
970 /// query.to_string(MysqlQueryBuilder),
971 /// r#"INSERT (`id`) VALUES (DEFAULT)"#
972 /// );
973 /// assert_eq!(
974 /// query.to_string(PostgresQueryBuilder),
975 /// r#"INSERT ("id") VALUES (DEFAULT)"#
976 /// );
977 /// ```
978 pub fn keyword_default() -> Self {
979 Self::Keyword(Keyword::Default)
980 }
981
982 /// Custom keyword.
983 ///
984 /// # Examples
985 ///
986 /// ```
987 /// use sea_query::*;
988 ///
989 /// let query = Query::select()
990 /// .expr(Expr::custom_keyword("test"))
991 /// .to_owned();
992 ///
993 /// assert_eq!(query.to_string(MysqlQueryBuilder), r#"SELECT test"#);
994 /// assert_eq!(query.to_string(PostgresQueryBuilder), r#"SELECT test"#);
995 /// assert_eq!(query.to_string(SqliteQueryBuilder), r#"SELECT test"#);
996 /// ```
997 pub fn custom_keyword<T>(i: T) -> Self
998 where
999 T: IntoIden,
1000 {
1001 Self::Keyword(Keyword::Custom(i.into_iden()))
1002 }
1003
1004 pub(crate) fn is_binary(&self) -> bool {
1005 matches!(self, Self::Binary(_, _, _))
1006 }
1007
1008 pub(crate) fn get_bin_oper(&self) -> Option<&BinOper> {
1009 match self {
1010 Self::Binary(_, oper, _) => Some(oper),
1011 _ => None,
1012 }
1013 }
1014}
1015
1016impl<T> From<T> for Expr
1017where
1018 T: Into<Value>,
1019{
1020 fn from(v: T) -> Self {
1021 Self::Value(v.into())
1022 }
1023}
1024
1025impl From<Vec<Value>> for Expr {
1026 fn from(v: Vec<Value>) -> Self {
1027 Self::Values(v)
1028 }
1029}
1030
1031impl From<SubQueryStatement> for Expr {
1032 fn from(v: SubQueryStatement) -> Self {
1033 Self::SubQuery(None, Box::new(v))
1034 }
1035}
1036
1037macro_rules! from_into_subquery_expr {
1038 ($($ty:ty),+) => {
1039 $(
1040 impl From<$ty> for Expr {
1041 fn from(v: $ty) -> Self {
1042 Self::SubQuery(None, Box::new(v.into()))
1043 }
1044 }
1045 )+
1046 };
1047}
1048
1049from_into_subquery_expr!(
1050 WithQuery,
1051 DeleteStatement,
1052 UpdateStatement,
1053 InsertStatement,
1054 SelectStatement
1055);
1056
1057impl From<FunctionCall> for Expr {
1058 fn from(func: FunctionCall) -> Self {
1059 Self::FunctionCall(func)
1060 }
1061}
1062
1063impl From<ColumnRef> for Expr {
1064 fn from(col: ColumnRef) -> Self {
1065 Self::Column(col)
1066 }
1067}
1068
1069impl From<Keyword> for Expr {
1070 fn from(k: Keyword) -> Self {
1071 Self::Keyword(k)
1072 }
1073}
1074
1075impl From<LikeExpr> for Expr {
1076 fn from(like: LikeExpr) -> Self {
1077 match like.escape {
1078 Some(escape) => Self::Binary(
1079 Box::new(like.pattern.into()),
1080 BinOper::Escape,
1081 Box::new(Expr::Constant(escape.into())),
1082 ),
1083 None => like.pattern.into(),
1084 }
1085 }
1086}
1087
1088impl From<TypeRef> for Expr {
1089 fn from(type_name: TypeRef) -> Self {
1090 Self::TypeName(type_name)
1091 }
1092}