backbone_orm/
query_builder.rs

1//! Query builder for PostgreSQL with parameterized queries
2
3use sqlx::{PgPool, FromRow, Postgres};
4use super::raw_query::{JoinType, JoinClause};
5
6/// Query parameter values
7#[derive(Debug, Clone)]
8pub enum QueryValue {
9    Text(String),
10    Integer(i64),
11    Float(f64),
12    Boolean(bool),
13    Uuid(uuid::Uuid),
14    Timestamp(chrono::NaiveDateTime),
15    Null,
16}
17
18/// SQL query builder with parameterized queries and JOIN support
19pub struct QueryBuilder {
20    table: String,
21    fields: Vec<String>,
22    joins: Vec<JoinClause>,
23    conditions: Vec<(String, QueryValue)>,
24    order_by: Vec<String>,
25    limit: Option<u32>,
26    offset: Option<u32>,
27    next_param_id: usize,
28}
29
30impl QueryBuilder {
31    pub fn new(table: &str) -> Self {
32        Self {
33            table: table.to_string(),
34            fields: vec!["*".to_string()],
35            joins: Vec::new(),
36            conditions: Vec::new(),
37            order_by: Vec::new(),
38            limit: None,
39            offset: None,
40            next_param_id: 1,
41        }
42    }
43
44    /// Select specific fields
45    pub fn select(mut self, fields: &[&str]) -> Self {
46        self.fields = fields.iter().map(|f| f.to_string()).collect();
47        self
48    }
49
50    /// Add WHERE field = ? condition (parameterized)
51    pub fn where_eq(mut self, field: &str, value: QueryValue) -> Self {
52        self.conditions.push((format!("{} = ${}", field, self.next_param_id), value));
53        self.next_param_id += 1;
54        self
55    }
56
57    /// Add WHERE field != ? condition (parameterized)
58    pub fn where_ne(mut self, field: &str, value: QueryValue) -> Self {
59        self.conditions.push((format!("{} != ${}", field, self.next_param_id), value));
60        self.next_param_id += 1;
61        self
62    }
63
64    /// Add WHERE field > ? condition (parameterized)
65    pub fn where_gt(mut self, field: &str, value: QueryValue) -> Self {
66        self.conditions.push((format!("{} > ${}", field, self.next_param_id), value));
67        self.next_param_id += 1;
68        self
69    }
70
71    /// Add WHERE field < ? condition (parameterized)
72    pub fn where_lt(mut self, field: &str, value: QueryValue) -> Self {
73        self.conditions.push((format!("{} < ${}", field, self.next_param_id), value));
74        self.next_param_id += 1;
75        self
76    }
77
78    /// Add WHERE field LIKE ? condition (parameterized)
79    pub fn where_like(mut self, field: &str, value: QueryValue) -> Self {
80        self.conditions.push((format!("{} LIKE ${}", field, self.next_param_id), value));
81        self.next_param_id += 1;
82        self
83    }
84
85    /// Add WHERE field IN (?) condition (parameterized)
86    pub fn where_in(mut self, field: &str, values: Vec<QueryValue>) -> Self {
87        if values.is_empty() {
88            return self;
89        }
90
91        let placeholders: Vec<String> = (0..values.len())
92            .map(|i| format!("${}", self.next_param_id + i))
93            .collect();
94
95        self.conditions.push((
96            format!("{} IN ({})", field, placeholders.join(", ")),
97            values[0].clone(), // First value for simplicity (would need list handling)
98        ));
99        self.next_param_id += values.len();
100        self
101    }
102
103    /// Add JOIN clause
104    pub fn join(mut self, join_type: JoinType, table: &str, on_condition: &str) -> Self {
105        self.joins.push(JoinClause {
106            join_type,
107            table: table.to_string(),
108            on_condition: on_condition.to_string(),
109            alias: None,
110        });
111        self
112    }
113
114    /// Add JOIN clause with table alias
115    pub fn join_alias(mut self, join_type: JoinType, table: &str, alias: &str, on_condition: &str) -> Self {
116        self.joins.push(JoinClause {
117            join_type,
118            table: table.to_string(),
119            on_condition: on_condition.to_string(),
120            alias: Some(alias.to_string()),
121        });
122        self
123    }
124
125    /// Add raw WHERE condition (for complex conditions)
126    pub fn where_raw(mut self, condition: &str, param: QueryValue) -> Self {
127        self.conditions.push((condition.to_string(), param));
128        self.next_param_id += 1;
129        self
130    }
131
132    /// Add ORDER BY clause
133    pub fn order_by(mut self, field: &str, direction: &str) -> Self {
134        let direction = direction.to_uppercase();
135        if direction == "ASC" || direction == "DESC" {
136            self.order_by.push(format!("{} {}", field, direction));
137        } else {
138            self.order_by.push(format!("{} ASC", field));
139        }
140        self
141    }
142
143    /// Add LIMIT clause
144    pub fn limit(mut self, limit: u32) -> Self {
145        self.limit = Some(limit);
146        self
147    }
148
149    /// Add OFFSET clause
150    pub fn offset(mut self, offset: u32) -> Self {
151        self.offset = Some(offset);
152        self
153    }
154
155    /// Build the SQL query string
156    pub fn build_sql(&self) -> String {
157        let mut query = format!("SELECT {} FROM {}", self.fields.join(", "), self.table);
158
159        // Add JOINs
160        for join in &self.joins {
161            let join_str = match join.join_type {
162                JoinType::Inner => "INNER JOIN",
163                JoinType::Left => "LEFT JOIN",
164                JoinType::Right => "RIGHT JOIN",
165                JoinType::Full => "FULL JOIN",
166                JoinType::Cross => "CROSS JOIN",
167            };
168
169            query.push(' ');
170            query.push_str(join_str);
171            query.push(' ');
172            query.push_str(&join.table);
173
174            if let Some(alias) = &join.alias {
175                query.push_str(" AS ");
176                query.push_str(alias);
177            }
178
179            query.push_str(" ON ");
180            query.push_str(&join.on_condition);
181        }
182
183        if !self.conditions.is_empty() {
184            let condition_strings: Vec<String> = self.conditions.iter()
185                .map(|(condition, _)| condition.clone())
186                .collect();
187            query.push_str(" WHERE ");
188            query.push_str(&condition_strings.join(" AND "));
189        }
190
191        if !self.order_by.is_empty() {
192            query.push_str(" ORDER BY ");
193            query.push_str(&self.order_by.join(", "));
194        }
195
196        if let Some(limit) = self.limit {
197            query.push_str(&format!(" LIMIT {}", limit));
198        }
199
200        if let Some(offset) = self.offset {
201            query.push_str(&format!(" OFFSET {}", offset));
202        }
203
204        query
205    }
206
207    /// Build query with parameters for execution
208    pub fn build_query(&self) -> (String, Vec<QueryValue>) {
209        let sql = self.build_sql();
210        let params: Vec<QueryValue> = self.conditions.iter()
211            .map(|(_, value)| value.clone())
212            .collect();
213
214        (sql, params)
215    }
216
217    /// Execute query and map results to a struct
218    pub async fn execute<T>(&self, pool: &PgPool) -> anyhow::Result<Vec<T>>
219    where
220        T: for<'r> FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
221    {
222        let (sql, params) = self.build_query();
223
224        let mut query = sqlx::query_as::<Postgres, T>(&sql);
225
226        // Bind parameters in order
227        for param in params {
228            match param {
229                QueryValue::Text(val) => query = query.bind(val),
230                QueryValue::Integer(val) => query = query.bind(val),
231                QueryValue::Float(val) => query = query.bind(val),
232                QueryValue::Boolean(val) => query = query.bind(val),
233                QueryValue::Uuid(val) => query = query.bind(val),
234                QueryValue::Timestamp(val) => query = query.bind(val),
235                QueryValue::Null => query = query.bind::<Option<String>>(None),
236            }
237        }
238
239        let results = query.fetch_all(pool).await?;
240        Ok(results)
241    }
242
243    /// Execute query and return first result
244    pub async fn execute_first<T>(&self, pool: &PgPool) -> anyhow::Result<Option<T>>
245    where
246        T: for<'r> FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
247    {
248        let (sql, params) = self.build_query();
249
250        let mut query = sqlx::query_as::<Postgres, T>(&sql);
251
252        // Bind parameters in order
253        for param in params {
254            match param {
255                QueryValue::Text(val) => query = query.bind(val),
256                QueryValue::Integer(val) => query = query.bind(val),
257                QueryValue::Float(val) => query = query.bind(val),
258                QueryValue::Boolean(val) => query = query.bind(val),
259                QueryValue::Uuid(val) => query = query.bind(val),
260                QueryValue::Timestamp(val) => query = query.bind(val),
261                QueryValue::Null => query = query.bind::<Option<String>>(None),
262            }
263        }
264
265        let result = query.fetch_optional(pool).await?;
266        Ok(result)
267    }
268}
269
270/// Convenience functions for creating query values
271impl QueryValue {
272    pub fn text<T: Into<String>>(value: T) -> Self {
273        QueryValue::Text(value.into())
274    }
275
276    pub fn integer(value: i64) -> Self {
277        QueryValue::Integer(value)
278    }
279
280    pub fn float(value: f64) -> Self {
281        QueryValue::Float(value)
282    }
283
284    pub fn boolean(value: bool) -> Self {
285        QueryValue::Boolean(value)
286    }
287
288    pub fn uuid(value: uuid::Uuid) -> Self {
289        QueryValue::Uuid(value)
290    }
291
292    pub fn timestamp(value: chrono::NaiveDateTime) -> Self {
293        QueryValue::Timestamp(value)
294    }
295
296    pub fn null() -> Self {
297        QueryValue::Null
298    }
299}
backbone_orm/query_builder.rs

backbone_orm/
query_builder.rs
