bottle_orm/query_builder.rs
1//! # Query Builder Module
2//!
3//! This module provides a fluent interface for constructing and executing SQL queries.
4//! It handles SELECT, INSERT, filtering (WHERE), pagination (LIMIT/OFFSET), and ordering operations
5//! with type-safe parameter binding across different database drivers.
6//!
7//! ## Features
8//!
9//! - **Fluent API**: Chainable methods for building complex queries
10//! - **Type-Safe Binding**: Automatic parameter binding with support for multiple types
11//! - **Multi-Driver Support**: Works with PostgreSQL, MySQL, and SQLite
12//! - **UUID Support**: Full support for UUID versions 1-7
13//! - **Pagination**: Built-in LIMIT/OFFSET support with helper methods
14//! - **Custom Filters**: Support for manual SQL construction with closures
15//!
16//! ## Example Usage
17//!
18//! ```rust,ignore
19//! use bottle_orm::{Database, Model};
20//! use uuid::Uuid;
21//!
22//! // Simple query
23//! let users: Vec<User> = db.model::<User>()
24//! .filter("age", ">=", 18)
25//! .order("created_at DESC")
26//! .limit(10)
27//! .scan()
28//! .await?;
29//!
30//! // Query with UUID filter
31//! let user_id = Uuid::new_v4();
32//! let user: User = db.model::<User>()
33//! .filter("id", "=", user_id)
34//! .first()
35//! .await?;
36//!
37//! // Insert a new record
38//! let new_user = User {
39//! id: Uuid::new_v7(uuid::Timestamp::now(uuid::NoContext)),
40//! username: "john_doe".to_string(),
41//! age: 25,
42//! };
43//! db.model::<User>().insert(&new_user).await?;
44//! ```
45
46// ============================================================================
47// External Crate Imports
48// ============================================================================
49
50use futures::future::BoxFuture;
51use heck::ToSnakeCase;
52use sqlx::{Any, Arguments, Decode, Encode, Type, any::AnyArguments};
53use std::marker::PhantomData;
54use uuid::Uuid;
55
56// ============================================================================
57// Internal Crate Imports
58// ============================================================================
59
60use crate::{
61 AnyImpl, Error,
62 any_struct::FromAnyRow,
63 database::{Connection, Drivers},
64 model::{ColumnInfo, Model},
65 temporal::{self, is_temporal_type},
66 value_binding::ValueBinder,
67};
68
69// ============================================================================
70// Type Aliases
71// ============================================================================
72
73/// A type alias for filter closures that support manual SQL construction and argument binding.
74///
75/// Filter functions receive the following parameters:
76/// 1. `&mut String` - The SQL query buffer being built
77/// 2. `&mut AnyArguments` - The argument container for binding values
78/// 3. `&Drivers` - The current database driver (determines placeholder syntax)
79/// 4. `&mut usize` - The argument counter (for PostgreSQL `$n` placeholders)
80///
81/// ## Example
82///
83/// ```rust,ignore
84/// let custom_filter: FilterFn = Box::new(|query, args, driver, counter| {
85/// query.push_str(" AND age > ");
86/// match driver {
87/// Drivers::Postgres => {
88/// query.push_str(&format!("${}", counter));
89/// *counter += 1;
90/// }
91/// _ => query.push('?'),
92/// }
93/// args.add(18);
94/// });
95/// });\n/// ```
96pub type FilterFn = Box<dyn Fn(&mut String, &mut AnyArguments<'_>, &Drivers, &mut usize) + Send + Sync>;
97
98// ============================================================================
99// Comparison Operators Enum
100// ============================================================================
101
102/// Type-safe comparison operators for filter conditions.
103///
104/// Use these instead of string operators for autocomplete support and type safety.
105///
106/// # Example
107///
108/// ```rust,ignore
109/// use bottle_orm::Op;
110///
111/// db.model::<User>()
112/// .filter(user_fields::AGE, Op::Gte, 18)
113/// .filter(user_fields::NAME, Op::Like, "%John%")
114/// .scan()
115/// .await?;
116/// ```
117#[derive(Debug, Clone, Copy, PartialEq, Eq)]
118pub enum Op {
119 /// Equal: `=`
120 Eq,
121 /// Not Equal: `!=` or `<>`
122 Ne,
123 /// Greater Than: `>`
124 Gt,
125 /// Greater Than or Equal: `>=`
126 Gte,
127 /// Less Than: `<`
128 Lt,
129 /// Less Than or Equal: `<=`
130 Lte,
131 /// SQL LIKE pattern matching
132 Like,
133 /// SQL NOT LIKE pattern matching
134 NotLike,
135 /// SQL IN (for arrays/lists)
136 In,
137 /// SQL NOT IN
138 NotIn,
139 /// SQL BETWEEN
140 Between,
141 /// SQL NOT BETWEEN
142 NotBetween,
143}
144
145impl Op {
146 /// Converts the operator to its SQL string representation.
147 pub fn as_sql(&self) -> &'static str {
148 match self {
149 Op::Eq => "=",
150 Op::Ne => "!=",
151 Op::Gt => ">",
152 Op::Gte => ">=",
153 Op::Lt => "<",
154 Op::Lte => "<=",
155 Op::Like => "LIKE",
156 Op::NotLike => "NOT LIKE",
157 Op::In => "IN",
158 Op::NotIn => "NOT IN",
159 Op::Between => "BETWEEN",
160 Op::NotBetween => "NOT BETWEEN",
161 }
162 }
163}
164
165// ============================================================================
166// QueryBuilder Struct
167// ============================================================================
168
169/// A fluent Query Builder for constructing SQL queries.
170///
171/// `QueryBuilder` provides a type-safe, ergonomic interface for building and executing
172/// SQL queries across different database backends. It supports filtering, ordering,
173/// pagination, and both SELECT and INSERT operations.
174///
175/// ## Type Parameter
176///
177/// * `'a` - Lifetime of the database reference (used for PhantomData)
178/// * `T` - The Model type this query operates on
179/// * `E` - The connection type (Database or Transaction)
180///
181/// ## Fields
182///
183/// * `db` - Reference to the database connection pool or transaction
184/// * `table_name` - Static string containing the table name
185/// * `columns_info` - Metadata about each column in the table
186/// * `columns` - List of column names in snake_case format
187/// * `select_columns` - Specific columns to select (empty = SELECT *)
188/// * `where_clauses` - List of filter functions to apply
189/// * `order_clauses` - List of ORDER BY clauses
190/// * `limit` - Maximum number of rows to return
191/// * `offset` - Number of rows to skip (for pagination)
192/// * `_marker` - PhantomData to bind the generic type T
193pub struct QueryBuilder<'a, T, E> {
194 /// Reference to the database connection pool
195 pub(crate) tx: E,
196
197 /// Database driver type
198 pub(crate) driver: Drivers,
199
200 /// Name of the database table (in original case)
201 pub(crate) table_name: &'static str,
202
203 pub(crate) alias: Option<String>,
204
205 /// Metadata information about each column
206 pub(crate) columns_info: Vec<ColumnInfo>,
207
208 /// List of column names (in snake_case)
209 pub(crate) columns: Vec<String>,
210
211 /// Specific columns to select (empty means SELECT *)
212 pub(crate) select_columns: Vec<String>,
213
214 /// Collection of WHERE clause filter functions
215 pub(crate) where_clauses: Vec<FilterFn>,
216
217 /// Collection of ORDER BY clauses
218 pub(crate) order_clauses: Vec<String>,
219
220 /// Collection of JOIN clause to filter entry tables
221 pub(crate) joins_clauses: Vec<String>,
222
223 /// Map of table names to their aliases used in JOINS
224 pub(crate) join_aliases: std::collections::HashMap<String, String>,
225
226 /// Maximum number of rows to return (LIMIT)
227 pub(crate) limit: Option<usize>,
228
229 /// Number of rows to skip (OFFSET)
230 pub(crate) offset: Option<usize>,
231
232 /// Activate debug mode in query
233 pub(crate) debug_mode: bool,
234
235 /// Clauses for GROUP BY
236 pub(crate) group_by_clauses: Vec<String>,
237
238 /// Clauses for HAVING
239 pub(crate) having_clauses: Vec<FilterFn>,
240
241 /// Distinct flag
242 pub(crate) is_distinct: bool,
243
244 /// Columns to omit from the query results (inverse of select_columns)
245 pub(crate) omit_columns: Vec<String>,
246
247 /// Whether to include soft-deleted records in query results
248 pub(crate) with_deleted: bool,
249
250 /// PhantomData to bind the generic type T
251 pub(crate) _marker: PhantomData<&'a T>,
252}
253
254// ============================================================================
255// QueryBuilder Implementation
256// ============================================================================
257
258impl<'a, T, E> QueryBuilder<'a, T, E>
259where
260 T: Model + Send + Sync + Unpin,
261 E: Connection + Send,
262{
263 // ========================================================================
264 // Constructor
265 // ========================================================================
266
267 /// Creates a new QueryBuilder instance.
268 ///
269 /// This constructor is typically called internally via `db.model::<T>()`.
270 /// You rarely need to call this directly.
271 ///
272 /// # Arguments
273 ///
274 /// * `db` - Reference to the database connection
275 /// * `table_name` - Name of the table to query
276 /// * `columns_info` - Metadata about table columns
277 /// * `columns` - List of column names
278 ///
279 /// # Returns
280 ///
281 /// A new `QueryBuilder` instance ready for query construction
282 ///
283 /// # Example
284 ///
285 /// ```rust,ignore
286 /// // Usually called via db.model::<User>()
287 /// let query = db.model::<User>();
288 /// ```
289 pub fn new(
290 tx: E,
291 driver: Drivers,
292 table_name: &'static str,
293 columns_info: Vec<ColumnInfo>,
294 columns: Vec<String>,
295 ) -> Self {
296 // Pre-populate omit_columns with globally omitted columns (from #[orm(omit)] attribute)
297 let omit_columns: Vec<String> =
298 columns_info.iter().filter(|c| c.omit).map(|c| c.name.to_snake_case()).collect();
299
300 Self {
301 tx,
302 alias: None,
303 driver,
304 table_name,
305 columns_info,
306 columns,
307 debug_mode: false,
308 select_columns: Vec::new(),
309 where_clauses: Vec::new(),
310 order_clauses: Vec::new(),
311 joins_clauses: Vec::new(),
312 join_aliases: std::collections::HashMap::new(),
313 group_by_clauses: Vec::new(),
314 having_clauses: Vec::new(),
315 is_distinct: false,
316 omit_columns,
317 limit: None,
318 offset: None,
319 with_deleted: false,
320 _marker: PhantomData,
321 }
322 }
323
324 /// Returns the table name or alias if set.
325 pub(crate) fn get_table_identifier(&self) -> String {
326 self.alias.clone().unwrap_or_else(|| self.table_name.to_snake_case())
327 }
328
329 // ========================================================================
330 // Query Building Methods
331 // ========================================================================
332
333 /// Internal helper to add a WHERE clause with a specific join operator.
334 fn filter_internal<V>(mut self, joiner: &str, col: &'static str, op: Op, value: V) -> Self
335 where
336 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
337 {
338 let op_str = op.as_sql();
339 let table_id = self.get_table_identifier();
340 // Check if the column exists in the main table to avoid ambiguous references in JOINS
341 let is_main_col = self.columns.contains(&col.to_snake_case());
342 let joiner_owned = joiner.to_string();
343 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
344 query.push_str(&joiner_owned);
345 if let Some((table, column)) = col.split_once(".") {
346 // If explicit table prefix is provided, use it
347 query.push_str(&format!("\"{}\".\"{}\"", table, column));
348 } else if is_main_col {
349 // If it's a known column of the main table, apply the table name/alias prefix
350 query.push_str(&format!("\"{}\".\"{}\"", table_id, col));
351 } else {
352 // Otherwise leave it unqualified so the DB can resolve it (or fail if ambiguous)
353 query.push_str(&format!("\"{}\"", col));
354 }
355 query.push(' ');
356 query.push_str(op_str);
357 query.push(' ');
358
359 // Handle different placeholder syntaxes based on database driver
360 match driver {
361 // PostgreSQL uses numbered placeholders: $1, $2, $3, ...
362 Drivers::Postgres => {
363 query.push_str(&format!("${}", arg_counter));
364 *arg_counter += 1;
365 }
366 // MySQL and SQLite use question mark placeholders: ?
367 _ => query.push('?'),
368 }
369
370 // Bind the value to the query
371 let _ = args.add(value.clone());
372 });
373
374 self.where_clauses.push(clause);
375 self
376 }
377
378 /// Adds a WHERE clause to the query.
379 ///
380 /// This method adds a filter condition to the query. Multiple filters can be chained
381 /// and will be combined with AND operators. The value is bound as a parameter to
382 /// prevent SQL injection.
383 ///
384 /// # Type Parameters
385 ///
386 /// * `V` - The type of the value to filter by. Must be encodable for SQL queries.
387 ///
388 /// # Arguments
389 ///
390 /// * `col` - The column name to filter on
391 /// * `op` - The comparison operator (e.g., "=", ">", "LIKE", "IN")
392 /// * `value` - The value to compare against
393 ///
394 /// # Example
395 ///
396 /// ```rust,ignore
397 /// query.filter("age", Op::Gte, 18)
398 /// ```
399 pub fn filter<V>(self, col: &'static str, op: Op, value: V) -> Self
400 where
401 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
402 {
403 self.filter_internal(" AND ", col, op, value)
404 }
405
406 /// Adds an OR WHERE clause to the query.
407 ///
408 /// # Example
409 ///
410 /// ```rust,ignore
411 /// query.filter("age", Op::Lt, 18).or_filter("active", Op::Eq, false)
412 /// ```
413 pub fn or_filter<V>(self, col: &'static str, op: Op, value: V) -> Self
414 where
415 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
416 {
417 self.filter_internal(" OR ", col, op, value)
418 }
419
420 /// Adds an AND NOT WHERE clause to the query.
421 pub fn not_filter<V>(self, col: &'static str, op: Op, value: V) -> Self
422 where
423 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
424 {
425 self.filter_internal(" AND NOT ", col, op, value)
426 }
427
428 /// Adds an OR NOT WHERE clause to the query.
429 pub fn or_not_filter<V>(self, col: &'static str, op: Op, value: V) -> Self
430 where
431 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
432 {
433 self.filter_internal(" OR NOT ", col, op, value)
434 }
435
436 /// Adds a BETWEEN clause to the query.
437 pub fn between<V>(mut self, col: &'static str, start: V, end: V) -> Self
438 where
439 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
440 {
441 let table_id = self.get_table_identifier();
442 let is_main_col = self.columns.contains(&col.to_snake_case());
443 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
444 query.push_str(" AND ");
445 if is_main_col {
446 query.push_str(&format!("\"{}\".\"{}\"", table_id, col));
447 } else {
448 query.push_str(&format!("\"{}\"", col));
449 }
450 query.push_str(" BETWEEN ");
451
452 match driver {
453 Drivers::Postgres => {
454 query.push_str(&format!("${} AND ${}", arg_counter, *arg_counter + 1));
455 *arg_counter += 2;
456 }
457 _ => query.push_str("? AND ?"),
458 }
459
460 let _ = args.add(start.clone());
461 let _ = args.add(end.clone());
462 });
463 self.where_clauses.push(clause);
464 self
465 }
466
467 /// Adds an OR BETWEEN clause to the query.
468 pub fn or_between<V>(mut self, col: &'static str, start: V, end: V) -> Self
469 where
470 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
471 {
472 let table_id = self.get_table_identifier();
473 let is_main_col = self.columns.contains(&col.to_snake_case());
474 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
475 query.push_str(" OR ");
476 if is_main_col {
477 query.push_str(&format!("\"{}\".\"{}\"", table_id, col));
478 } else {
479 query.push_str(&format!("\"{}\"", col));
480 }
481 query.push_str(" BETWEEN ");
482
483 match driver {
484 Drivers::Postgres => {
485 query.push_str(&format!("${} AND ${}", arg_counter, *arg_counter + 1));
486 *arg_counter += 2;
487 }
488 _ => query.push_str("? AND ?"),
489 }
490
491 let _ = args.add(start.clone());
492 let _ = args.add(end.clone());
493 });
494 self.where_clauses.push(clause);
495 self
496 }
497
498 /// Adds an IN list clause to the query.
499 pub fn in_list<V>(mut self, col: &'static str, values: Vec<V>) -> Self
500 where
501 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
502 {
503 if values.is_empty() {
504 // WHERE 1=0 to ensure empty result
505 let clause: FilterFn = Box::new(|query, _, _, _| {
506 query.push_str(" AND 1=0");
507 });
508 self.where_clauses.push(clause);
509 return self;
510 }
511
512 let table_id = self.get_table_identifier();
513 let is_main_col = self.columns.contains(&col.to_snake_case());
514 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
515 query.push_str(" AND ");
516 if is_main_col {
517 query.push_str(&format!("\"{}\".\"{}\"", table_id, col));
518 } else {
519 query.push_str(&format!("\"{}\"", col));
520 }
521 query.push_str(" IN (");
522
523 let mut placeholders = Vec::new();
524 for _ in &values {
525 match driver {
526 Drivers::Postgres => {
527 placeholders.push(format!("${}", arg_counter));
528 *arg_counter += 1;
529 }
530 _ => placeholders.push("?".to_string()),
531 }
532 }
533 query.push_str(&placeholders.join(", "));
534 query.push(')');
535
536 for val in &values {
537 let _ = args.add(val.clone());
538 }
539 });
540 self.where_clauses.push(clause);
541 self
542 }
543
544 /// Adds an OR IN list clause to the query.
545 pub fn or_in_list<V>(mut self, col: &'static str, values: Vec<V>) -> Self
546 where
547 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
548 {
549 if values.is_empty() {
550 return self;
551 }
552
553 let table_id = self.get_table_identifier();
554 let is_main_col = self.columns.contains(&col.to_snake_case());
555 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
556 query.push_str(" OR ");
557 if is_main_col {
558 query.push_str(&format!("\"{}\".\"{}\"", table_id, col));
559 } else {
560 query.push_str(&format!("\"{}\"", col));
561 }
562 query.push_str(" IN (");
563
564 let mut placeholders = Vec::new();
565 for _ in &values {
566 match driver {
567 Drivers::Postgres => {
568 placeholders.push(format!("${}", arg_counter));
569 *arg_counter += 1;
570 }
571 _ => placeholders.push("?".to_string()),
572 }
573 }
574 query.push_str(&placeholders.join(", "));
575 query.push(')');
576
577 for val in &values {
578 let _ = args.add(val.clone());
579 }
580 });
581 self.where_clauses.push(clause);
582 self
583 }
584
585 /// Groups filters inside parentheses with an AND operator.
586 pub fn group<F>(mut self, f: F) -> Self
587 where
588 F: FnOnce(Self) -> Self,
589 {
590 let old_clauses = std::mem::take(&mut self.where_clauses);
591 self = f(self);
592 let group_clauses = std::mem::take(&mut self.where_clauses);
593 self.where_clauses = old_clauses;
594
595 if !group_clauses.is_empty() {
596 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
597 query.push_str(" AND (1=1");
598 for c in &group_clauses {
599 c(query, args, driver, arg_counter);
600 }
601 query.push_str(")");
602 });
603 self.where_clauses.push(clause);
604 }
605 self
606 }
607
608 /// Groups filters inside parentheses with an OR operator.
609 pub fn or_group<F>(mut self, f: F) -> Self
610 where
611 F: FnOnce(Self) -> Self,
612 {
613 let old_clauses = std::mem::take(&mut self.where_clauses);
614 self = f(self);
615 let group_clauses = std::mem::take(&mut self.where_clauses);
616 self.where_clauses = old_clauses;
617
618 if !group_clauses.is_empty() {
619 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
620 query.push_str(" OR (1=1");
621 for c in &group_clauses {
622 c(query, args, driver, arg_counter);
623 }
624 query.push_str(")");
625 });
626 self.where_clauses.push(clause);
627 }
628 self
629 }
630
631 /// Adds a raw WHERE clause with a placeholder and a single value.
632 ///
633 /// This allows writing raw SQL conditions with a `?` placeholder.
634 /// To use multiple placeholders with different types, chain multiple `where_raw` calls.
635 ///
636 /// # Arguments
637 ///
638 /// * `sql` - Raw SQL string with one `?` placeholder (e.g., "age > ?")
639 /// * `value` - Value to bind
640 ///
641 /// # Example
642 ///
643 /// ```rust,ignore
644 /// db.model::<User>()
645 /// .where_raw("name = ?", "Alice".to_string())
646 /// .where_raw("age >= ?", 18)
647 /// .scan()
648 /// .await?;
649 /// ```
650 pub fn where_raw<V>(mut self, sql: &str, value: V) -> Self
651 where
652 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
653 {
654 self.where_clauses.push(self.create_raw_clause(" AND ", sql, value));
655 self
656 }
657
658 /// Adds a raw OR WHERE clause with a placeholder.
659 pub fn or_where_raw<V>(mut self, sql: &str, value: V) -> Self
660 where
661 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
662 {
663 self.where_clauses.push(self.create_raw_clause(" OR ", sql, value));
664 self
665 }
666
667 /// Internal helper to create a raw SQL clause with a single value.
668 fn create_raw_clause<V>(&self, joiner: &'static str, sql: &str, value: V) -> FilterFn
669 where
670 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
671 {
672 let sql_owned = sql.to_string();
673 Box::new(move |query, args, driver, arg_counter| {
674 query.push_str(joiner);
675
676 let mut processed_sql = sql_owned.clone();
677 if let Some(pos) = processed_sql.find('?') {
678 let placeholder = match driver {
679 Drivers::Postgres => {
680 let p = format!("${}", arg_counter);
681 *arg_counter += 1;
682 p
683 }
684 _ => "?".to_string(),
685 };
686 processed_sql.replace_range(pos..pos + 1, &placeholder);
687 }
688
689 query.push_str(&processed_sql);
690 let _ = args.add(value.clone());
691 })
692 }
693
694 /// Adds an equality filter to the query.
695 ///
696 /// This is a convenience wrapper around `filter()` for simple equality checks.
697 /// It is equivalent to calling `filter(col, "=", value)`.
698 ///
699 /// # Type Parameters
700 ///
701 /// * `V` - The type of the value to compare against.
702 ///
703 /// # Arguments
704 ///
705 /// * `col` - The column name to filter on.
706 /// * `value` - The value to match.
707 ///
708 /// # Example
709 ///
710 /// ```rust,ignore
711 /// // Equivalent to filter("age", Op::Eq, 18)
712 /// query.equals("age", 18)
713 /// ```
714 pub fn equals<V>(self, col: &'static str, value: V) -> Self
715 where
716 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
717 {
718 self.filter(col, Op::Eq, value)
719 }
720
721 /// Adds an ORDER BY clause to the query.
722 ///
723 /// Specifies the sort order for the query results. Multiple order clauses
724 /// can be added and will be applied in the order they were added.
725 ///
726 /// # Arguments
727 ///
728 /// * `order` - The ORDER BY expression (e.g., "created_at DESC", "age ASC, name DESC")
729 ///
730 /// # Example
731 ///
732 /// ```rust,ignore
733 /// // Single column ascending (ASC is default)
734 /// query.order("age")
735 ///
736 /// // Single column descending
737 /// query.order("created_at DESC")
738 ///
739 /// // Multiple columns
740 /// query.order("age DESC, username ASC")
741 ///
742 /// // Chain multiple order clauses
743 /// query
744 /// .order("priority DESC")
745 /// .order("created_at ASC")
746 /// ```
747 pub fn order(mut self, order: &str) -> Self {
748 self.order_clauses.push(order.to_string());
749 self
750 }
751
752 /// Defines a SQL alias for the primary table in the query.
753 ///
754 /// This method allows you to set a short alias for the model's underlying table.
755 /// It is highly recommended when writing complex queries with multiple `JOIN` clauses,
756 /// preventing the need to repeat the full table name in `.filter()`, `.equals()`, or `.select()`.
757 ///
758 /// # Arguments
759 ///
760 /// * `alias` - A string slice representing the alias to be used (e.g., "u", "rp").
761 ///
762 /// # Example
763 ///
764 /// ```rust,ignore
765 /// // Using 'u' as an alias for the User table
766 /// let results = db.model::<User>()
767 /// .alias("u")
768 /// .join("role_permissions rp", "rp.role_id = u.role")
769 /// .equals("u.id", user_id)
770 /// .select("u.username, rp.permission_id")
771 /// .scan_as::<UserPermissionDTO>()
772 /// .await?;
773 /// ```
774 pub fn alias(mut self, alias: &str) -> Self {
775 self.alias = Some(alias.to_string());
776 self
777 }
778
779 /// Placeholder for eager loading relationships (preload).
780 ///
781 /// This method is reserved for future implementation of relationship preloading.
782 /// Currently, it returns `self` unchanged to maintain the fluent interface.
783 ///
784 /// # Future Implementation
785 ///
786 /// Will support eager loading of related models to avoid N+1 query problems:
787 ///
788 /// ```rust,ignore
789 /// // Future usage example
790 /// query.preload("posts").preload("comments")
791 /// ```
792 // pub fn preload(self) -> Self {
793 // // TODO: Implement relationship preloading
794 // self
795 // }
796
797 /// Activates debug mode for this query.
798 ///
799 /// When enabled, the generated SQL query will be logged using the `log` crate
800 /// at the `DEBUG` level before execution.
801 ///
802 /// # Note
803 ///
804 /// To see the output, you must initialize a logger in your application (e.g., using `env_logger`)
805 /// and configure it to display `debug` logs for `bottle_orm`.
806 ///
807 /// # Example
808 ///
809 /// ```rust,ignore
810 /// db.model::<User>()
811 /// .filter("active", "=", true)
812 /// .debug() // Logs SQL: SELECT * FROM "user" WHERE "active" = $1
813 /// .scan()
814 /// .await?;
815 /// ```
816 pub fn debug(mut self) -> Self {
817 self.debug_mode = true;
818 self
819 }
820
821 /// Adds an IS NULL filter for the specified column.
822 ///
823 /// # Arguments
824 ///
825 /// * `col` - The column name to check for NULL
826 ///
827 /// # Example
828 ///
829 /// ```rust,ignore
830 /// db.model::<User>()
831 /// .is_null("deleted_at")
832 /// .scan()
833 /// .await?;
834 /// // SQL: SELECT * FROM "user" WHERE "deleted_at" IS NULL
835 /// ```
836 pub fn is_null(mut self, col: &str) -> Self {
837 let col_owned = col.to_string();
838 let table_id = self.get_table_identifier();
839 let is_main_col = self.columns.contains(&col_owned.to_snake_case());
840 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
841 query.push_str(" AND ");
842 if let Some((table, column)) = col_owned.split_once(".") {
843 query.push_str(&format!("\"{}\".\"{}\"", table, column));
844 } else if is_main_col {
845 query.push_str(&format!("\"{}\".\"{}\"", table_id, col_owned));
846 } else {
847 query.push_str(&format!("\"{}\"", col_owned));
848 }
849 query.push_str(" IS NULL");
850 });
851 self.where_clauses.push(clause);
852 self
853 }
854
855 /// Adds an IS NOT NULL filter for the specified column.
856 ///
857 /// # Arguments
858 ///
859 /// * `col` - The column name to check for NOT NULL
860 ///
861 /// # Example
862 ///
863 /// ```rust,ignore
864 /// db.model::<User>()
865 /// .is_not_null("email")
866 /// .scan()
867 /// .await?;
868 /// // SQL: SELECT * FROM "user" WHERE "email" IS NOT NULL
869 /// ```
870 pub fn is_not_null(mut self, col: &str) -> Self {
871 let col_owned = col.to_string();
872 let table_id = self.get_table_identifier();
873 let is_main_col = self.columns.contains(&col_owned.to_snake_case());
874 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
875 query.push_str(" AND ");
876 if let Some((table, column)) = col_owned.split_once(".") {
877 query.push_str(&format!("\"{}\".\"{}\"", table, column));
878 } else if is_main_col {
879 query.push_str(&format!("\"{}\".\"{}\"", table_id, col_owned));
880 } else {
881 query.push_str(&format!("\"{}\"", col_owned));
882 }
883 query.push_str(" IS NOT NULL");
884 });
885 self.where_clauses.push(clause);
886 self
887 }
888
889 /// Includes soft-deleted records in query results.
890 ///
891 /// By default, queries on models with a `#[orm(soft_delete)]` column exclude
892 /// records where that column is not NULL. This method disables that filter.
893 ///
894 /// # Example
895 ///
896 /// ```rust,ignore
897 /// // Get all users including deleted ones
898 /// db.model::<User>()
899 /// .with_deleted()
900 /// .scan()
901 /// .await?;
902 /// ```
903 pub fn with_deleted(mut self) -> Self {
904 self.with_deleted = true;
905 self
906 }
907
908 /// Placeholder for JOIN operations.
909 ///
910 /// This method is reserved for future implementation of SQL JOINs.
911 /// Currently, it returns `self` unchanged to maintain the fluent interface.
912 ///
913 /// # Future Implementation
914 ///
915 /// Will support various types of JOINs (INNER, LEFT, RIGHT, FULL):
916 ///
917 /// ```rust,ignore
918 /// Adds a JOIN clause to the query.
919 ///
920 /// # Arguments
921 ///
922 /// * `table` - The name of the table to join.
923 /// * `s_query` - The ON clause condition (e.g., "users.id = posts.user_id").
924 ///
925 /// # Example
926 ///
927 /// ```rust,ignore
928 /// query.join("posts", "users.id = posts.user_id")
929 /// ```
930 pub fn join(mut self, table: &str, s_query: &str) -> Self {
931 let trimmed_value = s_query.replace(" ", "");
932 let values = trimmed_value.split_once("=");
933 let parsed_query: String;
934 if let Some((first, second)) = values {
935 let ref_table = first.split_once(".").expect("failed to parse JOIN clause");
936 let to_table = second.split_once(".").expect("failed to parse JOIN clause");
937 parsed_query = format!("\"{}\".\"{}\" = \"{}\".\"{}\"", ref_table.0, ref_table.1, to_table.0, to_table.1);
938 } else {
939 panic!("Failed to parse JOIN, Ex to use: .join(\"table2\", \"table2.column = table.column\")")
940 }
941
942 if let Some((table_name, alias)) = table.split_once(" ") {
943 self.join_aliases.insert(table_name.to_snake_case(), alias.to_string());
944 self.joins_clauses.push(format!("JOIN \"{}\" {} ON {}", table_name, alias, parsed_query));
945 } else {
946 self.joins_clauses.push(format!("JOIN \"{}\" ON {}", table, parsed_query));
947 }
948 self
949 }
950
951 /// Internal helper for specific join types
952 fn join_generic(mut self, join_type: &str, table: &str, s_query: &str) -> Self {
953 let trimmed_value = s_query.replace(" ", "");
954 let values = trimmed_value.split_once("=");
955 let parsed_query: String;
956 if let Some((first, second)) = values {
957 let ref_table = first.split_once(".").expect("failed to parse JOIN clause");
958 let to_table = second.split_once(".").expect("failed to parse JOIN clause");
959 parsed_query = format!("\"{}\".\"{}\" = \"{}\".\"{}\"", ref_table.0, ref_table.1, to_table.0, to_table.1);
960 } else {
961 panic!("Failed to parse JOIN, Ex to use: .join(\"table2\", \"table2.column = table.column\")")
962 }
963
964 if let Some((table_name, alias)) = table.split_once(" ") {
965 self.join_aliases.insert(table_name.to_snake_case(), alias.to_string());
966 self.joins_clauses.push(format!("{} JOIN \"{}\" {} ON {}", join_type, table_name, alias, parsed_query));
967 } else {
968 self.joins_clauses.push(format!("{} JOIN \"{}\" ON {}", join_type, table, parsed_query));
969 }
970 self
971 }
972
973 /// Adds a LEFT JOIN clause.
974 ///
975 /// Performs a LEFT JOIN with another table. Returns all records from the left table,
976 /// and the matched records from the right table (or NULL if no match).
977 ///
978 /// # Arguments
979 ///
980 /// * `table` - The name of the table to join with
981 /// * `on` - The join condition (e.g., "users.id = posts.user_id")
982 ///
983 /// # Example
984 ///
985 /// ```rust,ignore
986 /// // Get all users and their posts (if any)
987 /// let users_with_posts = db.model::<User>()
988 /// .left_join("posts", "users.id = posts.user_id")
989 /// .scan()
990 /// .await?;
991 /// ```
992 pub fn left_join(self, table: &str, on: &str) -> Self {
993 self.join_generic("LEFT", table, on)
994 }
995
996 /// Adds a RIGHT JOIN clause.
997 ///
998 /// Performs a RIGHT JOIN with another table. Returns all records from the right table,
999 /// and the matched records from the left table (or NULL if no match).
1000 ///
1001 /// # Arguments
1002 ///
1003 /// * `table` - The name of the table to join with
1004 /// * `on` - The join condition
1005 ///
1006 /// # Example
1007 ///
1008 /// ```rust,ignore
1009 /// let posts_with_users = db.model::<Post>()
1010 /// .right_join("users", "posts.user_id = users.id")
1011 /// .scan()
1012 /// .await?;
1013 /// ```
1014 pub fn right_join(self, table: &str, on: &str) -> Self {
1015 self.join_generic("RIGHT", table, on)
1016 }
1017
1018 /// Adds an INNER JOIN clause.
1019 ///
1020 /// Performs an INNER JOIN with another table. Returns records that have matching
1021 /// values in both tables.
1022 ///
1023 /// # Arguments
1024 ///
1025 /// * `table` - The name of the table to join with
1026 /// * `on` - The join condition
1027 ///
1028 /// # Example
1029 ///
1030 /// ```rust,ignore
1031 /// // Get only users who have posts
1032 /// let active_users = db.model::<User>()
1033 /// .inner_join("posts", "users.id = posts.user_id")
1034 /// .scan()
1035 /// .await?;
1036 /// ```
1037 pub fn inner_join(self, table: &str, on: &str) -> Self {
1038 self.join_generic("INNER", table, on)
1039 }
1040
1041 /// Adds a FULL JOIN clause.
1042 ///
1043 /// Performs a FULL OUTER JOIN. Returns all records when there is a match in
1044 /// either left or right table.
1045 ///
1046 /// # Arguments
1047 ///
1048 /// * `table` - The name of the table to join with
1049 /// * `on` - The join condition
1050 ///
1051 /// # Note
1052 ///
1053 /// Support for FULL JOIN depends on the underlying database engine (e.g., SQLite
1054 /// does not support FULL JOIN directly).
1055 pub fn full_join(self, table: &str, on: &str) -> Self {
1056 self.join_generic("FULL", table, on)
1057 }
1058
1059 /// Marks the query to return DISTINCT results.
1060 ///
1061 /// Adds the `DISTINCT` keyword to the SELECT statement, ensuring that unique
1062 /// rows are returned.
1063 ///
1064 /// # Example
1065 ///
1066 /// ```rust,ignore
1067 /// // Get unique ages of users
1068 /// let unique_ages: Vec<i32> = db.model::<User>()
1069 /// .select("age")
1070 /// .distinct()
1071 /// .scan()
1072 /// .await?;
1073 /// ```
1074 pub fn distinct(mut self) -> Self {
1075 self.is_distinct = true;
1076 self
1077 }
1078
1079 /// Adds a GROUP BY clause to the query.
1080 ///
1081 /// Groups rows that have the same values into summary rows. Often used with
1082 /// aggregate functions (COUNT, MAX, MIN, SUM, AVG).
1083 ///
1084 /// # Arguments
1085 ///
1086 /// * `columns` - Comma-separated list of columns to group by
1087 ///
1088 /// # Example
1089 ///
1090 /// ```rust,ignore
1091 /// // Count users by age group
1092 /// let stats: Vec<(i32, i64)> = db.model::<User>()
1093 /// .select("age, COUNT(*)")
1094 /// .group_by("age")
1095 /// .scan()
1096 /// .await?;
1097 /// ```
1098 pub fn group_by(mut self, columns: &str) -> Self {
1099 self.group_by_clauses.push(columns.to_string());
1100 self
1101 }
1102
1103 /// Adds a HAVING clause to the query.
1104 ///
1105 /// Used to filter groups created by `group_by`. Similar to `filter` (WHERE),
1106 /// but operates on grouped records and aggregate functions.
1107 ///
1108 /// # Arguments
1109 ///
1110 /// * `col` - The column or aggregate function to filter on
1111 /// * `op` - Comparison operator
1112 /// * `value` - Value to compare against
1113 ///
1114 /// # Example
1115 ///
1116 /// ```rust,ignore
1117 /// // Get ages with more than 5 users
1118 /// let popular_ages = db.model::<User>()
1119 /// .select("age, COUNT(*)")
1120 /// .group_by("age")
1121 /// .having("COUNT(*)", Op::Gt, 5)
1122 /// .scan()
1123 /// .await?;
1124 /// ```
1125 pub fn having<V>(mut self, col: &'static str, op: Op, value: V) -> Self
1126 where
1127 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
1128 {
1129 let op_str = op.as_sql();
1130 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
1131 query.push_str(" AND ");
1132 query.push_str(col);
1133 query.push(' ');
1134 query.push_str(op_str);
1135 query.push(' ');
1136
1137 match driver {
1138 Drivers::Postgres => {
1139 query.push_str(&format!("${}", arg_counter));
1140 *arg_counter += 1;
1141 }
1142 _ => query.push('?'),
1143 }
1144 let _ = args.add(value.clone());
1145 });
1146
1147 self.having_clauses.push(clause);
1148 self
1149 }
1150
1151 /// Returns the COUNT of rows matching the query.
1152 ///
1153 /// A convenience method that automatically sets `SELECT COUNT(*)` and returns
1154 /// the result as an `i64`.
1155 ///
1156 /// # Returns
1157 ///
1158 /// * `Ok(i64)` - The count of rows
1159 /// * `Err(sqlx::Error)` - Database error
1160 ///
1161 /// # Example
1162 ///
1163 /// ```rust,ignore
1164 /// let user_count = db.model::<User>().count().await?;
1165 /// ```
1166 pub async fn count(mut self) -> Result<i64, sqlx::Error> {
1167 self.select_columns = vec!["COUNT(*)".to_string()];
1168 self.scalar::<i64>().await
1169 }
1170
1171 /// Returns the SUM of the specified column.
1172 ///
1173 /// Calculates the sum of a numeric column.
1174 ///
1175 /// # Arguments
1176 ///
1177 /// * `column` - The column to sum
1178 ///
1179 /// # Example
1180 ///
1181 /// ```rust,ignore
1182 /// let total_age: i64 = db.model::<User>().sum("age").await?;
1183 /// ```
1184 pub async fn sum<N>(mut self, column: &str) -> Result<N, sqlx::Error>
1185 where
1186 N: FromAnyRow + for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
1187 {
1188 self.select_columns = vec![format!("SUM({})", column)];
1189 self.scalar::<N>().await
1190 }
1191
1192 /// Returns the AVG of the specified column.
1193 ///
1194 /// Calculates the average value of a numeric column.
1195 ///
1196 /// # Arguments
1197 ///
1198 /// * `column` - The column to average
1199 ///
1200 /// # Example
1201 ///
1202 /// ```rust,ignore
1203 /// let avg_age: f64 = db.model::<User>().avg("age").await?;
1204 /// ```
1205 pub async fn avg<N>(mut self, column: &str) -> Result<N, sqlx::Error>
1206 where
1207 N: FromAnyRow + for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
1208 {
1209 self.select_columns = vec![format!("AVG({})", column)];
1210 self.scalar::<N>().await
1211 }
1212
1213 /// Returns the MIN of the specified column.
1214 ///
1215 /// Finds the minimum value in a column.
1216 ///
1217 /// # Arguments
1218 ///
1219 /// * `column` - The column to check
1220 ///
1221 /// # Example
1222 ///
1223 /// ```rust,ignore
1224 /// let min_age: i32 = db.model::<User>().min("age").await?;
1225 /// ```
1226 pub async fn min<N>(mut self, column: &str) -> Result<N, sqlx::Error>
1227 where
1228 N: FromAnyRow + for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
1229 {
1230 self.select_columns = vec![format!("MIN({})", column)];
1231 self.scalar::<N>().await
1232 }
1233
1234 /// Returns the MAX of the specified column.
1235 ///
1236 /// Finds the maximum value in a column.
1237 ///
1238 /// # Arguments
1239 ///
1240 /// * `column` - The column to check
1241 ///
1242 /// # Example
1243 ///
1244 /// ```rust,ignore
1245 /// let max_age: i32 = db.model::<User>().max("age").await?;
1246 /// ```
1247 pub async fn max<N>(mut self, column: &str) -> Result<N, sqlx::Error>
1248 where
1249 N: FromAnyRow + for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
1250 {
1251 self.select_columns = vec![format!("MAX({})", column)];
1252 self.scalar::<N>().await
1253 }
1254
1255 /// Applies pagination with validation and limits.
1256 ///
1257 /// This is a convenience method that combines `limit()` and `offset()` with
1258 /// built-in validation and maximum value enforcement for safer pagination.
1259 ///
1260 /// # Arguments
1261 ///
1262 /// * `max_value` - Maximum allowed items per page
1263 /// * `default` - Default value if `value` exceeds `max_value`
1264 /// * `page` - Zero-based page number
1265 /// * `value` - Requested items per page
1266 ///
1267 /// # Returns
1268 ///
1269 /// * `Ok(Self)` - The updated QueryBuilder with pagination applied
1270 /// * `Err(Error)` - If `value` is negative
1271 ///
1272 /// # Pagination Logic
1273 ///
1274 /// 1. Validates that `value` is non-negative
1275 /// 2. If `value` > `max_value`, uses `default` instead
1276 /// 3. Calculates offset as: `value * page`
1277 /// 4. Sets limit to `value`
1278 ///
1279 /// # Example
1280 ///
1281 /// ```rust,ignore
1282 /// // Page 0 with 10 items (page 1 in 1-indexed systems)
1283 /// query.pagination(100, 20, 0, 10)? // LIMIT 10 OFFSET 0
1284 ///
1285 /// // Page 2 with 25 items (page 3 in 1-indexed systems)
1286 /// query.pagination(100, 20, 2, 25)? // LIMIT 25 OFFSET 50
1287 ///
1288 /// // Request too many items, falls back to default
1289 /// query.pagination(100, 20, 0, 150)? // LIMIT 20 OFFSET 0 (150 > 100)
1290 ///
1291 /// // Error: negative value
1292 /// query.pagination(100, 20, 0, -10)? // Returns Error
1293 /// ```
1294 pub fn pagination(mut self, max_value: usize, default: usize, page: usize, value: isize) -> Result<Self, Error> {
1295 // Validate that value is non-negative
1296 if value < 0 {
1297 return Err(Error::InvalidArgument("value cannot be negative".into()));
1298 }
1299
1300 let mut f_value = value as usize;
1301
1302 // Enforce maximum value limit
1303 if f_value > max_value {
1304 f_value = default;
1305 }
1306
1307 // Apply offset and limit
1308 self = self.offset(f_value * page);
1309 self = self.limit(f_value);
1310
1311 Ok(self)
1312 }
1313
1314 /// Selects specific columns to return.
1315 ///
1316 /// By default, queries use `SELECT *` to return all columns. This method
1317 /// allows you to specify exactly which columns should be returned.
1318 ///
1319 /// **Note:** Columns are pushed exactly as provided, without automatic
1320 /// snake_case conversion, allowing for aliases and raw SQL fragments.
1321 ///
1322 /// # Arguments
1323 ///
1324 /// * `columns` - Comma-separated list of column names to select
1325 ///
1326 /// # Example
1327 ///
1328 /// ```rust,ignore
1329 /// // Select single column
1330 /// query.select("id")
1331 ///
1332 /// // Select multiple columns
1333 /// query.select("id, username, email")
1334 ///
1335 /// // Select with SQL functions and aliases (now supported)
1336 /// query.select("COUNT(*) as total_count")
1337 /// ```
1338 pub fn select(mut self, columns: &str) -> Self {
1339 self.select_columns.push(columns.to_string());
1340 self
1341 }
1342
1343 /// Excludes specific columns from the query results.
1344 ///
1345 /// This is the inverse of `select()`. Instead of specifying which columns to include,
1346 /// you specify which columns to exclude. All other columns will be returned.
1347 ///
1348 /// # Arguments
1349 ///
1350 /// * `columns` - Comma-separated list of column names to exclude
1351 ///
1352 /// # Priority
1353 ///
1354 /// If both `select()` and `omit()` are used, `select()` takes priority.
1355 ///
1356 /// # Example
1357 ///
1358 /// ```rust,ignore
1359 /// // Exclude password from results
1360 /// let user = db.model::<User>()
1361 /// .omit("password")
1362 /// .first()
1363 /// .await?;
1364 ///
1365 /// // Exclude multiple fields
1366 /// let user = db.model::<User>()
1367 /// .omit("password, secret_token")
1368 /// .first()
1369 /// .await?;
1370 ///
1371 /// // Using with generated field constants (autocomplete support)
1372 /// let user = db.model::<User>()
1373 /// .omit(user_fields::PASSWORD)
1374 /// .first()
1375 /// .await?;
1376 /// ```
1377 pub fn omit(mut self, columns: &str) -> Self {
1378 for col in columns.split(',') {
1379 self.omit_columns.push(col.trim().to_snake_case());
1380 }
1381 self
1382 }
1383
1384 /// Sets the query offset (pagination).
1385 ///
1386 /// Specifies the number of rows to skip before starting to return rows.
1387 /// Commonly used in combination with `limit()` for pagination.
1388 ///
1389 /// # Arguments
1390 ///
1391 /// * `offset` - Number of rows to skip
1392 ///
1393 /// # Example
1394 ///
1395 /// ```rust,ignore
1396 /// // Skip first 20 rows
1397 /// query.offset(20)
1398 ///
1399 /// // Pagination: page 3 with 10 items per page
1400 /// query.limit(10).offset(20) // Skip 2 pages = 20 items
1401 /// ```
1402 pub fn offset(mut self, offset: usize) -> Self {
1403 self.offset = Some(offset);
1404 self
1405 }
1406
1407 /// Sets the maximum number of records to return.
1408 ///
1409 /// Limits the number of rows returned by the query. Essential for pagination
1410 /// and preventing accidentally fetching large result sets.
1411 ///
1412 /// # Arguments
1413 ///
1414 /// * `limit` - Maximum number of rows to return
1415 ///
1416 /// # Example
1417 ///
1418 /// ```rust,ignore
1419 /// // Return at most 10 rows
1420 /// query.limit(10)
1421 ///
1422 /// // Pagination: 50 items per page
1423 /// query.limit(50).offset(page * 50)
1424 /// ```
1425 pub fn limit(mut self, limit: usize) -> Self {
1426 self.limit = Some(limit);
1427 self
1428 }
1429
1430 // ========================================================================
1431 // Insert Operation
1432 // ========================================================================
1433
1434 /// Inserts a new record into the database based on the model instance.
1435 ///
1436 /// This method serializes the model into a SQL INSERT statement with proper
1437 /// type handling for primitives, dates, UUIDs, and other supported types.
1438 ///
1439 /// # Type Binding Strategy
1440 ///
1441 /// The method uses string parsing as a temporary solution for type binding.
1442 /// Values are converted to strings via the model's `to_map()` method, then
1443 /// parsed back to their original types for proper SQL binding.
1444 ///
1445 /// # Supported Types for Insert
1446 ///
1447 /// - **Integers**: `i32`, `i64` (INTEGER, BIGINT)
1448 /// - **Boolean**: `bool` (BOOLEAN)
1449 /// - **Float**: `f64` (DOUBLE PRECISION)
1450 /// - **Text**: `String` (TEXT, VARCHAR)
1451 /// - **UUID**: `Uuid` (UUID) - All versions 1-7 supported
1452 /// - **DateTime**: `DateTime<Utc>` (TIMESTAMPTZ)
1453 /// - **NaiveDateTime**: (TIMESTAMP)
1454 /// - **NaiveDate**: (DATE)
1455 /// - **NaiveTime**: (TIME)
1456 ///
1457 /// # Arguments
1458 ///
1459 /// * `model` - Reference to the model instance to insert
1460 ///
1461 /// # Returns
1462 ///
1463 /// * `Ok(&Self)` - Reference to self for method chaining
1464 /// * `Err(sqlx::Error)` - Database error during insertion
1465 ///
1466 /// # Example
1467 ///
1468 /// ```rust,ignore
1469 /// use uuid::Uuid;
1470 /// use chrono::Utc;
1471 ///
1472 /// let new_user = User {
1473 /// id: Uuid::new_v4(),
1474 /// username: "john_doe".to_string(),
1475 /// email: "john@example.com".to_string(),
1476 /// age: 25,
1477 /// active: true,
1478 /// created_at: Utc::now(),
1479 /// };
1480 ///
1481 /// db.model::<User>().insert(&new_user).await?;
1482 /// ```
1483 pub fn insert<'b>(&'b mut self, model: &'b T) -> BoxFuture<'b, Result<(), sqlx::Error>> {
1484 Box::pin(async move {
1485 // Serialize model to a HashMap of column_name -> string_value
1486 let data_map = model.to_map();
1487
1488 // Early return if no data to insert
1489 if data_map.is_empty() {
1490 return Ok(());
1491 }
1492
1493 let table_name = self.table_name.to_snake_case();
1494 let columns_info = T::columns();
1495
1496 let mut target_columns = Vec::new();
1497 let mut bindings: Vec<(String, &str)> = Vec::new();
1498
1499 // Build column list and collect values with their SQL types
1500 for (col_name, value) in data_map {
1501 // Strip the "r#" prefix if present (for Rust keywords used as field names)
1502 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
1503 target_columns.push(format!("\"{}\"", col_name_clean));
1504
1505 // Find the SQL type for this column
1506 let sql_type = columns_info.iter().find(|c| c.name == col_name).map(|c| c.sql_type).unwrap_or("TEXT");
1507
1508 bindings.push((value, sql_type));
1509 }
1510
1511 // Generate placeholders with proper type casting for PostgreSQL
1512 let placeholders: Vec<String> = bindings
1513 .iter()
1514 .enumerate()
1515 .map(|(i, (_, sql_type))| match self.driver {
1516 Drivers::Postgres => {
1517 let idx = i + 1;
1518 // PostgreSQL requires explicit type casting for some types
1519 if temporal::is_temporal_type(sql_type) {
1520 // Use temporal module for type casting
1521 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
1522 } else {
1523 match *sql_type {
1524 "UUID" => format!("${}::UUID", idx),
1525 "JSONB" | "jsonb" => format!("${}::JSONB", idx),
1526 _ => format!("${}", idx),
1527 }
1528 }
1529 }
1530 // MySQL and SQLite use simple ? placeholders
1531 _ => "?".to_string(),
1532 })
1533 .collect();
1534
1535 // Construct the INSERT query
1536 let query_str = format!(
1537 "INSERT INTO \"{}\" ({}) VALUES ({})",
1538 table_name,
1539 target_columns.join(", "),
1540 placeholders.join(", ")
1541 );
1542
1543 // If debug mode is enabled, log the generated SQL query before execution
1544 if self.debug_mode {
1545 log::debug!("SQL: {}", query_str);
1546 }
1547
1548 let mut query = sqlx::query::<sqlx::Any>(&query_str);
1549
1550 // Bind values using the optimized value_binding module
1551 // This provides type-safe binding with driver-specific optimizations
1552 for (val_str, sql_type) in bindings {
1553 // Create temporary AnyArguments to collect the bound value
1554 let mut temp_args = AnyArguments::default();
1555
1556 // Use the ValueBinder trait for type-safe binding
1557 if temp_args.bind_value(&val_str, sql_type, &self.driver).is_ok() {
1558 // For now, we need to convert back to individual bindings
1559 // This is a workaround until we can better integrate AnyArguments
1560 match sql_type {
1561 "INTEGER" | "INT" | "SERIAL" | "serial" | "int4" => {
1562 if let Ok(val) = val_str.parse::<i32>() {
1563 query = query.bind(val);
1564 } else {
1565 query = query.bind(val_str);
1566 }
1567 }
1568 "BIGINT" | "INT8" | "int8" | "BIGSERIAL" => {
1569 if let Ok(val) = val_str.parse::<i64>() {
1570 query = query.bind(val);
1571 } else {
1572 query = query.bind(val_str);
1573 }
1574 }
1575 "BOOLEAN" | "BOOL" | "bool" => {
1576 if let Ok(val) = val_str.parse::<bool>() {
1577 query = query.bind(val);
1578 } else {
1579 query = query.bind(val_str);
1580 }
1581 }
1582 "DOUBLE PRECISION" | "FLOAT" | "float8" | "REAL" | "NUMERIC" | "DECIMAL" => {
1583 if let Ok(val) = val_str.parse::<f64>() {
1584 query = query.bind(val);
1585 } else {
1586 query = query.bind(val_str);
1587 }
1588 }
1589 "UUID" => {
1590 if let Ok(val) = val_str.parse::<Uuid>() {
1591 query = query.bind(val.hyphenated().to_string());
1592 } else {
1593 query = query.bind(val_str);
1594 }
1595 }
1596 "TIMESTAMPTZ" | "DateTime" => {
1597 if let Ok(val) = temporal::parse_datetime_utc(&val_str) {
1598 let formatted = temporal::format_datetime_for_driver(&val, &self.driver);
1599 query = query.bind(formatted);
1600 } else {
1601 query = query.bind(val_str);
1602 }
1603 }
1604 "TIMESTAMP" | "NaiveDateTime" => {
1605 if let Ok(val) = temporal::parse_naive_datetime(&val_str) {
1606 let formatted = temporal::format_naive_datetime_for_driver(&val, &self.driver);
1607 query = query.bind(formatted);
1608 } else {
1609 query = query.bind(val_str);
1610 }
1611 }
1612 "DATE" | "NaiveDate" => {
1613 if let Ok(val) = temporal::parse_naive_date(&val_str) {
1614 let formatted = val.format("%Y-%m-%d").to_string();
1615 query = query.bind(formatted);
1616 } else {
1617 query = query.bind(val_str);
1618 }
1619 }
1620 "TIME" | "NaiveTime" => {
1621 if let Ok(val) = temporal::parse_naive_time(&val_str) {
1622 let formatted = val.format("%H:%M:%S%.6f").to_string();
1623 query = query.bind(formatted);
1624 } else {
1625 query = query.bind(val_str);
1626 }
1627 }
1628 _ => {
1629 query = query.bind(val_str);
1630 }
1631 }
1632 } else {
1633 // Fallback: bind as string if type conversion fails
1634 query = query.bind(val_str);
1635 }
1636 }
1637
1638 // Execute the INSERT query
1639 query.execute(self.tx.executor()).await?;
1640 Ok(())
1641 })
1642 }
1643
1644 /// Inserts multiple records into the database in a single batch operation.
1645 ///
1646 /// This is significantly faster than performing individual inserts in a loop
1647 /// as it generates a single SQL statement with multiple VALUES groups.
1648 ///
1649 /// # Type Binding Strategy
1650 ///
1651 /// Similar to the single record `insert`, this method uses string parsing for
1652 /// type binding. It ensures that all columns defined in the model are included
1653 /// in the insert statement, providing NULL for any missing optional values.
1654 ///
1655 /// # Arguments
1656 ///
1657 /// * `models` - A slice of model instances to insert
1658 ///
1659 /// # Returns
1660 ///
1661 /// * `Ok(())` - Successfully inserted all records
1662 /// * `Err(sqlx::Error)` - Database error during insertion
1663 ///
1664 /// # Example
1665 ///
1666 /// ```rust,ignore
1667 /// let users = vec![
1668 /// User { username: "alice".to_string(), ... },
1669 /// User { username: "bob".to_string(), ... },
1670 /// ];
1671 ///
1672 /// db.model::<User>().batch_insert(&users).await?;
1673 /// ```
1674 pub fn batch_insert<'b>(&'b mut self, models: &'b [T]) -> BoxFuture<'b, Result<(), sqlx::Error>> {
1675 Box::pin(async move {
1676 if models.is_empty() {
1677 return Ok(());
1678 }
1679
1680 let table_name = self.table_name.to_snake_case();
1681 let columns_info = T::columns();
1682
1683 // Collect all column names for the INSERT statement
1684 // We use all columns defined in the model to ensure consistency across the batch
1685 let target_columns: Vec<String> = columns_info
1686 .iter()
1687 .map(|c| {
1688 let col_name_clean = c.name.strip_prefix("r#").unwrap_or(c.name).to_snake_case();
1689 format!("\"{}\"", col_name_clean)
1690 })
1691 .collect();
1692
1693 let mut value_groups = Vec::new();
1694 let mut bind_index = 1;
1695
1696 // Generate placeholders for all models
1697 for _ in models {
1698 let mut placeholders = Vec::new();
1699 for col in &columns_info {
1700 match self.driver {
1701 Drivers::Postgres => {
1702 let p = if temporal::is_temporal_type(col.sql_type) {
1703 format!("${}{}", bind_index, temporal::get_postgres_type_cast(col.sql_type))
1704 } else {
1705 match col.sql_type {
1706 "UUID" => format!("${}::UUID", bind_index),
1707 "JSONB" | "jsonb" => format!("${}::JSONB", bind_index),
1708 _ => format!("${}", bind_index),
1709 }
1710 };
1711 placeholders.push(p);
1712 bind_index += 1;
1713 }
1714 _ => {
1715 placeholders.push("?".to_string());
1716 }
1717 }
1718 }
1719 value_groups.push(format!("({})", placeholders.join(", ")));
1720 }
1721
1722 let query_str = format!(
1723 "INSERT INTO \"{}\" ({}) VALUES {}",
1724 table_name,
1725 target_columns.join(", "),
1726 value_groups.join(", ")
1727 );
1728
1729 if self.debug_mode {
1730 log::debug!("SQL Batch: {}", query_str);
1731 }
1732
1733 let mut query = sqlx::query::<sqlx::Any>(&query_str);
1734
1735 for model in models {
1736 let data_map = model.to_map();
1737 for col in &columns_info {
1738 let val_opt = data_map.get(col.name);
1739 let sql_type = col.sql_type;
1740
1741 if let Some(val_str) = val_opt {
1742 // Use the same binding logic as single insert
1743 match sql_type {
1744 "INTEGER" | "INT" | "SERIAL" | "serial" | "int4" => {
1745 if let Ok(val) = val_str.parse::<i32>() {
1746 query = query.bind(val);
1747 } else {
1748 query = query.bind(val_str.clone());
1749 }
1750 }
1751 "BIGINT" | "INT8" | "int8" | "BIGSERIAL" => {
1752 if let Ok(val) = val_str.parse::<i64>() {
1753 query = query.bind(val);
1754 } else {
1755 query = query.bind(val_str.clone());
1756 }
1757 }
1758 "BOOLEAN" | "BOOL" | "bool" => {
1759 if let Ok(val) = val_str.parse::<bool>() {
1760 query = query.bind(val);
1761 } else {
1762 query = query.bind(val_str.clone());
1763 }
1764 }
1765 "DOUBLE PRECISION" | "FLOAT" | "float8" | "REAL" | "NUMERIC" | "DECIMAL" => {
1766 if let Ok(val) = val_str.parse::<f64>() {
1767 query = query.bind(val);
1768 } else {
1769 query = query.bind(val_str.clone());
1770 }
1771 }
1772 "UUID" => {
1773 if let Ok(val) = val_str.parse::<Uuid>() {
1774 query = query.bind(val.hyphenated().to_string());
1775 } else {
1776 query = query.bind(val_str.clone());
1777 }
1778 }
1779 "TIMESTAMPTZ" | "DateTime" => {
1780 if let Ok(val) = temporal::parse_datetime_utc(val_str) {
1781 let formatted = temporal::format_datetime_for_driver(&val, &self.driver);
1782 query = query.bind(formatted);
1783 } else {
1784 query = query.bind(val_str.clone());
1785 }
1786 }
1787 "TIMESTAMP" | "NaiveDateTime" => {
1788 if let Ok(val) = temporal::parse_naive_datetime(val_str) {
1789 let formatted = temporal::format_naive_datetime_for_driver(&val, &self.driver);
1790 query = query.bind(formatted);
1791 } else {
1792 query = query.bind(val_str.clone());
1793 }
1794 }
1795 "DATE" | "NaiveDate" => {
1796 if let Ok(val) = temporal::parse_naive_date(val_str) {
1797 let formatted = val.format("%Y-%m-%d").to_string();
1798 query = query.bind(formatted);
1799 } else {
1800 query = query.bind(val_str.clone());
1801 }
1802 }
1803 "TIME" | "NaiveTime" => {
1804 if let Ok(val) = temporal::parse_naive_time(val_str) {
1805 let formatted = val.format("%H:%M:%S%.6f").to_string();
1806 query = query.bind(formatted);
1807 } else {
1808 query = query.bind(val_str.clone());
1809 }
1810 }
1811 _ => {
1812 query = query.bind(val_str.clone());
1813 }
1814 }
1815 } else {
1816 // Bind NULL for missing values (usually from Option::None)
1817 query = query.bind(None::<String>);
1818 }
1819 }
1820 }
1821
1822 // Execute the batch INSERT query
1823 query.execute(self.tx.executor()).await?;
1824 Ok(())
1825 })
1826 }
1827
1828 // ========================================================================
1829 // Query Execution Methods
1830 // ========================================================================
1831
1832 /// Returns the generated SQL string for debugging purposes.
1833 ///
1834 /// This method constructs the SQL query string without executing it.
1835 /// Useful for debugging and logging query construction. Note that this
1836 /// shows placeholders (?, $1, etc.) rather than actual bound values.
1837 ///
1838 /// # Returns
1839 ///
1840 /// A `String` containing the SQL query that would be executed
1841 ///
1842 /// # Example
1843 ///
1844 /// ```rust,ignore
1845 /// let query = db.model::<User>()
1846 /// .filter("age", ">=", 18)
1847 /// .order("created_at DESC")
1848 /// .limit(10);
1849 ///
1850 /// println!("SQL: {}", query.to_sql());
1851 /// // Output: SELECT * FROM "user" WHERE 1=1 AND "age" >= $1 ORDER BY created_at DESC
1852 /// ```
1853 pub fn to_sql(&self) -> String {
1854 let mut query = String::from("SELECT ");
1855
1856 if self.is_distinct {
1857 query.push_str("DISTINCT ");
1858 }
1859
1860 // Handle column selection
1861 if self.select_columns.is_empty() {
1862 query.push('*');
1863 } else {
1864 query.push_str(&self.select_columns.join(", "));
1865 }
1866
1867 query.push_str(" FROM \"");
1868 query.push_str(&self.table_name.to_snake_case());
1869 query.push_str("\" ");
1870
1871 if let Some(alias) = &self.alias {
1872 query.push_str(&format!("{} ", alias));
1873 }
1874
1875 if !self.joins_clauses.is_empty() {
1876 query.push_str(&self.joins_clauses.join(" "));
1877 }
1878
1879 query.push_str(" WHERE 1=1");
1880
1881 // Apply WHERE clauses with dummy arguments
1882 let mut dummy_args = AnyArguments::default();
1883 let mut dummy_counter = 1;
1884
1885 for clause in &self.where_clauses {
1886 clause(&mut query, &mut dummy_args, &self.driver, &mut dummy_counter);
1887 }
1888
1889 // Apply GROUP BY
1890 if !self.group_by_clauses.is_empty() {
1891 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1892 }
1893
1894 // Apply HAVING
1895 if !self.having_clauses.is_empty() {
1896 query.push_str(" HAVING 1=1");
1897 for clause in &self.having_clauses {
1898 clause(&mut query, &mut dummy_args, &self.driver, &mut dummy_counter);
1899 }
1900 }
1901
1902 // Apply ORDER BY if present
1903 if !self.order_clauses.is_empty() {
1904 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
1905 }
1906
1907 query
1908 }
1909
1910 /// Generates the list of column selection SQL arguments.
1911 ///
1912 /// This helper function constructs the column list for the SELECT statement.
1913 /// It handles:
1914 /// 1. Mapping specific columns if `select_columns` is set.
1915 /// 2. Defaulting to all columns from the struct `R` if no columns are specified.
1916 /// 3. applying `to_json(...)` casting for temporal types when using `AnyImpl` structs,
1917 /// ensuring compatibility with the `FromAnyRow` deserialization logic.
1918 fn select_args_sql<R: AnyImpl>(&self) -> Vec<String> {
1919 let struct_cols = R::columns();
1920 let table_id = self.get_table_identifier();
1921
1922 if !struct_cols.is_empty() {
1923 if !self.select_columns.is_empty() {
1924 let mut args = Vec::new();
1925
1926 // Flatten potential multi-column strings like "col1, col2"
1927 // This ensures each column is processed individually for prefixes and temporal types
1928 let mut flat_selects = Vec::new();
1929 for s in &self.select_columns {
1930 if s.contains(',') {
1931 for sub in s.split(',') {
1932 flat_selects.push(sub.trim().to_string());
1933 }
1934 } else {
1935 flat_selects.push(s.trim().to_string());
1936 }
1937 }
1938
1939 for col_info in struct_cols {
1940 let col_snake = col_info.column.to_snake_case();
1941 let sql_type = col_info.sql_type;
1942
1943 // Check if this column (or table.column) is in our select list
1944 // We check against the column name alone OR the table-qualified name
1945 let is_selected = flat_selects.iter().any(|s| {
1946 if s == &col_snake {
1947 return true;
1948 }
1949 if let Some((t, c)) = s.split_once('.') {
1950 let t_clean = t.trim().trim_matches('"');
1951 let c_clean = c.trim().trim_matches('"');
1952 // Matches if the table prefix is either the original table name or the alias
1953 return (t_clean == table_id || t_clean == self.table_name.to_snake_case())
1954 && c_clean == col_snake;
1955 }
1956 false
1957 });
1958
1959 if is_selected {
1960 if is_temporal_type(sql_type) && matches!(self.driver, Drivers::Postgres) {
1961 if !self.joins_clauses.is_empty() || self.alias.is_some() {
1962 args.push(format!(
1963 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"",
1964 table_id,
1965 col_snake,
1966 col_snake
1967 ));
1968 } else {
1969 args.push(format!("to_json(\"{}\") #>> '{{}}' AS \"{}\"", col_snake, col_snake));
1970 }
1971 } else if !self.joins_clauses.is_empty() || self.alias.is_some() {
1972 args.push(format!("\"{}\".\"{}\"", table_id, col_snake));
1973 } else {
1974 args.push(format!("\"{}\"", col_snake));
1975 }
1976 }
1977 }
1978 return args;
1979 } else {
1980 // For omitted columns, return 'omited' as placeholder value
1981 return struct_cols
1982 .iter()
1983 .map(|c| {
1984 let col_snake = c.column.to_snake_case();
1985 let is_omitted = self.omit_columns.contains(&col_snake);
1986
1987 // table_to_alias is used for the result set mapping (AS "table__col")
1988 // It MUST use the original table name snake_cased for the ORM to map it correctly
1989 let table_to_alias = if !c.table.is_empty() {
1990 c.table.to_snake_case()
1991 } else {
1992 self.table_name.to_snake_case()
1993 };
1994
1995 // table_to_ref is used in the SELECT clause (SELECT "table"."col")
1996 // It uses the alias if defined, or the original table name
1997 let table_to_ref = if !c.table.is_empty() {
1998 let c_table_snake = c.table.to_snake_case();
1999 if c_table_snake == self.table_name.to_snake_case() {
2000 table_id.clone()
2001 } else {
2002 // Check if we have an alias for this joined table
2003 self.join_aliases.get(&c_table_snake).cloned().unwrap_or(c_table_snake)
2004 }
2005 } else {
2006 table_id.clone()
2007 };
2008
2009 if is_omitted {
2010 // Return type-appropriate placeholder based on sql_type
2011 let placeholder = match c.sql_type {
2012 // String types
2013 "TEXT" | "VARCHAR" | "CHAR" | "STRING" => "'omited'",
2014 // Date/Time types - use epoch timestamp
2015 "TIMESTAMP" | "TIMESTAMPTZ" | "TIMESTAMP WITH TIME ZONE" => "'1970-01-01T00:00:00Z'",
2016 "DATE" => "'1970-01-01'",
2017 "TIME" => "'00:00:00'",
2018 // Numeric types
2019 "INTEGER" | "INT" | "SMALLINT" | "BIGINT" | "INT4" | "INT8" => "0",
2020 "REAL" | "FLOAT" | "DOUBLE" | "FLOAT4" | "FLOAT8" | "DECIMAL" | "NUMERIC" => "0.0",
2021 // Boolean
2022 "BOOLEAN" | "BOOL" => "false",
2023 // UUID - nil UUID
2024 "UUID" => "'00000000-0000-0000-0000-000000000000'",
2025 // JSON types
2026 "JSON" | "JSONB" => "'{}'",
2027 // Default fallback for unknown types
2028 _ => "'omited'",
2029 };
2030 format!("{} AS \"{}__{}\"", placeholder, table_to_alias, col_snake)
2031 } else if is_temporal_type(c.sql_type) && matches!(self.driver, Drivers::Postgres) {
2032 format!(
2033 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}__{}\"",
2034 table_to_ref, col_snake, table_to_alias, col_snake
2035 )
2036 } else {
2037 format!("\"{}\".\"{}\" AS \"{}__{}\"", table_to_ref, col_snake, table_to_alias, col_snake)
2038 }
2039 })
2040 .collect();
2041 }
2042 }
2043
2044 if !self.select_columns.is_empty() {
2045 return self
2046 .select_columns
2047 .iter()
2048 .map(|c| if c.contains('(') { c.clone() } else { format!("\"{}\"", c) })
2049 .collect();
2050 }
2051
2052 vec!["*".to_string()]
2053 }
2054
2055 /// Executes the query and returns a list of results.
2056 ///
2057 /// This method builds and executes a SELECT query with all accumulated filters,
2058 /// ordering, and pagination settings. It returns all matching rows as a vector.
2059 ///
2060 /// # Type Parameters
2061 ///
2062 /// * `R` - The result type. Must implement `FromRow` for deserialization from database rows.
2063 ///
2064 /// # Returns
2065 ///
2066 /// * `Ok(Vec<R>)` - Vector of results (empty if no matches)
2067 /// * `Err(sqlx::Error)` - Database error during query execution
2068 ///
2069 /// # Example
2070 ///
2071 /// ```rust,ignore
2072 /// // Get all adult users, ordered by age, limited to 10
2073 /// let users: Vec<User> = db.model::<User>()
2074 /// .filter("age", ">=", 18)
2075 /// .order("age DESC")
2076 /// .limit(10)
2077 /// .scan()
2078 /// .await?;
2079 ///
2080 /// // Get users by UUID
2081 /// let user_id = Uuid::parse_str("550e8400-e29b-41d4-a716-446655440000")?;
2082 /// let users: Vec<User> = db.model::<User>()
2083 /// .filter("id", "=", user_id)
2084 /// .scan()
2085 /// .await?;
2086 ///
2087 /// // Empty result is Ok
2088 /// let results: Vec<User> = db.model::<User>()
2089 /// .filter("age", ">", 200)
2090 /// .scan()
2091 /// .await?; // Returns empty Vec, not an error
2092 /// ```
2093 pub async fn scan<R>(mut self) -> Result<Vec<R>, sqlx::Error>
2094 where
2095 R: FromAnyRow + AnyImpl + Send + Unpin,
2096 {
2097 // Apply default soft delete filter if not disabled
2098 if !self.with_deleted {
2099 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
2100 self = self.is_null(soft_delete_col);
2101 }
2102 }
2103
2104 // Build SELECT clause
2105 let mut query = String::from("SELECT ");
2106
2107 if self.is_distinct {
2108 query.push_str("DISTINCT ");
2109 }
2110
2111 query.push_str(&self.select_args_sql::<R>().join(", "));
2112
2113 // Build FROM clause
2114 query.push_str(" FROM \"");
2115 query.push_str(&self.table_name.to_snake_case());
2116 query.push_str("\" ");
2117 if let Some(alias) = &self.alias {
2118 query.push_str(&format!("{} ", alias));
2119 }
2120
2121 if !self.joins_clauses.is_empty() {
2122 query.push_str(&self.joins_clauses.join(" "));
2123 }
2124
2125 query.push_str(" WHERE 1=1");
2126
2127 // Apply WHERE clauses
2128 let mut args = AnyArguments::default();
2129 let mut arg_counter = 1;
2130
2131 for clause in &self.where_clauses {
2132 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2133 }
2134
2135 // Apply GROUP BY
2136 if !self.group_by_clauses.is_empty() {
2137 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
2138 }
2139
2140 // Apply HAVING
2141 if !self.having_clauses.is_empty() {
2142 query.push_str(" HAVING 1=1");
2143 for clause in &self.having_clauses {
2144 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2145 }
2146 }
2147
2148 // Apply ORDER BY clauses
2149 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
2150 if !self.order_clauses.is_empty() {
2151 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
2152 }
2153
2154 // Apply LIMIT clause
2155 if let Some(limit) = self.limit {
2156 query.push_str(" LIMIT ");
2157 match self.driver {
2158 Drivers::Postgres => {
2159 query.push_str(&format!("${}", arg_counter));
2160 arg_counter += 1;
2161 }
2162 _ => query.push('?'),
2163 }
2164 let _ = args.add(limit as i64);
2165 }
2166
2167 // Apply OFFSET clause
2168 if let Some(offset) = self.offset {
2169 query.push_str(" OFFSET ");
2170 match self.driver {
2171 Drivers::Postgres => {
2172 query.push_str(&format!("${}", arg_counter));
2173 // arg_counter += 1; // Not needed as this is the last clause
2174 }
2175 _ => query.push('?'),
2176 }
2177 let _ = args.add(offset as i64);
2178 }
2179
2180 // Print SQL query to logs if debug mode is active
2181 if self.debug_mode {
2182 log::debug!("SQL: {}", query);
2183 }
2184
2185 // Execute query and fetch all results
2186 let rows = sqlx::query_with(&query, args).fetch_all(self.tx.executor()).await?;
2187
2188 rows.iter().map(|row| R::from_any_row(row)).collect()
2189 }
2190
2191 /// Executes the query and maps the result to a custom DTO.
2192 ///
2193 /// Ideal for JOINs and projections where the return type is not a full Model.
2194 ///
2195 /// # Type Parameters
2196 ///
2197 /// * `R` - The target result type. Must implement `FromAnyRow` and `AnyImpl`.
2198 ///
2199 /// # Returns
2200 ///
2201 /// * `Ok(Vec<R>)` - Vector of results mapped to type `R`.
2202 /// * `Err(sqlx::Error)` - Database error.
2203 ///
2204 /// # Example
2205 ///
2206 /// ```rust,ignore
2207 /// #[derive(FromAnyRow)]
2208 /// struct UserRoleDTO {
2209 /// username: String,
2210 /// role_name: String,
2211 /// }
2212 ///
2213 /// let results: Vec<UserRoleDTO> = db.model::<User>()
2214 /// .inner_join("roles", "users.role_id = roles.id")
2215 /// .select("users.username, roles.name as role_name")
2216 /// .scan_as::<UserRoleDTO>()
2217 /// .await?;
2218 /// ```
2219 pub async fn scan_as<R>(mut self) -> Result<Vec<R>, sqlx::Error>
2220 where
2221 R: FromAnyRow + AnyImpl + Send + Unpin,
2222 {
2223 // Apply default soft delete filter if not disabled
2224 if !self.with_deleted {
2225 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
2226 self = self.is_null(soft_delete_col);
2227 }
2228 }
2229
2230 let mut query = String::from("SELECT ");
2231 if self.is_distinct {
2232 query.push_str("DISTINCT ");
2233 }
2234
2235 let table_id = self.get_table_identifier();
2236
2237 if self.select_columns.is_empty() {
2238 let mut select_args = Vec::new();
2239 let struct_cols = R::columns();
2240 let main_table_snake = self.table_name.to_snake_case();
2241
2242 for c in struct_cols {
2243 let c_name = c.column.to_snake_case();
2244
2245 // Determine if we should use the table name from AnyInfo
2246 // If it matches a joined table or the main table, we use it.
2247 // Otherwise (like UserDTO), we default to the main table.
2248 let mut table_to_use = table_id.clone();
2249 if !c.table.is_empty() {
2250 let c_table_snake = c.table.to_snake_case();
2251 if c_table_snake == main_table_snake
2252 || self.joins_clauses.iter().any(|j| j.contains(&format!("JOIN \"{}\"", c_table_snake)))
2253 {
2254 if c_table_snake == main_table_snake {
2255 table_to_use = table_id.clone();
2256 } else {
2257 // Use join alias if available
2258 table_to_use = self.join_aliases.get(&c_table_snake).cloned().unwrap_or(c_table_snake);
2259 }
2260 }
2261 }
2262
2263 if is_temporal_type(c.sql_type) && matches!(self.driver, Drivers::Postgres) {
2264 select_args
2265 .push(format!("to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"", table_to_use, c_name, c_name));
2266 } else {
2267 select_args.push(format!("\"{}\".\"{}\" AS \"{}\"", table_to_use, c_name, c_name));
2268 }
2269 }
2270
2271 if select_args.is_empty() {
2272 query.push('*');
2273 } else {
2274 query.push_str(&select_args.join(", "));
2275 }
2276 } else {
2277 let mut select_cols = Vec::with_capacity(self.select_columns.capacity());
2278 let struct_cols = R::columns();
2279
2280 // Flatten multi-column strings
2281 let mut flat_selects = Vec::new();
2282 for s in &self.select_columns {
2283 if s.contains(',') {
2284 for sub in s.split(',') {
2285 flat_selects.push(sub.trim().to_string());
2286 }
2287 } else {
2288 flat_selects.push(s.trim().to_string());
2289 }
2290 }
2291
2292 for col in &flat_selects {
2293 let col_trimmed = col.trim();
2294 if col_trimmed == "*" {
2295 for c in &self.columns_info {
2296 let c_name = c.name.strip_prefix("r#").unwrap_or(c.name).to_snake_case();
2297 let mut is_c_temporal = false;
2298 if let Some(r_info) = struct_cols.iter().find(|rc| rc.column.to_snake_case() == c_name) {
2299 if is_temporal_type(r_info.sql_type) {
2300 is_c_temporal = true;
2301 }
2302 }
2303
2304 if is_c_temporal && matches!(self.driver, Drivers::Postgres) {
2305 select_cols.push(format!(
2306 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"",
2307 table_id,
2308 c_name,
2309 c_name
2310 ));
2311 } else {
2312 select_cols.push(format!(
2313 "\"{}\".\"{}\" AS \"{}\"",
2314 table_id,
2315 c_name,
2316 c_name
2317 ));
2318 }
2319 }
2320 continue;
2321 }
2322
2323 // Check if this column is temporal in the target DTO
2324 let mut is_temporal = false;
2325
2326 // We need to keep the lowercase string alive to use its slice in col_name
2327 let col_lower = col_trimmed.to_lowercase();
2328 let mut col_name = col_trimmed;
2329
2330 // Handle aliases (e.g., "created_at as time" or "user.created_at as time")
2331 if let Some((_, alias)) = col_lower.split_once(" as ") {
2332 col_name = alias.trim().trim_matches('"').trim_matches('\'');
2333 } else if col_trimmed.contains('.') {
2334 if let Some((_, actual_col)) = col_trimmed.split_once('.') {
2335 col_name = actual_col.trim().trim_matches('"').trim_matches('\'');
2336 }
2337 }
2338
2339 if let Some(info) = struct_cols.iter().find(|c| c.column.to_snake_case() == col_name.to_snake_case()) {
2340 if is_temporal_type(info.sql_type) {
2341 is_temporal = true;
2342 }
2343 }
2344
2345 if col_trimmed.contains('.') {
2346 if let Some((table, column)) = col_trimmed.split_once('.') {
2347 let clean_table = table.trim().trim_matches('"');
2348 let clean_column = column.trim().trim_matches('"').split_whitespace().next().unwrap_or(column);
2349
2350 if clean_column == "*" {
2351 let mut expanded = false;
2352 let table_to_compare = clean_table.to_snake_case();
2353 if table_to_compare == self.table_name.to_snake_case() || table_to_compare == table_id {
2354 for c in &self.columns_info {
2355 let c_name = c.name.strip_prefix("r#").unwrap_or(c.name).to_snake_case();
2356 let mut is_c_temporal = false;
2357 if let Some(r_info) =
2358 struct_cols.iter().find(|rc| rc.column.to_snake_case() == c_name)
2359 {
2360 if is_temporal_type(r_info.sql_type) {
2361 is_c_temporal = true;
2362 }
2363 }
2364
2365 if is_c_temporal && matches!(self.driver, Drivers::Postgres) {
2366 select_cols.push(format!(
2367 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"",
2368 clean_table, c_name, c_name
2369 ));
2370 } else {
2371 select_cols
2372 .push(format!("\"{}\".\"{}\" AS \"{}\"", clean_table, c_name, c_name));
2373 }
2374 }
2375 expanded = true;
2376 }
2377
2378 if !expanded {
2379 select_cols.push(format!("\"{}\".*", clean_table));
2380 }
2381 } else if is_temporal && matches!(self.driver, Drivers::Postgres) {
2382 select_cols.push(format!(
2383 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"",
2384 clean_table, clean_column, col_name
2385 ));
2386 } else {
2387 select_cols.push(format!("\"{}\".\"{}\" AS \"{}\"", clean_table, clean_column, col_name));
2388 }
2389 }
2390 } else if is_temporal && matches!(self.driver, Drivers::Postgres) {
2391 // Extract column name from potential expression
2392 let clean_col = col_trimmed.trim_matches('"');
2393 select_cols.push(format!("to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"", table_id, clean_col, col_name));
2394 } else if col_trimmed != col_name {
2395 select_cols.push(format!("{} AS \"{}\"", col_trimmed, col_name));
2396 } else {
2397 let is_main_col = self.columns.contains(&col_trimmed.to_snake_case());
2398 if is_main_col {
2399 select_cols.push(format!("\"{}\".\"{}\"", table_id, col_trimmed));
2400 } else {
2401 select_cols.push(format!("\"{}\"", col_trimmed));
2402 }
2403 }
2404 }
2405 query.push_str(&select_cols.join(", "));
2406 }
2407
2408 // Build FROM clause
2409 query.push_str(" FROM \"");
2410 query.push_str(&self.table_name.to_snake_case());
2411 query.push_str("\" ");
2412 if let Some(alias) = &self.alias {
2413 query.push_str(&format!("{} ", alias));
2414 }
2415
2416 if !self.joins_clauses.is_empty() {
2417 query.push_str(&self.joins_clauses.join(" "));
2418 }
2419
2420 query.push_str(" WHERE 1=1");
2421
2422 let mut args = sqlx::any::AnyArguments::default();
2423 let mut arg_counter = 1;
2424
2425 for clause in &self.where_clauses {
2426 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2427 }
2428
2429 if !self.group_by_clauses.is_empty() {
2430 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
2431 }
2432
2433 if !self.having_clauses.is_empty() {
2434 query.push_str(" HAVING 1=1");
2435 for clause in &self.having_clauses {
2436 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2437 }
2438 }
2439
2440 if !self.order_clauses.is_empty() {
2441 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
2442 }
2443
2444 if let Some(limit) = self.limit {
2445 query.push_str(" LIMIT ");
2446 match self.driver {
2447 Drivers::Postgres => {
2448 query.push_str(&format!("${}", arg_counter));
2449 arg_counter += 1;
2450 }
2451 _ => query.push('?'),
2452 }
2453 let _ = args.add(limit as i64);
2454 }
2455
2456 if let Some(offset) = self.offset {
2457 query.push_str(" OFFSET ");
2458 match self.driver {
2459 Drivers::Postgres => {
2460 query.push_str(&format!("${}", arg_counter));
2461 }
2462 _ => query.push('?'),
2463 }
2464 let _ = args.add(offset as i64);
2465 }
2466
2467 if self.debug_mode {
2468 log::debug!("SQL: {}", query);
2469 }
2470
2471 let rows = sqlx::query_with(&query, args).fetch_all(self.tx.executor()).await?;
2472 rows.iter().map(|row| R::from_any_row(row)).collect()
2473 }
2474
2475 /// Executes the query and returns only the first result.
2476 ///
2477 /// This method automatically adds `LIMIT 1` and orders by the Primary Key
2478 /// (if available) to ensure consistent results. It's optimized for fetching
2479 /// a single row and will return an error if no rows match.
2480 ///
2481 /// # Type Parameters
2482 ///
2483 /// * `R` - The result type. Must implement `FromRow` for deserialization.
2484 ///
2485 /// # Returns
2486 ///
2487 /// * `Ok(R)` - The first matching row
2488 /// * `Err(sqlx::Error)` - No rows found or database error
2489 ///
2490 /// # Error Handling
2491 ///
2492 /// Returns `sqlx::Error::RowNotFound` if no rows match the query.
2493 /// Use `scan()` instead if you want an empty Vec rather than an error.
2494 ///
2495 /// # Example
2496 ///
2497 /// ```rust,ignore
2498 /// // Get a specific user by ID
2499 /// let user: User = db.model::<User>()
2500 /// .filter("id", "=", 1)
2501 /// .first()
2502 /// .await?;
2503 ///
2504 /// // Get user by UUID
2505 /// let user_id = Uuid::new_v4();
2506 /// let user: User = db.model::<User>()
2507 /// .filter("id", "=", user_id)
2508 /// .first()
2509 /// .await?;
2510 ///
2511 /// // Get the oldest user
2512 /// let oldest: User = db.model::<User>()
2513 /// .order("age DESC")
2514 /// .first()
2515 /// .await?;
2516 ///
2517 /// // Error handling
2518 /// match db.model::<User>().filter("id", "=", 999).first().await {
2519 /// Ok(user) => println!("Found: {:?}", user),
2520 /// Err(sqlx::Error::RowNotFound) => println!("User not found"),
2521 /// Err(e) => println!("Database error: {}", e),
2522 /// }
2523 /// ```
2524 pub async fn first<R>(mut self) -> Result<R, sqlx::Error>
2525 where
2526 R: FromAnyRow + AnyImpl + Send + Unpin,
2527 {
2528 // Apply default soft delete filter if not disabled
2529 if !self.with_deleted {
2530 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
2531 self = self.is_null(soft_delete_col);
2532 }
2533 }
2534
2535 // Build SELECT clause
2536 let mut query = String::from("SELECT ");
2537
2538 if self.is_distinct {
2539 query.push_str("DISTINCT ");
2540 }
2541
2542 query.push_str(&self.select_args_sql::<R>().join(", "));
2543
2544 // Build FROM clause
2545 query.push_str(" FROM \"");
2546 query.push_str(&self.table_name.to_snake_case());
2547 query.push_str("\" ");
2548 if let Some(alias) = &self.alias {
2549 query.push_str(&format!("{} ", alias));
2550 }
2551 if !self.joins_clauses.is_empty() {
2552 query.push_str(&self.joins_clauses.join(" "));
2553 }
2554
2555 query.push_str(" WHERE 1=1");
2556
2557 // Apply WHERE clauses
2558 let mut args = AnyArguments::default();
2559 let mut arg_counter = 1;
2560
2561 for clause in &self.where_clauses {
2562 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2563 }
2564
2565 // Apply GROUP BY
2566 if !self.group_by_clauses.is_empty() {
2567 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
2568 }
2569
2570 // Apply HAVING
2571 if !self.having_clauses.is_empty() {
2572 query.push_str(" HAVING 1=1");
2573 for clause in &self.having_clauses {
2574 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2575 }
2576 }
2577
2578 // Find primary key column for consistent ordering
2579 let pk_column = T::columns()
2580 .iter()
2581 .find(|c| c.is_primary_key)
2582 .map(|c| c.name.strip_prefix("r#").unwrap_or(c.name).to_snake_case());
2583
2584 let table_id = self.get_table_identifier();
2585
2586 // Apply ORDER BY clauses
2587 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
2588 if !self.order_clauses.is_empty() {
2589 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
2590 } else if let Some(pk) = pk_column {
2591 // Fallback to PK ordering if no custom order is specified (ensures deterministic results)
2592 query.push_str(" ORDER BY ");
2593 query.push_str(&format!("\"{}\".\"{}\"", table_id, pk));
2594 query.push_str(" ASC");
2595 }
2596
2597 // Always add LIMIT 1 for first() queries
2598 query.push_str(" LIMIT 1");
2599
2600 // Print SQL query to logs if debug mode is active
2601 log::debug!("SQL: {}", query);
2602
2603 // Execute query and fetch exactly one result
2604 let row = sqlx::query_with(&query, args).fetch_one(self.tx.executor()).await?;
2605 R::from_any_row(&row)
2606 }
2607
2608 /// Executes the query and returns a single scalar value.
2609 ///
2610 /// This method is useful for fetching single values like counts, max/min values,
2611 /// or specific columns without mapping to a struct or tuple.
2612 ///
2613 /// # Type Parameters
2614 ///
2615 /// * `O` - The output type. Must implement `Decode` and `Type`.
2616 ///
2617 /// # Example
2618 ///
2619 /// ```rust,ignore
2620 /// // Get count of users
2621 /// let count: i64 = db.model::<User>()
2622 /// .select("count(*)")
2623 /// .scalar()
2624 /// .await?;
2625 ///
2626 /// // Get specific field
2627 /// let username: String = db.model::<User>()
2628 /// .filter("id", "=", 1)
2629 /// .select("username")
2630 /// .scalar()
2631 /// .await?;
2632 /// ```
2633 pub async fn scalar<O>(mut self) -> Result<O, sqlx::Error>
2634 where
2635 O: FromAnyRow + Send + Unpin,
2636 {
2637 // Apply default soft delete filter if not disabled
2638 if !self.with_deleted {
2639 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
2640 self = self.is_null(soft_delete_col);
2641 }
2642 }
2643
2644 // Build SELECT clause
2645 let mut query = String::from("SELECT ");
2646
2647 if self.is_distinct {
2648 query.push_str("DISTINCT ");
2649 }
2650
2651 if self.select_columns.is_empty() {
2652 return Err(sqlx::Error::ColumnNotFound("is not possible get data without column".to_string()));
2653 }
2654
2655 let table_id = self.get_table_identifier();
2656
2657 let mut select_cols = Vec::with_capacity(self.select_columns.capacity());
2658 for col in self.select_columns {
2659 let col_snake = col.to_snake_case();
2660 let is_main_col = self.columns.contains(&col_snake);
2661
2662 // Check if this is a temporal type that needs special handling on Postgres
2663 let mut is_temporal = false;
2664 if matches!(self.driver, Drivers::Postgres) {
2665 if let Some(info) = self.columns_info.iter().find(|c| c.name.to_snake_case() == col_snake) {
2666 if is_temporal_type(info.sql_type) {
2667 is_temporal = true;
2668 }
2669 }
2670 }
2671
2672 if !self.joins_clauses.is_empty() || self.alias.is_some() {
2673 if let Some((table, column)) = col.split_once(".") {
2674 if is_temporal {
2675 select_cols.push(format!("to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"", table, column, column));
2676 } else {
2677 select_cols.push(format!("\"{}\".\"{}\"", table, column));
2678 }
2679 } else if col.contains('(') {
2680 select_cols.push(col);
2681 } else if is_main_col {
2682 if is_temporal {
2683 select_cols.push(format!("to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"", table_id, col, col));
2684 } else {
2685 select_cols.push(format!("\"{}\".\"{}\"", table_id, col));
2686 }
2687 } else {
2688 select_cols.push(format!("\"{}\"", col));
2689 }
2690 continue;
2691 }
2692
2693 if is_temporal {
2694 select_cols.push(format!("to_json(\"{}\") #>> '{{}}' AS \"{}\"", col, col));
2695 } else {
2696 select_cols.push(col);
2697 }
2698 }
2699
2700 query.push_str(&select_cols.join(", "));
2701
2702 // Build FROM clause
2703 query.push_str(" FROM \"");
2704 query.push_str(&self.table_name.to_snake_case());
2705 query.push_str("\" ");
2706 if let Some(alias) = &self.alias {
2707 query.push_str(&format!("{} ", alias));
2708 }
2709
2710 if !self.joins_clauses.is_empty() {
2711 query.push_str(&self.joins_clauses.join(" "));
2712 }
2713
2714 query.push_str(" WHERE 1=1");
2715
2716 // Apply WHERE clauses
2717 let mut args = AnyArguments::default();
2718 let mut arg_counter = 1;
2719
2720 for clause in &self.where_clauses {
2721 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2722 }
2723
2724 // Apply GROUP BY
2725 if !self.group_by_clauses.is_empty() {
2726 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
2727 }
2728
2729 // Apply HAVING
2730 if !self.having_clauses.is_empty() {
2731 query.push_str(" HAVING 1=1");
2732 for clause in &self.having_clauses {
2733 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2734 }
2735 }
2736
2737 // Apply ORDER BY
2738 if !self.order_clauses.is_empty() {
2739 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
2740 }
2741
2742 // Always add LIMIT 1 for scalar queries
2743 query.push_str(" LIMIT 1");
2744
2745 // Print SQL query to logs if debug mode is active
2746 if self.debug_mode {
2747 log::debug!("SQL: {}", query);
2748 }
2749
2750 // Execute query and fetch one row
2751 let row = sqlx::query_with::<_, _>(&query, args).fetch_one(self.tx.executor()).await?;
2752
2753 // Map row to the output type using FromAnyRow
2754 O::from_any_row(&row).map_err(|e| sqlx::Error::Decode(Box::new(e)))
2755 }
2756
2757 /// Updates a single column in the database.
2758 ///
2759 /// # Arguments
2760 ///
2761 /// * `col` - The column name to update
2762 /// * `value` - The new value
2763 ///
2764 /// # Returns
2765 ///
2766 /// * `Ok(u64)` - The number of rows affected
2767 pub fn update<'b, V>(&'b mut self, col: &str, value: V) -> BoxFuture<'b, Result<u64, sqlx::Error>>
2768 where
2769 V: ToString + Send + Sync,
2770 {
2771 let mut map = std::collections::HashMap::new();
2772 map.insert(col.to_string(), value.to_string());
2773 self.execute_update(map)
2774 }
2775
2776 /// Updates all columns based on the model instance.
2777 ///
2778 /// This method updates all active columns of the table with values from the provided model.
2779 ///
2780 /// # Arguments
2781 ///
2782 /// * `model` - The model instance containing new values
2783 ///
2784 /// # Returns
2785 ///
2786 /// * `Ok(u64)` - The number of rows affected
2787 pub fn updates<'b>(&'b mut self, model: &T) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
2788 self.execute_update(model.to_map())
2789 }
2790
2791 /// Updates columns based on a partial model (struct implementing AnyImpl).
2792 ///
2793 /// This allows updating a subset of columns using a custom struct.
2794 /// The struct must implement `AnyImpl` (usually via `#[derive(FromAnyRow)]`).
2795 ///
2796 /// # Arguments
2797 ///
2798 /// * `partial` - The partial model containing new values
2799 ///
2800 /// # Returns
2801 ///
2802 /// * `Ok(u64)` - The number of rows affected
2803 pub fn update_partial<'b, P: AnyImpl>(&'b mut self, partial: &P) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
2804 self.execute_update(partial.to_map())
2805 }
2806
2807 /// Internal helper to execute an UPDATE query from a map of values.
2808 fn execute_update<'b>(
2809 &'b mut self,
2810 data_map: std::collections::HashMap<String, String>,
2811 ) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
2812 // Apply default soft delete filter if not disabled
2813 if !self.with_deleted {
2814 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
2815 let col_owned = soft_delete_col.to_string();
2816 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
2817 query.push_str(" AND ");
2818 query.push_str(&format!("\"{}\"", col_owned));
2819 query.push_str(" IS NULL");
2820 });
2821 self.where_clauses.push(clause);
2822 }
2823 }
2824
2825 Box::pin(async move {
2826 let table_name = self.table_name.to_snake_case();
2827 let mut query = format!("UPDATE \"{}\" ", table_name);
2828 if let Some(alias) = &self.alias {
2829 query.push_str(&format!("{} ", alias));
2830 }
2831 query.push_str("SET ");
2832
2833 let mut bindings: Vec<(String, &str)> = Vec::new();
2834 let mut set_clauses = Vec::new();
2835
2836 // Maintain argument counter for PostgreSQL ($1, $2, ...)
2837 let mut arg_counter = 1;
2838
2839 // Build SET clause
2840 for (col_name, value) in data_map {
2841 // Strip the "r#" prefix if present
2842 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
2843
2844 // Find the SQL type for this column from the Model metadata
2845 let sql_type = self
2846 .columns_info
2847 .iter()
2848 .find(|c| c.name == col_name || c.name == col_name_clean)
2849 .map(|c| c.sql_type)
2850 .unwrap_or("TEXT");
2851
2852 // Generate placeholder
2853 let placeholder = match self.driver {
2854 Drivers::Postgres => {
2855 let idx = arg_counter;
2856 arg_counter += 1;
2857
2858 if temporal::is_temporal_type(sql_type) {
2859 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
2860 } else {
2861 match sql_type {
2862 "UUID" => format!("${}::UUID", idx),
2863 "JSONB" | "jsonb" => format!("${}::JSONB", idx),
2864 _ => format!("${}", idx),
2865 }
2866 }
2867 }
2868 _ => "?".to_string(),
2869 };
2870
2871 set_clauses.push(format!("\"{}\" = {}", col_name_clean, placeholder));
2872 bindings.push((value, sql_type));
2873 }
2874
2875 // If no fields to update, return 0
2876 if set_clauses.is_empty() {
2877 return Ok(0);
2878 }
2879
2880 query.push_str(&set_clauses.join(", "));
2881
2882 // Build WHERE clause
2883 query.push_str(" WHERE 1=1");
2884
2885 let mut args = AnyArguments::default();
2886
2887 // Bind SET values
2888 for (val_str, sql_type) in bindings {
2889 if args.bind_value(&val_str, sql_type, &self.driver).is_err() {
2890 let _ = args.add(val_str);
2891 }
2892 }
2893
2894 // Apply WHERE clauses (appending to args and query)
2895 for clause in &self.where_clauses {
2896 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2897 }
2898
2899 // Print SQL query to logs if debug mode is active
2900 if self.debug_mode {
2901 log::debug!("SQL: {}", query);
2902 }
2903
2904 // Execute the UPDATE query
2905 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2906
2907 Ok(result.rows_affected())
2908 })
2909 }
2910
2911 /// Executes a DELETE query based on the current filters.
2912 ///
2913 /// If the model has a `#[orm(soft_delete)]` column, this method performs
2914 /// an UPDATE setting the soft delete column to the current timestamp instead
2915 /// of physically deleting the record.
2916 ///
2917 /// For permanent deletion, use `hard_delete()`.
2918 ///
2919 /// # Returns
2920 ///
2921 /// * `Ok(u64)` - The number of rows deleted (or soft-deleted)
2922 /// * `Err(sqlx::Error)` - Database error
2923 pub async fn delete(mut self) -> Result<u64, sqlx::Error> {
2924 // Check for soft delete column
2925 let soft_delete_col = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name);
2926
2927 if let Some(col) = soft_delete_col {
2928 // Soft Delete: Update the column to current timestamp
2929 let table_name = self.table_name.to_snake_case();
2930 let mut query = format!("UPDATE \"{}\" ", table_name);
2931 if let Some(alias) = &self.alias {
2932 query.push_str(&format!("{} ", alias));
2933 }
2934 query.push_str(&format!("SET \"{}\" = ", col));
2935
2936 match self.driver {
2937 Drivers::Postgres => query.push_str("NOW()"),
2938 Drivers::SQLite => query.push_str("strftime('%Y-%m-%dT%H:%M:%SZ', 'now')"),
2939 Drivers::MySQL => query.push_str("NOW()"),
2940 }
2941
2942 query.push_str(" WHERE 1=1");
2943
2944 let mut args = AnyArguments::default();
2945 let mut arg_counter = 1;
2946
2947 // Apply filters
2948 for clause in &self.where_clauses {
2949 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2950 }
2951
2952 // Print SQL query to logs if debug mode is active
2953 if self.debug_mode {
2954 log::debug!("SQL: {}", query);
2955 }
2956
2957 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2958 Ok(result.rows_affected())
2959 } else {
2960 // Standard Delete (no soft delete column)
2961 let mut query = String::from("DELETE FROM \"");
2962 query.push_str(&self.table_name.to_snake_case());
2963 query.push_str("\" WHERE 1=1");
2964
2965 let mut args = AnyArguments::default();
2966 let mut arg_counter = 1;
2967
2968 for clause in &self.where_clauses {
2969 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2970 }
2971
2972 // Print SQL query to logs if debug mode is active
2973 if self.debug_mode {
2974 log::debug!("SQL: {}", query);
2975 }
2976
2977 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2978 Ok(result.rows_affected())
2979 }
2980 }
2981
2982 /// Permanently removes records from the database.
2983 ///
2984 /// This method performs a physical DELETE, bypassing any soft delete logic.
2985 /// Use this when you need to permanently remove records.
2986 ///
2987 /// # Returns
2988 ///
2989 /// * `Ok(u64)` - The number of rows deleted
2990 /// * `Err(sqlx::Error)` - Database error
2991 ///
2992 /// # Example
2993 ///
2994 /// ```rust,ignore
2995 /// // Permanently delete soft-deleted records older than 30 days
2996 /// db.model::<User>()
2997 /// .with_deleted()
2998 /// .filter("deleted_at", "<", thirty_days_ago)
2999 /// .hard_delete()
3000 /// .await?;
3001 /// ```
3002 pub async fn hard_delete(mut self) -> Result<u64, sqlx::Error> {
3003 let mut query = String::from("DELETE FROM \"");
3004 query.push_str(&self.table_name.to_snake_case());
3005 query.push_str("\" WHERE 1=1");
3006
3007 let mut args = AnyArguments::default();
3008 let mut arg_counter = 1;
3009
3010 for clause in &self.where_clauses {
3011 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
3012 }
3013
3014 // Print SQL query to logs if debug mode is active
3015 if self.debug_mode {
3016 log::debug!("SQL: {}", query);
3017 }
3018
3019 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
3020 Ok(result.rows_affected())
3021 }
3022}