diesel/pg/expression/expression_methods.rs
1//! PostgreSQL specific expression methods
2
3use super::operators::*;
4use expression::{AsExpression, Expression};
5use sql_types::{Array, Nullable, Text};
6
7/// PostgreSQL specific methods which are present on all expressions.
8pub trait PgExpressionMethods: Expression + Sized {
9 /// Creates a PostgreSQL `IS NOT DISTINCT FROM` expression.
10 ///
11 /// This behaves identically to the `=` operator, except that `NULL` is
12 /// treated as a normal value.
13 ///
14 /// # Example
15 ///
16 /// ```rust
17 /// # #[macro_use] extern crate diesel;
18 /// # include!("../../doctest_setup.rs");
19 /// #
20 /// # fn main() {
21 /// # use schema::users::dsl::*;
22 /// # let connection = establish_connection();
23 /// let distinct = users.select(id).filter(name.is_distinct_from("Sean"));
24 /// let not_distinct = users.select(id).filter(name.is_not_distinct_from("Sean"));
25 /// assert_eq!(Ok(2), distinct.first(&connection));
26 /// assert_eq!(Ok(1), not_distinct.first(&connection));
27 /// # }
28 /// ```
29 fn is_not_distinct_from<T>(self, other: T) -> IsNotDistinctFrom<Self, T::Expression>
30 where
31 T: AsExpression<Self::SqlType>,
32 {
33 IsNotDistinctFrom::new(self, other.as_expression())
34 }
35
36 /// Creates a PostgreSQL `IS DISTINCT FROM` expression.
37 ///
38 /// This behaves identically to the `!=` operator, except that `NULL` is
39 /// treated as a normal value.
40 ///
41 /// # Example
42 ///
43 /// ```rust
44 /// # #[macro_use] extern crate diesel;
45 /// # include!("../../doctest_setup.rs");
46 /// #
47 /// # fn main() {
48 /// # use schema::users::dsl::*;
49 /// # let connection = establish_connection();
50 /// let distinct = users.select(id).filter(name.is_distinct_from("Sean"));
51 /// let not_distinct = users.select(id).filter(name.is_not_distinct_from("Sean"));
52 /// assert_eq!(Ok(2), distinct.first(&connection));
53 /// assert_eq!(Ok(1), not_distinct.first(&connection));
54 /// # }
55 /// ```
56 fn is_distinct_from<T>(self, other: T) -> IsDistinctFrom<Self, T::Expression>
57 where
58 T: AsExpression<Self::SqlType>,
59 {
60 IsDistinctFrom::new(self, other.as_expression())
61 }
62}
63
64impl<T: Expression> PgExpressionMethods for T {}
65
66use super::date_and_time::{AtTimeZone, DateTimeLike};
67use sql_types::VarChar;
68
69/// PostgreSQL specific methods present on timestamp expressions.
70pub trait PgTimestampExpressionMethods: Expression + Sized {
71 /// Creates a PostgreSQL "AT TIME ZONE" expression.
72 ///
73 /// When this is called on a `TIMESTAMP WITHOUT TIME ZONE` column,
74 /// the value will be treated as if were in the given time zone,
75 /// and then converted to UTC.
76 ///
77 /// When this is called on a `TIMESTAMP WITH TIME ZONE` column,
78 /// the value will be converted to the given time zone,
79 /// and then have its time zone information removed.
80 ///
81 /// # Example
82 ///
83 /// ```rust
84 /// # #[macro_use] extern crate diesel;
85 /// # #[cfg(feature = "chrono")]
86 /// # extern crate chrono;
87 /// # include!("../../doctest_setup.rs");
88 /// #
89 /// # table! {
90 /// # timestamps (timestamp) {
91 /// # timestamp -> Timestamp,
92 /// # }
93 /// # }
94 /// #
95 /// # fn main() {
96 /// # run_test().unwrap();
97 /// # }
98 /// #
99 /// # #[cfg(all(feature = "postgres", feature = "chrono"))]
100 /// # fn run_test() -> QueryResult<()> {
101 /// # use timestamps::dsl::*;
102 /// # use chrono::*;
103 /// # let connection = establish_connection();
104 /// # connection.execute("CREATE TABLE timestamps (\"timestamp\"
105 /// # timestamp NOT NULL)")?;
106 /// let christmas_morning = NaiveDate::from_ymd(2017, 12, 25)
107 /// .and_hms(8, 0, 0);
108 /// diesel::insert_into(timestamps)
109 /// .values(timestamp.eq(christmas_morning))
110 /// .execute(&connection)?;
111 ///
112 /// let utc_time = timestamps
113 /// .select(timestamp.at_time_zone("UTC"))
114 /// .first(&connection)?;
115 /// assert_eq!(christmas_morning, utc_time);
116 ///
117 /// let eastern_time = timestamps
118 /// .select(timestamp.at_time_zone("EST"))
119 /// .first(&connection)?;
120 /// let five_hours_later = christmas_morning + Duration::hours(5);
121 /// assert_eq!(five_hours_later, eastern_time);
122 /// # Ok(())
123 /// # }
124 /// #
125 /// # #[cfg(not(all(feature = "postgres", feature = "chrono")))]
126 /// # fn run_test() -> QueryResult<()> {
127 /// # Ok(())
128 /// # }
129 /// ```
130 fn at_time_zone<T>(self, timezone: T) -> AtTimeZone<Self, T::Expression>
131 where
132 T: AsExpression<VarChar>,
133 {
134 AtTimeZone::new(self, timezone.as_expression())
135 }
136}
137
138impl<T: Expression> PgTimestampExpressionMethods for T where T::SqlType: DateTimeLike {}
139
140/// PostgreSQL specific methods present on array expressions.
141pub trait PgArrayExpressionMethods<ST>: Expression<SqlType = Array<ST>> + Sized {
142 /// Creates a PostgreSQL `&&` expression.
143 ///
144 /// This operator returns whether two arrays have common elements.
145 ///
146 /// # Example
147 ///
148 /// ```rust
149 /// # #[macro_use] extern crate diesel;
150 /// # include!("../../doctest_setup.rs");
151 /// #
152 /// # table! {
153 /// # posts {
154 /// # id -> Integer,
155 /// # tags -> Array<VarChar>,
156 /// # }
157 /// # }
158 /// #
159 /// # fn main() {
160 /// # run_test().unwrap();
161 /// # }
162 /// #
163 /// # fn run_test() -> QueryResult<()> {
164 /// # use self::posts::dsl::*;
165 /// # let conn = establish_connection();
166 /// # conn.execute("DROP TABLE IF EXISTS posts").unwrap();
167 /// # conn.execute("CREATE TABLE posts (id SERIAL PRIMARY KEY, tags TEXT[] NOT NULL)").unwrap();
168 /// #
169 /// diesel::insert_into(posts)
170 /// .values(&vec![
171 /// tags.eq(vec!["cool", "awesome"]),
172 /// tags.eq(vec!["awesome", "great"]),
173 /// tags.eq(vec!["cool", "great"]),
174 /// ])
175 /// .execute(&conn)?;
176 ///
177 /// let data = posts.select(id)
178 /// .filter(tags.overlaps_with(vec!["horrid", "cool"]))
179 /// .load::<i32>(&conn)?;
180 /// assert_eq!(vec![1, 3], data);
181 ///
182 /// let data = posts.select(id)
183 /// .filter(tags.overlaps_with(vec!["cool", "great"]))
184 /// .load::<i32>(&conn)?;
185 /// assert_eq!(vec![1, 2, 3], data);
186 ///
187 /// let data = posts.select(id)
188 /// .filter(tags.overlaps_with(vec!["horrid"]))
189 /// .load::<i32>(&conn)?;
190 /// assert!(data.is_empty());
191 /// # Ok(())
192 /// # }
193 /// ```
194 fn overlaps_with<T>(self, other: T) -> OverlapsWith<Self, T::Expression>
195 where
196 T: AsExpression<Self::SqlType>,
197 {
198 OverlapsWith::new(self, other.as_expression())
199 }
200
201 /// Creates a PostgreSQL `@>` expression.
202 ///
203 /// This operator returns whether an array contains another array.
204 ///
205 /// # Example
206 ///
207 /// ```rust
208 /// # #[macro_use] extern crate diesel;
209 /// # include!("../../doctest_setup.rs");
210 /// #
211 /// # table! {
212 /// # posts {
213 /// # id -> Integer,
214 /// # tags -> Array<VarChar>,
215 /// # }
216 /// # }
217 /// #
218 /// # fn main() {
219 /// # run_test().unwrap();
220 /// # }
221 /// #
222 /// # fn run_test() -> QueryResult<()> {
223 /// # use self::posts::dsl::*;
224 /// # let conn = establish_connection();
225 /// # conn.execute("DROP TABLE IF EXISTS posts").unwrap();
226 /// # conn.execute("CREATE TABLE posts (id SERIAL PRIMARY KEY, tags TEXT[] NOT NULL)").unwrap();
227 /// #
228 /// diesel::insert_into(posts)
229 /// .values(tags.eq(vec!["cool", "awesome"]))
230 /// .execute(&conn)?;
231 ///
232 /// let cool_posts = posts.select(id)
233 /// .filter(tags.contains(vec!["cool"]))
234 /// .load::<i32>(&conn)?;
235 /// assert_eq!(vec![1], cool_posts);
236 ///
237 /// let amazing_posts = posts.select(id)
238 /// .filter(tags.contains(vec!["cool", "amazing"]))
239 /// .load::<i32>(&conn)?;
240 /// assert!(amazing_posts.is_empty());
241 /// # Ok(())
242 /// # }
243 /// ```
244 fn contains<T>(self, other: T) -> Contains<Self, T::Expression>
245 where
246 T: AsExpression<Self::SqlType>,
247 {
248 Contains::new(self, other.as_expression())
249 }
250
251 /// Creates a PostgreSQL `<@` expression.
252 ///
253 /// This operator returns whether an array is contained by another array.
254 /// `foo.contains(bar)` is the same as `bar.is_contained_by(foo)`
255 ///
256 /// # Example
257 ///
258 /// ```rust
259 /// # #[macro_use] extern crate diesel;
260 /// # include!("../../doctest_setup.rs");
261 /// #
262 /// # table! {
263 /// # posts {
264 /// # id -> Integer,
265 /// # tags -> Array<VarChar>,
266 /// # }
267 /// # }
268 /// #
269 /// # fn main() {
270 /// # run_test().unwrap();
271 /// # }
272 /// #
273 /// # fn run_test() -> QueryResult<()> {
274 /// # use self::posts::dsl::*;
275 /// # let conn = establish_connection();
276 /// # conn.execute("DROP TABLE IF EXISTS posts").unwrap();
277 /// # conn.execute("CREATE TABLE posts (id SERIAL PRIMARY KEY, tags TEXT[] NOT NULL)").unwrap();
278 /// #
279 /// diesel::insert_into(posts)
280 /// .values(tags.eq(vec!["cool", "awesome"]))
281 /// .execute(&conn)?;
282 ///
283 /// let data = posts.select(id)
284 /// .filter(tags.is_contained_by(vec!["cool", "awesome", "amazing"]))
285 /// .load::<i32>(&conn)?;
286 /// assert_eq!(vec![1], data);
287 ///
288 /// let data = posts.select(id)
289 /// .filter(tags.is_contained_by(vec!["cool"]))
290 /// .load::<i32>(&conn)?;
291 /// assert!(data.is_empty());
292 /// # Ok(())
293 /// # }
294 /// ```
295 fn is_contained_by<T>(self, other: T) -> IsContainedBy<Self, T::Expression>
296 where
297 T: AsExpression<Self::SqlType>,
298 {
299 IsContainedBy::new(self, other.as_expression())
300 }
301}
302
303impl<T, ST> PgArrayExpressionMethods<ST> for T where T: Expression<SqlType = Array<ST>> {}
304
305use expression::operators::{Asc, Desc};
306
307/// PostgreSQL expression methods related to sorting.
308///
309/// This trait is only implemented for `Asc` and `Desc`. Although `.asc` is
310/// implicit if no order is given, you will need to call `.asc()` explicitly in
311/// order to call these methods.
312pub trait PgSortExpressionMethods: Sized {
313 /// Specify that nulls should come before other values in this ordering.
314 ///
315 /// Normally, nulls come last when sorting in ascending order and first
316 /// when sorting in descending order.
317 ///
318 /// # Example
319 ///
320 /// ```rust
321 /// # #[macro_use] extern crate diesel;
322 /// # include!("../../doctest_setup.rs");
323 /// #
324 /// # table! {
325 /// # nullable_numbers (nullable_number) {
326 /// # nullable_number -> Nullable<Integer>,
327 /// # }
328 /// # }
329 /// #
330 /// # fn main() {
331 /// # run_test().unwrap();
332 /// # }
333 /// #
334 /// # fn run_test() -> QueryResult<()> {
335 /// # use self::nullable_numbers::dsl::*;
336 /// # let connection = connection_no_data();
337 /// # connection.execute("CREATE TABLE nullable_numbers (nullable_number INTEGER)")?;
338 /// diesel::insert_into(nullable_numbers)
339 /// .values(&vec![
340 /// nullable_number.eq(None),
341 /// nullable_number.eq(Some(1)),
342 /// nullable_number.eq(Some(2)),
343 /// ])
344 /// .execute(&connection)?;
345 ///
346 /// let asc_default_nulls = nullable_numbers.select(nullable_number)
347 /// .order(nullable_number.asc())
348 /// .load(&connection)?;
349 /// assert_eq!(vec![Some(1), Some(2), None], asc_default_nulls);
350 ///
351 /// let asc_nulls_first = nullable_numbers.select(nullable_number)
352 /// .order(nullable_number.asc().nulls_first())
353 /// .load(&connection)?;
354 /// assert_eq!(vec![None, Some(1), Some(2)], asc_nulls_first);
355 /// # Ok(())
356 /// # }
357 /// ```
358 fn nulls_first(self) -> NullsFirst<Self> {
359 NullsFirst::new(self)
360 }
361
362 /// Specify that nulls should come after other values in this ordering.
363 ///
364 /// Normally, nulls come last when sorting in ascending order and first
365 /// when sorting in descending order.
366 ///
367 /// # Example
368 ///
369 /// ```rust
370 /// # #[macro_use] extern crate diesel;
371 /// # include!("../../doctest_setup.rs");
372 /// #
373 /// # table! {
374 /// # nullable_numbers (nullable_number) {
375 /// # nullable_number -> Nullable<Integer>,
376 /// # }
377 /// # }
378 /// #
379 /// # fn main() {
380 /// # run_test().unwrap();
381 /// # }
382 /// #
383 /// # fn run_test() -> QueryResult<()> {
384 /// # use self::nullable_numbers::dsl::*;
385 /// # let connection = connection_no_data();
386 /// # connection.execute("CREATE TABLE nullable_numbers (nullable_number INTEGER)")?;
387 /// diesel::insert_into(nullable_numbers)
388 /// .values(&vec![
389 /// nullable_number.eq(None),
390 /// nullable_number.eq(Some(1)),
391 /// nullable_number.eq(Some(2)),
392 /// ])
393 /// .execute(&connection)?;
394 ///
395 /// let desc_default_nulls = nullable_numbers.select(nullable_number)
396 /// .order(nullable_number.desc())
397 /// .load(&connection)?;
398 /// assert_eq!(vec![None, Some(2), Some(1)], desc_default_nulls);
399 ///
400 /// let desc_nulls_last = nullable_numbers.select(nullable_number)
401 /// .order(nullable_number.desc().nulls_last())
402 /// .load(&connection)?;
403 /// assert_eq!(vec![Some(2), Some(1), None], desc_nulls_last);
404 /// # Ok(())
405 /// # }
406 /// ```
407 fn nulls_last(self) -> NullsLast<Self> {
408 NullsLast::new(self)
409 }
410}
411
412impl<T> PgSortExpressionMethods for Asc<T> {}
413impl<T> PgSortExpressionMethods for Desc<T> {}
414
415/// PostgreSQL specific methods present on text expressions.
416pub trait PgTextExpressionMethods: Expression + Sized {
417 /// Creates a PostgreSQL `ILIKE` expression
418 ///
419 /// # Example
420 ///
421 /// ```rust
422 /// # #[macro_use] extern crate diesel;
423 /// # include!("../../doctest_setup.rs");
424 /// #
425 /// # fn main() {
426 /// # run_test().unwrap();
427 /// # }
428 /// #
429 /// # fn run_test() -> QueryResult<()> {
430 /// # use schema::animals::dsl::*;
431 /// # let connection = establish_connection();
432 /// let starts_with_s = animals
433 /// .select(species)
434 /// .filter(name.ilike("s%").or(species.ilike("s%")))
435 /// .get_results::<String>(&connection)?;
436 /// assert_eq!(vec!["spider"], starts_with_s);
437 /// # Ok(())
438 /// # }
439 /// ```
440 fn ilike<T: AsExpression<Text>>(self, other: T) -> ILike<Self, T::Expression> {
441 ILike::new(self.as_expression(), other.as_expression())
442 }
443
444 /// Creates a PostgreSQL `NOT ILIKE` expression
445 ///
446 /// # Example
447 ///
448 /// ```rust
449 /// # #[macro_use] extern crate diesel;
450 /// # include!("../../doctest_setup.rs");
451 /// #
452 /// # fn main() {
453 /// # run_test().unwrap();
454 /// # }
455 /// #
456 /// # fn run_test() -> QueryResult<()> {
457 /// # use schema::animals::dsl::*;
458 /// # let connection = establish_connection();
459 /// let doesnt_start_with_s = animals
460 /// .select(species)
461 /// .filter(name.not_ilike("s%").and(species.not_ilike("s%")))
462 /// .get_results::<String>(&connection)?;
463 /// assert_eq!(vec!["dog"], doesnt_start_with_s);
464 /// # Ok(())
465 /// # }
466 /// ```
467 fn not_ilike<T: AsExpression<Text>>(self, other: T) -> NotILike<Self, T::Expression> {
468 NotILike::new(self.as_expression(), other.as_expression())
469 }
470}
471
472#[doc(hidden)]
473/// Marker trait used to implement `PgTextExpressionMethods` on the appropriate
474/// types. Once coherence takes associated types into account, we can remove
475/// this trait.
476pub trait TextOrNullableText {}
477
478impl TextOrNullableText for Text {}
479impl TextOrNullableText for Nullable<Text> {}
480
481impl<T> PgTextExpressionMethods for T
482where
483 T: Expression,
484 T::SqlType: TextOrNullableText,
485{
486}