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::AnyArguments, Any, Arguments, Decode, Encode, Row, Type};
53use std::marker::PhantomData;
54use uuid::Uuid;
55
56// ============================================================================
57// Internal Crate Imports
58// ============================================================================
59
60use crate::{
61 any_struct::FromAnyRow,
62 database::{Connection, Drivers},
63 model::{ColumnInfo, Model},
64 temporal::{self, is_temporal_type},
65 value_binding::ValueBinder,
66 AnyImpl, Error,
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}
140
141impl Op {
142 /// Converts the operator to its SQL string representation.
143 pub fn as_sql(&self) -> &'static str {
144 match self {
145 Op::Eq => "=",
146 Op::Ne => "!=",
147 Op::Gt => ">",
148 Op::Gte => ">=",
149 Op::Lt => "<",
150 Op::Lte => "<=",
151 Op::Like => "LIKE",
152 Op::NotLike => "NOT LIKE",
153 Op::In => "IN",
154 Op::NotIn => "NOT IN",
155 }
156 }
157}
158
159// ============================================================================
160// QueryBuilder Struct
161// ============================================================================
162
163/// A fluent Query Builder for constructing SQL queries.
164///
165/// `QueryBuilder` provides a type-safe, ergonomic interface for building and executing
166/// SQL queries across different database backends. It supports filtering, ordering,
167/// pagination, and both SELECT and INSERT operations.
168///
169/// ## Type Parameter
170///
171/// * `'a` - Lifetime of the database reference (used for PhantomData)
172/// * `T` - The Model type this query operates on
173/// * `E` - The connection type (Database or Transaction)
174///
175/// ## Fields
176///
177/// * `db` - Reference to the database connection pool or transaction
178/// * `table_name` - Static string containing the table name
179/// * `columns_info` - Metadata about each column in the table
180/// * `columns` - List of column names in snake_case format
181/// * `select_columns` - Specific columns to select (empty = SELECT *)
182/// * `where_clauses` - List of filter functions to apply
183/// * `order_clauses` - List of ORDER BY clauses
184/// * `limit` - Maximum number of rows to return
185/// * `offset` - Number of rows to skip (for pagination)
186/// * `_marker` - PhantomData to bind the generic type T
187pub struct QueryBuilder<'a, T, E> {
188 /// Reference to the database connection pool
189 pub(crate) tx: E,
190
191 /// Database driver type
192 pub(crate) driver: Drivers,
193
194 /// Name of the database table (in original case)
195 pub(crate) table_name: &'static str,
196
197 /// Metadata information about each column
198 pub(crate) columns_info: Vec<ColumnInfo>,
199
200 /// List of column names (in snake_case)
201 pub(crate) columns: Vec<String>,
202
203 /// Specific columns to select (empty means SELECT *)
204 pub(crate) select_columns: Vec<String>,
205
206 /// Collection of WHERE clause filter functions
207 pub(crate) where_clauses: Vec<FilterFn>,
208
209 /// Collection of ORDER BY clauses
210 pub(crate) order_clauses: Vec<String>,
211
212 /// Collection of JOIN clause to filter entry tables
213 pub(crate) joins_clauses: Vec<String>,
214
215 /// Maximum number of rows to return (LIMIT)
216 pub(crate) limit: Option<usize>,
217
218 /// Number of rows to skip (OFFSET)
219 pub(crate) offset: Option<usize>,
220
221 /// Activate debug mode in query
222 pub(crate) debug_mode: bool,
223
224 /// Clauses for GROUP BY
225 pub(crate) group_by_clauses: Vec<String>,
226
227 /// Clauses for HAVING
228 pub(crate) having_clauses: Vec<FilterFn>,
229
230 /// Distinct flag
231 pub(crate) is_distinct: bool,
232
233 /// Columns to omit from the query results (inverse of select_columns)
234 pub(crate) omit_columns: Vec<String>,
235
236 /// Whether to include soft-deleted records in query results
237 pub(crate) with_deleted: bool,
238
239 /// PhantomData to bind the generic type T
240 pub(crate) _marker: PhantomData<&'a T>,
241}
242
243// ============================================================================
244// QueryBuilder Implementation
245// ============================================================================
246
247impl<'a, T, E> QueryBuilder<'a, T, E>
248where
249 T: Model + Send + Sync + Unpin,
250 E: Connection + Send,
251{
252 // ========================================================================
253 // Constructor
254 // ========================================================================
255
256 /// Creates a new QueryBuilder instance.
257 ///
258 /// This constructor is typically called internally via `db.model::<T>()`.
259 /// You rarely need to call this directly.
260 ///
261 /// # Arguments
262 ///
263 /// * `db` - Reference to the database connection
264 /// * `table_name` - Name of the table to query
265 /// * `columns_info` - Metadata about table columns
266 /// * `columns` - List of column names
267 ///
268 /// # Returns
269 ///
270 /// A new `QueryBuilder` instance ready for query construction
271 ///
272 /// # Example
273 ///
274 /// ```rust,ignore
275 /// // Usually called via db.model::<User>()
276 /// let query = db.model::<User>();
277 /// ```
278 pub fn new(
279 tx: E,
280 driver: Drivers,
281 table_name: &'static str,
282 columns_info: Vec<ColumnInfo>,
283 columns: Vec<String>,
284 ) -> Self {
285 // Pre-populate omit_columns with globally omitted columns (from #[orm(omit)] attribute)
286 let omit_columns: Vec<String> =
287 columns_info.iter().filter(|c| c.omit).map(|c| c.name.to_snake_case()).collect();
288
289 Self {
290 tx,
291 driver,
292 table_name,
293 columns_info,
294 columns,
295 debug_mode: false,
296 select_columns: Vec::new(),
297 where_clauses: Vec::new(),
298 order_clauses: Vec::new(),
299 joins_clauses: Vec::new(),
300 group_by_clauses: Vec::new(),
301 having_clauses: Vec::new(),
302 is_distinct: false,
303 omit_columns,
304 limit: None,
305 offset: None,
306 with_deleted: false,
307 _marker: PhantomData,
308 }
309 }
310
311 // ========================================================================
312 // Query Building Methods
313 // ========================================================================
314
315 /// Adds a WHERE clause to the query.
316 ///
317 /// This method adds a filter condition to the query. Multiple filters can be chained
318 /// and will be combined with AND operators. The value is bound as a parameter to
319 /// prevent SQL injection.
320 ///
321 /// # Type Parameters
322 ///
323 /// * `V` - The type of the value to filter by. Must be encodable for SQL queries.
324 ///
325 /// # Arguments
326 ///
327 /// * `col` - The column name to filter on
328 /// * `op` - The comparison operator (e.g., "=", ">", "LIKE", "IN")
329 /// * `value` - The value to compare against
330 ///
331 /// # Supported Types
332 ///
333 /// - Primitives: `i32`, `i64`, `f64`, `bool`, `String`
334 /// - UUID: `Uuid` (all versions 1-7)
335 /// - Date/Time: `DateTime<Utc>`, `NaiveDateTime`, `NaiveDate`, `NaiveTime`
336 /// - Options: `Option<T>` for any supported type T
337 ///
338 /// # Example
339 ///
340 /// ```rust,ignore
341 /// // Filter by integer
342 /// query.filter("age", ">=", 18)
343 ///
344 /// // Filter by string
345 /// query.filter("username", "=", "john_doe")
346 ///
347 /// // Filter by UUID
348 /// let user_id = Uuid::new_v4();
349 /// query.filter("id", "=", user_id)
350 ///
351 /// // Filter with LIKE operator
352 /// query.filter("email", "LIKE", "%@example.com")
353 ///
354 /// // Chain multiple filters
355 /// query
356 /// .filter("age", Op::Gte, 18)
357 /// .filter("active", Op::Eq, true)
358 /// .filter("role", Op::Eq, "admin")
359 /// ```
360 pub fn filter<V>(mut self, col: &'static str, op: Op, value: V) -> Self
361 where
362 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
363 {
364 let op_str = op.as_sql();
365 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
366 query.push_str(" AND ");
367 if let Some((table, column)) = col.split_once(".") {
368 query.push_str(&format!("\"{}\".\"{}\"", table, column));
369 } else {
370 query.push_str(&format!("\"{}\"", col));
371 }
372 query.push(' ');
373 query.push_str(op_str);
374 query.push(' ');
375
376 // Handle different placeholder syntaxes based on database driver
377 match driver {
378 // PostgreSQL uses numbered placeholders: $1, $2, $3, ...
379 Drivers::Postgres => {
380 query.push_str(&format!("${}", arg_counter));
381 *arg_counter += 1;
382 }
383 // MySQL and SQLite use question mark placeholders: ?
384 _ => query.push('?'),
385 }
386
387 // Bind the value to the query
388 let _ = args.add(value.clone());
389 });
390
391 self.where_clauses.push(clause);
392 self
393 }
394
395 /// Adds an equality filter to the query.
396 ///
397 /// This is a convenience wrapper around `filter()` for simple equality checks.
398 /// It is equivalent to calling `filter(col, "=", value)`.
399 ///
400 /// # Type Parameters
401 ///
402 /// * `V` - The type of the value to compare against.
403 ///
404 /// # Arguments
405 ///
406 /// * `col` - The column name to filter on.
407 /// * `value` - The value to match.
408 ///
409 /// # Example
410 ///
411 /// ```rust,ignore
412 /// // Equivalent to filter("age", Op::Eq, 18)
413 /// query.equals("age", 18)
414 /// ```
415 pub fn equals<V>(self, col: &'static str, value: V) -> Self
416 where
417 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
418 {
419 self.filter(col, Op::Eq, value)
420 }
421
422 /// Adds an ORDER BY clause to the query.
423 ///
424 /// Specifies the sort order for the query results. Multiple order clauses
425 /// can be added and will be applied in the order they were added.
426 ///
427 /// # Arguments
428 ///
429 /// * `order` - The ORDER BY expression (e.g., "created_at DESC", "age ASC, name DESC")
430 ///
431 /// # Example
432 ///
433 /// ```rust,ignore
434 /// // Single column ascending (ASC is default)
435 /// query.order("age")
436 ///
437 /// // Single column descending
438 /// query.order("created_at DESC")
439 ///
440 /// // Multiple columns
441 /// query.order("age DESC, username ASC")
442 ///
443 /// // Chain multiple order clauses
444 /// query
445 /// .order("priority DESC")
446 /// .order("created_at ASC")
447 /// ```
448 pub fn order(mut self, order: &str) -> Self {
449 self.order_clauses.push(order.to_string());
450 self
451 }
452
453 /// Placeholder for eager loading relationships (preload).
454 ///
455 /// This method is reserved for future implementation of relationship preloading.
456 /// Currently, it returns `self` unchanged to maintain the fluent interface.
457 ///
458 /// # Future Implementation
459 ///
460 /// Will support eager loading of related models to avoid N+1 query problems:
461 ///
462 /// ```rust,ignore
463 /// // Future usage example
464 /// query.preload("posts").preload("comments")
465 /// ```
466 // pub fn preload(self) -> Self {
467 // // TODO: Implement relationship preloading
468 // self
469 // }
470
471 /// Activates debug mode for this query.
472 ///
473 /// When enabled, the generated SQL query will be logged using the `log` crate
474 /// at the `DEBUG` level before execution.
475 ///
476 /// # Note
477 ///
478 /// To see the output, you must initialize a logger in your application (e.g., using `env_logger`)
479 /// and configure it to display `debug` logs for `bottle_orm`.
480 ///
481 /// # Example
482 ///
483 /// ```rust,ignore
484 /// db.model::<User>()
485 /// .filter("active", "=", true)
486 /// .debug() // Logs SQL: SELECT * FROM "user" WHERE "active" = $1
487 /// .scan()
488 /// .await?;
489 /// ```
490 pub fn debug(mut self) -> Self {
491 self.debug_mode = true;
492 self
493 }
494
495 /// Adds an IS NULL filter for the specified column.
496 ///
497 /// # Arguments
498 ///
499 /// * `col` - The column name to check for NULL
500 ///
501 /// # Example
502 ///
503 /// ```rust,ignore
504 /// db.model::<User>()
505 /// .is_null("deleted_at")
506 /// .scan()
507 /// .await?;
508 /// // SQL: SELECT * FROM "user" WHERE "deleted_at" IS NULL
509 /// ```
510 pub fn is_null(mut self, col: &str) -> Self {
511 let col_owned = col.to_string();
512 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
513 query.push_str(" AND ");
514 if let Some((table, column)) = col_owned.split_once(".") {
515 query.push_str(&format!("\"{}\".\"{}\"", table, column));
516 } else {
517 query.push_str(&format!("\"{}\"", col_owned));
518 }
519 query.push_str(" IS NULL");
520 });
521 self.where_clauses.push(clause);
522 self
523 }
524
525 /// Adds an IS NOT NULL filter for the specified column.
526 ///
527 /// # Arguments
528 ///
529 /// * `col` - The column name to check for NOT NULL
530 ///
531 /// # Example
532 ///
533 /// ```rust,ignore
534 /// db.model::<User>()
535 /// .is_not_null("email")
536 /// .scan()
537 /// .await?;
538 /// // SQL: SELECT * FROM "user" WHERE "email" IS NOT NULL
539 /// ```
540 pub fn is_not_null(mut self, col: &str) -> Self {
541 let col_owned = col.to_string();
542 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
543 query.push_str(" AND ");
544 if let Some((table, column)) = col_owned.split_once(".") {
545 query.push_str(&format!("\"{}\".\"{}\"", table, column));
546 } else {
547 query.push_str(&format!("\"{}\"", col_owned));
548 }
549 query.push_str(" IS NOT NULL");
550 });
551 self.where_clauses.push(clause);
552 self
553 }
554
555 /// Includes soft-deleted records in query results.
556 ///
557 /// By default, queries on models with a `#[orm(soft_delete)]` column exclude
558 /// records where that column is not NULL. This method disables that filter.
559 ///
560 /// # Example
561 ///
562 /// ```rust,ignore
563 /// // Get all users including deleted ones
564 /// db.model::<User>()
565 /// .with_deleted()
566 /// .scan()
567 /// .await?;
568 /// ```
569 pub fn with_deleted(mut self) -> Self {
570 self.with_deleted = true;
571 self
572 }
573
574 /// Placeholder for JOIN operations.
575 ///
576 /// This method is reserved for future implementation of SQL JOINs.
577 /// Currently, it returns `self` unchanged to maintain the fluent interface.
578 ///
579 /// # Future Implementation
580 ///
581 /// Will support various types of JOINs (INNER, LEFT, RIGHT, FULL):
582 ///
583 /// ```rust,ignore
584 /// Adds a JOIN clause to the query.
585 ///
586 /// # Arguments
587 ///
588 /// * `table` - The name of the table to join.
589 /// * `s_query` - The ON clause condition (e.g., "users.id = posts.user_id").
590 ///
591 /// # Example
592 ///
593 /// ```rust,ignore
594 /// query.join("posts", "users.id = posts.user_id")
595 /// ```
596 pub fn join(mut self, table: &str, s_query: &str) -> Self {
597 let trimmed_value = s_query.replace(" ", "");
598 let values = trimmed_value.split_once("=");
599 let parsed_query: String;
600 if let Some((first, second)) = values {
601 let ref_table = first.split_once(".").expect("failed to parse JOIN clause");
602 let to_table = second.split_once(".").expect("failed to parse JOIN clause");
603 parsed_query = format!("\"{}\".\"{}\" = \"{}\".\"{}\"", ref_table.0, ref_table.1, to_table.0, to_table.1);
604 } else {
605 panic!("Failed to parse JOIN, Ex to use: .join(\"table2\", \"table.column = table2.column2\")")
606 }
607
608 self.joins_clauses.push(format!("JOIN \"{}\" ON {}", table, parsed_query));
609 self
610 }
611
612 /// Internal helper for specific join types
613 fn join_generic(mut self, join_type: &str, table: &str, s_query: &str) -> Self {
614 let trimmed_value = s_query.replace(" ", "");
615 let values = trimmed_value.split_once("=");
616 let parsed_query: String;
617 if let Some((first, second)) = values {
618 let ref_table = first.split_once(".").expect("failed to parse JOIN clause");
619 let to_table = second.split_once(".").expect("failed to parse JOIN clause");
620 parsed_query = format!("\"{}\".\"{}\" = \"{}\".\"{}\"", ref_table.0, ref_table.1, to_table.0, to_table.1);
621 } else {
622 panic!("Failed to parse JOIN, Ex to use: .join(\"table2\", \"table.column = table2.column2\")")
623 }
624
625 self.joins_clauses.push(format!("{} JOIN \"{}\" ON {}", join_type, table, parsed_query));
626 self
627 }
628
629 /// Adds a LEFT JOIN clause.
630 ///
631 /// Performs a LEFT JOIN with another table. Returns all records from the left table,
632 /// and the matched records from the right table (or NULL if no match).
633 ///
634 /// # Arguments
635 ///
636 /// * `table` - The name of the table to join with
637 /// * `on` - The join condition (e.g., "users.id = posts.user_id")
638 ///
639 /// # Example
640 ///
641 /// ```rust,ignore
642 /// // Get all users and their posts (if any)
643 /// let users_with_posts = db.model::<User>()
644 /// .left_join("posts", "users.id = posts.user_id")
645 /// .scan()
646 /// .await?;
647 /// ```
648 pub fn left_join(self, table: &str, on: &str) -> Self {
649 self.join_generic("LEFT", table, on)
650 }
651
652 /// Adds a RIGHT JOIN clause.
653 ///
654 /// Performs a RIGHT JOIN with another table. Returns all records from the right table,
655 /// and the matched records from the left table (or NULL if no match).
656 ///
657 /// # Arguments
658 ///
659 /// * `table` - The name of the table to join with
660 /// * `on` - The join condition
661 ///
662 /// # Example
663 ///
664 /// ```rust,ignore
665 /// let posts_with_users = db.model::<Post>()
666 /// .right_join("users", "posts.user_id = users.id")
667 /// .scan()
668 /// .await?;
669 /// ```
670 pub fn right_join(self, table: &str, on: &str) -> Self {
671 self.join_generic("RIGHT", table, on)
672 }
673
674 /// Adds an INNER JOIN clause.
675 ///
676 /// Performs an INNER JOIN with another table. Returns records that have matching
677 /// values in both tables.
678 ///
679 /// # Arguments
680 ///
681 /// * `table` - The name of the table to join with
682 /// * `on` - The join condition
683 ///
684 /// # Example
685 ///
686 /// ```rust,ignore
687 /// // Get only users who have posts
688 /// let active_users = db.model::<User>()
689 /// .inner_join("posts", "users.id = posts.user_id")
690 /// .scan()
691 /// .await?;
692 /// ```
693 pub fn inner_join(self, table: &str, on: &str) -> Self {
694 self.join_generic("INNER", table, on)
695 }
696
697 /// Adds a FULL JOIN clause.
698 ///
699 /// Performs a FULL OUTER JOIN. Returns all records when there is a match in
700 /// either left or right table.
701 ///
702 /// # Arguments
703 ///
704 /// * `table` - The name of the table to join with
705 /// * `on` - The join condition
706 ///
707 /// # Note
708 ///
709 /// Support for FULL JOIN depends on the underlying database engine (e.g., SQLite
710 /// does not support FULL JOIN directly).
711 pub fn full_join(self, table: &str, on: &str) -> Self {
712 self.join_generic("FULL", table, on)
713 }
714
715 /// Marks the query to return DISTINCT results.
716 ///
717 /// Adds the `DISTINCT` keyword to the SELECT statement, ensuring that unique
718 /// rows are returned.
719 ///
720 /// # Example
721 ///
722 /// ```rust,ignore
723 /// // Get unique ages of users
724 /// let unique_ages: Vec<i32> = db.model::<User>()
725 /// .select("age")
726 /// .distinct()
727 /// .scan()
728 /// .await?;
729 /// ```
730 pub fn distinct(mut self) -> Self {
731 self.is_distinct = true;
732 self
733 }
734
735 /// Adds a GROUP BY clause to the query.
736 ///
737 /// Groups rows that have the same values into summary rows. Often used with
738 /// aggregate functions (COUNT, MAX, MIN, SUM, AVG).
739 ///
740 /// # Arguments
741 ///
742 /// * `columns` - Comma-separated list of columns to group by
743 ///
744 /// # Example
745 ///
746 /// ```rust,ignore
747 /// // Count users by age group
748 /// let stats: Vec<(i32, i64)> = db.model::<User>()
749 /// .select("age, COUNT(*)")
750 /// .group_by("age")
751 /// .scan()
752 /// .await?;
753 /// ```
754 pub fn group_by(mut self, columns: &str) -> Self {
755 self.group_by_clauses.push(columns.to_string());
756 self
757 }
758
759 /// Adds a HAVING clause to the query.
760 ///
761 /// Used to filter groups created by `group_by`. Similar to `filter` (WHERE),
762 /// but operates on grouped records and aggregate functions.
763 ///
764 /// # Arguments
765 ///
766 /// * `col` - The column or aggregate function to filter on
767 /// * `op` - Comparison operator
768 /// * `value` - Value to compare against
769 ///
770 /// # Example
771 ///
772 /// ```rust,ignore
773 /// // Get ages with more than 5 users
774 /// let popular_ages = db.model::<User>()
775 /// .select("age, COUNT(*)")
776 /// .group_by("age")
777 /// .having("COUNT(*)", ">", 5)
778 /// .scan()
779 /// .await?;
780 /// ```
781 pub fn having<V>(mut self, col: &'static str, op: &'static str, value: V) -> Self
782 where
783 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
784 {
785 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
786 query.push_str(" AND ");
787 query.push_str(col);
788 query.push(' ');
789 query.push_str(op);
790 query.push(' ');
791
792 match driver {
793 Drivers::Postgres => {
794 query.push_str(&format!("${}", arg_counter));
795 *arg_counter += 1;
796 }
797 _ => query.push('?'),
798 }
799 let _ = args.add(value.clone());
800 });
801
802 self.having_clauses.push(clause);
803 self
804 }
805
806 /// Returns the COUNT of rows matching the query.
807 ///
808 /// A convenience method that automatically sets `SELECT COUNT(*)` and returns
809 /// the result as an `i64`.
810 ///
811 /// # Returns
812 ///
813 /// * `Ok(i64)` - The count of rows
814 /// * `Err(sqlx::Error)` - Database error
815 ///
816 /// # Example
817 ///
818 /// ```rust,ignore
819 /// let user_count = db.model::<User>().count().await?;
820 /// ```
821 pub async fn count(mut self) -> Result<i64, sqlx::Error> {
822 self.select_columns = vec!["COUNT(*)".to_string()];
823 self.scalar::<i64>().await
824 }
825
826 /// Returns the SUM of the specified column.
827 ///
828 /// Calculates the sum of a numeric column.
829 ///
830 /// # Arguments
831 ///
832 /// * `column` - The column to sum
833 ///
834 /// # Example
835 ///
836 /// ```rust,ignore
837 /// let total_age: i64 = db.model::<User>().sum("age").await?;
838 /// ```
839 pub async fn sum<N>(mut self, column: &str) -> Result<N, sqlx::Error>
840 where
841 N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
842 {
843 self.select_columns = vec![format!("SUM({})", column)];
844 self.scalar::<N>().await
845 }
846
847 /// Returns the AVG of the specified column.
848 ///
849 /// Calculates the average value of a numeric column.
850 ///
851 /// # Arguments
852 ///
853 /// * `column` - The column to average
854 ///
855 /// # Example
856 ///
857 /// ```rust,ignore
858 /// let avg_age: f64 = db.model::<User>().avg("age").await?;
859 /// ```
860 pub async fn avg<N>(mut self, column: &str) -> Result<N, sqlx::Error>
861 where
862 N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
863 {
864 self.select_columns = vec![format!("AVG({})", column)];
865 self.scalar::<N>().await
866 }
867
868 /// Returns the MIN of the specified column.
869 ///
870 /// Finds the minimum value in a column.
871 ///
872 /// # Arguments
873 ///
874 /// * `column` - The column to check
875 ///
876 /// # Example
877 ///
878 /// ```rust,ignore
879 /// let min_age: i32 = db.model::<User>().min("age").await?;
880 /// ```
881 pub async fn min<N>(mut self, column: &str) -> Result<N, sqlx::Error>
882 where
883 N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
884 {
885 self.select_columns = vec![format!("MIN({})", column)];
886 self.scalar::<N>().await
887 }
888
889 /// Returns the MAX of the specified column.
890 ///
891 /// Finds the maximum value in a column.
892 ///
893 /// # Arguments
894 ///
895 /// * `column` - The column to check
896 ///
897 /// # Example
898 ///
899 /// ```rust,ignore
900 /// let max_age: i32 = db.model::<User>().max("age").await?;
901 /// ```
902 pub async fn max<N>(mut self, column: &str) -> Result<N, sqlx::Error>
903 where
904 N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
905 {
906 self.select_columns = vec![format!("MAX({})", column)];
907 self.scalar::<N>().await
908 }
909
910 /// Applies pagination with validation and limits.
911 ///
912 /// This is a convenience method that combines `limit()` and `offset()` with
913 /// built-in validation and maximum value enforcement for safer pagination.
914 ///
915 /// # Arguments
916 ///
917 /// * `max_value` - Maximum allowed items per page
918 /// * `default` - Default value if `value` exceeds `max_value`
919 /// * `page` - Zero-based page number
920 /// * `value` - Requested items per page
921 ///
922 /// # Returns
923 ///
924 /// * `Ok(Self)` - The updated QueryBuilder with pagination applied
925 /// * `Err(Error)` - If `value` is negative
926 ///
927 /// # Pagination Logic
928 ///
929 /// 1. Validates that `value` is non-negative
930 /// 2. If `value` > `max_value`, uses `default` instead
931 /// 3. Calculates offset as: `value * page`
932 /// 4. Sets limit to `value`
933 ///
934 /// # Example
935 ///
936 /// ```rust,ignore
937 /// // Page 0 with 10 items (page 1 in 1-indexed systems)
938 /// query.pagination(100, 20, 0, 10)? // LIMIT 10 OFFSET 0
939 ///
940 /// // Page 2 with 25 items (page 3 in 1-indexed systems)
941 /// query.pagination(100, 20, 2, 25)? // LIMIT 25 OFFSET 50
942 ///
943 /// // Request too many items, falls back to default
944 /// query.pagination(100, 20, 0, 150)? // LIMIT 20 OFFSET 0 (150 > 100)
945 ///
946 /// // Error: negative value
947 /// query.pagination(100, 20, 0, -10)? // Returns Error
948 /// ```
949 pub fn pagination(mut self, max_value: usize, default: usize, page: usize, value: isize) -> Result<Self, Error> {
950 // Validate that value is non-negative
951 if value < 0 {
952 return Err(Error::InvalidArgument("value cannot be negative".into()));
953 }
954
955 let mut f_value = value as usize;
956
957 // Enforce maximum value limit
958 if f_value > max_value {
959 f_value = default;
960 }
961
962 // Apply offset and limit
963 self = self.offset(f_value * page);
964 self = self.limit(f_value);
965
966 Ok(self)
967 }
968
969 /// Selects specific columns to return.
970 ///
971 /// By default, queries use `SELECT *` to return all columns. This method
972 /// allows you to specify exactly which columns should be returned.
973 ///
974 /// **Note:** Columns are pushed exactly as provided, without automatic
975 /// snake_case conversion, allowing for aliases and raw SQL fragments.
976 ///
977 /// # Arguments
978 ///
979 /// * `columns` - Comma-separated list of column names to select
980 ///
981 /// # Example
982 ///
983 /// ```rust,ignore
984 /// // Select single column
985 /// query.select("id")
986 ///
987 /// // Select multiple columns
988 /// query.select("id, username, email")
989 ///
990 /// // Select with SQL functions and aliases (now supported)
991 /// query.select("COUNT(*) as total_count")
992 /// ```
993 pub fn select(mut self, columns: &str) -> Self {
994 self.select_columns.push(columns.to_string());
995 self
996 }
997
998 /// Excludes specific columns from the query results.
999 ///
1000 /// This is the inverse of `select()`. Instead of specifying which columns to include,
1001 /// you specify which columns to exclude. All other columns will be returned.
1002 ///
1003 /// # Arguments
1004 ///
1005 /// * `columns` - Comma-separated list of column names to exclude
1006 ///
1007 /// # Priority
1008 ///
1009 /// If both `select()` and `omit()` are used, `select()` takes priority.
1010 ///
1011 /// # Example
1012 ///
1013 /// ```rust,ignore
1014 /// // Exclude password from results
1015 /// let user = db.model::<User>()
1016 /// .omit("password")
1017 /// .first()
1018 /// .await?;
1019 ///
1020 /// // Exclude multiple fields
1021 /// let user = db.model::<User>()
1022 /// .omit("password, secret_token")
1023 /// .first()
1024 /// .await?;
1025 ///
1026 /// // Using with generated field constants (autocomplete support)
1027 /// let user = db.model::<User>()
1028 /// .omit(user_fields::PASSWORD)
1029 /// .first()
1030 /// .await?;
1031 /// ```
1032 pub fn omit(mut self, columns: &str) -> Self {
1033 for col in columns.split(',') {
1034 self.omit_columns.push(col.trim().to_snake_case());
1035 }
1036 self
1037 }
1038
1039 /// Sets the query offset (pagination).
1040 ///
1041 /// Specifies the number of rows to skip before starting to return rows.
1042 /// Commonly used in combination with `limit()` for pagination.
1043 ///
1044 /// # Arguments
1045 ///
1046 /// * `offset` - Number of rows to skip
1047 ///
1048 /// # Example
1049 ///
1050 /// ```rust,ignore
1051 /// // Skip first 20 rows
1052 /// query.offset(20)
1053 ///
1054 /// // Pagination: page 3 with 10 items per page
1055 /// query.limit(10).offset(20) // Skip 2 pages = 20 items
1056 /// ```
1057 pub fn offset(mut self, offset: usize) -> Self {
1058 self.offset = Some(offset);
1059 self
1060 }
1061
1062 /// Sets the maximum number of records to return.
1063 ///
1064 /// Limits the number of rows returned by the query. Essential for pagination
1065 /// and preventing accidentally fetching large result sets.
1066 ///
1067 /// # Arguments
1068 ///
1069 /// * `limit` - Maximum number of rows to return
1070 ///
1071 /// # Example
1072 ///
1073 /// ```rust,ignore
1074 /// // Return at most 10 rows
1075 /// query.limit(10)
1076 ///
1077 /// // Pagination: 50 items per page
1078 /// query.limit(50).offset(page * 50)
1079 /// ```
1080 pub fn limit(mut self, limit: usize) -> Self {
1081 self.limit = Some(limit);
1082 self
1083 }
1084
1085 // ========================================================================
1086 // Insert Operation
1087 // ========================================================================
1088
1089 /// Inserts a new record into the database based on the model instance.
1090 ///
1091 /// This method serializes the model into a SQL INSERT statement with proper
1092 /// type handling for primitives, dates, UUIDs, and other supported types.
1093 ///
1094 /// # Type Binding Strategy
1095 ///
1096 /// The method uses string parsing as a temporary solution for type binding.
1097 /// Values are converted to strings via the model's `to_map()` method, then
1098 /// parsed back to their original types for proper SQL binding.
1099 ///
1100 /// # Supported Types for Insert
1101 ///
1102 /// - **Integers**: `i32`, `i64` (INTEGER, BIGINT)
1103 /// - **Boolean**: `bool` (BOOLEAN)
1104 /// - **Float**: `f64` (DOUBLE PRECISION)
1105 /// - **Text**: `String` (TEXT, VARCHAR)
1106 /// - **UUID**: `Uuid` (UUID) - All versions 1-7 supported
1107 /// - **DateTime**: `DateTime<Utc>` (TIMESTAMPTZ)
1108 /// - **NaiveDateTime**: (TIMESTAMP)
1109 /// - **NaiveDate**: (DATE)
1110 /// - **NaiveTime**: (TIME)
1111 ///
1112 /// # Arguments
1113 ///
1114 /// * `model` - Reference to the model instance to insert
1115 ///
1116 /// # Returns
1117 ///
1118 /// * `Ok(&Self)` - Reference to self for method chaining
1119 /// * `Err(sqlx::Error)` - Database error during insertion
1120 ///
1121 /// # Example
1122 ///
1123 /// ```rust,ignore
1124 /// use uuid::Uuid;
1125 /// use chrono::Utc;
1126 ///
1127 /// let new_user = User {
1128 /// id: Uuid::new_v4(),
1129 /// username: "john_doe".to_string(),
1130 /// email: "john@example.com".to_string(),
1131 /// age: 25,
1132 /// active: true,
1133 /// created_at: Utc::now(),
1134 /// };
1135 ///
1136 /// db.model::<User>().insert(&new_user).await?;
1137 /// ```
1138 pub fn insert<'b>(&'b mut self, model: &'b T) -> BoxFuture<'b, Result<(), sqlx::Error>> {
1139 Box::pin(async move {
1140 // Serialize model to a HashMap of column_name -> string_value
1141 let data_map = model.to_map();
1142
1143 // Early return if no data to insert
1144 if data_map.is_empty() {
1145 return Ok(());
1146 }
1147
1148 let table_name = self.table_name.to_snake_case();
1149 let columns_info = T::columns();
1150
1151 let mut target_columns = Vec::new();
1152 let mut bindings: Vec<(String, &str)> = Vec::new();
1153
1154 // Build column list and collect values with their SQL types
1155 for (col_name, value) in data_map {
1156 // Strip the "r#" prefix if present (for Rust keywords used as field names)
1157 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
1158 target_columns.push(format!("\"{}\"", col_name_clean));
1159
1160 // Find the SQL type for this column
1161 let sql_type = columns_info.iter().find(|c| c.name == col_name).map(|c| c.sql_type).unwrap_or("TEXT");
1162
1163 bindings.push((value, sql_type));
1164 }
1165
1166 // Generate placeholders with proper type casting for PostgreSQL
1167 let placeholders: Vec<String> = bindings
1168 .iter()
1169 .enumerate()
1170 .map(|(i, (_, sql_type))| match self.driver {
1171 Drivers::Postgres => {
1172 let idx = i + 1;
1173 // PostgreSQL requires explicit type casting for some types
1174 if temporal::is_temporal_type(sql_type) {
1175 // Use temporal module for type casting
1176 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
1177 } else {
1178 match *sql_type {
1179 "UUID" => format!("${}::UUID", idx),
1180 "JSONB" | "jsonb" => format!("${}::JSONB", idx),
1181 _ => format!("${}", idx),
1182 }
1183 }
1184 }
1185 // MySQL and SQLite use simple ? placeholders
1186 _ => "?".to_string(),
1187 })
1188 .collect();
1189
1190 // Construct the INSERT query
1191 let query_str = format!(
1192 "INSERT INTO \"{}\" ({}) VALUES ({})",
1193 table_name,
1194 target_columns.join(", "),
1195 placeholders.join(", ")
1196 );
1197
1198 // If debug mode is enabled, log the generated SQL query before execution
1199 if self.debug_mode {
1200 log::debug!("SQL: {}", query_str);
1201 }
1202
1203 let mut query = sqlx::query::<sqlx::Any>(&query_str);
1204
1205 // Bind values using the optimized value_binding module
1206 // This provides type-safe binding with driver-specific optimizations
1207 for (val_str, sql_type) in bindings {
1208 // Create temporary AnyArguments to collect the bound value
1209 let mut temp_args = AnyArguments::default();
1210
1211 // Use the ValueBinder trait for type-safe binding
1212 if temp_args.bind_value(&val_str, sql_type, &self.driver).is_ok() {
1213 // For now, we need to convert back to individual bindings
1214 // This is a workaround until we can better integrate AnyArguments
1215 match sql_type {
1216 "INTEGER" | "INT" | "SERIAL" | "serial" | "int4" => {
1217 if let Ok(val) = val_str.parse::<i32>() {
1218 query = query.bind(val);
1219 } else {
1220 query = query.bind(val_str);
1221 }
1222 }
1223 "BIGINT" | "INT8" | "int8" | "BIGSERIAL" => {
1224 if let Ok(val) = val_str.parse::<i64>() {
1225 query = query.bind(val);
1226 } else {
1227 query = query.bind(val_str);
1228 }
1229 }
1230 "BOOLEAN" | "BOOL" | "bool" => {
1231 if let Ok(val) = val_str.parse::<bool>() {
1232 query = query.bind(val);
1233 } else {
1234 query = query.bind(val_str);
1235 }
1236 }
1237 "DOUBLE PRECISION" | "FLOAT" | "float8" | "REAL" | "NUMERIC" | "DECIMAL" => {
1238 if let Ok(val) = val_str.parse::<f64>() {
1239 query = query.bind(val);
1240 } else {
1241 query = query.bind(val_str);
1242 }
1243 }
1244 "UUID" => {
1245 if let Ok(val) = val_str.parse::<Uuid>() {
1246 query = query.bind(val.hyphenated().to_string());
1247 } else {
1248 query = query.bind(val_str);
1249 }
1250 }
1251 "TIMESTAMPTZ" | "DateTime" => {
1252 if let Ok(val) = temporal::parse_datetime_utc(&val_str) {
1253 let formatted = temporal::format_datetime_for_driver(&val, &self.driver);
1254 query = query.bind(formatted);
1255 } else {
1256 query = query.bind(val_str);
1257 }
1258 }
1259 "TIMESTAMP" | "NaiveDateTime" => {
1260 if let Ok(val) = temporal::parse_naive_datetime(&val_str) {
1261 let formatted = temporal::format_naive_datetime_for_driver(&val, &self.driver);
1262 query = query.bind(formatted);
1263 } else {
1264 query = query.bind(val_str);
1265 }
1266 }
1267 "DATE" | "NaiveDate" => {
1268 if let Ok(val) = temporal::parse_naive_date(&val_str) {
1269 let formatted = val.format("%Y-%m-%d").to_string();
1270 query = query.bind(formatted);
1271 } else {
1272 query = query.bind(val_str);
1273 }
1274 }
1275 "TIME" | "NaiveTime" => {
1276 if let Ok(val) = temporal::parse_naive_time(&val_str) {
1277 let formatted = val.format("%H:%M:%S%.6f").to_string();
1278 query = query.bind(formatted);
1279 } else {
1280 query = query.bind(val_str);
1281 }
1282 }
1283 _ => {
1284 query = query.bind(val_str);
1285 }
1286 }
1287 } else {
1288 // Fallback: bind as string if type conversion fails
1289 query = query.bind(val_str);
1290 }
1291 }
1292
1293 // Execute the INSERT query
1294 query.execute(self.tx.executor()).await?;
1295 Ok(())
1296 })
1297 }
1298
1299 // ========================================================================
1300 // Query Execution Methods
1301 // ========================================================================
1302
1303 /// Returns the generated SQL string for debugging purposes.
1304 ///
1305 /// This method constructs the SQL query string without executing it.
1306 /// Useful for debugging and logging query construction. Note that this
1307 /// shows placeholders (?, $1, etc.) rather than actual bound values.
1308 ///
1309 /// # Returns
1310 ///
1311 /// A `String` containing the SQL query that would be executed
1312 ///
1313 /// # Example
1314 ///
1315 /// ```rust,ignore
1316 /// let query = db.model::<User>()
1317 /// .filter("age", ">=", 18)
1318 /// .order("created_at DESC")
1319 /// .limit(10);
1320 ///
1321 /// println!("SQL: {}", query.to_sql());
1322 /// // Output: SELECT * FROM "user" WHERE 1=1 AND "age" >= $1 ORDER BY created_at DESC
1323 /// ```
1324 pub fn to_sql(&self) -> String {
1325 let mut query = String::from("SELECT ");
1326
1327 if self.is_distinct {
1328 query.push_str("DISTINCT ");
1329 }
1330
1331 // Handle column selection
1332 if self.select_columns.is_empty() {
1333 query.push('*');
1334 } else {
1335 query.push_str(&self.select_columns.join(", "));
1336 }
1337
1338 query.push_str(" FROM \"");
1339 query.push_str(&self.table_name.to_snake_case());
1340 query.push_str("\" ");
1341
1342 if !self.joins_clauses.is_empty() {
1343 query.push_str(&self.joins_clauses.join(" "));
1344 }
1345
1346 query.push_str(" WHERE 1=1");
1347
1348 // Apply WHERE clauses with dummy arguments
1349 let mut dummy_args = AnyArguments::default();
1350 let mut dummy_counter = 1;
1351
1352 for clause in &self.where_clauses {
1353 clause(&mut query, &mut dummy_args, &self.driver, &mut dummy_counter);
1354 }
1355
1356 // Apply GROUP BY
1357 if !self.group_by_clauses.is_empty() {
1358 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1359 }
1360
1361 // Apply HAVING
1362 if !self.having_clauses.is_empty() {
1363 query.push_str(" HAVING 1=1");
1364 for clause in &self.having_clauses {
1365 clause(&mut query, &mut dummy_args, &self.driver, &mut dummy_counter);
1366 }
1367 }
1368
1369 // Apply ORDER BY if present
1370 if !self.order_clauses.is_empty() {
1371 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
1372 }
1373
1374 query
1375 }
1376
1377 /// Generates the list of column selection SQL arguments.
1378 ///
1379 /// This helper function constructs the column list for the SELECT statement.
1380 /// It handles:
1381 /// 1. Mapping specific columns if `select_columns` is set.
1382 /// 2. Defaulting to all columns from the struct `R` if no columns are specified.
1383 /// 3. applying `to_json(...)` casting for temporal types when using `AnyImpl` structs,
1384 /// ensuring compatibility with the `FromAnyRow` deserialization logic.
1385 fn select_args_sql<R: AnyImpl>(&self) -> Vec<String> {
1386 let struct_cols = R::columns();
1387
1388 if !struct_cols.is_empty() {
1389 if !self.select_columns.is_empty() {
1390 let mut args = Vec::new();
1391 for col_info in struct_cols {
1392 let col_snake = col_info.column.to_snake_case();
1393 let sql_type = col_info.sql_type;
1394 if self.select_columns.contains(&col_snake) {
1395 if is_temporal_type(sql_type) && matches!(self.driver, Drivers::Postgres) {
1396 if !self.joins_clauses.is_empty() {
1397 args.push(format!(
1398 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"",
1399 self.table_name.to_snake_case(),
1400 col_snake,
1401 col_snake
1402 ));
1403 } else {
1404 args.push(format!("to_json(\"{}\") #>> '{{}}' AS \"{}\"", col_snake, col_snake));
1405 }
1406 } else if !self.joins_clauses.is_empty() {
1407 args.push(format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), col_snake));
1408 } else {
1409 args.push(format!("\"{}\"", col_snake));
1410 }
1411 }
1412 }
1413 return args;
1414 } else {
1415 // For omitted columns, return 'omited' as placeholder value
1416 return struct_cols
1417 .iter()
1418 .map(|c| {
1419 let col_snake = c.column.to_snake_case();
1420 let is_omitted = self.omit_columns.contains(&col_snake);
1421 let table_name =
1422 if !c.table.is_empty() { c.table.to_snake_case() } else { self.table_name.to_snake_case() };
1423
1424 if is_omitted {
1425 // Return type-appropriate placeholder based on sql_type
1426 let placeholder = match c.sql_type {
1427 // String types
1428 "TEXT" | "VARCHAR" | "CHAR" | "STRING" => "'omited'",
1429 // Date/Time types - use epoch timestamp
1430 "TIMESTAMP" | "TIMESTAMPTZ" | "TIMESTAMP WITH TIME ZONE" => "'1970-01-01T00:00:00Z'",
1431 "DATE" => "'1970-01-01'",
1432 "TIME" => "'00:00:00'",
1433 // Numeric types
1434 "INTEGER" | "INT" | "SMALLINT" | "BIGINT" | "INT4" | "INT8" => "0",
1435 "REAL" | "FLOAT" | "DOUBLE" | "FLOAT4" | "FLOAT8" | "DECIMAL" | "NUMERIC" => "0.0",
1436 // Boolean
1437 "BOOLEAN" | "BOOL" => "false",
1438 // UUID - nil UUID
1439 "UUID" => "'00000000-0000-0000-0000-000000000000'",
1440 // JSON types
1441 "JSON" | "JSONB" => "'{}'",
1442 // Default fallback for unknown types
1443 _ => "'omited'",
1444 };
1445 format!("{} AS \"{}__{}\"", placeholder, table_name, col_snake)
1446 } else if is_temporal_type(c.sql_type) && matches!(self.driver, Drivers::Postgres) {
1447 format!(
1448 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}__{}\"",
1449 table_name, col_snake, table_name, col_snake
1450 )
1451 } else {
1452 format!("\"{}\".\"{}\" AS \"{}__{}\"", table_name, col_snake, table_name, col_snake)
1453 }
1454 })
1455 .collect();
1456 }
1457 }
1458
1459 if !self.select_columns.is_empty() {
1460 return self
1461 .select_columns
1462 .iter()
1463 .map(|c| if c.contains('(') { c.clone() } else { format!("\"{}\"", c) })
1464 .collect();
1465 }
1466
1467 vec!["*".to_string()]
1468 }
1469
1470 /// Executes the query and returns a list of results.
1471 ///
1472 /// This method builds and executes a SELECT query with all accumulated filters,
1473 /// ordering, and pagination settings. It returns all matching rows as a vector.
1474 ///
1475 /// # Type Parameters
1476 ///
1477 /// * `R` - The result type. Must implement `FromRow` for deserialization from database rows.
1478 ///
1479 /// # Returns
1480 ///
1481 /// * `Ok(Vec<R>)` - Vector of results (empty if no matches)
1482 /// * `Err(sqlx::Error)` - Database error during query execution
1483 ///
1484 /// # Example
1485 ///
1486 /// ```rust,ignore
1487 /// // Get all adult users, ordered by age, limited to 10
1488 /// let users: Vec<User> = db.model::<User>()
1489 /// .filter("age", ">=", 18)
1490 /// .order("age DESC")
1491 /// .limit(10)
1492 /// .scan()
1493 /// .await?;
1494 ///
1495 /// // Get users by UUID
1496 /// let user_id = Uuid::parse_str("550e8400-e29b-41d4-a716-446655440000")?;
1497 /// let users: Vec<User> = db.model::<User>()
1498 /// .filter("id", "=", user_id)
1499 /// .scan()
1500 /// .await?;
1501 ///
1502 /// // Empty result is Ok
1503 /// let results: Vec<User> = db.model::<User>()
1504 /// .filter("age", ">", 200)
1505 /// .scan()
1506 /// .await?; // Returns empty Vec, not an error
1507 /// ```
1508 pub async fn scan<R>(mut self) -> Result<Vec<R>, sqlx::Error>
1509 where
1510 R: FromAnyRow + AnyImpl + Send + Unpin,
1511 {
1512 // Apply default soft delete filter if not disabled
1513 if !self.with_deleted {
1514 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
1515 self = self.is_null(soft_delete_col);
1516 }
1517 }
1518
1519 // Build SELECT clause
1520 let mut query = String::from("SELECT ");
1521
1522 if self.is_distinct {
1523 query.push_str("DISTINCT ");
1524 }
1525
1526 query.push_str(&self.select_args_sql::<R>().join(", "));
1527
1528 // Build FROM clause
1529 query.push_str(" FROM \"");
1530 query.push_str(&self.table_name.to_snake_case());
1531 query.push_str("\" ");
1532 if !self.joins_clauses.is_empty() {
1533 query.push_str(&self.joins_clauses.join(" "));
1534 }
1535
1536 query.push_str(" WHERE 1=1");
1537
1538 // Apply WHERE clauses
1539 let mut args = AnyArguments::default();
1540 let mut arg_counter = 1;
1541
1542 for clause in &self.where_clauses {
1543 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1544 }
1545
1546 // Apply GROUP BY
1547 if !self.group_by_clauses.is_empty() {
1548 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1549 }
1550
1551 // Apply HAVING
1552 if !self.having_clauses.is_empty() {
1553 query.push_str(" HAVING 1=1");
1554 for clause in &self.having_clauses {
1555 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1556 }
1557 }
1558
1559 // Apply ORDER BY clauses
1560 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
1561 if !self.order_clauses.is_empty() {
1562 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
1563 }
1564
1565 // Apply LIMIT clause
1566 if let Some(limit) = self.limit {
1567 query.push_str(" LIMIT ");
1568 match self.driver {
1569 Drivers::Postgres => {
1570 query.push_str(&format!("${}", arg_counter));
1571 arg_counter += 1;
1572 }
1573 _ => query.push('?'),
1574 }
1575 let _ = args.add(limit as i64);
1576 }
1577
1578 // Apply OFFSET clause
1579 if let Some(offset) = self.offset {
1580 query.push_str(" OFFSET ");
1581 match self.driver {
1582 Drivers::Postgres => {
1583 query.push_str(&format!("${}", arg_counter));
1584 // arg_counter += 1; // Not needed as this is the last clause
1585 }
1586 _ => query.push('?'),
1587 }
1588 let _ = args.add(offset as i64);
1589 }
1590
1591 // Print SQL query to logs if debug mode is active
1592 if self.debug_mode {
1593 log::debug!("SQL: {}", query);
1594 }
1595
1596 // Execute query and fetch all results
1597 let rows = sqlx::query_with(&query, args).fetch_all(self.tx.executor()).await?;
1598
1599 rows.iter().map(|row| R::from_any_row(row)).collect()
1600 }
1601
1602 /// Executes the query and maps the result to a custom DTO.
1603 ///
1604 /// Ideal for JOINs and projections where the return type is not a full Model.
1605 /// Unlike `scan`, this method does not require `R` to implement `AnyImpl`,
1606 /// making it more flexible for custom result structures.
1607 ///
1608 /// # Type Parameters
1609 ///
1610 /// * `R` - The target result type. Must implement `FromAnyRow`.
1611 ///
1612 /// # Returns
1613 ///
1614 /// * `Ok(Vec<R>)` - Vector of results mapped to type `R`.
1615 /// * `Err(sqlx::Error)` - Database error.
1616 ///
1617 /// # Example
1618 ///
1619 /// ```rust,ignore
1620 /// #[derive(FromAnyRow)]
1621 /// struct UserRoleDTO {
1622 /// username: String,
1623 /// role_name: String,
1624 /// }
1625 ///
1626 /// let results: Vec<UserRoleDTO> = db.model::<User>()
1627 /// .inner_join("roles", "users.role_id = roles.id")
1628 /// .select("users.username, roles.name as role_name")
1629 /// .scan_as::<UserRoleDTO>()
1630 /// .await?;
1631 /// ```
1632 pub async fn scan_as<R>(mut self) -> Result<Vec<R>, sqlx::Error>
1633 where
1634 R: FromAnyRow + Send + Unpin,
1635 {
1636 // Apply default soft delete filter if not disabled
1637 if !self.with_deleted {
1638 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
1639 self = self.is_null(soft_delete_col);
1640 }
1641 }
1642
1643 let mut query = String::from("SELECT ");
1644 if self.is_distinct {
1645 query.push_str("DISTINCT ");
1646 }
1647
1648 if self.select_columns.is_empty() {
1649 query.push('*');
1650 } else {
1651 let mut select_cols = Vec::with_capacity(self.select_columns.capacity());
1652 for col in &self.select_columns {
1653 if !self.joins_clauses.is_empty() && col.contains('.') {
1654 if let Some((table, column)) = col.split_once('.') {
1655 if column == "*" {
1656 // Trata corretamente o "table.*", sem colocar aspas no asterisco
1657 select_cols.push(format!("\"{}\".*", table));
1658 } else {
1659 select_cols.push(format!("\"{}\".\"{}\"", table, column));
1660 }
1661 }
1662 } else {
1663 select_cols.push(col.clone());
1664 }
1665 }
1666 query.push_str(&select_cols.join(", "));
1667 }
1668
1669 // Build FROM clause
1670 query.push_str(" FROM \"");
1671 query.push_str(&self.table_name.to_snake_case());
1672 query.push_str("\" ");
1673
1674 if !self.joins_clauses.is_empty() {
1675 query.push_str(&self.joins_clauses.join(" "));
1676 }
1677
1678 query.push_str(" WHERE 1=1");
1679
1680 let mut args = sqlx::any::AnyArguments::default();
1681 let mut arg_counter = 1;
1682
1683 for clause in &self.where_clauses {
1684 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1685 }
1686
1687 if !self.group_by_clauses.is_empty() {
1688 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1689 }
1690
1691 if !self.having_clauses.is_empty() {
1692 query.push_str(" HAVING 1=1");
1693 for clause in &self.having_clauses {
1694 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1695 }
1696 }
1697
1698 if !self.order_clauses.is_empty() {
1699 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
1700 }
1701
1702 if let Some(limit) = self.limit {
1703 query.push_str(" LIMIT ");
1704 match self.driver {
1705 Drivers::Postgres => {
1706 query.push_str(&format!("${}", arg_counter));
1707 arg_counter += 1;
1708 }
1709 _ => query.push('?'),
1710 }
1711 let _ = args.add(limit as i64);
1712 }
1713
1714 if let Some(offset) = self.offset {
1715 query.push_str(" OFFSET ");
1716 match self.driver {
1717 Drivers::Postgres => {
1718 query.push_str(&format!("${}", arg_counter));
1719 }
1720 _ => query.push('?'),
1721 }
1722 let _ = args.add(offset as i64);
1723 }
1724
1725 if self.debug_mode {
1726 log::debug!("SQL: {}", query);
1727 }
1728
1729 let rows = sqlx::query_with(&query, args).fetch_all(self.tx.executor()).await?;
1730 rows.iter().map(|row| R::from_any_row(row)).collect()
1731 }
1732
1733 /// Executes the query and returns only the first result.
1734 ///
1735 /// This method automatically adds `LIMIT 1` and orders by the Primary Key
1736 /// (if available) to ensure consistent results. It's optimized for fetching
1737 /// a single row and will return an error if no rows match.
1738 ///
1739 /// # Type Parameters
1740 ///
1741 /// * `R` - The result type. Must implement `FromRow` for deserialization.
1742 ///
1743 /// # Returns
1744 ///
1745 /// * `Ok(R)` - The first matching row
1746 /// * `Err(sqlx::Error)` - No rows found or database error
1747 ///
1748 /// # Error Handling
1749 ///
1750 /// Returns `sqlx::Error::RowNotFound` if no rows match the query.
1751 /// Use `scan()` instead if you want an empty Vec rather than an error.
1752 ///
1753 /// # Example
1754 ///
1755 /// ```rust,ignore
1756 /// // Get a specific user by ID
1757 /// let user: User = db.model::<User>()
1758 /// .filter("id", "=", 1)
1759 /// .first()
1760 /// .await?;
1761 ///
1762 /// // Get user by UUID
1763 /// let user_id = Uuid::new_v4();
1764 /// let user: User = db.model::<User>()
1765 /// .filter("id", "=", user_id)
1766 /// .first()
1767 /// .await?;
1768 ///
1769 /// // Get the oldest user
1770 /// let oldest: User = db.model::<User>()
1771 /// .order("age DESC")
1772 /// .first()
1773 /// .await?;
1774 ///
1775 /// // Error handling
1776 /// match db.model::<User>().filter("id", "=", 999).first().await {
1777 /// Ok(user) => println!("Found: {:?}", user),
1778 /// Err(sqlx::Error::RowNotFound) => println!("User not found"),
1779 /// Err(e) => println!("Database error: {}", e),
1780 /// }
1781 /// ```
1782 pub async fn first<R>(mut self) -> Result<R, sqlx::Error>
1783 where
1784 R: FromAnyRow + AnyImpl + Send + Unpin,
1785 {
1786 // Apply default soft delete filter if not disabled
1787 if !self.with_deleted {
1788 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
1789 self = self.is_null(soft_delete_col);
1790 }
1791 }
1792
1793 // Build SELECT clause
1794 let mut query = String::from("SELECT ");
1795
1796 if self.is_distinct {
1797 query.push_str("DISTINCT ");
1798 }
1799
1800 query.push_str(&self.select_args_sql::<R>().join(", "));
1801
1802 // Build FROM clause
1803 query.push_str(" FROM \"");
1804 query.push_str(&self.table_name.to_snake_case());
1805 query.push_str("\" ");
1806 if !self.joins_clauses.is_empty() {
1807 query.push_str(&self.joins_clauses.join(" "));
1808 }
1809
1810 query.push_str(" WHERE 1=1");
1811
1812 // Apply WHERE clauses
1813 let mut args = AnyArguments::default();
1814 let mut arg_counter = 1;
1815
1816 for clause in &self.where_clauses {
1817 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1818 }
1819
1820 // Apply GROUP BY
1821 if !self.group_by_clauses.is_empty() {
1822 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1823 }
1824
1825 // Apply HAVING
1826 if !self.having_clauses.is_empty() {
1827 query.push_str(" HAVING 1=1");
1828 for clause in &self.having_clauses {
1829 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1830 }
1831 }
1832
1833 // Find primary key column for consistent ordering
1834 let pk_column = T::columns()
1835 .iter()
1836 .find(|c| c.is_primary_key)
1837 .map(|c| c.name.strip_prefix("r#").unwrap_or(c.name).to_snake_case());
1838
1839 // Apply ORDER BY clauses
1840 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
1841 if !self.order_clauses.is_empty() {
1842 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
1843 } else if let Some(pk) = pk_column {
1844 // Fallback to PK ordering if no custom order is specified (ensures deterministic results)
1845 query.push_str(" ORDER BY ");
1846 query.push_str(&format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), pk));
1847 query.push_str(" ASC");
1848 }
1849
1850 // Always add LIMIT 1 for first() queries
1851 query.push_str(" LIMIT 1");
1852
1853 // Print SQL query to logs if debug mode is active
1854 log::debug!("SQL: {}", query);
1855
1856 // Execute query and fetch exactly one result
1857 let row = sqlx::query_with(&query, args).fetch_one(self.tx.executor()).await?;
1858 R::from_any_row(&row)
1859 }
1860
1861 /// Executes the query and returns a single scalar value.
1862 ///
1863 /// This method is useful for fetching single values like counts, max/min values,
1864 /// or specific columns without mapping to a struct or tuple.
1865 ///
1866 /// # Type Parameters
1867 ///
1868 /// * `O` - The output type. Must implement `Decode` and `Type`.
1869 ///
1870 /// # Example
1871 ///
1872 /// ```rust,ignore
1873 /// // Get count of users
1874 /// let count: i64 = db.model::<User>()
1875 /// .select("count(*)")
1876 /// .scalar()
1877 /// .await?;
1878 ///
1879 /// // Get specific field
1880 /// let username: String = db.model::<User>()
1881 /// .filter("id", "=", 1)
1882 /// .select("username")
1883 /// .scalar()
1884 /// .await?;
1885 /// ```
1886 pub async fn scalar<O>(mut self) -> Result<O, sqlx::Error>
1887 where
1888 O: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
1889 {
1890 // Apply default soft delete filter if not disabled
1891 if !self.with_deleted {
1892 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
1893 self = self.is_null(soft_delete_col);
1894 }
1895 }
1896
1897 // Build SELECT clause
1898 let mut query = String::from("SELECT ");
1899
1900 if self.is_distinct {
1901 query.push_str("DISTINCT ");
1902 }
1903
1904 if self.select_columns.is_empty() {
1905 return Err(sqlx::Error::ColumnNotFound("is not possible get data without column".to_string()));
1906 }
1907
1908 let mut select_cols = Vec::with_capacity(self.select_columns.capacity());
1909 for col in self.select_columns {
1910 if !self.joins_clauses.is_empty() {
1911 if let Some((table, column)) = col.split_once(".") {
1912 select_cols.push(format!("\"{}\".\"{}\"", table, column));
1913 } else {
1914 select_cols.push(format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), col));
1915 }
1916 continue;
1917 }
1918 select_cols.push(col);
1919 }
1920
1921 query.push_str(&select_cols.join(", "));
1922
1923 // Build FROM clause
1924 query.push_str(" FROM \"");
1925 query.push_str(&self.table_name.to_snake_case());
1926 query.push_str("\" ");
1927
1928 if !self.joins_clauses.is_empty() {
1929 query.push_str(&self.joins_clauses.join(" "));
1930 }
1931
1932 query.push_str(" WHERE 1=1");
1933
1934 // Apply WHERE clauses
1935 let mut args = AnyArguments::default();
1936 let mut arg_counter = 1;
1937
1938 for clause in &self.where_clauses {
1939 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1940 }
1941
1942 // Apply GROUP BY
1943 if !self.group_by_clauses.is_empty() {
1944 query.push_str(&format!(" GROUP BY {}", self.group_by_clauses.join(", ")));
1945 }
1946
1947 // Apply HAVING
1948 if !self.having_clauses.is_empty() {
1949 query.push_str(" HAVING 1=1");
1950 for clause in &self.having_clauses {
1951 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
1952 }
1953 }
1954
1955 // Apply ORDER BY
1956 if !self.order_clauses.is_empty() {
1957 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
1958 }
1959
1960 // Always add LIMIT 1 for scalar queries
1961 query.push_str(" LIMIT 1");
1962
1963 // Print SQL query to logs if debug mode is active
1964 if self.debug_mode {
1965 log::debug!("SQL: {}", query);
1966 }
1967
1968 // Execute query and fetch one row
1969 let row = sqlx::query_with::<_, _>(&query, args).fetch_one(self.tx.executor()).await?;
1970
1971 // Get the first column
1972 row.try_get::<O, _>(0)
1973 }
1974
1975 /// Updates a single column in the database.
1976 ///
1977 /// # Arguments
1978 ///
1979 /// * `col` - The column name to update
1980 /// * `value` - The new value
1981 ///
1982 /// # Returns
1983 ///
1984 /// * `Ok(u64)` - The number of rows affected
1985 pub fn update<'b, V>(&'b mut self, col: &str, value: V) -> BoxFuture<'b, Result<u64, sqlx::Error>>
1986 where
1987 V: ToString + Send + Sync,
1988 {
1989 let mut map = std::collections::HashMap::new();
1990 map.insert(col.to_string(), value.to_string());
1991 self.execute_update(map)
1992 }
1993
1994 /// Updates all columns based on the model instance.
1995 ///
1996 /// This method updates all active columns of the table with values from the provided model.
1997 ///
1998 /// # Arguments
1999 ///
2000 /// * `model` - The model instance containing new values
2001 ///
2002 /// # Returns
2003 ///
2004 /// * `Ok(u64)` - The number of rows affected
2005 pub fn updates<'b>(&'b mut self, model: &T) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
2006 self.execute_update(model.to_map())
2007 }
2008
2009 /// Updates columns based on a partial model (struct implementing AnyImpl).
2010 ///
2011 /// This allows updating a subset of columns using a custom struct.
2012 /// The struct must implement `AnyImpl` (usually via `#[derive(FromAnyRow)]`).
2013 ///
2014 /// # Arguments
2015 ///
2016 /// * `partial` - The partial model containing new values
2017 ///
2018 /// # Returns
2019 ///
2020 /// * `Ok(u64)` - The number of rows affected
2021 pub fn update_partial<'b, P: AnyImpl>(&'b mut self, partial: &P) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
2022 self.execute_update(partial.to_map())
2023 }
2024
2025 /// Internal helper to execute an UPDATE query from a map of values.
2026 fn execute_update<'b>(
2027 &'b mut self,
2028 data_map: std::collections::HashMap<String, String>,
2029 ) -> BoxFuture<'b, Result<u64, sqlx::Error>> {
2030 // Apply default soft delete filter if not disabled
2031 if !self.with_deleted {
2032 if let Some(soft_delete_col) = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name) {
2033 let col_owned = soft_delete_col.to_string();
2034 let clause: FilterFn = Box::new(move |query, _args, _driver, _arg_counter| {
2035 query.push_str(" AND ");
2036 query.push_str(&format!("\"{}\"", col_owned));
2037 query.push_str(" IS NULL");
2038 });
2039 self.where_clauses.push(clause);
2040 }
2041 }
2042
2043 Box::pin(async move {
2044 let table_name = self.table_name.to_snake_case();
2045 let mut query = format!("UPDATE \"{}\" SET ", table_name);
2046
2047 let mut bindings: Vec<(String, &str)> = Vec::new();
2048 let mut set_clauses = Vec::new();
2049
2050 // Maintain argument counter for PostgreSQL ($1, $2, ...)
2051 let mut arg_counter = 1;
2052
2053 // Build SET clause
2054 for (col_name, value) in data_map {
2055 // Strip the "r#" prefix if present
2056 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
2057
2058 // Find the SQL type for this column from the Model metadata
2059 let sql_type = self
2060 .columns_info
2061 .iter()
2062 .find(|c| c.name == col_name || c.name == col_name_clean)
2063 .map(|c| c.sql_type)
2064 .unwrap_or("TEXT");
2065
2066 // Generate placeholder
2067 let placeholder = match self.driver {
2068 Drivers::Postgres => {
2069 let idx = arg_counter;
2070 arg_counter += 1;
2071
2072 if temporal::is_temporal_type(sql_type) {
2073 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
2074 } else {
2075 match sql_type {
2076 "UUID" => format!("${}::UUID", idx),
2077 "JSONB" | "jsonb" => format!("${}::JSONB", idx),
2078 _ => format!("${}", idx),
2079 }
2080 }
2081 }
2082 _ => "?".to_string(),
2083 };
2084
2085 set_clauses.push(format!("\"{}\" = {}", col_name_clean, placeholder));
2086 bindings.push((value, sql_type));
2087 }
2088
2089 // If no fields to update, return 0
2090 if set_clauses.is_empty() {
2091 return Ok(0);
2092 }
2093
2094 query.push_str(&set_clauses.join(", "));
2095
2096 // Build WHERE clause
2097 query.push_str(" WHERE 1=1");
2098
2099 let mut args = AnyArguments::default();
2100
2101 // Bind SET values
2102 for (val_str, sql_type) in bindings {
2103 if args.bind_value(&val_str, sql_type, &self.driver).is_err() {
2104 let _ = args.add(val_str);
2105 }
2106 }
2107
2108 // Apply WHERE clauses (appending to args and query)
2109 for clause in &self.where_clauses {
2110 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2111 }
2112
2113 // Print SQL query to logs if debug mode is active
2114 if self.debug_mode {
2115 log::debug!("SQL: {}", query);
2116 }
2117
2118 // Execute the UPDATE query
2119 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2120
2121 Ok(result.rows_affected())
2122 })
2123 }
2124
2125 /// Executes a DELETE query based on the current filters.
2126 ///
2127 /// If the model has a `#[orm(soft_delete)]` column, this method performs
2128 /// an UPDATE setting the soft delete column to the current timestamp instead
2129 /// of physically deleting the record.
2130 ///
2131 /// For permanent deletion, use `hard_delete()`.
2132 ///
2133 /// # Returns
2134 ///
2135 /// * `Ok(u64)` - The number of rows deleted (or soft-deleted)
2136 /// * `Err(sqlx::Error)` - Database error
2137 pub async fn delete(mut self) -> Result<u64, sqlx::Error> {
2138 // Check for soft delete column
2139 let soft_delete_col = self.columns_info.iter().find(|c| c.soft_delete).map(|c| c.name);
2140
2141 if let Some(col) = soft_delete_col {
2142 // Soft Delete: Update the column to current timestamp
2143 let table_name = self.table_name.to_snake_case();
2144 let mut query = format!("UPDATE \"{}\" SET \"{}\" = ", table_name, col);
2145
2146 match self.driver {
2147 Drivers::Postgres => query.push_str("NOW()"),
2148 Drivers::SQLite => query.push_str("strftime('%Y-%m-%dT%H:%M:%SZ', 'now')"),
2149 Drivers::MySQL => query.push_str("NOW()"),
2150 }
2151
2152 query.push_str(" WHERE 1=1");
2153
2154 let mut args = AnyArguments::default();
2155 let mut arg_counter = 1;
2156
2157 // Apply filters
2158 for clause in &self.where_clauses {
2159 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2160 }
2161
2162 // Print SQL query to logs if debug mode is active
2163 if self.debug_mode {
2164 log::debug!("SQL: {}", query);
2165 }
2166
2167 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2168 Ok(result.rows_affected())
2169 } else {
2170 // Standard Delete (no soft delete column)
2171 let mut query = String::from("DELETE FROM \"");
2172 query.push_str(&self.table_name.to_snake_case());
2173 query.push_str("\" WHERE 1=1");
2174
2175 let mut args = AnyArguments::default();
2176 let mut arg_counter = 1;
2177
2178 for clause in &self.where_clauses {
2179 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2180 }
2181
2182 // Print SQL query to logs if debug mode is active
2183 if self.debug_mode {
2184 log::debug!("SQL: {}", query);
2185 }
2186
2187 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2188 Ok(result.rows_affected())
2189 }
2190 }
2191
2192 /// Permanently removes records from the database.
2193 ///
2194 /// This method performs a physical DELETE, bypassing any soft delete logic.
2195 /// Use this when you need to permanently remove records.
2196 ///
2197 /// # Returns
2198 ///
2199 /// * `Ok(u64)` - The number of rows deleted
2200 /// * `Err(sqlx::Error)` - Database error
2201 ///
2202 /// # Example
2203 ///
2204 /// ```rust,ignore
2205 /// // Permanently delete soft-deleted records older than 30 days
2206 /// db.model::<User>()
2207 /// .with_deleted()
2208 /// .filter("deleted_at", "<", thirty_days_ago)
2209 /// .hard_delete()
2210 /// .await?;
2211 /// ```
2212 pub async fn hard_delete(mut self) -> Result<u64, sqlx::Error> {
2213 let mut query = String::from("DELETE FROM \"");
2214 query.push_str(&self.table_name.to_snake_case());
2215 query.push_str("\" WHERE 1=1");
2216
2217 let mut args = AnyArguments::default();
2218 let mut arg_counter = 1;
2219
2220 for clause in &self.where_clauses {
2221 clause(&mut query, &mut args, &self.driver, &mut arg_counter);
2222 }
2223
2224 // Print SQL query to logs if debug mode is active
2225 if self.debug_mode {
2226 log::debug!("SQL: {}", query);
2227 }
2228
2229 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
2230 Ok(result.rows_affected())
2231 }
2232}