diesel/expression_methods/
global_expression_methods.rs

1use crate::dsl;
2use crate::expression::array_comparison::{AsInExpression, In, NotIn};
3use crate::expression::grouped::Grouped;
4use crate::expression::operators::*;
5use crate::expression::{assume_not_null, nullable, AsExpression, Expression};
6use crate::sql_types::{SingleValue, SqlType};
7
8/// Methods present on all expressions, except tuples
9pub trait ExpressionMethods: Expression + Sized {
10    /// Creates a SQL `=` expression.
11    ///
12    /// Note that this function follows SQL semantics around `None`/`null` values,
13    /// so `eq(None)` will never match. Use [`is_null`](ExpressionMethods::is_null()) instead.
14    ///
15    ///
16    #[cfg_attr(
17        any(feature = "sqlite", feature = "postgres"),
18        doc = "To get behavior that is more like the Rust `=` operator you can also use the"
19    )]
20    #[cfg_attr(
21        feature = "sqlite",
22        doc = "sqlite-specific [`is`](crate::SqliteExpressionMethods::is())"
23    )]
24    #[cfg_attr(all(feature = "sqlite", feature = "postgres"), doc = "or the")]
25    #[cfg_attr(
26        feature = "postgres",
27        doc = "postgres-specific [`is_not_distinct_from`](crate::PgExpressionMethods::is_not_distinct_from())"
28    )]
29    #[cfg_attr(any(feature = "sqlite", feature = "postgres"), doc = ".")]
30    ///
31    /// # Example
32    ///
33    /// ```rust
34    /// # include!("../doctest_setup.rs");
35    /// #
36    /// # fn main() {
37    /// #     use schema::users::dsl::*;
38    /// #     let connection = &mut establish_connection();
39    /// let data = users.select(id).filter(name.eq("Sean"));
40    /// assert_eq!(Ok(1), data.first(connection));
41    /// # }
42    /// ```
43    ///
44    /// Matching against `None` follows SQL semantics:
45    /// ```rust
46    /// # include!("../doctest_setup.rs");
47    /// #
48    /// # fn main() {
49    /// #     run_test().unwrap();
50    /// # }
51    /// #
52    /// # fn run_test() -> QueryResult<()> {
53    /// #     use schema::animals::dsl::*;
54    /// #     let connection = &mut establish_connection();
55    /// #
56    /// let data = animals
57    ///     .select(species)
58    ///     .filter(name.eq::<Option<String>>(None))
59    ///     .first::<String>(connection);
60    /// assert_eq!(Err(diesel::NotFound), data);
61    ///
62    /// let data = animals
63    ///     .select(species)
64    ///     .filter(name.is_null())
65    ///     .first::<String>(connection)?;
66    /// assert_eq!("spider", data);
67    /// #     Ok(())
68    /// # }
69    /// ```
70    #[doc(alias = "=")]
71    fn eq<T>(self, other: T) -> dsl::Eq<Self, T>
72    where
73        Self::SqlType: SqlType,
74        T: AsExpression<Self::SqlType>,
75    {
76        Grouped(Eq::new(self, other.as_expression()))
77    }
78
79    /// Creates a SQL `!=` expression.
80    ///
81    /// # Example
82    ///
83    /// ```rust
84    /// # include!("../doctest_setup.rs");
85    /// #
86    /// # fn main() {
87    /// #     use schema::users::dsl::*;
88    /// #     let connection = &mut establish_connection();
89    /// let data = users.select(id).filter(name.ne("Sean"));
90    /// assert_eq!(Ok(2), data.first(connection));
91    /// # }
92    /// ```
93    #[doc(alias = "<>")]
94    fn ne<T>(self, other: T) -> dsl::NotEq<Self, T>
95    where
96        Self::SqlType: SqlType,
97        T: AsExpression<Self::SqlType>,
98    {
99        Grouped(NotEq::new(self, other.as_expression()))
100    }
101
102    /// Creates a SQL `IN` statement.
103    ///
104    /// Queries using this method will not typically be
105    /// placed in the prepared statement cache. However,
106    /// in cases when a subquery is passed to the method, that
107    /// query will use the cache (assuming the subquery
108    /// itself is safe to cache).
109    /// On PostgreSQL, this method automatically performs a `= ANY()`
110    /// query.
111    ///
112    /// # Example
113    ///
114    /// ```rust
115    /// # include!("../doctest_setup.rs");
116    /// #
117    /// # fn main() {
118    /// #     use schema::users;
119    /// #     use schema::posts;
120    /// #     let connection = &mut establish_connection();
121    /// #     diesel::sql_query("INSERT INTO users (name) VALUES
122    /// #         ('Jim')").execute(connection).unwrap();
123    /// let data = users::table.select(users::id).filter(users::name.eq_any(vec!["Sean", "Jim"]));
124    /// assert_eq!(Ok(vec![1, 3]), data.load(connection));
125    ///
126    /// // Calling `eq_any` with an empty array is the same as doing `WHERE 1=0`
127    /// let data = users::table.select(users::id).filter(users::name.eq_any(Vec::<String>::new()));
128    /// assert_eq!(Ok(vec![]), data.load::<i32>(connection));
129    ///
130    /// // Calling `eq_any` with a subquery is the same as using
131    /// // `WHERE {column} IN {subquery}`.
132    ///
133    /// let subquery = users::table.filter(users::name.eq("Sean")).select(users::id).into_boxed();
134    /// let data = posts::table.select(posts::id).filter(posts::user_id.eq_any(subquery));
135    /// assert_eq!(Ok(vec![1, 2]), data.load::<i32>(connection));
136    ///
137    /// # }
138    /// ```
139    #[doc(alias = "in")]
140    fn eq_any<T>(self, values: T) -> dsl::EqAny<Self, T>
141    where
142        Self::SqlType: SqlType,
143        T: AsInExpression<Self::SqlType>,
144    {
145        Grouped(In::new(self, values.as_in_expression()))
146    }
147
148    /// Creates a SQL `NOT IN` statement.
149    ///
150    /// Queries using this method will not be
151    /// placed in the prepared statement cache. On PostgreSQL, this
152    /// method automatically performs a `!= ALL()` query.
153    ///
154    /// # Example
155    ///
156    /// ```rust
157    /// # include!("../doctest_setup.rs");
158    /// #
159    /// # fn main() {
160    /// #     use schema::users::dsl::*;
161    /// #     let connection = &mut establish_connection();
162    /// #     diesel::sql_query("INSERT INTO users (name) VALUES
163    /// #         ('Jim')").execute(connection).unwrap();
164    /// let data = users.select(id).filter(name.ne_all(vec!["Sean", "Jim"]));
165    /// assert_eq!(Ok(vec![2]), data.load(connection));
166    ///
167    /// let data = users.select(id).filter(name.ne_all(vec!["Tess"]));
168    /// assert_eq!(Ok(vec![1, 3]), data.load(connection));
169    ///
170    /// // Calling `ne_any` with an empty array is the same as doing `WHERE 1=1`
171    /// let data = users.select(id).filter(name.ne_all(Vec::<String>::new()));
172    /// assert_eq!(Ok(vec![1, 2, 3]), data.load(connection));
173    /// # }
174    /// ```
175    #[doc(alias = "in")]
176    fn ne_all<T>(self, values: T) -> dsl::NeAny<Self, T>
177    where
178        Self::SqlType: SqlType,
179        T: AsInExpression<Self::SqlType>,
180    {
181        Grouped(NotIn::new(self, values.as_in_expression()))
182    }
183
184    /// Creates a SQL `IS NULL` expression.
185    ///
186    /// # Example
187    ///
188    /// ```rust
189    /// # include!("../doctest_setup.rs");
190    /// #
191    /// # fn main() {
192    /// #     run_test().unwrap();
193    /// # }
194    /// #
195    /// # fn run_test() -> QueryResult<()> {
196    /// #     use schema::animals::dsl::*;
197    /// #     let connection = &mut establish_connection();
198    /// #
199    /// let data = animals
200    ///     .select(species)
201    ///     .filter(name.is_null())
202    ///     .first::<String>(connection)?;
203    /// assert_eq!("spider", data);
204    /// #     Ok(())
205    /// # }
206    /// ```
207    // This method is part of the public API,
208    // so we cannot just change the name to appease clippy
209    // (Otherwise it's also named after the `IS NULL` sql expression
210    // so that name is really fine)
211    #[allow(clippy::wrong_self_convention)]
212    fn is_null(self) -> dsl::IsNull<Self> {
213        Grouped(IsNull::new(self))
214    }
215
216    /// Creates a SQL `IS NOT NULL` expression.
217    ///
218    /// # Example
219    ///
220    /// ```rust
221    /// # include!("../doctest_setup.rs");
222    /// #
223    /// # fn main() {
224    /// #     run_test().unwrap();
225    /// # }
226    /// #
227    /// # fn run_test() -> QueryResult<()> {
228    /// #     use schema::animals::dsl::*;
229    /// #     let connection = &mut establish_connection();
230    /// #
231    /// let data = animals
232    ///     .select(species)
233    ///     .filter(name.is_not_null())
234    ///     .first::<String>(connection)?;
235    /// assert_eq!("dog", data);
236    /// #     Ok(())
237    /// # }
238    /// ```
239    // This method is part of the public API,
240    // so we cannot just change the name to appease clippy
241    // (Otherwise it's also named after the `IS NOT NULL` sql expression
242    // so that name is really fine)
243    #[allow(clippy::wrong_self_convention)]
244    fn is_not_null(self) -> dsl::IsNotNull<Self> {
245        Grouped(IsNotNull::new(self))
246    }
247
248    /// Creates a SQL `>` expression.
249    ///
250    /// # Example
251    ///
252    /// ```rust
253    /// # include!("../doctest_setup.rs");
254    /// #
255    /// # fn main() {
256    /// #     run_test().unwrap();
257    /// # }
258    /// #
259    /// # fn run_test() -> QueryResult<()> {
260    /// #     use schema::users::dsl::*;
261    /// #     let connection = &mut establish_connection();
262    /// let data = users
263    ///     .select(name)
264    ///     .filter(id.gt(1))
265    ///     .first::<String>(connection)?;
266    /// assert_eq!("Tess", data);
267    /// #     Ok(())
268    /// # }
269    /// ```
270    #[doc(alias = ">")]
271    fn gt<T>(self, other: T) -> dsl::Gt<Self, T>
272    where
273        Self::SqlType: SqlType,
274        T: AsExpression<Self::SqlType>,
275    {
276        Grouped(Gt::new(self, other.as_expression()))
277    }
278
279    /// Creates a SQL `>=` expression.
280    ///
281    /// # Example
282    ///
283    /// ```rust
284    /// # include!("../doctest_setup.rs");
285    /// #
286    /// # fn main() {
287    /// #     run_test().unwrap();
288    /// # }
289    /// #
290    /// # fn run_test() -> QueryResult<()> {
291    /// #     use schema::users::dsl::*;
292    /// #     let connection = &mut establish_connection();
293    /// let data = users
294    ///     .select(name)
295    ///     .filter(id.ge(2))
296    ///     .first::<String>(connection)?;
297    /// assert_eq!("Tess", data);
298    /// #     Ok(())
299    /// # }
300    /// ```
301    #[doc(alias = ">=")]
302    fn ge<T>(self, other: T) -> dsl::GtEq<Self, T>
303    where
304        Self::SqlType: SqlType,
305        T: AsExpression<Self::SqlType>,
306    {
307        Grouped(GtEq::new(self, other.as_expression()))
308    }
309
310    /// Creates a SQL `<` expression.
311    ///
312    /// # Example
313    ///
314    /// ```rust
315    /// # include!("../doctest_setup.rs");
316    /// #
317    /// # fn main() {
318    /// #     run_test().unwrap();
319    /// # }
320    /// #
321    /// # fn run_test() -> QueryResult<()> {
322    /// #     use schema::users::dsl::*;
323    /// #     let connection = &mut establish_connection();
324    /// let data = users
325    ///     .select(name)
326    ///     .filter(id.lt(2))
327    ///     .first::<String>(connection)?;
328    /// assert_eq!("Sean", data);
329    /// #     Ok(())
330    /// # }
331    /// ```
332    #[doc(alias = "<")]
333    fn lt<T>(self, other: T) -> dsl::Lt<Self, T>
334    where
335        Self::SqlType: SqlType,
336        T: AsExpression<Self::SqlType>,
337    {
338        Grouped(Lt::new(self, other.as_expression()))
339    }
340
341    /// Creates a SQL `<=` expression.
342    ///
343    /// # Example
344    ///
345    /// ```rust
346    /// # include!("../doctest_setup.rs");
347    /// #
348    /// # fn main() {
349    /// #     run_test().unwrap();
350    /// # }
351    /// #
352    /// # fn run_test() -> QueryResult<()> {
353    /// #     use schema::users::dsl::*;
354    /// #     let connection = &mut establish_connection();
355    /// let data = users
356    ///     .select(name)
357    ///     .filter(id.le(2))
358    ///     .first::<String>(connection)?;
359    /// assert_eq!("Sean", data);
360    /// #     Ok(())
361    /// # }
362    /// ```
363    #[doc(alias = "<=")]
364    fn le<T>(self, other: T) -> dsl::LtEq<Self, T>
365    where
366        Self::SqlType: SqlType,
367        T: AsExpression<Self::SqlType>,
368    {
369        Grouped(LtEq::new(self, other.as_expression()))
370    }
371
372    /// Creates a SQL `BETWEEN` expression using the given lower and upper
373    /// bounds.
374    ///
375    /// # Example
376    ///
377    /// ```rust
378    /// # include!("../doctest_setup.rs");
379    /// #
380    /// # fn main() {
381    /// #     use schema::animals::dsl::*;
382    /// #     let connection = &mut establish_connection();
383    /// #
384    /// let data = animals
385    ///     .select(species)
386    ///     .filter(legs.between(2, 6))
387    ///     .first(connection);
388    /// #
389    /// assert_eq!(Ok("dog".to_string()), data);
390    /// # }
391    /// ```
392    fn between<T, U>(self, lower: T, upper: U) -> dsl::Between<Self, T, U>
393    where
394        Self::SqlType: SqlType,
395        T: AsExpression<Self::SqlType>,
396        U: AsExpression<Self::SqlType>,
397    {
398        Grouped(Between::new(
399            self,
400            And::new(lower.as_expression(), upper.as_expression()),
401        ))
402    }
403
404    /// Creates a SQL `NOT BETWEEN` expression using the given lower and upper
405    /// bounds.
406    ///
407    /// # Example
408    ///
409    /// ```rust
410    /// # include!("../doctest_setup.rs");
411    /// #
412    /// # fn main() {
413    /// #     run_test().unwrap();
414    /// # }
415    /// #
416    /// # fn run_test() -> QueryResult<()> {
417    /// #     use schema::animals::dsl::*;
418    /// #     let connection = &mut establish_connection();
419    /// #
420    /// let data = animals
421    ///     .select(species)
422    ///     .filter(legs.not_between(2, 6))
423    ///     .first::<String>(connection)?;
424    /// assert_eq!("spider", data);
425    /// #     Ok(())
426    /// # }
427    /// ```
428    fn not_between<T, U>(self, lower: T, upper: U) -> dsl::NotBetween<Self, T, U>
429    where
430        Self::SqlType: SqlType,
431        T: AsExpression<Self::SqlType>,
432        U: AsExpression<Self::SqlType>,
433    {
434        Grouped(NotBetween::new(
435            self,
436            And::new(lower.as_expression(), upper.as_expression()),
437        ))
438    }
439
440    /// Creates a SQL `DESC` expression, representing this expression in
441    /// descending order.
442    ///
443    /// # Example
444    ///
445    /// ```rust
446    /// # include!("../doctest_setup.rs");
447    /// #
448    /// # fn main() {
449    /// #     run_test().unwrap();
450    /// # }
451    /// #
452    /// # fn run_test() -> QueryResult<()> {
453    /// #     use schema::users::dsl::*;
454    /// #     let connection = &mut establish_connection();
455    /// #
456    /// let names = users
457    ///     .select(name)
458    ///     .order(name.desc())
459    ///     .load::<String>(connection)?;
460    /// assert_eq!(vec!["Tess", "Sean"], names);
461    /// #     Ok(())
462    /// # }
463    /// ```
464    fn desc(self) -> dsl::Desc<Self> {
465        Desc::new(self)
466    }
467
468    /// Creates a SQL `ASC` expression, representing this expression in
469    /// ascending order.
470    ///
471    /// This is the same as leaving the direction unspecified. It is useful if
472    /// you need to provide an unknown ordering, and need to box the return
473    /// value of a function.
474    ///
475    /// # Example
476    ///
477    /// ```rust
478    /// # include!("../doctest_setup.rs");
479    /// # use diesel::expression::expression_types::NotSelectable;
480    /// #
481    /// # fn main() {
482    /// #     use schema::users::dsl::*;
483    /// #     let order = "name";
484    /// let ordering: Box<dyn BoxableExpression<users, DB, SqlType = NotSelectable>> =
485    ///     if order == "name" {
486    ///         Box::new(name.desc())
487    ///     } else {
488    ///         Box::new(id.asc())
489    ///     };
490    /// # }
491    /// ```
492    fn asc(self) -> dsl::Asc<Self> {
493        Asc::new(self)
494    }
495}
496
497impl<T> ExpressionMethods for T
498where
499    T: Expression,
500    T::SqlType: SingleValue,
501{
502}
503
504/// Methods present on all expressions
505pub trait NullableExpressionMethods: Expression + Sized {
506    /// Converts this potentially non-null expression into one which is treated
507    /// as nullable. This method has no impact on the generated SQL, and is only
508    /// used to allow certain comparisons that would otherwise fail to compile.
509    ///
510    /// # Example
511    /// ```no_run
512    /// # #![allow(dead_code)]
513    /// # include!("../doctest_setup.rs");
514    /// # use diesel::sql_types::*;
515    /// # use schema::users;
516    /// #
517    /// table! {
518    ///     posts {
519    ///         id -> Integer,
520    ///         user_id -> Integer,
521    ///         author_name -> Nullable<VarChar>,
522    ///     }
523    /// }
524    /// #
525    /// # joinable!(posts -> users (user_id));
526    /// # allow_tables_to_appear_in_same_query!(posts, users);
527    ///
528    /// fn main() {
529    ///     use self::users::dsl::*;
530    ///     use self::posts::dsl::{posts, author_name};
531    ///     let connection = &mut establish_connection();
532    ///
533    ///     let data = users.inner_join(posts)
534    ///         .filter(name.nullable().eq(author_name))
535    ///         .select(name)
536    ///         .load::<String>(connection);
537    ///     println!("{:?}", data);
538    /// }
539    /// ```
540    fn nullable(self) -> dsl::Nullable<Self> {
541        nullable::Nullable::new(self)
542    }
543
544    /// Converts this potentially nullable expression into one which will be **assumed**
545    /// to be not-null. This method has no impact on the generated SQL, however it will
546    /// enable you to attempt deserialization of the returned value in a non-`Option`.
547    ///
548    /// This is meant to cover for cases where you know that given the `WHERE` clause
549    /// the field returned by the database will never be `NULL`.
550    ///
551    /// This **will cause runtime errors** on `load()` if the "assume" turns out to be incorrect.
552    ///
553    /// # Examples
554    /// ## Normal usage
555    /// ```rust
556    /// # #![allow(dead_code)]
557    /// # include!("../doctest_setup.rs");
558    /// # use diesel::sql_types::*;
559    /// #
560    /// table! {
561    ///     animals {
562    ///         id -> Integer,
563    ///         species -> VarChar,
564    ///         legs -> Integer,
565    ///         name -> Nullable<VarChar>,
566    ///     }
567    /// }
568    ///
569    /// fn main() {
570    ///     use self::animals::dsl::*;
571    ///     let connection = &mut establish_connection();
572    ///
573    ///     let result = animals
574    ///         .filter(name.is_not_null())
575    ///         .select(name.assume_not_null())
576    ///         .load::<String>(connection);
577    ///     assert!(result.is_ok());
578    /// }
579    /// ```
580    ///
581    /// ## Incorrect usage
582    /// ```rust
583    /// # #![allow(dead_code)]
584    /// # include!("../doctest_setup.rs");
585    /// # use diesel::sql_types::*;
586    /// #
587    /// table! {
588    ///     animals {
589    ///         id -> Integer,
590    ///         species -> VarChar,
591    ///         legs -> Integer,
592    ///         name -> Nullable<VarChar>,
593    ///     }
594    /// }
595    ///
596    /// fn main() {
597    ///     use diesel::result::{Error, UnexpectedNullError};
598    ///     use self::animals::dsl::*;
599    ///     let connection = &mut establish_connection();
600    ///
601    ///     let result = animals
602    ///         .select(name.assume_not_null())
603    ///         .load::<String>(connection);
604    ///     assert!(matches!(
605    ///         result,
606    ///         Err(Error::DeserializationError(err)) if err.is::<UnexpectedNullError>()
607    ///     ));
608    /// }
609    /// ```
610    ///
611    /// ## Advanced usage - use only if you're sure you know what you're doing!
612    ///
613    /// This will cause the `Option` to be `None` where the `left_join` succeeded but the
614    /// `author_name` turned out to be `NULL`, due to how `Option` deserialization works.
615    /// (see [`Queryable` documentation](crate::deserialize::Queryable))
616    ///
617    /// ```rust
618    /// # #![allow(dead_code)]
619    /// # include!("../doctest_setup.rs");
620    /// # use diesel::sql_types::*;
621    /// # use schema::users;
622    /// #
623    /// table! {
624    ///     posts {
625    ///         id -> Integer,
626    ///         user_id -> Integer,
627    ///         author_name -> Nullable<Text>,
628    ///     }
629    /// }
630    /// #
631    /// # joinable!(posts -> users (user_id));
632    /// # allow_tables_to_appear_in_same_query!(posts, users);
633    ///
634    /// fn main() {
635    ///     use self::posts;
636    ///     use self::users;
637    ///     let connection = &mut establish_connection();
638    ///
639    /// #   diesel::sql_query("ALTER TABLE posts ADD COLUMN author_name Text")
640    /// #       .execute(connection)
641    /// #       .unwrap();
642    /// #   diesel::update(posts::table.filter(posts::user_id.eq(1)))
643    /// #       .set(posts::author_name.eq("Sean"))
644    /// #       .execute(connection);
645    ///
646    ///     let result = posts::table.left_join(users::table)
647    ///         .select((posts::id, (users::id, posts::author_name.assume_not_null()).nullable()))
648    ///         .order_by(posts::id)
649    ///         .load::<(i32, Option<(i32, String)>)>(connection);
650    ///     let expected = Ok(vec![
651    ///         (1, Some((1, "Sean".to_owned()))),
652    ///         (2, Some((1, "Sean".to_owned()))),
653    ///         (3, None),
654    ///     ]);
655    ///     assert_eq!(expected, result);
656    /// }
657    /// ```
658    fn assume_not_null(self) -> dsl::AssumeNotNull<Self> {
659        assume_not_null::AssumeNotNull::new(self)
660    }
661}
662
663impl<T: Expression> NullableExpressionMethods for T {}