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