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