ormlite_core/query_builder/
select.rs

1use crate::error::{Error, Result};
2use crate::model::Model;
3use crate::query_builder::args::QueryBuilderArgs;
4use crate::query_builder::{Placeholder, util};
5use sql::{Expr, OrderBy, ToSql};
6
7use crate::join::{JoinDescription, criteria, select_columns};
8use sql::{Select, query::Where};
9use sqlx::{Executor, IntoArguments};
10use std::marker::PhantomData;
11
12// Add additional information to the sqlx::Database
13pub trait DatabaseMetadata {
14    fn dialect() -> sql::Dialect;
15    fn placeholder() -> Placeholder;
16}
17
18#[cfg(feature = "postgres")]
19impl DatabaseMetadata for sqlx::postgres::Postgres {
20    fn dialect() -> sql::Dialect {
21        sql::Dialect::Postgres
22    }
23
24    fn placeholder() -> Placeholder {
25        Placeholder::dollar_sign()
26    }
27}
28
29#[cfg(feature = "sqlite")]
30impl DatabaseMetadata for sqlx::sqlite::Sqlite {
31    fn dialect() -> sql::Dialect {
32        sql::Dialect::Sqlite
33    }
34
35    fn placeholder() -> Placeholder {
36        Placeholder::question_mark()
37    }
38}
39
40pub struct SelectQueryBuilder<'args, DB, Model>
41where
42    DB: sqlx::Database,
43{
44    pub query: Select,
45    arguments: QueryBuilderArgs<'args, DB>,
46    model: PhantomData<Model>,
47    placeholder: Placeholder,
48}
49
50impl<'args, DB, M> SelectQueryBuilder<'args, DB, M>
51where
52    M: Sized + Send + Sync + Unpin + for<'r> sqlx::FromRow<'r, DB::Row> + 'static + Model<DB>,
53    DB: sqlx::Database + DatabaseMetadata,
54    DB::Arguments<'args>: IntoArguments<'args, DB>,
55{
56    pub async fn fetch_all<'executor, E>(self, db: E) -> Result<Vec<M>>
57    where
58        E: Executor<'executor, Database = DB>,
59    {
60        let (text, args) = self.into_query_and_args()?;
61        let z: &str = &text;
62        util::query_as_with_recast_lifetime::<DB, M>(z, args)
63            .fetch_all(db)
64            .await
65            .map_err(Error::from)
66    }
67
68    pub async fn fetch_one<'executor, E>(mut self, db: E) -> Result<M>
69    where
70        E: Executor<'executor, Database = DB>,
71    {
72        if self.query.limit.is_none() {
73            self.query.limit = Some(1);
74        }
75        let (text, args) = self.into_query_and_args()?;
76        let z: &str = &text;
77        util::query_as_with_recast_lifetime::<DB, M>(z, args)
78            .fetch_one(db)
79            .await
80            .map_err(Error::from)
81    }
82
83    pub async fn fetch_optional<'executor, E>(mut self, db: E) -> Result<Option<M>>
84    where
85        E: Executor<'executor, Database = DB>,
86    {
87        if self.query.limit.is_none() {
88            self.query.limit = Some(1);
89        }
90        let (text, args) = self.into_query_and_args()?;
91        let z: &str = &text;
92        util::query_as_with_recast_lifetime::<DB, M>(z, args)
93            .fetch_optional(db)
94            .await
95            .map_err(Error::from)
96    }
97
98    pub fn with(mut self, name: &str, query: &str) -> Self {
99        self.query = self.query.with_raw(name, query);
100        self
101    }
102
103    /// Add a column to the query. Note you typically don't need this, as creating a query from
104    /// `Model::select` will automatically add that model's columns.
105    ///
106    /// # Arguments
107    /// * `column` - The column to add. Examples: "id", "name", "person.*"
108    pub fn select(mut self, column: impl Into<String>) -> Self {
109        self.query = self.query.select_raw(column.into());
110        self
111    }
112
113    /// Add a WHERE clause to the query.
114    /// Do not use format! to add parameters. Instead, use `?` as the placeholder, and add
115    /// parameters with [`bind`](Self::bind).
116    ///
117    /// Postgres users: You can (and should) use `?` as the placeholder. You might not have a defined
118    /// numerical order for your parameters, preventing $<N> syntax. Upon execution, the query
119    /// builder replaces `?` with `$<N>`. If you need the same parameter multiple times, you should
120    /// bind it multiple times. Arguments aren't moved, so this doesn't incur a memory cost. If you
121    /// still want to re-use parameters, you can use $<N> placeholders. However, don't mix `?` and
122    /// `$<N>` placeholders, as they will conflict.
123    ///
124    /// # Arguments
125    /// * `clause` - The clause to add. Examples: "id = ?", "name = ?", "person.id = ?"
126    pub fn where_(mut self, clause: &'static str) -> Self {
127        self.query = self.query.where_raw(clause);
128        self
129    }
130
131    /// Convenience method to add a `WHERE` and bind a value in one call.
132    pub fn where_bind<T>(mut self, clause: &'static str, value: T) -> Self
133    where
134        T: 'args + Send + sqlx::Type<DB> + sqlx::Encode<'args, DB>,
135    {
136        self.query = self.query.where_raw(clause);
137        self.arguments.add(value);
138        self
139    }
140    /// Dangerous because it takes a string that could be user crafted. You should prefer `.where_` which
141    /// takes a &'static str, and pass arguments with `.bind()`.
142    pub fn dangerous_where(mut self, clause: &str) -> Self {
143        self.query = self.query.where_raw(clause);
144        self
145    }
146
147    pub fn join(mut self, join_description: JoinDescription) -> Self {
148        match &join_description {
149            JoinDescription::ManyToOne {
150                columns,
151                foreign_table,
152                field,
153                foreign_key,
154                local_column,
155            } => {
156                let join = sql::query::Join {
157                    typ: sql::query::JoinType::Left,
158                    table: sql::query::JoinTable::Table {
159                        schema: None,
160                        table: foreign_table.to_string(),
161                    },
162                    alias: Some(field.to_string()),
163                    criteria: criteria(M::table_name(), local_column, field, foreign_key),
164                };
165                self.query.join.push(join);
166                self.query.columns.extend(select_columns(columns, field))
167            }
168        }
169        self
170    }
171
172    #[doc(hidden)]
173    #[deprecated(note = "Please use `where_` instead")]
174    pub fn filter(self, clause: &'static str) -> Self {
175        self.where_(clause)
176    }
177
178    /// Add a HAVING clause to the query.
179    pub fn having(mut self, clause: &str) -> Self {
180        self.query = self.query.having(Where::Expr(Expr::Raw(clause.to_string())));
181        self
182    }
183
184    /// Add a GROUP BY clause to the query.
185    ///
186    /// # Arguments:
187    /// * `clause`: The GROUP BY clause to add. Examples: "id", "id, date", "1, 2, ROLLUP(3)"
188    pub fn group_by(mut self, clause: &str) -> Self {
189        self.query = self.query.group_by(clause);
190        self
191    }
192
193    /// Add an ORDER BY clause to the query.
194    ///
195    /// # Arguments:
196    /// * `clause`: The ORDER BY clause to add. "created_at DESC", "id ASC NULLS FIRST"
197    /// * `direction`: Direction::Asc or Direction::Desc
198    pub fn order_by(mut self, order: OrderBy) -> Self {
199        self.query = self.query.order_by(order);
200        self
201    }
202
203    pub fn order_asc(mut self, clause: &str) -> Self {
204        self.query = self.query.order_asc(clause);
205        self
206    }
207
208    pub fn order_desc(mut self, clause: &str) -> Self {
209        self.query = self.query.order_desc(clause);
210        self
211    }
212
213    /// Add a limit to the query.
214    pub fn limit(mut self, limit: usize) -> Self {
215        self.query = self.query.limit(limit);
216        self
217    }
218
219    /// Add an offset to the query.
220    pub fn offset(mut self, offset: usize) -> Self {
221        self.query = self.query.offset(offset);
222        self
223    }
224
225    /// Bind an argument to the query.
226    pub fn bind<T>(mut self, value: T) -> Self
227    where
228        T: 'args + Send + sqlx::Type<DB> + sqlx::Encode<'args, DB>,
229    {
230        self.arguments.add(value);
231        self
232    }
233
234    pub fn into_query_and_args(mut self) -> Result<(String, QueryBuilderArgs<'args, DB>)> {
235        let q = self.query.to_sql(DB::dialect());
236        let args = self.arguments;
237        let (q, placeholder_count) = util::replace_placeholders(&q, &mut self.placeholder)?;
238        if placeholder_count != args.len() {
239            return Err(Error::OrmliteError(format!(
240                "Failing to build query. {} placeholders were found in the query, but \
241                {} arguments were provided.",
242                placeholder_count,
243                args.len(),
244            )));
245        }
246        Ok((q, args))
247    }
248}
249
250impl<'args, DB: sqlx::Database + DatabaseMetadata, M: Model<DB>> Default for SelectQueryBuilder<'args, DB, M> {
251    fn default() -> Self {
252        Self {
253            query: Select::default().from(M::table_name()),
254            arguments: QueryBuilderArgs::default(),
255            model: PhantomData,
256            placeholder: DB::placeholder(),
257        }
258    }
259}
ormlite_core/query_builder/select.rs

ormlite_core/query_builder/
select.rs
