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