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 {}