sea_query/query/
case.rs

1use crate::{Condition, IntoCondition, SimpleExpr};
2
3#[derive(Debug, Clone, PartialEq)]
4pub(crate) struct CaseStatementCondition {
5    pub(crate) condition: Condition,
6    pub(crate) result: SimpleExpr,
7}
8
9#[derive(Debug, Clone, Default, PartialEq)]
10pub struct CaseStatement {
11    pub(crate) when: Vec<CaseStatementCondition>,
12    pub(crate) r#else: Option<SimpleExpr>,
13}
14
15impl CaseStatement {
16    /// Creates a new case statement expression
17    ///
18    /// # Examples
19    ///
20    /// ```
21    /// use sea_query::{*, tests_cfg::*};
22    ///
23    /// let query = Query::select()
24    ///     .expr_as(
25    ///         CaseStatement::new()
26    ///             .case(Expr::col((Glyph::Table, Glyph::Aspect)).is_in([2, 4]), true)
27    ///             .finally(false),
28    ///          Alias::new("is_even")
29    ///     )
30    ///     .from(Glyph::Table)
31    ///     .to_owned();
32    ///
33    /// assert_eq!(
34    ///     query.to_string(PostgresQueryBuilder),
35    ///     r#"SELECT (CASE WHEN ("glyph"."aspect" IN (2, 4)) THEN TRUE ELSE FALSE END) AS "is_even" FROM "glyph""#
36    /// );    
37    /// ```
38    pub fn new() -> Self {
39        Self::default()
40    }
41
42    /// Adds new `CASE WHEN` to existing case statement.
43    ///
44    /// # Examples
45    ///
46    /// ```
47    /// use sea_query::{*, tests_cfg::*};
48    ///
49    /// let query = Query::select()
50    ///     .expr_as(
51    ///             Expr::case(
52    ///                 Expr::col((Glyph::Table, Glyph::Aspect)).gt(0),
53    ///                 "positive"
54    ///              )
55    ///             .case(
56    ///                 Expr::col((Glyph::Table, Glyph::Aspect)).lt(0),
57    ///                 "negative"
58    ///              )
59    ///             .finally("zero"),
60    ///          Alias::new("polarity")
61    ///     )
62    ///     .from(Glyph::Table)
63    ///     .to_owned();
64    ///
65    /// assert_eq!(
66    ///     query.to_string(PostgresQueryBuilder),
67    ///     r#"SELECT (CASE WHEN ("glyph"."aspect" > 0) THEN 'positive' WHEN ("glyph"."aspect" < 0) THEN 'negative' ELSE 'zero' END) AS "polarity" FROM "glyph""#
68    /// );    
69    /// ```
70    pub fn case<C, T>(mut self, cond: C, then: T) -> Self
71    where
72        C: IntoCondition,
73        T: Into<SimpleExpr>,
74    {
75        self.when.push(CaseStatementCondition {
76            condition: cond.into_condition(),
77            result: then.into(),
78        });
79        self
80    }
81
82    /// Ends the case statement with the final `ELSE` result.
83    ///
84    /// # Examples
85    ///
86    /// ```
87    /// use sea_query::{*, tests_cfg::*};
88    ///
89    /// let query = Query::select()
90    ///     .expr_as(
91    ///         Expr::case(
92    ///             Cond::any()
93    ///                 .add(Expr::col((Character::Table, Character::FontSize)).gt(48))
94    ///                 .add(Expr::col((Character::Table, Character::SizeW)).gt(500)),
95    ///             "large"
96    ///         )
97    ///         .case(
98    ///             Cond::any()
99    ///                 .add(Expr::col((Character::Table, Character::FontSize)).between(24,48))
100    ///                 .add(Expr::col((Character::Table, Character::SizeW)).between(300,500)),
101    ///             "medium"
102    ///         )
103    ///         .finally("small"),
104    ///         Alias::new("char_size"))
105    ///     .from(Character::Table)
106    ///     .to_owned();
107    ///
108    /// assert_eq!(
109    ///     query.to_string(PostgresQueryBuilder),
110    ///     [
111    ///         r#"SELECT"#,
112    ///         r#"(CASE WHEN ("character"."font_size" > 48 OR "character"."size_w" > 500) THEN 'large'"#,
113    ///         r#"WHEN (("character"."font_size" BETWEEN 24 AND 48) OR ("character"."size_w" BETWEEN 300 AND 500)) THEN 'medium'"#,
114    ///         r#"ELSE 'small' END) AS "char_size""#,
115    ///         r#"FROM "character""#
116    ///     ]
117    ///     .join(" ")
118    /// );    
119    /// ```
120    pub fn finally<E>(mut self, r#else: E) -> Self
121    where
122        E: Into<SimpleExpr>,
123    {
124        self.r#else = Some(r#else.into());
125        self
126    }
127}
128
129#[allow(clippy::from_over_into)]
130impl Into<SimpleExpr> for CaseStatement {
131    fn into(self) -> SimpleExpr {
132        SimpleExpr::Case(Box::new(self))
133    }
134}
135
136#[cfg(test)]
137mod test {
138    use crate::*;
139
140    #[test]
141    #[cfg(feature = "backend-postgres")]
142    fn test_where_case_eq() {
143        let case_statement: SimpleExpr = Expr::case(
144            Expr::col(Alias::new("col")).lt(5),
145            Expr::col(Alias::new("othercol")),
146        )
147        .finally(Expr::col(Alias::new("finalcol")))
148        .into();
149
150        let result = Query::select()
151            .column(Asterisk)
152            .from(Alias::new("tbl"))
153            .and_where(case_statement.eq(10))
154            .to_string(PostgresQueryBuilder);
155        assert_eq!(
156            result,
157            r#"SELECT * FROM "tbl" WHERE (CASE WHEN ("col" < 5) THEN "othercol" ELSE "finalcol" END) = 10"#
158        );
159    }
160}
sea_query/query/case.rs

sea_query/query/
case.rs
