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