geekorm_core/queries/
builder.rs

1#[cfg(feature = "pagination")]
2use super::pages::Page;
3use crate::{
4    Error, Table, ToSqlite,
5    builder::{
6        joins::{TableJoin, TableJoinOptions, TableJoins},
7        models::{QueryCondition, QueryOrder, QueryType, WhereCondition},
8        values::{Value, Values},
9    },
10    queries::Query,
11};
12
13/// The QueryBuilder is how you can build dynamically queries using the builder pattern.
14///
15/// # Features
16///
17/// There is a number a features that are supported by the QueryBuilder:
18///
19/// - All Major Query Types
20///   - Select: Build a select query
21///   - Insert: Build an insert query
22///   - Update: Build an update query
23///   - Delete: Build a delete query
24/// - Conditions: Build a query with conditions
25///   - Where: Build a query with where conditions
26///   - Order By: Build a query with order by conditions
27///   - Limit: Build a query with a limit
28/// - Joins: Build a query with joins 2 tables
29///   - Only Inner Joins are supported currently
30///
31/// # Example
32/// ```rust
33/// use geekorm::prelude::*;
34///
35/// #[derive(Table, Debug, Default, Clone, serde::Serialize, serde::Deserialize)]
36/// pub struct Users {
37///     pub id: PrimaryKeyInteger,
38///     pub username: String,
39///     pub age: i32,
40///     pub postcode: Option<String>,
41/// }
42///
43/// # fn main() {
44/// // Build a query to create a new table
45/// let create_query = Users::query_create().build()
46///     .expect("Failed to build create query");
47/// println!("Create Query :: {}", create_query);
48///
49/// // Build a query to select rows from the table
50/// let select_query = Users::query_select()
51///     .where_eq("username", "geekmasher")
52///     .order_by("age", QueryOrder::Asc)
53///     .build()
54///     .expect("Failed to build select query");
55/// println!("Select Query :: {}", select_query);
56/// // Output:
57/// // SELECT (...) FROM User WHERE username = ? ORDER BY age ASC;
58/// # assert_eq!(
59/// #     select_query.query,
60/// #     "SELECT id, username, age, postcode FROM Users WHERE username = ? ORDER BY age ASC;"
61/// # );
62/// # }
63/// ```
64#[derive(Debug, Clone, Default)]
65pub struct QueryBuilder {
66    pub(crate) table: Table,
67    pub(crate) query_type: QueryType,
68    /// If a query should use aliases
69    pub(crate) aliases: bool,
70
71    pub(crate) columns: Vec<String>,
72
73    /// Count the rows instead of returning them
74    pub(crate) count: bool,
75    /// The limit of the rows to return
76    pub(crate) limit: Option<usize>,
77    /// The offset of the rows to return
78    pub(crate) offset: Option<usize>,
79
80    /// The where clause
81    pub(crate) where_clause: Vec<String>,
82    /// This variable is used to determine if the last where condition was set
83    pub(crate) where_condition_last: bool,
84    /// The order by clause
85    pub(crate) order_by: Vec<(String, QueryOrder)>,
86
87    pub(crate) joins: TableJoins,
88
89    /// The values are used for data inserted into the database
90    pub(crate) values: Values,
91
92    pub(crate) error: Option<Error>,
93}
94
95impl QueryBuilder {
96    /// Create a new QueryBuilder
97    pub fn new() -> Self {
98        QueryBuilder::default()
99    }
100    /// Build a select query
101    pub fn select() -> QueryBuilder {
102        QueryBuilder {
103            query_type: QueryType::Select,
104            ..Default::default()
105        }
106    }
107    /// Build a create query
108    pub fn create() -> QueryBuilder {
109        QueryBuilder {
110            query_type: QueryType::Create,
111            ..Default::default()
112        }
113    }
114
115    /// Build a "get all rows" query
116    pub fn all() -> QueryBuilder {
117        QueryBuilder {
118            query_type: QueryType::Select,
119            ..Default::default()
120        }
121    }
122
123    /// Build an insert query
124    pub fn insert() -> QueryBuilder {
125        QueryBuilder {
126            query_type: QueryType::Insert,
127            ..Default::default()
128        }
129    }
130
131    /// Build an update query
132    pub fn update() -> QueryBuilder {
133        QueryBuilder {
134            query_type: QueryType::Update,
135            ..Default::default()
136        }
137    }
138
139    /// Build a delete query
140    pub fn delete() -> QueryBuilder {
141        QueryBuilder {
142            query_type: QueryType::Delete,
143            ..Default::default()
144        }
145    }
146
147    /// Set the table for the query builder
148    pub fn table(mut self, table: Table) -> Self {
149        self.table = table.clone();
150        self
151    }
152
153    /// Set the columns for the query builder
154    pub fn columns(mut self, columns: Vec<&str>) -> Self {
155        self.columns = columns.iter().map(|c| c.to_string()).collect();
156        self
157    }
158
159    /// Add a value to the list of values for parameterized queries
160    pub fn add_value(mut self, column: &str, value: impl Into<Value>) -> Self {
161        self.values.push(column.to_string(), value.into());
162        self
163    }
164
165    /// Add an AND condition to the where clause
166    pub fn and(mut self) -> Self {
167        self.where_clause.push(WhereCondition::And.to_sqlite());
168        self.where_condition_last = true;
169        self
170    }
171
172    /// Add an OR condition to the where clause
173    pub fn or(mut self) -> Self {
174        self.where_clause.push(WhereCondition::Or.to_sqlite());
175        self.where_condition_last = true;
176        self
177    }
178
179    /// The underlying function to add a where clause
180    fn add_where(&mut self, column: &str, condition: QueryCondition, value: Value) {
181        let mut column_name: &str = column;
182
183        // Check if there is a `.` in the column name
184        let table: &Table = if let Some((ftable, fcolumn)) = column.split_once('.') {
185            match self.joins.get(ftable) {
186                Some(TableJoin::InnerJoin(TableJoinOptions { child, .. })) => {
187                    column_name = fcolumn;
188                    child
189                }
190                _ => {
191                    self.error = Some(Error::QueryBuilderError(
192                        format!("Table `{}` does not exist", ftable),
193                        String::from("where_eq"),
194                    ));
195                    &self.table
196                }
197            }
198        } else {
199            &self.table
200        };
201
202        if table.is_valid_column(column_name) {
203            // Check if the last condition was set
204            if !self.where_clause.is_empty() && !self.where_condition_last {
205                // Use the default where condition
206                self.where_clause
207                    .push(WhereCondition::default().to_sqlite());
208            }
209
210            self.where_clause
211                .push(format!("{} {} ?", column, condition.to_sqlite()));
212            self.values.push(column.to_string(), value);
213            self.where_condition_last = false;
214        } else {
215            self.error = Some(Error::QueryBuilderError(
216                format!(
217                    "Column `{}` does not exist in table `{}`",
218                    column_name, table.name
219                ),
220                String::from("where_eq"),
221            ));
222        }
223    }
224
225    /// Where clause for equals
226    pub fn where_eq(mut self, column: &str, value: impl Into<Value>) -> Self {
227        QueryBuilder::add_where(&mut self, column, QueryCondition::Eq, value.into());
228        self
229    }
230
231    /// Where clause for not equals
232    pub fn where_ne(mut self, column: &str, value: impl Into<Value>) -> Self {
233        QueryBuilder::add_where(&mut self, column, QueryCondition::Ne, value.into());
234        self
235    }
236
237    /// Where clause for like
238    pub fn where_like(mut self, column: &str, value: impl Into<Value>) -> Self {
239        QueryBuilder::add_where(&mut self, column, QueryCondition::Like, value.into());
240        self
241    }
242
243    /// Where clause for greater than
244    pub fn where_gt(mut self, column: &str, value: impl Into<Value>) -> Self {
245        QueryBuilder::add_where(&mut self, column, QueryCondition::Gt, value.into());
246        self
247    }
248
249    /// Where clause for less than
250    pub fn where_lt(mut self, column: &str, value: impl Into<Value>) -> Self {
251        QueryBuilder::add_where(&mut self, column, QueryCondition::Lt, value.into());
252        self
253    }
254
255    /// Where clause for greater than or equal to
256    pub fn where_gte(mut self, column: &str, value: impl Into<Value>) -> Self {
257        QueryBuilder::add_where(&mut self, column, QueryCondition::Gte, value.into());
258        self
259    }
260
261    /// Where clause for less than or equal to
262    pub fn where_lte(mut self, column: &str, value: impl Into<Value>) -> Self {
263        QueryBuilder::add_where(&mut self, column, QueryCondition::Lte, value.into());
264        self
265    }
266
267    /// Filter the query by multiple fields
268    pub fn filter(mut self, fields: Vec<(&str, impl Into<Value>)>) -> Self {
269        for (field, value) in fields {
270            if field.starts_with("=") {
271                let field = &field[1..];
272                self = self.where_eq(field, value.into());
273            } else if field.starts_with("~") {
274                let field = &field[1..];
275                self = self.where_like(field, value.into());
276            } else if field.starts_with("!") {
277                let field = &field[1..];
278                self = self.where_ne(field, value.into());
279            } else {
280                // Default to WHERE field = value with an OR operator
281                self = self.where_eq(field, value.into()).or();
282            }
283        }
284        self
285    }
286
287    /// Order the query by a particular column
288    pub fn order_by(mut self, column: &str, order: QueryOrder) -> Self {
289        if self.table.is_valid_column(column) {
290            self.order_by.push((column.to_string(), order));
291        } else {
292            self.error = Some(Error::QueryBuilderError(
293                format!(
294                    "Column `{}` does not exist in table `{}`",
295                    column, self.table.name
296                ),
297                String::from("order_by"),
298            ));
299        }
300        self
301    }
302
303    /// Adds a table to join with the current table
304    ///
305    /// Note: GeekOrm only joins tables with the `INNER JOIN` clause and primary keys
306    pub fn join(mut self, table: Table) -> Self {
307        let key = self.table.get_primary_key();
308        if table.is_valid_column(key.as_str()) || self.table.is_valid_column(key.as_str()) {
309            // TODO(geekmasher): The tables should be references to avoid cloning
310            self.joins
311                .push(TableJoin::new(self.table.clone(), table.clone()));
312        } else {
313            self.error = Some(Error::QueryBuilderError(
314                format!("Column `{}` does not exist in table `{}`", key, table.name),
315                String::from("join"),
316            ));
317        }
318        self
319    }
320
321    /// Count the number of rows in the query
322    pub fn count(mut self) -> Self {
323        self.count = true;
324        self
325    }
326
327    /// Add a limit to the query
328    pub fn limit(mut self, limit: usize) -> Self {
329        if limit != 0 {
330            self.limit = Some(limit);
331        } else {
332            self.error = Some(Error::QueryBuilderError(
333                String::from("Limit cannot be 0"),
334                String::from("limit"),
335            ));
336        }
337        self
338    }
339
340    /// Add an offset to the query
341    pub fn offset(mut self, offset: usize) -> Self {
342        self.offset = Some(offset);
343        self
344    }
345
346    /// Add a page to the query
347    #[cfg(feature = "pagination")]
348    pub fn page(mut self, page: &Page) -> Self {
349        self.offset = Some(page.offset() as usize);
350        self.limit = Some(page.limit as usize);
351        self
352    }
353
354    /// Build a Query from the QueryBuilder and perform some checks
355    pub fn build(&mut self) -> Result<Query, crate::Error> {
356        if let Some(ref error) = self.error {
357            return Err(error.clone());
358        }
359
360        // Check the last where condition
361        let mut pop_where_condition = false;
362        if let Some(last) = self.where_clause.last() {
363            if last == &WhereCondition::Or.to_sqlite() || last == &WhereCondition::And.to_sqlite() {
364                pop_where_condition = true;
365            }
366        }
367        // Pop the last where condition
368        if pop_where_condition {
369            self.where_clause.pop();
370        }
371
372        match self.query_type {
373            QueryType::Create => {
374                let query = self.table.on_create(self)?;
375                Ok(Query::new(
376                    self.query_type.clone(),
377                    query.clone(),
378                    Values::new(),
379                    Values::new(),
380                    self.columns.clone(),
381                    self.table.clone(),
382                ))
383            }
384            QueryType::Select => {
385                let query = self.table.on_select(self)?;
386                Ok(Query::new(
387                    self.query_type.clone(),
388                    query.clone(),
389                    self.values.clone(),
390                    Values::new(),
391                    self.columns.clone(),
392                    self.table.clone(),
393                ))
394            }
395            QueryType::Insert => {
396                let (query, parameters) = self.table.on_insert(self)?;
397                Ok(Query::new(
398                    self.query_type.clone(),
399                    query.clone(),
400                    self.values.clone(),
401                    parameters,
402                    self.columns.clone(),
403                    self.table.clone(),
404                ))
405            }
406            QueryType::Update => {
407                let (query, parameters) = self.table.on_update(self)?;
408                Ok(Query::new(
409                    self.query_type.clone(),
410                    query.clone(),
411                    self.values.clone(),
412                    parameters,
413                    self.columns.clone(),
414                    self.table.clone(),
415                ))
416            }
417            QueryType::Delete => {
418                let (query, parameters) = self.table.on_delete(self)?;
419                Ok(Query::new(
420                    self.query_type.clone(),
421                    query.clone(),
422                    self.values.clone(),
423                    parameters,
424                    self.columns.clone(),
425                    self.table.clone(),
426                ))
427            }
428            QueryType::Batch => Err(Error::QueryBuilderError(
429                String::from("Batch queries are not supported"),
430                String::from("build"),
431            )),
432        }
433    }
434}
435
436#[cfg(test)]
437mod tests {
438    use crate::{
439        Column, ColumnType, ColumnTypeOptions, QueryBuilder, Table, builder::values::Value,
440    };
441
442    fn simple_table() -> Table {
443        Table {
444            name: "users".to_string(),
445            database: None,
446            columns: crate::Columns::from(vec![
447                Column::new(
448                    "id".to_string(),
449                    ColumnType::Identifier(ColumnTypeOptions::primary_key()),
450                ),
451                Column::new(
452                    "username".to_string(),
453                    ColumnType::Text(ColumnTypeOptions::default()),
454                ),
455                Column::new(
456                    "email".to_string(),
457                    ColumnType::Text(ColumnTypeOptions::null()),
458                ),
459            ]),
460        }
461    }
462
463    #[test]
464    fn test_simple_select() {
465        let table = simple_table();
466
467        let query = QueryBuilder::select()
468            .table(table)
469            .build()
470            .expect("Failed to build query");
471
472        assert_eq!(query.query, "SELECT id, username, email FROM users;");
473    }
474
475    #[test]
476    fn test_where() {
477        let table = simple_table();
478        let query = QueryBuilder::select()
479            .table(table)
480            .where_eq("username", "geekmasher")
481            .or()
482            .where_like("email", "%geekmasher%")
483            .build()
484            .expect("Failed to build query");
485
486        assert_eq!(
487            query.query,
488            "SELECT id, username, email FROM users WHERE username = ? OR email LIKE ?;"
489        );
490        let first = query.values.get(&String::from("username")).unwrap();
491        assert_eq!(first, &Value::Text(String::from("geekmasher")));
492        let second = query.values.get(&String::from("email")).unwrap();
493        assert_eq!(second, &Value::Text(String::from("%geekmasher%")));
494    }
495}
geekorm_core/queries/builder.rs

geekorm_core/queries/
builder.rs
