diesel/query_dsl/mod.rs
1//! Traits that construct SELECT statements
2//!
3//! Traits in this module have methods that generally map to the keyword for the corresponding clause in SQL,
4//! unless it conflicts with a Rust keyword (such as `WHERE`/`where`).
5//!
6//! Methods for constructing queries lives on the [`QueryDsl`] trait.
7//! Methods for executing queries live on [`RunQueryDsl`].
8//!
9//! See also [`expression_methods`][expression_methods] and [`dsl`][dsl].
10//!
11//! [expression_methods]: super::expression_methods
12//! [dsl]: super::dsl
13
14use crate::backend::Backend;
15use crate::connection::Connection;
16use crate::expression::count::CountStar;
17use crate::expression::Expression;
18use crate::helper_types::*;
19use crate::query_builder::locking_clause as lock;
20use crate::query_source::{joins, Table};
21use crate::result::QueryResult;
22
23mod belonging_to_dsl;
24#[doc(hidden)]
25pub mod boxed_dsl;
26mod combine_dsl;
27mod distinct_dsl;
28#[doc(hidden)]
29pub mod filter_dsl;
30pub(crate) mod group_by_dsl;
31mod having_dsl;
32mod join_dsl;
33#[doc(hidden)]
34pub mod limit_dsl;
35#[doc(hidden)]
36pub mod load_dsl;
37mod locking_dsl;
38mod nullable_select_dsl;
39mod offset_dsl;
40pub(crate) mod order_dsl;
41#[doc(hidden)]
42pub mod positional_order_dsl;
43mod save_changes_dsl;
44#[doc(hidden)]
45pub mod select_dsl;
46mod single_value_dsl;
47
48pub use self::belonging_to_dsl::BelongingToDsl;
49pub use self::combine_dsl::CombineDsl;
50pub use self::join_dsl::{InternalJoinDsl, JoinOnDsl, JoinWithImplicitOnClause};
51#[cfg(feature = "i-implement-a-third-party-backend-and-opt-into-breaking-changes")]
52pub use self::load_dsl::CompatibleType;
53#[doc(hidden)]
54pub use self::load_dsl::LoadQuery;
55pub use self::save_changes_dsl::{SaveChangesDsl, UpdateAndFetchResults};
56
57/// The traits used by `QueryDsl`.
58///
59/// Each trait in this module represents exactly one method from `QueryDsl`.
60/// Apps should general rely on `QueryDsl` directly, rather than these traits.
61/// However, generic code may need to include a where clause that references
62/// these traits.
63pub mod methods {
64 pub use super::boxed_dsl::BoxedDsl;
65 pub use super::distinct_dsl::*;
66 #[doc(inline)]
67 pub use super::filter_dsl::*;
68 pub use super::group_by_dsl::GroupByDsl;
69 pub use super::having_dsl::HavingDsl;
70 pub use super::limit_dsl::LimitDsl;
71 pub use super::load_dsl::{ExecuteDsl, LoadQuery};
72 pub use super::locking_dsl::{LockingDsl, ModifyLockDsl};
73 pub use super::nullable_select_dsl::SelectNullableDsl;
74 pub use super::offset_dsl::OffsetDsl;
75 pub use super::order_dsl::{OrderDsl, ThenOrderDsl};
76 pub use super::select_dsl::SelectDsl;
77 pub use super::single_value_dsl::SingleValueDsl;
78
79 #[cfg(all(feature = "with-deprecated", not(feature = "without-deprecated")))]
80 #[doc(hidden)]
81 #[allow(deprecated)]
82 #[deprecated(note = "Use `LoadQuery::RowIter` directly")]
83 pub use super::load_dsl::LoadRet;
84}
85
86/// Methods used to construct select statements.
87pub trait QueryDsl: Sized {
88 /// Adds the `DISTINCT` keyword to a query.
89 ///
90 /// This method will override any previous distinct clause that was present.
91 /// For example, on PostgreSQL, `foo.distinct_on(bar).distinct()` will
92 /// create the same query as `foo.distinct()`.
93 ///
94 /// # Example
95 ///
96 /// ```rust
97 /// # include!("../doctest_setup.rs");
98 /// #
99 /// # fn main() {
100 /// # run_test().unwrap();
101 /// # }
102 /// #
103 /// # fn run_test() -> QueryResult<()> {
104 /// # use schema::users::dsl::*;
105 /// # let connection = &mut establish_connection();
106 /// # diesel::sql_query("DELETE FROM users").execute(connection).unwrap();
107 /// diesel::insert_into(users)
108 /// .values(&vec![name.eq("Sean"); 3])
109 /// .execute(connection)?;
110 /// let names = users.select(name).load::<String>(connection)?;
111 /// let distinct_names = users.select(name).distinct().load::<String>(connection)?;
112 ///
113 /// assert_eq!(vec!["Sean"; 3], names);
114 /// assert_eq!(vec!["Sean"; 1], distinct_names);
115 /// # Ok(())
116 /// # }
117 /// ```
118 fn distinct(self) -> Distinct<Self>
119 where
120 Self: methods::DistinctDsl,
121 {
122 methods::DistinctDsl::distinct(self)
123 }
124
125 /// Adds the `DISTINCT ON` clause to a query.
126 ///
127 /// Diesel performs compile time checks to verify that your `DISTINCT ON`
128 /// clause is compatible to any `ORDER BY` clause used by your query. It
129 /// requires that the first elements for both clauses are the same up to
130 /// the length of the shorter list.
131 /// By default this check allows only up to 5 columns in your `DISTINCT ON`
132 /// or `ORDER BY` clause. You can side step this limitation by wrapping
133 /// your columns same shaped tuples up to the size of 5 elements.
134 ///
135 ///
136 /// # Example
137 ///
138 /// ```rust
139 /// # include!("../doctest_setup.rs");
140 /// # use schema::animals;
141 /// #
142 /// # #[derive(Queryable, Debug, PartialEq)]
143 /// # struct Animal {
144 /// # species: String,
145 /// # name: Option<String>,
146 /// # legs: i32,
147 /// # }
148 /// #
149 /// # impl Animal {
150 /// # fn new<S: Into<String>>(species: S, name: Option<&str>, legs: i32) -> Self {
151 /// # Animal {
152 /// # species: species.into(),
153 /// # name: name.map(Into::into),
154 /// # legs
155 /// # }
156 /// # }
157 /// # }
158 /// #
159 /// # fn main() {
160 /// # use self::animals::dsl::*;
161 /// # let connection = &mut establish_connection();
162 /// # diesel::sql_query("DELETE FROM animals").execute(connection).unwrap();
163 /// diesel::insert_into(animals)
164 /// .values(&vec![
165 /// (species.eq("dog"), name.eq(Some("Jack")), legs.eq(4)),
166 /// (species.eq("dog"), name.eq(None), legs.eq(4)),
167 /// (species.eq("spider"), name.eq(None), legs.eq(8)),
168 /// ])
169 /// .execute(connection)
170 /// .unwrap();
171 /// let all_animals = animals.select((species, name, legs)).load(connection);
172 ///
173 /// // requires that `distinct_on` and `order_by` both start with the
174 /// // same column
175 /// let distinct_animals = animals
176 /// .select((species, name, legs))
177 /// .order_by((species, legs))
178 /// .distinct_on(species)
179 /// .load(connection);
180 ///
181 /// assert_eq!(
182 /// Ok(vec![
183 /// Animal::new("dog", Some("Jack"), 4),
184 /// Animal::new("dog", None, 4),
185 /// Animal::new("spider", None, 8)
186 /// ]),
187 /// all_animals
188 /// );
189 /// assert_eq!(
190 /// Ok(vec![
191 /// Animal::new("dog", Some("Jack"), 4),
192 /// Animal::new("spider", None, 8)
193 /// ]),
194 /// distinct_animals
195 /// );
196 /// # }
197 /// ```
198 #[cfg(feature = "postgres_backend")]
199 fn distinct_on<Expr>(self, expr: Expr) -> DistinctOn<Self, Expr>
200 where
201 Self: methods::DistinctOnDsl<Expr>,
202 {
203 methods::DistinctOnDsl::distinct_on(self, expr)
204 }
205
206 // FIXME: Needs usage example and doc rewrite
207 /// Adds a `SELECT` clause to the query.
208 ///
209 /// If there was already a select clause present, it will be overridden.
210 /// For example, `foo.select(bar).select(baz)` will produce the same
211 /// query as `foo.select(baz)`.
212 ///
213 /// By default, the select clause will be roughly equivalent to `SELECT *`
214 /// (however, Diesel will list all columns to ensure that they are in the
215 /// order we expect).
216 ///
217 /// `select` has slightly stricter bounds on its arguments than other
218 /// methods. In particular, when used with a left outer join, `.nullable`
219 /// must be called on columns that come from the right side of a join. It
220 /// can be called on the column itself, or on an expression containing that
221 /// column. `title.nullable()`, `lower(title).nullable()`, and `(id,
222 /// title).nullable()` would all be valid.
223 ///
224 /// In order to use this method with columns from different tables
225 /// a method like [`.inner_join`] or [`.left_join`] needs to be called before
226 /// calling [`.select`] (See examples below).
227 /// This is because you can only access columns from tables
228 /// that appear in your query before that function call.
229 ///
230 /// **Note:** When using `select` with `group_by`, the `group_by` call must appear
231 /// before the `select` call in the query chain.
232 ///
233 /// [`.inner_join`]: QueryDsl::inner_join()
234 /// [`.left_join`]: QueryDsl::left_join()
235 /// [`.select`]: QueryDsl::select()
236 ///
237 /// # Examples
238 ///
239 /// ```rust
240 /// # include!("../doctest_setup.rs");
241 /// # use schema::users;
242 /// #
243 /// # fn main() {
244 /// # run_test().unwrap();
245 /// # }
246 /// #
247 /// # fn run_test() -> QueryResult<()> {
248 /// # use self::users::dsl::*;
249 /// # let connection = &mut establish_connection();
250 /// // By default, all columns will be selected
251 /// let all_users = users.load::<(i32, String)>(connection)?;
252 /// assert_eq!(
253 /// vec![(1, String::from("Sean")), (2, String::from("Tess"))],
254 /// all_users
255 /// );
256 ///
257 /// let all_names = users.select(name).load::<String>(connection)?;
258 /// assert_eq!(vec!["Sean", "Tess"], all_names);
259 /// # Ok(())
260 /// # }
261 /// ```
262 ///
263 /// ### When used with a left join
264 ///
265 /// ```rust
266 /// # include!("../doctest_setup.rs");
267 /// # use schema::{users, posts};
268 /// #
269 /// # #[derive(Queryable, PartialEq, Eq, Debug)]
270 /// # struct User {
271 /// # id: i32,
272 /// # name: String,
273 /// # }
274 /// #
275 /// # impl User {
276 /// # fn new(id: i32, name: &str) -> Self {
277 /// # User {
278 /// # id,
279 /// # name: name.into(),
280 /// # }
281 /// # }
282 /// # }
283 /// #
284 /// # #[derive(Queryable, PartialEq, Eq, Debug)]
285 /// # struct Post {
286 /// # id: i32,
287 /// # user_id: i32,
288 /// # title: String,
289 /// # }
290 /// #
291 /// # impl Post {
292 /// # fn new(id: i32, user_id: i32, title: &str) -> Self {
293 /// # Post {
294 /// # id,
295 /// # user_id,
296 /// # title: title.into(),
297 /// # }
298 /// # }
299 /// # }
300 /// #
301 /// # fn main() {
302 /// # run_test().unwrap();
303 /// # }
304 /// #
305 /// # fn run_test() -> QueryResult<()> {
306 /// # let connection = &mut establish_connection();
307 /// # diesel::sql_query("DELETE FROM posts").execute(connection)?;
308 /// # diesel::insert_into(posts::table)
309 /// # .values((posts::user_id.eq(1), posts::title.eq("Sean's Post")))
310 /// # .execute(connection)?;
311 /// # let post_id = posts::table.select(posts::id)
312 /// # .first::<i32>(connection)?;
313 /// let join = users::table.left_join(posts::table);
314 ///
315 /// // By default, all columns from both tables are selected.
316 /// // If no explicit select clause is used this means that the result
317 /// // type of this query must contain all fields from the original schema in order.
318 /// let all_data = join.load::<(User, Option<Post>)>(connection)?;
319 /// let expected_data = vec![
320 /// (
321 /// User::new(1, "Sean"),
322 /// Some(Post::new(post_id, 1, "Sean's Post")),
323 /// ),
324 /// (User::new(2, "Tess"), None),
325 /// ];
326 /// assert_eq!(expected_data, all_data);
327 ///
328 /// // Since `posts` is on the right side of a left join, `.nullable` is
329 /// // needed.
330 /// let names_and_titles = join
331 /// .select((users::name, posts::title.nullable()))
332 /// .load::<(String, Option<String>)>(connection)?;
333 /// let expected_data = vec![
334 /// (String::from("Sean"), Some(String::from("Sean's Post"))),
335 /// (String::from("Tess"), None),
336 /// ];
337 /// assert_eq!(expected_data, names_and_titles);
338 /// # Ok(())
339 /// # }
340 /// ```
341 fn select<Selection>(self, selection: Selection) -> Select<Self, Selection>
342 where
343 Selection: Expression,
344 Self: methods::SelectDsl<Selection>,
345 {
346 methods::SelectDsl::select(self, selection)
347 }
348
349 /// Get the count of a query. This is equivalent to `.select(count_star())`
350 ///
351 /// # Example
352 ///
353 /// ```rust
354 /// # include!("../doctest_setup.rs");
355 /// #
356 /// # fn main() {
357 /// # use schema::users::dsl::*;
358 /// # let connection = &mut establish_connection();
359 /// let count = users.count().get_result(connection);
360 /// assert_eq!(Ok(2), count);
361 /// # }
362 /// ```
363 fn count(self) -> Select<Self, CountStar>
364 where
365 Self: methods::SelectDsl<CountStar>,
366 {
367 use crate::dsl::count_star;
368
369 QueryDsl::select(self, count_star())
370 }
371
372 /// Join two tables using a SQL `INNER JOIN`.
373 ///
374 /// If you have invoked [`joinable!`] for the two tables, you can pass that
375 /// table directly. Otherwise you will need to use [`.on`] to specify the `ON`
376 /// clause.
377 ///
378 /// [`joinable!`]: crate::joinable!
379 /// [`.on`]: JoinOnDsl::on()
380 ///
381 /// You can join to as many tables as you'd like in a query, with the
382 /// restriction that no table can appear in the query more than once. For
383 /// tables that appear more than once in a single query the usage of [`alias!`](crate::alias!)
384 /// is required.
385 ///
386 /// You will also need to call [`allow_tables_to_appear_in_same_query!`].
387 /// If you are using `diesel print-schema`, this will
388 /// have been generated for you.
389 /// See the documentation for [`allow_tables_to_appear_in_same_query!`] for
390 /// details.
391 ///
392 /// Diesel expects multi-table joins to be semantically grouped based on the
393 /// relationships. For example, `users.inner_join(posts.inner_join(comments))`
394 /// is not the same as `users.inner_join(posts).inner_join(comments)`. The first
395 /// would deserialize into `(User, (Post, Comment))` and generate the following
396 /// SQL:
397 ///
398 /// ```sql
399 /// SELECT * FROM users
400 /// INNER JOIN (
401 /// posts
402 /// INNER JOIN comments ON comments.post_id = posts.id
403 /// ) ON posts.user_id = users.id
404 /// ```
405 ///
406 /// While the second query would deserialize into `(User, Post, Comment)` and
407 /// generate the following SQL:
408 ///
409 /// ```sql
410 /// SELECT * FROM users
411 /// INNER JOIN posts ON posts.user_id = users.id
412 /// INNER JOIN comments ON comments.user_id = users.id
413 /// ```
414 ///
415 /// The exact generated SQL may change in future diesel version as long as the
416 /// generated query continues to produce same results. The currently generated
417 /// SQL is referred as ["explicit join"](https://www.postgresql.org/docs/current/explicit-joins.html)
418 /// by the PostgreSQL documentation and may have implications on the chosen query plan
419 /// for large numbers of joins in the same query. Checkout the documentation of the
420 /// [`join_collapse_limit` parameter](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-JOIN-COLLAPSE-LIMIT)
421 /// to control this behaviour.
422 ///
423 /// [associations]: crate::associations
424 /// [`allow_tables_to_appear_in_same_query!`]: crate::allow_tables_to_appear_in_same_query!
425 ///
426 /// Note that in order to use this method with [`.select`], you will need to use it before calling
427 /// [`.select`] (See examples below). This is because you can only access columns from tables
428 /// that appear in your query before the call to [`.select`].
429 ///
430 /// [`.select`]: QueryDsl::select()
431 ///
432 /// # Examples
433 ///
434 /// ### With implicit `ON` clause
435 ///
436 /// ```rust
437 /// # include!("../doctest_setup.rs");
438 /// # use schema::{users, posts};
439 /// # /*
440 /// joinable!(posts -> users (user_id));
441 /// allow_tables_to_appear_in_same_query!(users, posts);
442 /// # */
443 ///
444 /// # fn main() {
445 /// # use self::users::dsl::{users, name};
446 /// # use self::posts::dsl::{posts, user_id, title};
447 /// # let connection = &mut establish_connection();
448 /// let data = users.inner_join(posts)
449 /// .select((name, title))
450 /// .load(connection);
451 ///
452 /// let expected_data = vec![
453 /// (String::from("Sean"), String::from("My first post")),
454 /// (String::from("Sean"), String::from("About Rust")),
455 /// (String::from("Tess"), String::from("My first post too")),
456 /// ];
457 /// assert_eq!(Ok(expected_data), data);
458 /// # }
459 /// ```
460 ///
461 /// ### With explicit `ON` clause
462 ///
463 /// ```rust
464 /// # include!("../doctest_setup.rs");
465 /// # use schema::{users, posts};
466 /// #
467 /// # /*
468 /// allow_tables_to_appear_in_same_query!(users, posts);
469 /// # */
470 /// # fn main() {
471 /// # use self::users::dsl::{users, name};
472 /// # use self::posts::dsl::{posts, user_id, title};
473 /// # let connection = &mut establish_connection();
474 /// diesel::insert_into(posts)
475 /// .values(&vec![
476 /// (user_id.eq(1), title.eq("Sean's post")),
477 /// (user_id.eq(2), title.eq("Sean is a jerk")),
478 /// ])
479 /// .execute(connection)
480 /// .unwrap();
481 ///
482 /// let data = users
483 /// .inner_join(posts.on(title.like(name.concat("%"))))
484 /// .select((name, title))
485 /// .load(connection);
486 /// let expected_data = vec![
487 /// (String::from("Sean"), String::from("Sean's post")),
488 /// (String::from("Sean"), String::from("Sean is a jerk")),
489 /// ];
490 /// assert_eq!(Ok(expected_data), data);
491 /// # }
492 /// ```
493 ///
494 /// ### With explicit `ON` clause (struct)
495 ///
496 /// ```rust
497 /// # include!("../doctest_setup.rs");
498 /// # use schema::{users, posts};
499 /// #
500 /// # /*
501 /// allow_tables_to_appear_in_same_query!(users, posts);
502 /// # */
503 /// # fn main() {
504 /// # use self::users::dsl::{users, name};
505 /// # use self::posts::dsl::{posts, user_id, title};
506 /// # let connection = &mut establish_connection();
507 /// #[derive(Debug, PartialEq, Queryable)]
508 /// struct User {
509 /// id: i32,
510 /// name: String,
511 /// }
512 ///
513 /// #[derive(Debug, PartialEq, Queryable)]
514 /// struct Post {
515 /// id: i32,
516 /// user_id: i32,
517 /// title: String,
518 /// }
519 ///
520 /// diesel::insert_into(posts)
521 /// .values(&vec![
522 /// (user_id.eq(1), title.eq("Sean's post")),
523 /// (user_id.eq(2), title.eq("Sean is a jerk")),
524 /// ])
525 /// .execute(connection)
526 /// .unwrap();
527 ///
528 /// // By default, all columns from both tables are selected.
529 /// // If no explicit select clause is used this means that the
530 /// // result type of this query must contain all fields from the
531 /// // original schema in order.
532 /// let data = users
533 /// .inner_join(posts.on(title.like(name.concat("%"))))
534 /// .load::<(User, Post)>(connection); // type could be elided
535 /// let expected_data = vec![
536 /// (
537 /// User {
538 /// id: 1,
539 /// name: String::from("Sean"),
540 /// },
541 /// Post {
542 /// id: 4,
543 /// user_id: 1,
544 /// title: String::from("Sean's post"),
545 /// },
546 /// ),
547 /// (
548 /// User {
549 /// id: 1,
550 /// name: String::from("Sean"),
551 /// },
552 /// Post {
553 /// id: 5,
554 /// user_id: 2,
555 /// title: String::from("Sean is a jerk"),
556 /// },
557 /// ),
558 /// ];
559 /// assert_eq!(Ok(expected_data), data);
560 /// # }
561 /// ```
562 fn inner_join<Rhs>(self, rhs: Rhs) -> InnerJoin<Self, Rhs>
563 where
564 Self: JoinWithImplicitOnClause<Rhs, joins::Inner>,
565 {
566 self.join_with_implicit_on_clause(rhs, joins::Inner)
567 }
568
569 /// Join two tables using a SQL `LEFT OUTER JOIN`.
570 ///
571 /// Behaves similarly to [`inner_join`], but will produce a left join
572 /// instead. See [`inner_join`] for usage examples.
573 ///
574 /// [`inner_join`]: QueryDsl::inner_join()
575 ///
576 /// Columns in the right hand table will become `Nullable` which means
577 /// you must call `nullable()` on the corresponding fields in the select
578 /// clause:
579 ///
580 /// ### Selecting after a left join
581 ///
582 /// ```rust
583 /// # include!("../doctest_setup.rs");
584 /// # use schema::{users, posts};
585 /// #
586 /// # #[derive(Queryable, PartialEq, Eq, Debug)]
587 /// # struct User {
588 /// # id: i32,
589 /// # name: String,
590 /// # }
591 /// #
592 /// # impl User {
593 /// # fn new(id: i32, name: &str) -> Self {
594 /// # User {
595 /// # id,
596 /// # name: name.into(),
597 /// # }
598 /// # }
599 /// # }
600 /// #
601 /// # #[derive(Queryable, PartialEq, Eq, Debug)]
602 /// # struct Post {
603 /// # id: i32,
604 /// # user_id: i32,
605 /// # title: String,
606 /// # }
607 /// #
608 /// # impl Post {
609 /// # fn new(id: i32, user_id: i32, title: &str) -> Self {
610 /// # Post {
611 /// # id,
612 /// # user_id,
613 /// # title: title.into(),
614 /// # }
615 /// # }
616 /// # }
617 /// #
618 /// # fn main() {
619 /// # run_test().unwrap();
620 /// # }
621 /// #
622 /// # fn run_test() -> QueryResult<()> {
623 /// # let connection = &mut establish_connection();
624 /// # diesel::sql_query("DELETE FROM posts").execute(connection)?;
625 /// # diesel::insert_into(posts::table)
626 /// # .values((posts::user_id.eq(1), posts::title.eq("Sean's Post")))
627 /// # .execute(connection)?;
628 /// # let post_id = posts::table.select(posts::id)
629 /// # .first::<i32>(connection)?;
630 /// let join = users::table.left_join(posts::table);
631 ///
632 /// // Since `posts` is on the right side of a left join, `.nullable` is
633 /// // needed.
634 /// let names_and_titles = join
635 /// .select((users::name, posts::title.nullable()))
636 /// .load::<(String, Option<String>)>(connection)?;
637 /// let expected_data = vec![
638 /// (String::from("Sean"), Some(String::from("Sean's Post"))),
639 /// (String::from("Tess"), None),
640 /// ];
641 /// assert_eq!(expected_data, names_and_titles);
642 /// # Ok(())
643 /// # }
644 /// ```
645 fn left_outer_join<Rhs>(self, rhs: Rhs) -> LeftJoin<Self, Rhs>
646 where
647 Self: JoinWithImplicitOnClause<Rhs, joins::LeftOuter>,
648 {
649 self.join_with_implicit_on_clause(rhs, joins::LeftOuter)
650 }
651
652 /// Alias for [`left_outer_join`].
653 ///
654 /// [`left_outer_join`]: QueryDsl::left_outer_join()
655 fn left_join<Rhs>(self, rhs: Rhs) -> LeftJoin<Self, Rhs>
656 where
657 Self: JoinWithImplicitOnClause<Rhs, joins::LeftOuter>,
658 {
659 self.left_outer_join(rhs)
660 }
661
662 /// Adds to the `WHERE` clause of a query.
663 ///
664 /// If there is already a `WHERE` clause, the result will be `old AND new`.
665 ///
666 /// Note that in order to use this method with columns from different tables, you need to call
667 /// [`.inner_join`] or [`.left_join`] beforehand.
668 /// This is because you can only access columns from tables
669 /// that appear in your query before the call to [`.filter`].
670 ///
671 /// [`.inner_join`]: QueryDsl::inner_join()
672 /// [`.left_join`]: QueryDsl::left_join()
673 /// [`.filter`]: QueryDsl::filter()
674 ///
675 /// # Example:
676 ///
677 /// ```rust
678 /// # include!("../doctest_setup.rs");
679 /// #
680 /// # fn main() {
681 /// # use schema::users::dsl::*;
682 /// # let connection = &mut establish_connection();
683 /// let seans_id = users.filter(name.eq("Sean")).select(id).first(connection);
684 /// assert_eq!(Ok(1), seans_id);
685 /// let tess_id = users.filter(name.eq("Tess")).select(id).first(connection);
686 /// assert_eq!(Ok(2), tess_id);
687 /// # }
688 /// ```
689 #[doc(alias = "where")]
690 fn filter<Predicate>(self, predicate: Predicate) -> Filter<Self, Predicate>
691 where
692 Self: methods::FilterDsl<Predicate>,
693 {
694 methods::FilterDsl::filter(self, predicate)
695 }
696
697 /// Adds to the `WHERE` clause of a query using `OR`
698 ///
699 /// If there is already a `WHERE` clause, the result will be `(old OR new)`.
700 /// Calling `foo.filter(bar).or_filter(baz)`
701 /// is identical to `foo.filter(bar.or(baz))`.
702 /// However, the second form is much harder to do dynamically.
703 ///
704 /// # Example:
705 ///
706 /// ```rust
707 /// # include!("../doctest_setup.rs");
708 /// #
709 /// # fn main() {
710 /// # run_test().unwrap();
711 /// # }
712 /// #
713 /// # fn run_test() -> QueryResult<()> {
714 /// # use schema::animals::dsl::*;
715 /// # let connection = &mut establish_connection();
716 /// # diesel::delete(animals).execute(connection)?;
717 /// diesel::insert_into(animals)
718 /// .values(&vec![
719 /// (species.eq("cat"), legs.eq(4), name.eq("Sinatra")),
720 /// (species.eq("dog"), legs.eq(3), name.eq("Fido")),
721 /// (species.eq("spider"), legs.eq(8), name.eq("Charlotte")),
722 /// ])
723 /// .execute(connection)?;
724 ///
725 /// let good_animals = animals
726 /// .filter(name.eq("Fido"))
727 /// .or_filter(legs.eq(4))
728 /// .select(name)
729 /// .get_results::<Option<String>>(connection)?;
730 /// let expected = vec![Some(String::from("Sinatra")), Some(String::from("Fido"))];
731 /// assert_eq!(expected, good_animals);
732 /// # Ok(())
733 /// # }
734 /// ```
735 #[doc(alias = "where")]
736 fn or_filter<Predicate>(self, predicate: Predicate) -> OrFilter<Self, Predicate>
737 where
738 Self: methods::OrFilterDsl<Predicate>,
739 {
740 methods::OrFilterDsl::or_filter(self, predicate)
741 }
742
743 /// Attempts to find a single record from the given table by primary key.
744 ///
745 /// # Example
746 ///
747 /// ```rust
748 /// # include!("../doctest_setup.rs");
749 /// #
750 /// # fn main() {
751 /// # use schema::users::dsl::*;
752 /// # use diesel::result::Error::NotFound;
753 /// # let connection = &mut establish_connection();
754 /// let sean = (1, "Sean".to_string());
755 /// let tess = (2, "Tess".to_string());
756 /// assert_eq!(Ok(sean), users.find(1).first(connection));
757 /// assert_eq!(Ok(tess), users.find(2).first(connection));
758 /// assert_eq!(
759 /// Err::<(i32, String), _>(NotFound),
760 /// users.find(3).first(connection)
761 /// );
762 /// # }
763 /// ```
764 fn find<PK>(self, id: PK) -> Find<Self, PK>
765 where
766 Self: methods::FindDsl<PK>,
767 {
768 methods::FindDsl::find(self, id)
769 }
770
771 /// Sets the order clause of a query.
772 ///
773 /// If there was already an order clause, it will be overridden. See
774 /// also:
775 /// [`.desc()`](crate::expression_methods::ExpressionMethods::desc())
776 /// and
777 /// [`.asc()`](crate::expression_methods::ExpressionMethods::asc())
778 ///
779 /// Ordering by multiple columns can be achieved by passing a tuple of those
780 /// columns.
781 /// To construct an order clause of an unknown number of columns,
782 /// see [`QueryDsl::then_order_by`](QueryDsl::then_order_by())
783 ///
784 /// If you combine a `ORDER BY` clause with a [`DISTINCT ON`](QueryDsl::distinct_on())
785 /// clause you need to make sure that the columns used in both clauses are
786 /// the same up to the number of elements in the shorter of both clauses.
787 /// Diesel imposes a limit of 5 columns for this check. If you need to
788 /// have more order by expressions make sure to use `.order_by`
789 /// for the columns used by the `DISTINCT ON` clause and add additional
790 /// columns via [`QueryDsl::then_order_by`](QueryDsl::then_order_by())
791 ///
792 /// # Examples
793 ///
794 /// ```rust
795 /// # include!("../doctest_setup.rs");
796 /// #
797 /// # fn main() {
798 /// # run_test();
799 /// # }
800 /// #
801 /// # fn run_test() -> QueryResult<()> {
802 /// # use schema::users::dsl::*;
803 /// # use schema::{comments, posts};
804 /// # let connection = &mut establish_connection();
805 /// # diesel::sql_query("DELETE FROM users").execute(connection)?;
806 /// diesel::insert_into(users)
807 /// .values(&vec![name.eq("Saul"), name.eq("Steve"), name.eq("Stan")])
808 /// .execute(connection)?;
809 ///
810 /// let ordered_names = users
811 /// .select(name)
812 /// .order(name.desc())
813 /// .load::<String>(connection)?;
814 /// assert_eq!(vec!["Steve", "Stan", "Saul"], ordered_names);
815 ///
816 /// diesel::insert_into(users)
817 /// .values(name.eq("Stan"))
818 /// .execute(connection)?;
819 ///
820 /// let data = users
821 /// .select((name, id))
822 /// .order((name.asc(), id.desc()))
823 /// .load(connection)?;
824 /// let expected_data = vec![
825 /// (String::from("Saul"), 3),
826 /// (String::from("Stan"), 6),
827 /// (String::from("Stan"), 5),
828 /// (String::from("Steve"), 4),
829 /// ];
830 /// assert_eq!(expected_data, data);
831 ///
832 /// # #[cfg(feature = "postgres")]
833 /// let data = users
834 /// .inner_join(posts::table.inner_join(comments::table.on(comments::post_id.eq(posts::id))))
835 /// # .select((name, id))
836 /// .distinct_on(id)
837 /// .order_by(id)
838 /// .then_order_by((
839 /// posts::id,
840 /// posts::user_id,
841 /// posts::title,
842 /// comments::id,
843 /// comments::body,
844 /// comments::post_id,
845 /// ))
846 /// .load(connection)?;
847 /// # let _: Vec<(String, i32)> = data;
848 /// # Ok(())
849 /// # }
850 /// ```
851 fn order<Expr>(self, expr: Expr) -> Order<Self, Expr>
852 where
853 Expr: Expression,
854 Self: methods::OrderDsl<Expr>,
855 {
856 methods::OrderDsl::order(self, expr)
857 }
858
859 /// Alias for `order`
860 fn order_by<Expr>(self, expr: Expr) -> OrderBy<Self, Expr>
861 where
862 Expr: Expression,
863 Self: methods::OrderDsl<Expr>,
864 {
865 QueryDsl::order(self, expr)
866 }
867
868 /// Appends to the `ORDER BY` clause of this SQL query.
869 ///
870 /// Unlike `.order`, this method will append rather than replace.
871 /// In other words,
872 /// `.order_by(foo).order_by(bar)` is equivalent to `.order_by(bar)`.
873 /// In contrast,
874 /// `.order_by(foo).then_order_by(bar)` is equivalent to `.order((foo, bar))`.
875 ///
876 /// # Examples
877 ///
878 /// ```rust
879 /// # include!("../doctest_setup.rs");
880 /// #
881 /// # fn main() {
882 /// # run_test();
883 /// # }
884 /// #
885 /// # fn run_test() -> QueryResult<()> {
886 /// # use schema::users::dsl::*;
887 /// # let connection = &mut establish_connection();
888 /// # diesel::sql_query("DELETE FROM users").execute(connection)?;
889 /// diesel::insert_into(users)
890 /// .values(&vec![
891 /// name.eq("Saul"),
892 /// name.eq("Steve"),
893 /// name.eq("Stan"),
894 /// name.eq("Stan"),
895 /// ])
896 /// .execute(connection)?;
897 ///
898 /// let data = users
899 /// .select((name, id))
900 /// .order_by(name.asc())
901 /// .then_order_by(id.desc())
902 /// .load(connection)?;
903 /// let expected_data = vec![
904 /// (String::from("Saul"), 3),
905 /// (String::from("Stan"), 6),
906 /// (String::from("Stan"), 5),
907 /// (String::from("Steve"), 4),
908 /// ];
909 /// assert_eq!(expected_data, data);
910 /// # Ok(())
911 /// # }
912 /// ```
913 fn then_order_by<Order>(self, order: Order) -> ThenOrderBy<Self, Order>
914 where
915 Self: methods::ThenOrderDsl<Order>,
916 {
917 methods::ThenOrderDsl::then_order_by(self, order)
918 }
919
920 /// Sets the limit clause of the query.
921 ///
922 /// If there was already a limit clause, it will be overridden.
923 ///
924 /// # Example
925 ///
926 /// ```rust
927 /// # include!("../doctest_setup.rs");
928 /// # use schema::users;
929 /// #
930 /// # fn main() {
931 /// # run_test().unwrap();
932 /// # }
933 /// #
934 /// # fn run_test() -> QueryResult<()> {
935 /// # use self::users::dsl::*;
936 /// # let connection = &mut establish_connection();
937 /// # diesel::delete(users).execute(connection)?;
938 /// # diesel::insert_into(users)
939 /// # .values(&vec![
940 /// # name.eq("Sean"),
941 /// # name.eq("Bastien"),
942 /// # name.eq("Pascal"),
943 /// # ])
944 /// # .execute(connection)?;
945 /// #
946 /// // Using a limit
947 /// let limited = users
948 /// .select(name)
949 /// .order(id)
950 /// .limit(1)
951 /// .load::<String>(connection)?;
952 ///
953 /// // Without a limit
954 /// let no_limit = users.select(name).order(id).load::<String>(connection)?;
955 ///
956 /// assert_eq!(vec!["Sean"], limited);
957 /// assert_eq!(vec!["Sean", "Bastien", "Pascal"], no_limit);
958 /// # Ok(())
959 /// # }
960 /// ```
961 fn limit(self, limit: i64) -> Limit<Self>
962 where
963 Self: methods::LimitDsl,
964 {
965 methods::LimitDsl::limit(self, limit)
966 }
967
968 /// Sets the offset clause of the query.
969 ///
970 /// If there was already a offset clause, it will be overridden.
971 ///
972 /// # Example
973 ///
974 /// ```rust
975 /// # include!("../doctest_setup.rs");
976 /// # use schema::users;
977 /// #
978 /// # fn main() {
979 /// # run_test().unwrap();
980 /// # }
981 /// #
982 /// # fn run_test() -> QueryResult<()> {
983 /// # use self::users::dsl::*;
984 /// # let connection = &mut establish_connection();
985 /// # diesel::delete(users).execute(connection)?;
986 /// # diesel::insert_into(users)
987 /// # .values(&vec![
988 /// # name.eq("Sean"),
989 /// # name.eq("Bastien"),
990 /// # name.eq("Pascal"),
991 /// # ])
992 /// # .execute(connection)?;
993 /// #
994 /// // Using an offset
995 /// let offset = users
996 /// .select(name)
997 /// .order(id)
998 /// .limit(2)
999 /// .offset(1)
1000 /// .load::<String>(connection)?;
1001 ///
1002 /// // No Offset
1003 /// let no_offset = users
1004 /// .select(name)
1005 /// .order(id)
1006 /// .limit(2)
1007 /// .load::<String>(connection)?;
1008 ///
1009 /// assert_eq!(vec!["Bastien", "Pascal"], offset);
1010 /// assert_eq!(vec!["Sean", "Bastien"], no_offset);
1011 /// # Ok(())
1012 /// # }
1013 /// ```
1014 fn offset(self, offset: i64) -> Offset<Self>
1015 where
1016 Self: methods::OffsetDsl,
1017 {
1018 methods::OffsetDsl::offset(self, offset)
1019 }
1020
1021 /// Sets the `group by` clause of a query.
1022 ///
1023 /// **Note:** Queries having a `group by` clause require a custom select clause.
1024 /// Use [`QueryDsl::select()`] to specify one.
1025 ///
1026 /// **Note:** When using `group_by` with `select`, the `group_by` call must appear
1027 /// before the `select` call in the query chain.
1028 ///
1029 /// If there was already a group by clause, it will be overridden.
1030 /// Grouping by multiple columns can be achieved by passing a tuple of those
1031 /// columns.
1032 ///
1033 /// Diesel follows postgresql's group by semantic, this means any column
1034 /// appearing in a group by clause is considered to be aggregated. If a
1035 /// primary key is part of the group by clause every column from the
1036 /// corresponding table is considered to be aggregated. Select clauses
1037 /// cannot mix aggregated and non aggregated expressions.
1038 ///
1039 /// For group by clauses containing columns from more than one table it
1040 /// is required to call [`allow_columns_to_appear_in_same_group_by_clause!`]
1041 ///
1042 /// [`allow_columns_to_appear_in_same_group_by_clause!`]: crate::allow_columns_to_appear_in_same_group_by_clause!
1043 ///
1044 /// # Examples
1045 /// ```rust
1046 /// # include!("../doctest_setup.rs");
1047 /// # fn main() {
1048 /// # run_test();
1049 /// # }
1050 /// #
1051 /// # fn run_test() -> QueryResult<()> {
1052 /// # use crate::schema::{users, posts};
1053 /// # use diesel::dsl::count;
1054 /// # let connection = &mut establish_connection();
1055 /// let data = users::table
1056 /// .inner_join(posts::table)
1057 /// .group_by(users::id)
1058 /// .select((users::name, count(posts::id)))
1059 /// # .order_by(users::id.asc())
1060 /// .load::<(String, i64)>(connection)?;
1061 ///
1062 /// assert_eq!(
1063 /// vec![(String::from("Sean"), 2), (String::from("Tess"), 1)],
1064 /// data
1065 /// );
1066 /// # Ok(())
1067 /// # }
1068 /// ```
1069 fn group_by<GB>(self, group_by: GB) -> GroupBy<Self, GB>
1070 where
1071 GB: Expression,
1072 Self: methods::GroupByDsl<GB>,
1073 {
1074 methods::GroupByDsl::group_by(self, group_by)
1075 }
1076
1077 /// Adds to the `HAVING` clause of a query.
1078 ///
1079 /// # Examples
1080 /// ```rust
1081 /// # include!("../doctest_setup.rs");
1082 /// # fn main() {
1083 /// # run_test();
1084 /// # }
1085 /// #
1086 /// # fn run_test() -> QueryResult<()> {
1087 /// # use crate::schema::{users, posts};
1088 /// # use diesel::dsl::count;
1089 /// # let connection = &mut establish_connection();
1090 /// let data = users::table
1091 /// .inner_join(posts::table)
1092 /// .group_by(users::id)
1093 /// .having(count(posts::id).gt(1))
1094 /// .select((users::name, count(posts::id)))
1095 /// .load::<(String, i64)>(connection)?;
1096 ///
1097 /// assert_eq!(vec![(String::from("Sean"), 2)], data);
1098 /// # Ok(())
1099 /// # }
1100 /// ```
1101 fn having<Predicate>(self, predicate: Predicate) -> Having<Self, Predicate>
1102 where
1103 Self: methods::HavingDsl<Predicate>,
1104 {
1105 methods::HavingDsl::having(self, predicate)
1106 }
1107
1108 /// Adds `FOR UPDATE` to the end of the select statement.
1109 ///
1110 /// This method is only available for MySQL and PostgreSQL. SQLite does not
1111 /// provide any form of row locking.
1112 ///
1113 /// Additionally, `.for_update` cannot be used on queries with a distinct
1114 /// clause, group by clause, having clause, or any unions. Queries with
1115 /// a `FOR UPDATE` clause cannot be boxed.
1116 ///
1117 /// # Example
1118 ///
1119 /// ```
1120 /// # include!("../doctest_setup.rs");
1121 /// # fn main() {
1122 /// # run_test();
1123 /// # }
1124 /// #
1125 /// # #[cfg(any(feature = "mysql", feature = "postgres"))]
1126 /// # fn run_test() -> QueryResult<()> {
1127 /// # use crate::schema::users;
1128 /// # let connection = &mut establish_connection();
1129 /// // Executes `SELECT * FROM users FOR UPDATE`
1130 /// let users_for_update = users::table.for_update().load(connection)?;
1131 /// # let u: Vec<(i32, String)> = users_for_update;
1132 /// # Ok(())
1133 /// # }
1134 /// # #[cfg(feature = "sqlite")]
1135 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1136 /// ```
1137 fn for_update(self) -> ForUpdate<Self>
1138 where
1139 Self: methods::LockingDsl<lock::ForUpdate>,
1140 {
1141 methods::LockingDsl::with_lock(self, lock::ForUpdate)
1142 }
1143
1144 /// Adds `FOR NO KEY UPDATE` to the end of the select statement.
1145 ///
1146 /// This method is only available for PostgreSQL. SQLite does not
1147 /// provide any form of row locking, and MySQL does not support anything
1148 /// finer than row-level locking.
1149 ///
1150 /// Additionally, `.for_no_key_update` cannot be used on queries with a distinct
1151 /// clause, group by clause, having clause, or any unions. Queries with
1152 /// a `FOR NO KEY UPDATE` clause cannot be boxed.
1153 ///
1154 /// # Example
1155 ///
1156 /// ```
1157 /// # include!("../doctest_setup.rs");
1158 /// # fn main() {
1159 /// # run_test();
1160 /// # }
1161 /// #
1162 /// # #[cfg(feature = "postgres")]
1163 /// # fn run_test() -> QueryResult<()> {
1164 /// # use crate::schema::users;
1165 /// # let connection = &mut establish_connection();
1166 /// // Executes `SELECT * FROM users FOR NO KEY UPDATE`
1167 /// let users_for_no_key_update = users::table.for_no_key_update().load(connection)?;
1168 /// # let u: Vec<(i32, String)> = users_for_no_key_update;
1169 /// # Ok(())
1170 /// # }
1171 /// # #[cfg(not(feature = "postgres"))]
1172 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1173 /// ```
1174 fn for_no_key_update(self) -> ForNoKeyUpdate<Self>
1175 where
1176 Self: methods::LockingDsl<lock::ForNoKeyUpdate>,
1177 {
1178 methods::LockingDsl::with_lock(self, lock::ForNoKeyUpdate)
1179 }
1180
1181 /// Adds `FOR SHARE` to the end of the select statement.
1182 ///
1183 /// This method is only available for MySQL and PostgreSQL. SQLite does not
1184 /// provide any form of row locking.
1185 ///
1186 /// Additionally, `.for_share` cannot be used on queries with a distinct
1187 /// clause, group by clause, having clause, or any unions. Queries with
1188 /// a `FOR SHARE` clause cannot be boxed.
1189 ///
1190 /// # Example
1191 ///
1192 /// ```
1193 /// # include!("../doctest_setup.rs");
1194 /// # fn main() {
1195 /// # run_test();
1196 /// # }
1197 /// #
1198 /// # #[cfg(any(feature = "mysql", feature = "postgres"))]
1199 /// # fn run_test() -> QueryResult<()> {
1200 /// # use crate::schema::users;
1201 /// # let connection = &mut establish_connection();
1202 /// // Executes `SELECT * FROM users FOR SHARE`
1203 /// let users_for_share = users::table.for_share().load(connection)?;
1204 /// # let u: Vec<(i32, String)> = users_for_share;
1205 /// # Ok(())
1206 /// # }
1207 /// # #[cfg(feature = "sqlite")]
1208 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1209 /// ```
1210 fn for_share(self) -> ForShare<Self>
1211 where
1212 Self: methods::LockingDsl<lock::ForShare>,
1213 {
1214 methods::LockingDsl::with_lock(self, lock::ForShare)
1215 }
1216
1217 /// Adds `FOR KEY SHARE` to the end of the select statement.
1218 ///
1219 /// This method is only available for PostgreSQL. SQLite does not
1220 /// provide any form of row locking, and MySQL does not support anything
1221 /// finer than row-level locking.
1222 ///
1223 /// Additionally, `.for_key_share` cannot be used on queries with a distinct
1224 /// clause, group by clause, having clause, or any unions. Queries with
1225 /// a `FOR KEY SHARE` clause cannot be boxed.
1226 ///
1227 /// # Example
1228 ///
1229 /// ```
1230 /// # include!("../doctest_setup.rs");
1231 /// # fn main() {
1232 /// # run_test();
1233 /// # }
1234 ///
1235 /// # #[cfg(feature = "postgres")]
1236 /// # fn run_test() -> QueryResult<()> {
1237 /// # use crate::schema::users;
1238 /// # let connection = &mut establish_connection();
1239 /// // Executes `SELECT * FROM users FOR KEY SHARE`
1240 /// let users_for_key_share = users::table.for_key_share().load(connection)?;
1241 /// # let u: Vec<(i32, String)> = users_for_key_share;
1242 /// # Ok(())
1243 /// # }
1244 /// # #[cfg(not(feature = "postgres"))]
1245 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1246 /// ```
1247 fn for_key_share(self) -> ForKeyShare<Self>
1248 where
1249 Self: methods::LockingDsl<lock::ForKeyShare>,
1250 {
1251 methods::LockingDsl::with_lock(self, lock::ForKeyShare)
1252 }
1253
1254 /// Adds `SKIP LOCKED` to the end of a `FOR UPDATE` clause.
1255 ///
1256 /// This modifier is only supported in PostgreSQL 9.5+ and MySQL 8+.
1257 ///
1258 /// # Example
1259 ///
1260 /// ```
1261 /// # include!("../doctest_setup.rs");
1262 /// # fn main() {
1263 /// # run_test();
1264 /// # }
1265 /// #
1266 /// # #[cfg(any(feature = "postgres", feature = "mysql"))]
1267 /// # fn run_test() -> QueryResult<()> {
1268 /// # use crate::schema::users;
1269 /// # let connection = &mut establish_connection();
1270 /// // Executes `SELECT * FROM users FOR UPDATE SKIP LOCKED`
1271 /// let user_skipped_locked = users::table.for_update().skip_locked().load(connection)?;
1272 /// # let u: Vec<(i32, String)> = user_skipped_locked;
1273 /// # Ok(())
1274 /// # }
1275 /// # #[cfg(feature = "sqlite")]
1276 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1277 /// ```
1278 fn skip_locked(self) -> SkipLocked<Self>
1279 where
1280 Self: methods::ModifyLockDsl<lock::SkipLocked>,
1281 {
1282 methods::ModifyLockDsl::modify_lock(self, lock::SkipLocked)
1283 }
1284
1285 /// Adds `NOWAIT` to the end of a `FOR UPDATE` clause.
1286 ///
1287 /// This modifier is only supported in PostgreSQL 9.5+ and MySQL 8+.
1288 ///
1289 /// # Example
1290 ///
1291 /// ```
1292 /// # include!("../doctest_setup.rs");
1293 /// # fn main() {
1294 /// # run_test();
1295 /// # }
1296 /// #
1297 /// # #[cfg(any(feature = "mysql", feature = "postgres"))]
1298 /// # fn run_test() -> QueryResult<()> {
1299 /// # use crate::schema::users;
1300 /// # let connection = &mut establish_connection();
1301 /// // Executes `SELECT * FROM users FOR UPDATE NOWAIT`
1302 /// let users_no_wait = users::table.for_update().no_wait().load(connection)?;
1303 /// # let u: Vec<(i32, String)> = users_no_wait;
1304 /// # Ok(())
1305 /// # }
1306 /// # #[cfg(feature = "sqlite")]
1307 /// # fn run_test() -> QueryResult<()> { Ok(()) }
1308 /// ```
1309 fn no_wait(self) -> NoWait<Self>
1310 where
1311 Self: methods::ModifyLockDsl<lock::NoWait>,
1312 {
1313 methods::ModifyLockDsl::modify_lock(self, lock::NoWait)
1314 }
1315
1316 /// Boxes the pieces of a query into a single type.
1317 ///
1318 /// This is useful for cases where you want to conditionally modify a query,
1319 /// but need the type to remain the same. The backend must be specified as
1320 /// part of this. It is not possible to box a query and have it be useable
1321 /// on multiple backends.
1322 ///
1323 /// A boxed query will incur a minor performance penalty, as the query builder
1324 /// can no longer be inlined by the compiler. For most applications this cost
1325 /// will be minimal.
1326 ///
1327 /// ### Example
1328 ///
1329 /// ```rust
1330 /// # include!("../doctest_setup.rs");
1331 /// # use schema::users;
1332 /// #
1333 /// # fn main() {
1334 /// # use std::collections::HashMap;
1335 /// # let connection = &mut establish_connection();
1336 /// # let mut params = HashMap::new();
1337 /// # params.insert("name", "Sean");
1338 /// let mut query = users::table.into_boxed();
1339 /// if let Some(name) = params.get("name") {
1340 /// query = query.filter(users::name.eq(name));
1341 /// }
1342 /// let users = query.load(connection);
1343 /// # let expected = vec![(1, String::from("Sean"))];
1344 /// # assert_eq!(Ok(expected), users);
1345 /// # }
1346 /// ```
1347 ///
1348 /// Diesel queries also have a similar problem to [`Iterator`], where
1349 /// returning them from a function requires exposing the implementation of that
1350 /// function. The [`helper_types`][helper_types] module exists to help with this,
1351 /// but you might want to hide the return type or have it conditionally change.
1352 /// Boxing can achieve both.
1353 ///
1354 /// [helper_types]: crate::helper_types
1355 ///
1356 /// ### Example
1357 ///
1358 /// ```rust
1359 /// # include!("../doctest_setup.rs");
1360 /// # use schema::users;
1361 /// #
1362 /// # fn main() {
1363 /// # let connection = &mut establish_connection();
1364 /// fn users_by_name(name: &str) -> users::BoxedQuery<DB> {
1365 /// users::table.filter(users::name.eq(name)).into_boxed()
1366 /// }
1367 ///
1368 /// assert_eq!(
1369 /// Ok(1),
1370 /// users_by_name("Sean").select(users::id).first(connection)
1371 /// );
1372 /// assert_eq!(
1373 /// Ok(2),
1374 /// users_by_name("Tess").select(users::id).first(connection)
1375 /// );
1376 /// # }
1377 /// ```
1378 fn into_boxed<'a, DB>(self) -> IntoBoxed<'a, Self, DB>
1379 where
1380 DB: Backend,
1381 Self: methods::BoxedDsl<'a, DB>,
1382 {
1383 methods::BoxedDsl::internal_into_boxed(self)
1384 }
1385
1386 /// Wraps this select statement in parenthesis, allowing it to be used
1387 /// as an expression.
1388 ///
1389 /// SQL allows queries such as `foo = (SELECT ...)`, as long as the
1390 /// subselect returns only a single column, and 0 or 1 rows. This method
1391 /// indicates that you expect the query to only return a single value (this
1392 /// will be enforced by adding `LIMIT 1`).
1393 ///
1394 /// The SQL type of this will always be `Nullable`, as the query returns
1395 /// `NULL` if the table is empty or it otherwise returns 0 rows.
1396 ///
1397 /// # Example
1398 ///
1399 /// ```rust
1400 /// # include!("../doctest_setup.rs");
1401 /// #
1402 /// # fn main() {
1403 /// # run_test();
1404 /// # }
1405 /// #
1406 /// # fn run_test() -> QueryResult<()> {
1407 /// # use diesel::insert_into;
1408 /// # use schema::users::dsl::*;
1409 /// # use schema::posts;
1410 /// # let connection = &mut establish_connection();
1411 /// insert_into(posts::table)
1412 /// .values(posts::user_id.eq(1))
1413 /// .execute(connection)?;
1414 /// let last_post = posts::table.order(posts::id.desc());
1415 /// let most_recently_active_user = users
1416 /// .select(name)
1417 /// .filter(
1418 /// id.nullable()
1419 /// .eq(last_post.select(posts::user_id).single_value()),
1420 /// )
1421 /// .first::<String>(connection)?;
1422 /// assert_eq!("Sean", most_recently_active_user);
1423 /// # Ok(())
1424 /// # }
1425 /// ```
1426 fn single_value(self) -> SingleValue<Self>
1427 where
1428 Self: methods::SingleValueDsl,
1429 {
1430 methods::SingleValueDsl::single_value(self)
1431 }
1432
1433 /// Coerce the SQL type of the select clause to it's nullable equivalent.
1434 ///
1435 /// This is useful for writing queries that contain subselects on non null
1436 /// fields comparing them to nullable fields.
1437 /// ```rust
1438 /// # include!("../doctest_setup.rs");
1439 /// #
1440 /// # fn main() {
1441 /// # run_test();
1442 /// # }
1443 /// #
1444 /// # fn run_test() -> QueryResult<()> {
1445 /// # let connection = &mut establish_connection();
1446 /// table! {
1447 /// users {
1448 /// id -> Integer,
1449 /// name -> Text,
1450 /// }
1451 /// }
1452 ///
1453 /// table! {
1454 /// posts {
1455 /// id -> Integer,
1456 /// by_user -> Nullable<Text>,
1457 /// }
1458 /// }
1459 ///
1460 /// allow_tables_to_appear_in_same_query!(users, posts);
1461 ///
1462 /// # let _: Vec<(i32, Option<String>)> =
1463 /// posts::table.filter(
1464 /// posts::by_user.eq_any(users::table.select(users::name).nullable())
1465 /// ).load(connection)?;
1466 /// # Ok(())
1467 /// # }
1468 fn nullable(self) -> NullableSelect<Self>
1469 where
1470 Self: methods::SelectNullableDsl,
1471 {
1472 methods::SelectNullableDsl::nullable(self)
1473 }
1474}
1475
1476#[diagnostic::do_not_recommend]
1477impl<T: Table> QueryDsl for T {}
1478
1479/// Methods used to execute queries.
1480pub trait RunQueryDsl<Conn>: Sized {
1481 /// Executes the given command, returning the number of rows affected.
1482 ///
1483 /// `execute` is usually used in conjunction with [`insert_into`](crate::insert_into()),
1484 /// [`update`](crate::update()) and [`delete`](crate::delete()) where the number of
1485 /// affected rows is often enough information.
1486 ///
1487 /// When asking the database to return data from a query, [`load`](crate::query_dsl::RunQueryDsl::load()) should
1488 /// probably be used instead.
1489 ///
1490 /// # Example
1491 ///
1492 /// ```rust
1493 /// # include!("../doctest_setup.rs");
1494 /// #
1495 /// # fn main() {
1496 /// # run_test();
1497 /// # }
1498 /// #
1499 /// # fn run_test() -> QueryResult<()> {
1500 /// # use diesel::insert_into;
1501 /// # use schema::users::dsl::*;
1502 /// # let connection = &mut establish_connection();
1503 /// let inserted_rows = insert_into(users)
1504 /// .values(name.eq("Ruby"))
1505 /// .execute(connection)?;
1506 /// assert_eq!(1, inserted_rows);
1507 ///
1508 /// let inserted_rows = insert_into(users)
1509 /// .values(&vec![name.eq("Jim"), name.eq("James")])
1510 /// .execute(connection)?;
1511 /// assert_eq!(2, inserted_rows);
1512 /// # Ok(())
1513 /// # }
1514 /// ```
1515 fn execute(self, conn: &mut Conn) -> QueryResult<usize>
1516 where
1517 Conn: Connection,
1518 Self: methods::ExecuteDsl<Conn>,
1519 {
1520 methods::ExecuteDsl::execute(self, conn)
1521 }
1522
1523 /// Executes the given query, returning a [`Vec`] with the returned rows.
1524 ///
1525 /// When using the query builder, the return type can be
1526 /// a tuple of the values, or a struct which implements [`Queryable`].
1527 ///
1528 /// When this method is called on [`sql_query`],
1529 /// the return type can only be a struct which implements [`QueryableByName`]
1530 ///
1531 /// For insert, update, and delete operations where only a count of affected is needed,
1532 /// [`execute`] should be used instead.
1533 ///
1534 /// [`Queryable`]: crate::deserialize::Queryable
1535 /// [`QueryableByName`]: crate::deserialize::QueryableByName
1536 /// [`execute`]: crate::query_dsl::RunQueryDsl::execute()
1537 /// [`sql_query`]: crate::sql_query()
1538 ///
1539 /// ## How to resolve compiler errors while loading data from the database
1540 ///
1541 /// In case you getting uncomprehensable compiler errors while loading data
1542 /// from the database into a type using [`#[derive(Queryable)]`](derive@crate::prelude::Queryable)
1543 /// you might want to consider
1544 /// using [`#[derive(Selectable)]`](derive@crate::prelude::Selectable) +
1545 /// `#[diesel(check_for_backend(YourBackendType))]`
1546 /// to check for mismatching fields at compile time. This drastically improves
1547 /// the quality of the generated error messages by pointing to concrete type mismatches at
1548 /// field level.You need to specify the concrete database backend
1549 /// this specific struct is indented to be used with, as otherwise rustc cannot correctly
1550 /// identify the required deserialization implementation.
1551 ///
1552 /// # Examples
1553 ///
1554 /// ## Returning a single field
1555 ///
1556 /// ```rust
1557 /// # include!("../doctest_setup.rs");
1558 /// #
1559 /// # fn main() {
1560 /// # run_test();
1561 /// # }
1562 /// #
1563 /// # fn run_test() -> QueryResult<()> {
1564 /// # use diesel::insert_into;
1565 /// # use schema::users::dsl::*;
1566 /// # let connection = &mut establish_connection();
1567 /// let data = users.select(name).load::<String>(connection)?;
1568 /// assert_eq!(vec!["Sean", "Tess"], data);
1569 /// # Ok(())
1570 /// # }
1571 /// ```
1572 ///
1573 /// ## Returning a tuple
1574 ///
1575 /// ```rust
1576 /// # include!("../doctest_setup.rs");
1577 /// #
1578 /// # fn main() {
1579 /// # run_test();
1580 /// # }
1581 /// #
1582 /// # fn run_test() -> QueryResult<()> {
1583 /// # use diesel::insert_into;
1584 /// # use schema::users::dsl::*;
1585 /// # let connection = &mut establish_connection();
1586 /// let data = users.load::<(i32, String)>(connection)?;
1587 /// let expected_data = vec![(1, String::from("Sean")), (2, String::from("Tess"))];
1588 /// assert_eq!(expected_data, data);
1589 /// # Ok(())
1590 /// # }
1591 /// ```
1592 ///
1593 /// ## Returning a struct
1594 ///
1595 /// ```rust
1596 /// # include!("../doctest_setup.rs");
1597 /// #
1598 /// #[derive(Queryable, PartialEq, Debug)]
1599 /// struct User {
1600 /// id: i32,
1601 /// name: String,
1602 /// }
1603 ///
1604 /// # fn main() {
1605 /// # run_test();
1606 /// # }
1607 /// #
1608 /// # fn run_test() -> QueryResult<()> {
1609 /// # use diesel::insert_into;
1610 /// # use schema::users::dsl::*;
1611 /// # let connection = &mut establish_connection();
1612 /// let data = users.load::<User>(connection)?;
1613 /// let expected_data = vec![
1614 /// User {
1615 /// id: 1,
1616 /// name: String::from("Sean"),
1617 /// },
1618 /// User {
1619 /// id: 2,
1620 /// name: String::from("Tess"),
1621 /// },
1622 /// ];
1623 /// assert_eq!(expected_data, data);
1624 /// # Ok(())
1625 /// # }
1626 /// ```
1627 fn load<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
1628 where
1629 Self: LoadQuery<'query, Conn, U>,
1630 {
1631 self.internal_load(conn)?.collect()
1632 }
1633
1634 /// Executes the given query, returning an [`Iterator`] with the returned rows.
1635 ///
1636 /// The iterator's item is [`QueryResult<U>`](crate::result::QueryResult).
1637 ///
1638 /// You should normally prefer to use [`RunQueryDsl::load`] instead. This method
1639 /// is provided for situations where the result needs to be collected into a different
1640 /// container than a [`Vec`]
1641 ///
1642 /// When using the query builder, the return type can be
1643 /// a tuple of the values, or a struct which implements [`Queryable`].
1644 /// This type is specified by the first generic type of this function.
1645 ///
1646 /// The second generic type parameter specifies the so called loading mode,
1647 /// which describes how the connection implementation loads data from the database.
1648 /// All connections should provide a implementation for
1649 /// [`DefaultLoadingMode`](crate::connection::DefaultLoadingMode).
1650 ///
1651 /// They may provide additional modes. Checkout the documentation of the concrete
1652 /// connection types for details. For connection implementations that provide
1653 /// more than one loading mode it is **required** to specify this generic parameter.
1654 /// This is currently true for `PgConnection`.
1655 ///
1656 /// When this method is called on [`sql_query`],
1657 /// the return type can only be a struct which implements [`QueryableByName`]
1658 ///
1659 /// For insert, update, and delete operations where only a count of affected is needed,
1660 /// [`execute`] should be used instead.
1661 ///
1662 /// [`Queryable`]: crate::deserialize::Queryable
1663 /// [`QueryableByName`]: crate::deserialize::QueryableByName
1664 /// [`execute`]: crate::query_dsl::RunQueryDsl::execute()
1665 /// [`sql_query`]: crate::sql_query()
1666 ///
1667 /// # Examples
1668 ///
1669 /// ## Returning a single field
1670 ///
1671 /// ```rust
1672 /// # include!("../doctest_setup.rs");
1673 /// #
1674 /// # fn main() {
1675 /// # run_test();
1676 /// # }
1677 /// #
1678 /// # fn run_test() -> QueryResult<()> {
1679 /// # use diesel::insert_into;
1680 /// # use schema::users::dsl::*;
1681 /// # let connection = &mut establish_connection();
1682 /// use diesel::connection::DefaultLoadingMode;
1683 ///
1684 /// let data = users
1685 /// .select(name)
1686 /// .load_iter::<String, DefaultLoadingMode>(connection)?
1687 /// .collect::<QueryResult<Vec<_>>>()?;
1688 /// assert_eq!(vec!["Sean", "Tess"], data);
1689 /// # Ok(())
1690 /// # }
1691 /// ```
1692 ///
1693 /// ## Returning a tuple
1694 ///
1695 /// ```rust
1696 /// # include!("../doctest_setup.rs");
1697 /// #
1698 /// # fn main() {
1699 /// # run_test();
1700 /// # }
1701 /// #
1702 /// # fn run_test() -> QueryResult<()> {
1703 /// # use diesel::insert_into;
1704 /// # use schema::users::dsl::*;
1705 /// # let connection = &mut establish_connection();
1706 /// use diesel::connection::DefaultLoadingMode;
1707 ///
1708 /// let data = users
1709 /// .load_iter::<(i32, String), DefaultLoadingMode>(connection)?
1710 /// .collect::<QueryResult<Vec<_>>>()?;
1711 /// let expected_data = vec![(1, String::from("Sean")), (2, String::from("Tess"))];
1712 /// assert_eq!(expected_data, data);
1713 /// # Ok(())
1714 /// # }
1715 /// ```
1716 ///
1717 /// ## Returning a struct
1718 ///
1719 /// ```rust
1720 /// # include!("../doctest_setup.rs");
1721 /// #
1722 /// #[derive(Queryable, PartialEq, Debug)]
1723 /// struct User {
1724 /// id: i32,
1725 /// name: String,
1726 /// }
1727 ///
1728 /// # fn main() {
1729 /// # run_test();
1730 /// # }
1731 /// #
1732 /// # fn run_test() -> QueryResult<()> {
1733 /// # use diesel::insert_into;
1734 /// # use schema::users::dsl::*;
1735 /// # let connection = &mut establish_connection();
1736 /// use diesel::connection::DefaultLoadingMode;
1737 ///
1738 /// let data = users
1739 /// .load_iter::<User, DefaultLoadingMode>(connection)?
1740 /// .collect::<QueryResult<Vec<_>>>()?;
1741 /// let expected_data = vec![
1742 /// User {
1743 /// id: 1,
1744 /// name: String::from("Sean"),
1745 /// },
1746 /// User {
1747 /// id: 2,
1748 /// name: String::from("Tess"),
1749 /// },
1750 /// ];
1751 /// assert_eq!(expected_data, data);
1752 /// # Ok(())
1753 /// # }
1754 /// ```
1755 fn load_iter<'conn, 'query: 'conn, U, B>(
1756 self,
1757 conn: &'conn mut Conn,
1758 ) -> QueryResult<Self::RowIter<'conn>>
1759 where
1760 U: 'conn,
1761 Self: LoadQuery<'query, Conn, U, B> + 'conn,
1762 {
1763 self.internal_load(conn)
1764 }
1765
1766 /// Runs the command, and returns the affected row.
1767 ///
1768 /// `Err(NotFound)` will be returned if the query affected 0 rows. You can
1769 /// call `.optional()` on the result of this if the command was optional to
1770 /// get back a `Result<Option<U>>`
1771 ///
1772 /// When this method is called on an insert, update, or delete statement,
1773 /// it will implicitly add a `RETURNING *` to the query,
1774 /// unless a returning clause was already specified.
1775 ///
1776 /// This method only returns the first row that was affected, even if more
1777 /// rows are affected.
1778 ///
1779 /// # Example
1780 ///
1781 /// ```rust
1782 /// # include!("../doctest_setup.rs");
1783 /// #
1784 /// # fn main() {
1785 /// # run_test();
1786 /// # }
1787 /// #
1788 /// # #[cfg(feature = "postgres")]
1789 /// # fn run_test() -> QueryResult<()> {
1790 /// # use diesel::{insert_into, update};
1791 /// # use schema::users::dsl::*;
1792 /// # let connection = &mut establish_connection();
1793 /// let inserted_row = insert_into(users)
1794 /// .values(name.eq("Ruby"))
1795 /// .get_result(connection)?;
1796 /// assert_eq!((3, String::from("Ruby")), inserted_row);
1797 ///
1798 /// // This will return `NotFound`, as there is no user with ID 4
1799 /// let update_result = update(users.find(4))
1800 /// .set(name.eq("Jim"))
1801 /// .get_result::<(i32, String)>(connection);
1802 /// assert_eq!(Err(diesel::NotFound), update_result);
1803 /// # Ok(())
1804 /// # }
1805 /// #
1806 /// # #[cfg(not(feature = "postgres"))]
1807 /// # fn run_test() -> QueryResult<()> {
1808 /// # Ok(())
1809 /// # }
1810 /// ```
1811 fn get_result<'query, U>(self, conn: &mut Conn) -> QueryResult<U>
1812 where
1813 Self: LoadQuery<'query, Conn, U>,
1814 {
1815 match self.internal_load(conn)?.next() {
1816 Some(v) => v,
1817 None => Err(crate::result::Error::NotFound),
1818 }
1819 }
1820
1821 /// Runs the command, returning an `Vec` with the affected rows.
1822 ///
1823 /// This method is an alias for [`load`], but with a name that makes more
1824 /// sense for insert, update, and delete statements.
1825 ///
1826 /// [`load`]: crate::query_dsl::RunQueryDsl::load()
1827 fn get_results<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
1828 where
1829 Self: LoadQuery<'query, Conn, U>,
1830 {
1831 self.load(conn)
1832 }
1833
1834 /// Attempts to load a single record.
1835 ///
1836 /// This method is equivalent to `.limit(1).get_result()`
1837 ///
1838 /// Returns `Ok(record)` if found, and `Err(NotFound)` if no results are
1839 /// returned. If the query truly is optional, you can call `.optional()` on
1840 /// the result of this to get a `Result<Option<U>>`.
1841 ///
1842 /// # Example:
1843 ///
1844 /// ```rust
1845 /// # include!("../doctest_setup.rs");
1846 /// # fn main() {
1847 /// # run_test();
1848 /// # }
1849 /// #
1850 /// # fn run_test() -> QueryResult<()> {
1851 /// # use schema::users::dsl::*;
1852 /// # let connection = &mut establish_connection();
1853 /// diesel::insert_into(users)
1854 /// .values(&vec![name.eq("Sean"), name.eq("Pascal")])
1855 /// .execute(connection)?;
1856 ///
1857 /// let first_name = users.order(id).select(name).first(connection);
1858 /// assert_eq!(Ok(String::from("Sean")), first_name);
1859 ///
1860 /// let not_found = users
1861 /// .filter(name.eq("Foo"))
1862 /// .first::<(i32, String)>(connection);
1863 /// assert_eq!(Err(diesel::NotFound), not_found);
1864 /// # Ok(())
1865 /// # }
1866 /// ```
1867 fn first<'query, U>(self, conn: &mut Conn) -> QueryResult<U>
1868 where
1869 Self: methods::LimitDsl,
1870 Limit<Self>: LoadQuery<'query, Conn, U>,
1871 {
1872 methods::LimitDsl::limit(self, 1).get_result(conn)
1873 }
1874}
1875
1876// Note: We could have a blanket `AsQuery` impl here, which would apply to
1877// everything we want it to. However, when a query is invalid, we specifically
1878// want the error to happen on the where clause of the method instead of trait
1879// resolution. Otherwise our users will get an error saying `<3 page long type>:
1880// ExecuteDsl is not satisfied` instead of a specific error telling them what
1881// part of their query is wrong.
1882impl<T, Conn> RunQueryDsl<Conn> for T where T: Table {}