rok_orm_core/
query.rs

1//! [`QueryBuilder`] — fluent SQL builder.
2//!
3//! Use [`QueryBuilder::to_sql`] for PostgreSQL (`$N` placeholders) and
4//! [`QueryBuilder::to_sql_with_dialect`] when targeting SQLite (`?` placeholders).
5
6use std::marker::PhantomData;
7
8use crate::condition::{Condition, JoinOp, OrderDir, SqlValue};
9
10// ── Dialect ───────────────────────────────────────────────────────────────────
11
12/// SQL placeholder dialect.
13///
14/// - [`Dialect::Postgres`] — numbered placeholders (`$1`, `$2`, …)
15/// - [`Dialect::Sqlite`]   — anonymous placeholders (`?`, `?`, …)
16#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
17pub enum Dialect {
18    #[default]
19    Postgres,
20    Sqlite,
21}
22
23// ── Join ─────────────────────────────────────────────────────────────────────
24
25/// A SQL JOIN clause.
26#[derive(Debug, Clone)]
27pub enum Join {
28    /// `INNER JOIN table ON condition`
29    Inner(String, String),
30    /// `LEFT JOIN table ON condition`
31    Left(String, String),
32    /// `RIGHT JOIN table ON condition`
33    Right(String, String),
34    /// Raw join fragment appended verbatim.
35    Raw(String),
36}
37
38/// A fluent builder that produces parameterized SQL statements.
39///
40/// Conditions added with `where_*` methods are joined with `AND`.
41/// Use `or_where_*` variants to join with `OR`.
42///
43/// # Example
44///
45/// ```rust
46/// use rok_orm_core::{QueryBuilder, SqlValue};
47///
48/// let (sql, params) = QueryBuilder::<()>::new("users")
49///     .where_eq("active", true)
50///     .or_where_eq("role", "admin")
51///     .order_by_desc("created_at")
52///     .limit(20)
53///     .offset(40)
54///     .to_sql();
55///
56/// assert!(sql.contains("WHERE"));
57/// assert!(sql.contains("ORDER BY created_at DESC"));
58/// assert!(sql.contains("LIMIT 20"));
59/// assert!(sql.contains("OFFSET 40"));
60/// assert_eq!(params.len(), 2);
61/// ```
62#[derive(Debug, Clone)]
63pub struct QueryBuilder<T> {
64    table: String,
65    select_cols: Option<Vec<String>>,
66    select_raw: Option<String>,
67    distinct: bool,
68    distinct_on_cols: Vec<String>,
69    joins: Vec<Join>,
70    conditions: Vec<(JoinOp, Condition)>,
71    group_by: Vec<String>,
72    having: Option<String>,
73    order: Vec<(String, OrderDir)>,
74    order_raw: Option<String>,
75    limit_val: Option<usize>,
76    offset_val: Option<usize>,
77    /// When `true` (default), the execution layer should route this query to a
78    /// read replica if one is configured.  Set to `false` via [`on_write_db`].
79    pub use_replica: bool,
80    /// Extra SELECT expressions appended after the main column list.
81    /// Used by `with_count`, `with_sum`, etc. in `ModelQuery`.
82    extra_select_exprs: Vec<String>,
83    _marker: PhantomData<T>,
84}
85
86impl<T> QueryBuilder<T> {
87    pub fn new(table: impl Into<String>) -> Self {
88        Self {
89            table: table.into(),
90            select_cols: None,
91            select_raw: None,
92            distinct: false,
93            distinct_on_cols: Vec::new(),
94            joins: Vec::new(),
95            conditions: Vec::new(),
96            group_by: Vec::new(),
97            having: None,
98            order: Vec::new(),
99            order_raw: None,
100            limit_val: None,
101            offset_val: None,
102            use_replica: true,
103            extra_select_exprs: Vec::new(),
104            _marker: PhantomData,
105        }
106    }
107
108    // ── column selection ──────────────────────────────────────────────────
109
110    pub fn select(mut self, cols: &[&str]) -> Self {
111        self.select_cols = Some(cols.iter().map(|s| s.to_string()).collect());
112        self
113    }
114
115    /// Emit `SELECT <raw_expr> FROM …` — overrides `select()`.
116    pub fn select_raw(mut self, expr: &str) -> Self {
117        self.select_raw = Some(expr.to_string());
118        self
119    }
120
121    /// Append an extra expression to the SELECT list without replacing existing columns.
122    /// Used internally by `with_count`, `with_sum`, etc.
123    pub fn add_select_expr(mut self, expr: impl Into<String>) -> Self {
124        self.extra_select_exprs.push(expr.into());
125        self
126    }
127
128    /// Emit `SELECT DISTINCT …`.
129    pub fn distinct(mut self) -> Self {
130        self.distinct = true;
131        self
132    }
133
134    /// Emit `SELECT DISTINCT ON (col, …) …` (PostgreSQL only).
135    pub fn distinct_on(mut self, cols: &[&str]) -> Self {
136        self.distinct_on_cols = cols.iter().map(|s| s.to_string()).collect();
137        self
138    }
139
140    // ── joins ─────────────────────────────────────────────────────────────
141
142    /// Add an `INNER JOIN table ON condition`.
143    ///
144    /// ```rust
145    /// use rok_orm_core::QueryBuilder;
146    ///
147    /// let (sql, _) = QueryBuilder::<()>::new("orders")
148    ///     .inner_join("users", "users.id = orders.user_id")
149    ///     .select(&["orders.id", "users.name"])
150    ///     .to_sql();
151    ///
152    /// assert!(sql.contains("INNER JOIN users ON users.id = orders.user_id"));
153    /// ```
154    pub fn inner_join(mut self, table: &str, on: &str) -> Self {
155        self.joins
156            .push(Join::Inner(table.to_string(), on.to_string()));
157        self
158    }
159
160    /// Add a `LEFT JOIN table ON condition`.
161    pub fn left_join(mut self, table: &str, on: &str) -> Self {
162        self.joins
163            .push(Join::Left(table.to_string(), on.to_string()));
164        self
165    }
166
167    /// Add a `RIGHT JOIN table ON condition`.
168    pub fn right_join(mut self, table: &str, on: &str) -> Self {
169        self.joins
170            .push(Join::Right(table.to_string(), on.to_string()));
171        self
172    }
173
174    /// Append a raw JOIN fragment verbatim (e.g. `"INNER JOIN s ON s.user_id = users.id AND s.active"`).
175    pub fn join_raw(mut self, raw: &str) -> Self {
176        self.joins.push(Join::Raw(raw.to_string()));
177        self
178    }
179
180    // ── GROUP BY / HAVING ─────────────────────────────────────────────────
181
182    /// Add a `GROUP BY` clause.
183    ///
184    /// ```rust
185    /// use rok_orm_core::QueryBuilder;
186    ///
187    /// let (sql, _) = QueryBuilder::<()>::new("orders")
188    ///     .select(&["user_id", "COUNT(*) as total"])
189    ///     .group_by(&["user_id"])
190    ///     .having("COUNT(*) > 5")
191    ///     .to_sql();
192    ///
193    /// assert!(sql.contains("GROUP BY user_id"));
194    /// assert!(sql.contains("HAVING COUNT(*) > 5"));
195    /// ```
196    pub fn group_by(mut self, cols: &[&str]) -> Self {
197        self.group_by = cols.iter().map(|s| s.to_string()).collect();
198        self
199    }
200
201    /// Add a `HAVING` clause (requires [`group_by`]).
202    pub fn having(mut self, expr: &str) -> Self {
203        self.having = Some(expr.to_string());
204        self
205    }
206
207    // ── AND conditions ────────────────────────────────────────────────────
208
209    pub fn where_eq(self, col: &str, val: impl Into<SqlValue>) -> Self {
210        self.push(JoinOp::And, Condition::Eq(col.into(), val.into()))
211    }
212
213    pub fn where_ne(self, col: &str, val: impl Into<SqlValue>) -> Self {
214        self.push(JoinOp::And, Condition::Ne(col.into(), val.into()))
215    }
216
217    pub fn where_gt(self, col: &str, val: impl Into<SqlValue>) -> Self {
218        self.push(JoinOp::And, Condition::Gt(col.into(), val.into()))
219    }
220
221    pub fn where_gte(self, col: &str, val: impl Into<SqlValue>) -> Self {
222        self.push(JoinOp::And, Condition::Gte(col.into(), val.into()))
223    }
224
225    pub fn where_lt(self, col: &str, val: impl Into<SqlValue>) -> Self {
226        self.push(JoinOp::And, Condition::Lt(col.into(), val.into()))
227    }
228
229    pub fn where_lte(self, col: &str, val: impl Into<SqlValue>) -> Self {
230        self.push(JoinOp::And, Condition::Lte(col.into(), val.into()))
231    }
232
233    pub fn where_like(self, col: &str, pattern: &str) -> Self {
234        self.push(JoinOp::And, Condition::Like(col.into(), pattern.into()))
235    }
236
237    pub fn where_not_like(self, col: &str, pattern: &str) -> Self {
238        self.push(JoinOp::And, Condition::NotLike(col.into(), pattern.into()))
239    }
240
241    pub fn where_null(self, col: &str) -> Self {
242        self.push(JoinOp::And, Condition::IsNull(col.into()))
243    }
244
245    pub fn where_not_null(self, col: &str) -> Self {
246        self.push(JoinOp::And, Condition::IsNotNull(col.into()))
247    }
248
249    pub fn where_in(self, col: &str, vals: Vec<impl Into<SqlValue>>) -> Self {
250        self.push(
251            JoinOp::And,
252            Condition::In(col.into(), vals.into_iter().map(Into::into).collect()),
253        )
254    }
255
256    pub fn where_not_in(self, col: &str, vals: Vec<impl Into<SqlValue>>) -> Self {
257        self.push(
258            JoinOp::And,
259            Condition::NotIn(col.into(), vals.into_iter().map(Into::into).collect()),
260        )
261    }
262
263    pub fn where_between(
264        self,
265        col: &str,
266        lo: impl Into<SqlValue>,
267        hi: impl Into<SqlValue>,
268    ) -> Self {
269        self.push(
270            JoinOp::And,
271            Condition::Between(col.into(), lo.into(), hi.into()),
272        )
273    }
274
275    pub fn where_not_between(
276        self,
277        col: &str,
278        lo: impl Into<SqlValue>,
279        hi: impl Into<SqlValue>,
280    ) -> Self {
281        self.push(
282            JoinOp::And,
283            Condition::NotBetween(col.into(), lo.into(), hi.into()),
284        )
285    }
286
287    pub fn where_raw(self, sql: &str) -> Self {
288        self.push(JoinOp::And, Condition::Raw(sql.into()))
289    }
290
291    /// Add `AND col ILIKE pattern` (case-insensitive LIKE, PostgreSQL only).
292    pub fn where_ilike(self, col: &str, pattern: &str) -> Self {
293        self.push(JoinOp::And, Condition::ILike(col.into(), pattern.into()))
294    }
295
296    /// Add `OR col ILIKE pattern`.
297    pub fn or_where_ilike(self, col: &str, pattern: &str) -> Self {
298        self.push(JoinOp::Or, Condition::ILike(col.into(), pattern.into()))
299    }
300
301    /// Add `AND col->>'key' = val` (JSONB text extraction, PostgreSQL).
302    pub fn where_json(self, col: &str, key: &str, val: impl Into<SqlValue>) -> Self {
303        self.push(
304            JoinOp::And,
305            Condition::JsonGet(col.into(), key.into(), val.into()),
306        )
307    }
308
309    /// Add `OR col->>'key' = val`.
310    pub fn or_where_json(self, col: &str, key: &str, val: impl Into<SqlValue>) -> Self {
311        self.push(
312            JoinOp::Or,
313            Condition::JsonGet(col.into(), key.into(), val.into()),
314        )
315    }
316
317    /// Add `AND col @> 'json_val'::jsonb` (JSONB containment, PostgreSQL).
318    pub fn where_json_contains(self, col: &str, json_val: &str) -> Self {
319        self.push(
320            JoinOp::And,
321            Condition::Raw(format!("{col} @> '{json_val}'::jsonb")),
322        )
323    }
324
325    /// Add `AND col <op> val` — ergonomic alias for [`where_op`].
326    ///
327    /// ```rust
328    /// use rok_orm_core::QueryBuilder;
329    /// let (sql, _) = QueryBuilder::<()>::new("users").where_column("age", ">", 18i64).to_sql();
330    /// assert!(sql.contains("age > $1"));
331    /// ```
332    pub fn where_column(self, col: &str, op: &str, val: impl Into<SqlValue>) -> Self {
333        self.where_op(col, op, val)
334    }
335
336    /// Add a comparison condition with an explicit operator string (`=`, `!=`, `>`, `>=`, `<`, `<=`).
337    pub fn where_op(self, col: &str, op: &str, val: impl Into<SqlValue>) -> Self {
338        let cond = match op {
339            "=" | "==" => Condition::Eq(col.into(), val.into()),
340            "!=" | "<>" => Condition::Ne(col.into(), val.into()),
341            ">" => Condition::Gt(col.into(), val.into()),
342            ">=" => Condition::Gte(col.into(), val.into()),
343            "<" => Condition::Lt(col.into(), val.into()),
344            "<=" => Condition::Lte(col.into(), val.into()),
345            other => Condition::Raw(format!("{col} {other} {}", val.into())),
346        };
347        self.push(JoinOp::And, cond)
348    }
349
350    /// Add a grouped sub-condition: `AND (sub_cond1 AND/OR sub_cond2 …)`.
351    ///
352    /// The closure receives a fresh `QueryBuilder` to build the sub-conditions.
353    /// Only the conditions of that builder are used (table/select/order etc. are ignored).
354    pub fn where_group<F>(self, f: F) -> Self
355    where
356        F: FnOnce(QueryBuilder<T>) -> QueryBuilder<T>,
357    {
358        let inner_builder = f(QueryBuilder::new(""));
359        if inner_builder.conditions.is_empty() {
360            return self;
361        }
362        self.push(JoinOp::And, Condition::Group(inner_builder.conditions))
363    }
364
365    /// Add an OR grouped sub-condition: `OR (sub_cond1 AND/OR sub_cond2 …)`.
366    pub fn or_where_group<F>(self, f: F) -> Self
367    where
368        F: FnOnce(QueryBuilder<T>) -> QueryBuilder<T>,
369    {
370        let inner_builder = f(QueryBuilder::new(""));
371        if inner_builder.conditions.is_empty() {
372            return self;
373        }
374        self.push(JoinOp::Or, Condition::Group(inner_builder.conditions))
375    }
376
377    // ── OR conditions ─────────────────────────────────────────────────────
378
379    /// Add `OR col = val` — ergonomic shorthand for [`or_where_eq`].
380    pub fn or_where(self, col: &str, val: impl Into<SqlValue>) -> Self {
381        self.or_where_eq(col, val)
382    }
383
384    pub fn or_where_eq(self, col: &str, val: impl Into<SqlValue>) -> Self {
385        self.push(JoinOp::Or, Condition::Eq(col.into(), val.into()))
386    }
387
388    pub fn or_where_ne(self, col: &str, val: impl Into<SqlValue>) -> Self {
389        self.push(JoinOp::Or, Condition::Ne(col.into(), val.into()))
390    }
391
392    pub fn or_where_gt(self, col: &str, val: impl Into<SqlValue>) -> Self {
393        self.push(JoinOp::Or, Condition::Gt(col.into(), val.into()))
394    }
395
396    pub fn or_where_gte(self, col: &str, val: impl Into<SqlValue>) -> Self {
397        self.push(JoinOp::Or, Condition::Gte(col.into(), val.into()))
398    }
399
400    pub fn or_where_lt(self, col: &str, val: impl Into<SqlValue>) -> Self {
401        self.push(JoinOp::Or, Condition::Lt(col.into(), val.into()))
402    }
403
404    pub fn or_where_lte(self, col: &str, val: impl Into<SqlValue>) -> Self {
405        self.push(JoinOp::Or, Condition::Lte(col.into(), val.into()))
406    }
407
408    pub fn or_where_like(self, col: &str, pattern: &str) -> Self {
409        self.push(JoinOp::Or, Condition::Like(col.into(), pattern.into()))
410    }
411
412    pub fn or_where_null(self, col: &str) -> Self {
413        self.push(JoinOp::Or, Condition::IsNull(col.into()))
414    }
415
416    pub fn or_where_not_null(self, col: &str) -> Self {
417        self.push(JoinOp::Or, Condition::IsNotNull(col.into()))
418    }
419
420    pub fn or_where_in(self, col: &str, vals: Vec<impl Into<SqlValue>>) -> Self {
421        self.push(
422            JoinOp::Or,
423            Condition::In(col.into(), vals.into_iter().map(Into::into).collect()),
424        )
425    }
426
427    pub fn or_where_between(
428        self,
429        col: &str,
430        lo: impl Into<SqlValue>,
431        hi: impl Into<SqlValue>,
432    ) -> Self {
433        self.push(
434            JoinOp::Or,
435            Condition::Between(col.into(), lo.into(), hi.into()),
436        )
437    }
438
439    pub fn or_where_raw(self, sql: &str) -> Self {
440        self.push(JoinOp::Or, Condition::Raw(sql.into()))
441    }
442
443    // ── ordering ──────────────────────────────────────────────────────────
444
445    pub fn order_by(mut self, col: &str) -> Self {
446        self.order.push((col.into(), OrderDir::Asc));
447        self
448    }
449
450    pub fn order_by_desc(mut self, col: &str) -> Self {
451        self.order.push((col.into(), OrderDir::Desc));
452        self
453    }
454
455    /// Append a raw ORDER BY expression (e.g. `"NULLS LAST, score DESC"`).
456    /// Rendered after any column-based orders.
457    pub fn order_by_raw(mut self, expr: &str) -> Self {
458        self.order_raw = Some(expr.to_string());
459        self
460    }
461
462    /// Append multiple column orders at once.
463    pub fn order_by_many(mut self, cols: &[(&str, OrderDir)]) -> Self {
464        for (col, dir) in cols {
465            self.order.push((col.to_string(), *dir));
466        }
467        self
468    }
469
470    /// Replace all existing orders with `col ASC`.
471    pub fn reorder(mut self, col: &str) -> Self {
472        self.order.clear();
473        self.order_raw = None;
474        self.order.push((col.into(), OrderDir::Asc));
475        self
476    }
477
478    /// Replace all existing orders with `col DESC`.
479    pub fn reorder_desc(mut self, col: &str) -> Self {
480        self.order.clear();
481        self.order_raw = None;
482        self.order.push((col.into(), OrderDir::Desc));
483        self
484    }
485
486    // ── pagination ────────────────────────────────────────────────────────
487
488    pub fn limit(mut self, n: usize) -> Self {
489        self.limit_val = Some(n);
490        self
491    }
492
493    pub fn offset(mut self, n: usize) -> Self {
494        self.offset_val = Some(n);
495        self
496    }
497
498    // ── SQL generation ────────────────────────────────────────────────────
499
500    /// Build a parameterized `SELECT` statement (PostgreSQL `$N` placeholders).
501    ///
502    /// Returns `(sql, params)` — params are ordered to match `$1`, `$2`, …
503    ///
504    /// For SQLite use [`to_sql_with_dialect(Dialect::Sqlite)`](Self::to_sql_with_dialect).
505    pub fn to_sql(&self) -> (String, Vec<SqlValue>) {
506        self.to_sql_with_dialect(Dialect::Postgres)
507    }
508
509    /// Build a parameterized `SELECT` statement for the given [`Dialect`].
510    ///
511    /// - [`Dialect::Postgres`] emits `$1, $2, …`
512    /// - [`Dialect::Sqlite`]   emits `?, ?, …`
513    pub fn to_sql_with_dialect(&self, dialect: Dialect) -> (String, Vec<SqlValue>) {
514        let base_cols = if let Some(raw) = &self.select_raw {
515            raw.clone()
516        } else {
517            self.select_cols
518                .as_ref()
519                .map(|c| c.join(", "))
520                .unwrap_or_else(|| "*".into())
521        };
522        let cols = if self.extra_select_exprs.is_empty() {
523            base_cols
524        } else {
525            format!("{}, {}", base_cols, self.extra_select_exprs.join(", "))
526        };
527
528        let distinct_kw = if !self.distinct_on_cols.is_empty() {
529            format!("DISTINCT ON ({}) ", self.distinct_on_cols.join(", "))
530        } else if self.distinct {
531            "DISTINCT ".to_string()
532        } else {
533            String::new()
534        };
535        let mut sql = format!("SELECT {distinct_kw}{cols} FROM {}", self.table);
536        let mut params: Vec<SqlValue> = Vec::new();
537
538        sql.push_str(&self.build_joins());
539        sql.push_str(&self.build_where_dialect(dialect, &mut params));
540        sql.push_str(&self.build_group_by());
541        sql.push_str(&self.build_order());
542
543        if let Some(n) = self.limit_val {
544            sql.push_str(&format!(" LIMIT {n}"));
545        }
546        if let Some(n) = self.offset_val {
547            sql.push_str(&format!(" OFFSET {n}"));
548        }
549
550        (sql, params)
551    }
552
553    /// Build a `SELECT COUNT(*) FROM …` statement (PostgreSQL dialect).
554    pub fn to_count_sql(&self) -> (String, Vec<SqlValue>) {
555        self.to_count_sql_with_dialect(Dialect::Postgres)
556    }
557
558    /// Build a `SELECT COUNT(*) FROM …` statement for the given dialect.
559    pub fn to_count_sql_with_dialect(&self, dialect: Dialect) -> (String, Vec<SqlValue>) {
560        let mut params: Vec<SqlValue> = Vec::new();
561        let joins = self.build_joins();
562        let where_clause = self.build_where_dialect(dialect, &mut params);
563        (
564            format!(
565                "SELECT COUNT(*) FROM {}{}{}",
566                self.table, joins, where_clause
567            ),
568            params,
569        )
570    }
571
572    /// Build a `DELETE FROM … WHERE …` statement (PostgreSQL dialect).
573    pub fn to_delete_sql(&self) -> (String, Vec<SqlValue>) {
574        self.to_delete_sql_with_dialect(Dialect::Postgres)
575    }
576
577    /// Build a `DELETE FROM … WHERE …` statement for the given dialect.
578    pub fn to_delete_sql_with_dialect(&self, dialect: Dialect) -> (String, Vec<SqlValue>) {
579        let mut params: Vec<SqlValue> = Vec::new();
580        let where_clause = self.build_where_dialect(dialect, &mut params);
581        (
582            format!("DELETE FROM {}{}", self.table, where_clause),
583            params,
584        )
585    }
586
587    /// Build an `UPDATE … SET … WHERE …` statement (PostgreSQL dialect).
588    pub fn to_update_sql(&self, data: &[(&str, SqlValue)]) -> (String, Vec<SqlValue>) {
589        self.to_update_sql_with_dialect(Dialect::Postgres, data)
590    }
591
592    /// Build an `UPDATE … SET … WHERE …` statement for the given dialect.
593    pub fn to_update_sql_with_dialect(
594        &self,
595        dialect: Dialect,
596        data: &[(&str, SqlValue)],
597    ) -> (String, Vec<SqlValue>) {
598        let mut params: Vec<SqlValue> = Vec::new();
599        let set_clauses: Vec<String> = data
600            .iter()
601            .enumerate()
602            .map(|(i, (col, val))| {
603                params.push(val.clone());
604                match dialect {
605                    Dialect::Postgres => format!("{col} = ${}", i + 1),
606                    Dialect::Sqlite => format!("{col} = ?"),
607                }
608            })
609            .collect();
610
611        let mut sql = format!("UPDATE {} SET {}", self.table, set_clauses.join(", "));
612        sql.push_str(&self.build_where_dialect(dialect, &mut params));
613        (sql, params)
614    }
615
616    // ── static helpers ────────────────────────────────────────────────────
617
618    /// Build an `INSERT INTO` statement (PostgreSQL `$N` placeholders).
619    pub fn insert_sql(table: &str, data: &[(&str, SqlValue)]) -> (String, Vec<SqlValue>) {
620        Self::insert_sql_with_dialect(Dialect::Postgres, table, data)
621    }
622
623    /// Build an `INSERT INTO` statement for the given dialect.
624    pub fn insert_sql_with_dialect(
625        dialect: Dialect,
626        table: &str,
627        data: &[(&str, SqlValue)],
628    ) -> (String, Vec<SqlValue>) {
629        let cols: Vec<&str> = data.iter().map(|(c, _)| *c).collect();
630        let placeholders: Vec<String> = match dialect {
631            Dialect::Postgres => (1..=data.len()).map(|i| format!("${i}")).collect(),
632            Dialect::Sqlite => (0..data.len()).map(|_| "?".to_string()).collect(),
633        };
634        let params: Vec<SqlValue> = data.iter().map(|(_, v)| v.clone()).collect();
635        (
636            format!(
637                "INSERT INTO {table} ({}) VALUES ({})",
638                cols.join(", "),
639                placeholders.join(", ")
640            ),
641            params,
642        )
643    }
644
645    /// Build an `INSERT INTO … VALUES …, …` statement for multiple rows.
646    ///
647    /// All rows must have the same columns in the same order as the first row.
648    ///
649    /// ```rust
650    /// use rok_orm_core::{QueryBuilder, SqlValue};
651    ///
652    /// let rows: Vec<Vec<(&str, SqlValue)>> = vec![
653    ///     vec![("name", "Alice".into()), ("email", "a@a.com".into())],
654    ///     vec![("name", "Bob".into()),   ("email", "b@b.com".into())],
655    /// ];
656    /// let (sql, params) = QueryBuilder::<()>::bulk_insert_sql("users", &rows);
657    /// assert!(sql.contains("($1, $2), ($3, $4)"));
658    /// assert_eq!(params.len(), 4);
659    /// ```
660    pub fn bulk_insert_sql(table: &str, rows: &[Vec<(&str, SqlValue)>]) -> (String, Vec<SqlValue>) {
661        assert!(
662            !rows.is_empty(),
663            "bulk_insert_sql requires at least one row"
664        );
665        let cols: Vec<&str> = rows[0].iter().map(|(c, _)| *c).collect();
666        let mut params: Vec<SqlValue> = Vec::new();
667        let mut value_groups: Vec<String> = Vec::new();
668        let mut offset = 1usize;
669
670        for row in rows {
671            let placeholders: Vec<String> = (offset..offset + row.len())
672                .map(|i| format!("${i}"))
673                .collect();
674            value_groups.push(format!("({})", placeholders.join(", ")));
675            for (_, v) in row.iter() {
676                params.push(v.clone());
677            }
678            offset += row.len();
679        }
680
681        (
682            format!(
683                "INSERT INTO {table} ({}) VALUES {}",
684                cols.join(", "),
685                value_groups.join(", ")
686            ),
687            params,
688        )
689    }
690
691    /// Build an `UPDATE … SET … WHERE …` statement from explicit conditions.
692    ///
693    /// Prefer [`to_update_sql`] when you already have a `QueryBuilder`.
694    pub fn update_sql(
695        table: &str,
696        data: &[(&str, SqlValue)],
697        conditions: &[(JoinOp, Condition)],
698    ) -> (String, Vec<SqlValue>) {
699        let mut params: Vec<SqlValue> = Vec::new();
700        let set_clauses: Vec<String> = data
701            .iter()
702            .enumerate()
703            .map(|(i, (col, val))| {
704                params.push(val.clone());
705                format!("{col} = ${}", i + 1)
706            })
707            .collect();
708
709        let mut sql = format!("UPDATE {table} SET {}", set_clauses.join(", "));
710
711        if !conditions.is_empty() {
712            let where_frag = build_where_from(conditions, &mut params);
713            sql.push_str(&where_frag);
714        }
715
716        (sql, params)
717    }
718
719    // ── conditional chaining ──────────────────────────────────────────────────
720
721    /// Apply `f(self)` only when `condition` is `true`; otherwise pass through unchanged.
722    ///
723    /// ```rust
724    /// use rok_orm_core::QueryBuilder;
725    ///
726    /// let active_only = true;
727    /// let (sql, _) = QueryBuilder::<()>::new("users")
728    ///     .when(active_only, |q| q.where_eq("active", true))
729    ///     .to_sql();
730    /// assert!(sql.contains("WHERE active = $1"));
731    /// ```
732    pub fn when<F>(self, condition: bool, f: F) -> Self
733    where
734        F: FnOnce(Self) -> Self,
735    {
736        if condition {
737            f(self)
738        } else {
739            self
740        }
741    }
742
743    /// Apply `f(self, val)` when `opt` is `Some(val)`; otherwise pass through unchanged.
744    ///
745    /// ```rust
746    /// use rok_orm_core::QueryBuilder;
747    ///
748    /// let role: Option<&str> = Some("admin");
749    /// let (sql, _) = QueryBuilder::<()>::new("users")
750    ///     .when_some(role, |q, r| q.where_eq("role", r))
751    ///     .to_sql();
752    /// assert!(sql.contains("WHERE role = $1"));
753    /// ```
754    pub fn when_some<V, F>(self, opt: Option<V>, f: F) -> Self
755    where
756        F: FnOnce(Self, V) -> Self,
757    {
758        match opt {
759            Some(v) => f(self, v),
760            None => self,
761        }
762    }
763
764    /// Push an arbitrary [`Condition`] with the given join operator.
765    ///
766    /// Useful when building conditions programmatically (e.g. `Condition::Subquery`).
767    pub fn push_condition(self, op: JoinOp, cond: Condition) -> Self {
768        self.push(op, cond)
769    }
770
771    // ── Read-replica routing ──────────────────────────────────────────────────
772
773    /// Force this query to the **write** (primary) pool, bypassing read replicas.
774    pub fn on_write_db(mut self) -> Self {
775        self.use_replica = false;
776        self
777    }
778
779    // ── pgvector: vector distance queries ─────────────────────────────────────
780
781    /// KNN search: `ORDER BY {col} <-> '[…]'::vector LIMIT k` (L2 distance).
782    pub fn nearest_to(self, col: &str, embedding: &[f32], k: usize) -> Self {
783        let vec_lit = format_vector(embedding);
784        self.order_by_raw(&format!("{col} <-> '{vec_lit}'::vector"))
785            .limit(k)
786    }
787
788    /// `WHERE {col} <=> '[…]'::vector {op} {threshold}` (cosine distance).
789    pub fn where_cosine_distance(
790        self,
791        col: &str,
792        embedding: &[f32],
793        op: &str,
794        threshold: f64,
795    ) -> Self {
796        let vec_lit = format_vector(embedding);
797        self.where_raw(&format!("{col} <=> '{vec_lit}'::vector {op} {threshold}"))
798    }
799
800    /// `WHERE {col} <-> '[…]'::vector {op} {threshold}` (L2 distance filter).
801    pub fn where_vector_distance(
802        self,
803        col: &str,
804        embedding: &[f32],
805        op: &str,
806        threshold: f64,
807    ) -> Self {
808        let vec_lit = format_vector(embedding);
809        self.where_raw(&format!("{col} <-> '{vec_lit}'::vector {op} {threshold}"))
810    }
811
812    /// `WHERE {col} <#> '[…]'::vector {op} {threshold}` (negative inner product).
813    pub fn where_inner_product(
814        self,
815        col: &str,
816        embedding: &[f32],
817        op: &str,
818        threshold: f64,
819    ) -> Self {
820        let vec_lit = format_vector(embedding);
821        self.where_raw(&format!("{col} <#> '{vec_lit}'::vector {op} {threshold}"))
822    }
823
824    // ── internals ─────────────────────────────────────────────────────────
825
826    fn push(mut self, op: JoinOp, cond: Condition) -> Self {
827        self.conditions.push((op, cond));
828        self
829    }
830
831    fn build_joins(&self) -> String {
832        let mut out = String::new();
833        for join in &self.joins {
834            match join {
835                Join::Inner(t, on) => out.push_str(&format!(" INNER JOIN {t} ON {on}")),
836                Join::Left(t, on) => out.push_str(&format!(" LEFT JOIN {t} ON {on}")),
837                Join::Right(t, on) => out.push_str(&format!(" RIGHT JOIN {t} ON {on}")),
838                Join::Raw(raw) => {
839                    out.push(' ');
840                    out.push_str(raw);
841                }
842            }
843        }
844        out
845    }
846
847    fn build_where_dialect(&self, dialect: Dialect, params: &mut Vec<SqlValue>) -> String {
848        build_where_from_dialect(dialect, &self.conditions, params)
849    }
850
851    fn build_group_by(&self) -> String {
852        let mut out = String::new();
853        if !self.group_by.is_empty() {
854            out.push_str(&format!(" GROUP BY {}", self.group_by.join(", ")));
855        }
856        if let Some(ref h) = self.having {
857            out.push_str(&format!(" HAVING {h}"));
858        }
859        out
860    }
861
862    fn build_order(&self) -> String {
863        let mut parts: Vec<String> = self
864            .order
865            .iter()
866            .map(|(col, dir)| format!("{col} {dir}"))
867            .collect();
868        if let Some(raw) = &self.order_raw {
869            parts.push(raw.clone());
870        }
871        if parts.is_empty() {
872            return String::new();
873        }
874        format!(" ORDER BY {}", parts.join(", "))
875    }
876
877    /// Expose the raw conditions (useful for callers that need to inspect them).
878    pub fn conditions(&self) -> &[(JoinOp, Condition)] {
879        &self.conditions
880    }
881
882    /// Build only the `WHERE …` fragment and its bound parameters.
883    ///
884    /// Useful when you need to compose a raw `UPDATE … SET … WHERE …` or similar
885    /// statement while still leveraging the query builder's condition logic.
886    ///
887    /// Returns `("", vec![])` when there are no conditions.
888    pub fn to_where_clause(&self) -> (String, Vec<SqlValue>) {
889        let mut params = Vec::new();
890        let clause = self.build_where_dialect(Dialect::Postgres, &mut params);
891        (clause, params)
892    }
893
894    /// Build `SELECT <agg_expr> FROM … WHERE …` for aggregates like `MAX(col)`, `SUM(col)`.
895    ///
896    /// `agg_expr` is a raw SQL expression, e.g. `"MAX(total)"` or `"SUM(price)"`.
897    pub fn to_aggregate_sql(&self, agg_expr: &str) -> (String, Vec<SqlValue>) {
898        let mut params: Vec<SqlValue> = Vec::new();
899        let joins = self.build_joins();
900        let where_clause = self.build_where_dialect(Dialect::Postgres, &mut params);
901        (
902            format!(
903                "SELECT {agg_expr} FROM {}{}{}",
904                self.table, joins, where_clause
905            ),
906            params,
907        )
908    }
909
910    /// Build an `INSERT INTO … ON CONFLICT (cols) DO UPDATE SET …` statement (PostgreSQL).
911    ///
912    /// - `data` — column-value pairs to insert
913    /// - `conflict_cols` — the conflict target columns
914    /// - `update_cols` — which columns to update on conflict (defaults to all `data` columns
915    ///   that are not in `conflict_cols` when empty)
916    pub fn upsert_sql(
917        table: &str,
918        data: &[(&str, SqlValue)],
919        conflict_cols: &[&str],
920    ) -> (String, Vec<SqlValue>) {
921        let cols: Vec<&str> = data.iter().map(|(c, _)| *c).collect();
922        let placeholders: Vec<String> = (1..=data.len()).map(|i| format!("${i}")).collect();
923        let params: Vec<SqlValue> = data.iter().map(|(_, v)| v.clone()).collect();
924
925        let conflict_target = conflict_cols.join(", ");
926        let update_set: Vec<String> = cols
927            .iter()
928            .filter(|c| !conflict_cols.contains(c))
929            .map(|c| format!("{c} = EXCLUDED.{c}"))
930            .collect();
931
932        let sql = if update_set.is_empty() {
933            format!(
934                "INSERT INTO {table} ({}) VALUES ({}) ON CONFLICT ({conflict_target}) DO NOTHING",
935                cols.join(", "),
936                placeholders.join(", "),
937            )
938        } else {
939            format!(
940                "INSERT INTO {table} ({}) VALUES ({}) ON CONFLICT ({conflict_target}) DO UPDATE SET {}",
941                cols.join(", "),
942                placeholders.join(", "),
943                update_set.join(", "),
944            )
945        };
946
947        (sql, params)
948    }
949}
950
951/// Encode a `f32` slice as a pgvector literal string, e.g. `"[1.0,2.0,3.0]"`.
952fn format_vector(v: &[f32]) -> String {
953    let elems: Vec<String> = v.iter().map(|x| x.to_string()).collect();
954    format!("[{}]", elems.join(","))
955}
956
957fn build_where_from(conditions: &[(JoinOp, Condition)], params: &mut Vec<SqlValue>) -> String {
958    build_where_from_dialect(Dialect::Postgres, conditions, params)
959}
960
961fn build_where_from_dialect(
962    dialect: Dialect,
963    conditions: &[(JoinOp, Condition)],
964    params: &mut Vec<SqlValue>,
965) -> String {
966    if conditions.is_empty() {
967        return String::new();
968    }
969    let mut out = " WHERE ".to_string();
970    for (idx, (op, cond)) in conditions.iter().enumerate() {
971        let (frag, ps) = match dialect {
972            Dialect::Postgres => cond.to_param_sql(params.len() + 1),
973            Dialect::Sqlite => cond.to_param_sql_sqlite(),
974        };
975        params.extend(ps);
976        if idx > 0 {
977            out.push(' ');
978            out.push_str(&op.to_string());
979            out.push(' ');
980        }
981        out.push_str(&frag);
982    }
983    out
984}
985
986// ── tests ────────────────────────────────────────────────────────────────────
987
988#[cfg(test)]
989mod tests {
990    use super::*;
991
992    #[test]
993    fn simple_select() {
994        let (sql, params) = QueryBuilder::<()>::new("users").to_sql();
995        assert_eq!(sql, "SELECT * FROM users");
996        assert!(params.is_empty());
997    }
998
999    #[test]
1000    fn distinct_select() {
1001        let (sql, _) = QueryBuilder::<()>::new("users").distinct().to_sql();
1002        assert!(sql.starts_with("SELECT DISTINCT * FROM users"));
1003    }
1004
1005    #[test]
1006    fn where_eq_generates_param() {
1007        let (sql, params) = QueryBuilder::<()>::new("users")
1008            .where_eq("id", 42i64)
1009            .to_sql();
1010        assert!(sql.contains("WHERE id = $1"));
1011        assert_eq!(params.len(), 1);
1012        assert_eq!(params[0], SqlValue::Integer(42));
1013    }
1014
1015    #[test]
1016    fn multiple_conditions() {
1017        let (sql, params) = QueryBuilder::<()>::new("posts")
1018            .where_eq("active", true)
1019            .where_like("title", "%rust%")
1020            .to_sql();
1021        assert!(sql.contains("WHERE active = $1 AND title LIKE $2"));
1022        assert_eq!(params.len(), 2);
1023    }
1024
1025    #[test]
1026    fn or_conditions() {
1027        let (sql, params) = QueryBuilder::<()>::new("users")
1028            .where_eq("role", "admin")
1029            .or_where_eq("role", "moderator")
1030            .to_sql();
1031        assert!(sql.contains("WHERE role = $1 OR role = $2"));
1032        assert_eq!(params.len(), 2);
1033    }
1034
1035    #[test]
1036    fn where_between() {
1037        let (sql, params) = QueryBuilder::<()>::new("orders")
1038            .where_between("amount", 10i64, 100i64)
1039            .to_sql();
1040        assert!(sql.contains("amount BETWEEN $1 AND $2"));
1041        assert_eq!(params.len(), 2);
1042    }
1043
1044    #[test]
1045    fn where_not_in() {
1046        let (sql, params) = QueryBuilder::<()>::new("users")
1047            .where_not_in("status", vec!["banned", "deleted"])
1048            .to_sql();
1049        assert!(sql.contains("status NOT IN ($1, $2)"));
1050        assert_eq!(params.len(), 2);
1051    }
1052
1053    #[test]
1054    fn where_not_like() {
1055        let (sql, _) = QueryBuilder::<()>::new("users")
1056            .where_not_like("email", "%@spam.com")
1057            .to_sql();
1058        assert!(sql.contains("email NOT LIKE $1"));
1059    }
1060
1061    #[test]
1062    fn to_update_sql() {
1063        let (sql, params) = QueryBuilder::<()>::new("users")
1064            .where_eq("id", 1i64)
1065            .to_update_sql(&[("name", "Bob".into()), ("active", true.into())]);
1066        assert!(sql.starts_with("UPDATE users SET name = $1, active = $2"));
1067        assert!(sql.contains("WHERE id = $3"));
1068        assert_eq!(params.len(), 3);
1069    }
1070
1071    #[test]
1072    fn order_limit_offset() {
1073        let (sql, _) = QueryBuilder::<()>::new("users")
1074            .order_by_desc("created_at")
1075            .order_by("name")
1076            .limit(10)
1077            .offset(20)
1078            .to_sql();
1079        assert!(sql.contains("ORDER BY created_at DESC, name ASC"));
1080        assert!(sql.contains("LIMIT 10"));
1081        assert!(sql.contains("OFFSET 20"));
1082    }
1083
1084    #[test]
1085    fn count_sql() {
1086        let (sql, _) = QueryBuilder::<()>::new("users")
1087            .where_eq("active", true)
1088            .to_count_sql();
1089        assert!(sql.starts_with("SELECT COUNT(*) FROM users"));
1090    }
1091
1092    #[test]
1093    fn delete_sql() {
1094        let (sql, params) = QueryBuilder::<()>::new("sessions")
1095            .where_eq("user_id", 5i64)
1096            .to_delete_sql();
1097        assert!(sql.contains("DELETE FROM sessions WHERE user_id = $1"));
1098        assert_eq!(params.len(), 1);
1099    }
1100
1101    #[test]
1102    fn insert_sql() {
1103        let (sql, params) = QueryBuilder::<()>::insert_sql(
1104            "users",
1105            &[("name", "Alice".into()), ("email", "a@a.com".into())],
1106        );
1107        assert!(sql.contains("INSERT INTO users (name, email) VALUES ($1, $2)"));
1108        assert_eq!(params.len(), 2);
1109    }
1110
1111    #[test]
1112    fn where_in() {
1113        let (sql, params) = QueryBuilder::<()>::new("users")
1114            .where_in("id", vec![1i64, 2, 3])
1115            .to_sql();
1116        assert!(sql.contains("id IN ($1, $2, $3)"));
1117        assert_eq!(params.len(), 3);
1118    }
1119
1120    #[test]
1121    fn select_specific_columns() {
1122        let (sql, _) = QueryBuilder::<()>::new("users")
1123            .select(&["id", "email"])
1124            .to_sql();
1125        assert!(sql.starts_with("SELECT id, email FROM users"));
1126    }
1127
1128    #[test]
1129    fn option_value_null() {
1130        let val: SqlValue = Option::<i64>::None.into();
1131        assert_eq!(val, SqlValue::Null);
1132    }
1133
1134    #[test]
1135    fn option_value_some() {
1136        let val: SqlValue = Some(42i64).into();
1137        assert_eq!(val, SqlValue::Integer(42));
1138    }
1139
1140    #[test]
1141    fn inner_join() {
1142        let (sql, _) = QueryBuilder::<()>::new("orders")
1143            .inner_join("users", "users.id = orders.user_id")
1144            .to_sql();
1145        assert!(sql.contains("INNER JOIN users ON users.id = orders.user_id"));
1146    }
1147
1148    #[test]
1149    fn left_join_with_where() {
1150        let (sql, params) = QueryBuilder::<()>::new("orders")
1151            .left_join("users", "users.id = orders.user_id")
1152            .where_eq("orders.status", "paid")
1153            .to_sql();
1154        assert!(sql.contains("LEFT JOIN users ON users.id = orders.user_id"));
1155        assert!(sql.contains("WHERE orders.status = $1"));
1156        assert_eq!(params.len(), 1);
1157    }
1158
1159    #[test]
1160    fn right_join() {
1161        let (sql, _) = QueryBuilder::<()>::new("orders")
1162            .right_join("products", "products.id = orders.product_id")
1163            .to_sql();
1164        assert!(sql.contains("RIGHT JOIN products ON products.id = orders.product_id"));
1165    }
1166
1167    #[test]
1168    fn group_by_and_having() {
1169        let (sql, _) = QueryBuilder::<()>::new("orders")
1170            .select(&["user_id", "COUNT(*) as total"])
1171            .group_by(&["user_id"])
1172            .having("COUNT(*) > 5")
1173            .to_sql();
1174        assert!(sql.contains("GROUP BY user_id"));
1175        assert!(sql.contains("HAVING COUNT(*) > 5"));
1176        // GROUP BY must come before ORDER BY
1177        let gpos = sql.find("GROUP BY").unwrap();
1178        let hpos = sql.find("HAVING").unwrap();
1179        assert!(gpos < hpos);
1180    }
1181
1182    #[test]
1183    fn count_sql_with_join() {
1184        let (sql, _) = QueryBuilder::<()>::new("orders")
1185            .inner_join("users", "users.id = orders.user_id")
1186            .where_eq("users.active", true)
1187            .to_count_sql();
1188        assert!(sql.contains("INNER JOIN users ON users.id = orders.user_id"));
1189        assert!(sql.contains("SELECT COUNT(*) FROM orders"));
1190    }
1191
1192    #[test]
1193    fn bulk_insert_sql_two_rows() {
1194        let rows: Vec<Vec<(&str, SqlValue)>> = vec![
1195            vec![("name", "Alice".into()), ("email", "a@a.com".into())],
1196            vec![("name", "Bob".into()), ("email", "b@b.com".into())],
1197        ];
1198        let (sql, params) = QueryBuilder::<()>::bulk_insert_sql("users", &rows);
1199        assert!(sql.starts_with("INSERT INTO users (name, email) VALUES"));
1200        assert!(sql.contains("($1, $2), ($3, $4)"));
1201        assert_eq!(params.len(), 4);
1202    }
1203
1204    #[test]
1205    fn bulk_insert_sql_single_row() {
1206        let rows = vec![vec![("x", SqlValue::Integer(1))]];
1207        let (sql, params) = QueryBuilder::<()>::bulk_insert_sql("t", &rows);
1208        assert!(sql.contains("($1)"));
1209        assert_eq!(params.len(), 1);
1210    }
1211
1212    #[test]
1213    fn where_ilike() {
1214        let (sql, params) = QueryBuilder::<()>::new("users")
1215            .where_ilike("name", "alice%")
1216            .to_sql();
1217        assert!(sql.contains("name ILIKE $1"));
1218        assert_eq!(params.len(), 1);
1219    }
1220
1221    #[test]
1222    fn where_op_gt() {
1223        let (sql, params) = QueryBuilder::<()>::new("users")
1224            .where_op("age", ">", 18i64)
1225            .to_sql();
1226        assert!(sql.contains("age > $1"));
1227        assert_eq!(params.len(), 1);
1228    }
1229
1230    #[test]
1231    fn where_group_subquery() {
1232        let (sql, params) = QueryBuilder::<()>::new("users")
1233            .where_eq("active", true)
1234            .where_group(|q| q.where_eq("role", "admin").or_where_eq("role", "mod"))
1235            .to_sql();
1236        assert!(sql.contains("active = $1"));
1237        assert!(sql.contains("AND (role = $2 OR role = $3)"));
1238        assert_eq!(params.len(), 3);
1239    }
1240
1241    #[test]
1242    fn select_raw() {
1243        let (sql, _) = QueryBuilder::<()>::new("users")
1244            .select_raw("id, LOWER(email) as email_lower")
1245            .to_sql();
1246        assert!(sql.starts_with("SELECT id, LOWER(email) as email_lower FROM users"));
1247    }
1248
1249    #[test]
1250    fn distinct_on() {
1251        let (sql, _) = QueryBuilder::<()>::new("users")
1252            .distinct_on(&["email"])
1253            .to_sql();
1254        assert!(sql.starts_with("SELECT DISTINCT ON (email) * FROM users"));
1255    }
1256
1257    #[test]
1258    fn join_raw() {
1259        let (sql, _) = QueryBuilder::<()>::new("users")
1260            .join_raw("INNER JOIN subscriptions s ON s.user_id = users.id AND s.active = true")
1261            .to_sql();
1262        assert!(sql.contains("INNER JOIN subscriptions s ON s.user_id = users.id"));
1263    }
1264
1265    #[test]
1266    fn order_by_raw() {
1267        let (sql, _) = QueryBuilder::<()>::new("users")
1268            .order_by_raw("NULLS LAST, score DESC")
1269            .to_sql();
1270        assert!(sql.contains("ORDER BY NULLS LAST, score DESC"));
1271    }
1272
1273    #[test]
1274    fn order_by_many() {
1275        let (sql, _) = QueryBuilder::<()>::new("users")
1276            .order_by_many(&[("role", OrderDir::Asc), ("created_at", OrderDir::Desc)])
1277            .to_sql();
1278        assert!(sql.contains("ORDER BY role ASC, created_at DESC"));
1279    }
1280
1281    #[test]
1282    fn reorder_clears_previous() {
1283        let (sql, _) = QueryBuilder::<()>::new("users")
1284            .order_by("name")
1285            .reorder_desc("created_at")
1286            .to_sql();
1287        assert!(sql.contains("ORDER BY created_at DESC"));
1288        assert!(!sql.contains("name"));
1289    }
1290
1291    #[test]
1292    fn upsert_sql_basic() {
1293        let (sql, params) = QueryBuilder::<()>::upsert_sql(
1294            "users",
1295            &[("email", "x@y.com".into()), ("name", "Bob".into())],
1296            &["email"],
1297        );
1298        assert!(sql.contains("ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name"));
1299        assert_eq!(params.len(), 2);
1300    }
1301
1302    #[test]
1303    fn where_json_extraction() {
1304        let (sql, params) = QueryBuilder::<()>::new("users")
1305            .where_json("settings", "theme", "dark")
1306            .to_sql();
1307        assert!(sql.contains("settings->>'theme' = $1"), "sql={sql}");
1308        assert_eq!(params.len(), 1);
1309        assert_eq!(params[0], SqlValue::Text("dark".into()));
1310    }
1311
1312    #[test]
1313    fn where_json_contains_raw() {
1314        let (sql, _) = QueryBuilder::<()>::new("posts")
1315            .where_json_contains("tags", r#"["rust"]"#)
1316            .to_sql();
1317        assert!(sql.contains(r#"tags @> '["rust"]'::jsonb"#), "sql={sql}");
1318    }
1319
1320    #[test]
1321    fn to_aggregate_sql() {
1322        let (sql, params) = QueryBuilder::<()>::new("orders")
1323            .where_eq("user_id", 1i64)
1324            .to_aggregate_sql("MAX(total)");
1325        assert!(sql.starts_with("SELECT MAX(total) FROM orders"));
1326        assert!(sql.contains("WHERE user_id = $1"));
1327        assert_eq!(params.len(), 1);
1328    }
1329
1330    // ── new v4 methods ────────────────────────────────────────────────────────
1331
1332    #[test]
1333    fn when_applies_closure_when_true() {
1334        let (sql, params) = QueryBuilder::<()>::new("users")
1335            .when(true, |q| q.where_eq("active", true))
1336            .to_sql();
1337        assert!(sql.contains("WHERE active = $1"));
1338        assert_eq!(params.len(), 1);
1339    }
1340
1341    #[test]
1342    fn when_noop_when_false() {
1343        let (sql, params) = QueryBuilder::<()>::new("users")
1344            .when(false, |q| q.where_eq("active", true))
1345            .to_sql();
1346        assert!(!sql.contains("WHERE"));
1347        assert!(params.is_empty());
1348    }
1349
1350    #[test]
1351    fn when_some_applies_with_value() {
1352        let role: Option<&str> = Some("admin");
1353        let (sql, params) = QueryBuilder::<()>::new("users")
1354            .when_some(role, |q, r| q.where_eq("role", r))
1355            .to_sql();
1356        assert!(sql.contains("role = $1"));
1357        assert_eq!(params.len(), 1);
1358    }
1359
1360    #[test]
1361    fn when_some_noop_when_none() {
1362        let role: Option<&str> = None;
1363        let (sql, params) = QueryBuilder::<()>::new("users")
1364            .when_some(role, |q, r| q.where_eq("role", r))
1365            .to_sql();
1366        assert!(!sql.contains("WHERE"));
1367        assert!(params.is_empty());
1368    }
1369
1370    #[test]
1371    fn chained_when_calls() {
1372        let (sql, params) = QueryBuilder::<()>::new("users")
1373            .when(true, |q| q.where_eq("active", true))
1374            .when(true, |q| q.where_eq("role", "admin"))
1375            .when(false, |q| q.where_eq("deleted", true))
1376            .to_sql();
1377        assert!(sql.contains("active = $1"));
1378        assert!(sql.contains("role = $2"));
1379        assert!(!sql.contains("deleted"));
1380        assert_eq!(params.len(), 2);
1381    }
1382
1383    #[test]
1384    fn add_select_expr_appends_to_star() {
1385        let (sql, _) = QueryBuilder::<()>::new("users")
1386            .add_select_expr(
1387                "(SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) AS posts_count",
1388            )
1389            .to_sql();
1390        assert!(sql.starts_with("SELECT *, (SELECT COUNT(*)"));
1391    }
1392
1393    #[test]
1394    fn add_select_expr_appends_to_cols() {
1395        let (sql, _) = QueryBuilder::<()>::new("users")
1396            .select(&["id", "email"])
1397            .add_select_expr("42 AS answer")
1398            .to_sql();
1399        assert!(sql.starts_with("SELECT id, email, 42 AS answer FROM users"));
1400    }
1401
1402    #[test]
1403    fn multiple_add_select_exprs() {
1404        let (sql, _) = QueryBuilder::<()>::new("users")
1405            .add_select_expr("(SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) AS posts_count")
1406            .add_select_expr("(SELECT COUNT(*) FROM comments WHERE comments.user_id = users.id) AS comments_count")
1407            .to_sql();
1408        assert!(sql.contains("posts_count"));
1409        assert!(sql.contains("comments_count"));
1410    }
1411
1412    #[test]
1413    fn where_column_alias() {
1414        let (sql, params) = QueryBuilder::<()>::new("users")
1415            .where_column("age", ">", 18i64)
1416            .to_sql();
1417        assert!(sql.contains("age > $1"));
1418        assert_eq!(params.len(), 1);
1419    }
1420
1421    #[test]
1422    fn or_where_alias() {
1423        let (sql, params) = QueryBuilder::<()>::new("users")
1424            .where_eq("role", "admin")
1425            .or_where("role", "moderator")
1426            .to_sql();
1427        assert!(sql.contains("role = $1 OR role = $2"));
1428        assert_eq!(params.len(), 2);
1429    }
1430
1431    #[test]
1432    fn subquery_exists_no_inner() {
1433        use crate::condition::Condition;
1434        use crate::condition::JoinOp;
1435        let (sql, params) = QueryBuilder::<()>::new("users")
1436            .push_condition(
1437                JoinOp::And,
1438                Condition::Subquery {
1439                    exists: true,
1440                    table: "posts".to_string(),
1441                    fk_expr: "posts.user_id = users.id".to_string(),
1442                    inner: vec![],
1443                },
1444            )
1445            .to_sql();
1446        assert!(sql.contains("EXISTS (SELECT 1 FROM posts WHERE posts.user_id = users.id)"));
1447        assert!(params.is_empty());
1448    }
1449
1450    #[test]
1451    fn subquery_not_exists_with_inner() {
1452        let inner = vec![(
1453            JoinOp::And,
1454            Condition::Eq("published".to_string(), SqlValue::Bool(true)),
1455        )];
1456        let (sql, params) = QueryBuilder::<()>::new("users")
1457            .push_condition(
1458                JoinOp::And,
1459                Condition::Subquery {
1460                    exists: false,
1461                    table: "posts".to_string(),
1462                    fk_expr: "posts.user_id = users.id".to_string(),
1463                    inner,
1464                },
1465            )
1466            .to_sql();
1467        assert!(sql.contains(
1468            "NOT EXISTS (SELECT 1 FROM posts WHERE posts.user_id = users.id AND published = $1)"
1469        ));
1470        assert_eq!(params.len(), 1);
1471        assert_eq!(params[0], SqlValue::Bool(true));
1472    }
1473
1474    #[test]
1475    fn subquery_outer_params_plus_inner_params() {
1476        let inner = vec![(
1477            JoinOp::And,
1478            Condition::Eq("published".to_string(), SqlValue::Bool(true)),
1479        )];
1480        let (sql, params) = QueryBuilder::<()>::new("users")
1481            .where_eq("active", true) // $1
1482            .push_condition(
1483                JoinOp::And,
1484                Condition::Subquery {
1485                    exists: true,
1486                    table: "posts".to_string(),
1487                    fk_expr: "posts.user_id = users.id".to_string(),
1488                    inner,
1489                },
1490            )
1491            .to_sql();
1492        assert!(sql.contains("active = $1"));
1493        assert!(sql.contains("published = $2"), "sql={sql}");
1494        assert_eq!(params.len(), 2);
1495    }
1496
1497    // ── replica routing ───────────────────────────────────────────────────────
1498
1499    #[test]
1500    fn use_replica_default_true() {
1501        let q = QueryBuilder::<()>::new("users");
1502        assert!(q.use_replica);
1503    }
1504
1505    #[test]
1506    fn on_write_db_clears_replica() {
1507        let q = QueryBuilder::<()>::new("users").on_write_db();
1508        assert!(!q.use_replica);
1509    }
1510
1511    // ── pgvector ──────────────────────────────────────────────────────────────
1512
1513    #[test]
1514    fn nearest_to_generates_order_and_limit() {
1515        let embedding = vec![1.0f32, 2.0, 3.0];
1516        let (sql, params) = QueryBuilder::<()>::new("documents")
1517            .nearest_to("embedding", &embedding, 10)
1518            .to_sql();
1519        assert!(sql.contains("embedding <-> '[1,2,3]'::vector"), "sql={sql}");
1520        assert!(sql.contains("LIMIT 10"), "sql={sql}");
1521        assert!(params.is_empty());
1522    }
1523
1524    #[test]
1525    fn where_cosine_distance_generates_filter() {
1526        let embedding = vec![0.5f32, 0.5];
1527        let (sql, params) = QueryBuilder::<()>::new("docs")
1528            .where_cosine_distance("embedding", &embedding, "<", 0.3)
1529            .to_sql();
1530        assert!(
1531            sql.contains("embedding <=> '[0.5,0.5]'::vector < 0.3"),
1532            "sql={sql}"
1533        );
1534        assert!(params.is_empty());
1535    }
1536
1537    #[test]
1538    fn where_vector_distance_generates_filter() {
1539        let embedding = vec![1.0f32];
1540        let (sql, _) = QueryBuilder::<()>::new("docs")
1541            .where_vector_distance("vec", &embedding, "<", 1.5)
1542            .to_sql();
1543        assert!(sql.contains("vec <-> '[1]'::vector < 1.5"), "sql={sql}");
1544    }
1545
1546    #[test]
1547    fn nearest_to_with_additional_filter() {
1548        let embedding = vec![1.0f32, 0.0];
1549        let (sql, params) = QueryBuilder::<()>::new("docs")
1550            .where_eq("active", true)
1551            .nearest_to("emb", &embedding, 5)
1552            .to_sql();
1553        assert!(sql.contains("WHERE active = $1"), "sql={sql}");
1554        assert!(sql.contains("emb <-> '[1,0]'::vector"), "sql={sql}");
1555        assert!(sql.contains("LIMIT 5"), "sql={sql}");
1556        assert_eq!(params.len(), 1);
1557    }
1558}
rok_orm_core/query.rs

rok_orm_core/
query.rs
