1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
use super::*;
impl<M: Model> QueryBuilder<M> {
/// Add a CTE (WITH clause) to the query
///
/// CTEs allow you to define temporary named result sets that can be
/// referenced within the main query.
#[must_use]
pub fn with_cte(mut self, cte: CTE) -> Self {
if let Err(reason) = Self::validate_cte_clause(&cte) {
self.invalidate_query(format!("invalid CTE for with_cte(): {}", reason));
}
self.ctes.push(cte);
self
}
/// Add a CTE from another query builder
#[must_use]
pub fn with_query<N: Model>(mut self, name: &str, query: QueryBuilder<N>) -> Self {
if let Err(reason) = crate::query::db_sql::validate_identifier("CTE name", name) {
self.invalidate_query(reason);
}
if let Err(err) = query.ensure_query_is_valid() {
self.invalidate_query(format!("invalid subquery for with_query(): {}", err));
}
self.ctes
.push(CTE::new(name, query.build_base_select_sql()));
self
}
/// Add a CTE with column aliases
///
/// Trusted SQL only. Do not pass user-controlled input; prefer `with_query()` when the
/// subquery can be expressed with `QueryBuilder`.
#[must_use]
pub fn with_cte_columns(mut self, name: &str, columns: Vec<&str>, sql: &str) -> Self {
if let Err(reason) = crate::query::db_sql::validate_identifier("CTE name", name) {
self.invalidate_query(reason);
}
for column in &columns {
if let Err(reason) = crate::query::db_sql::validate_identifier("CTE column", column) {
self.invalidate_query(reason);
break;
}
}
if let Err(reason) = crate::query::db_sql::validate_subquery_sql(sql) {
self.invalidate_query(format!(
"invalid subquery for with_cte_columns(): {}",
reason
));
}
self.ctes
.push(CTE::with_columns(name, columns, sql.to_string()));
self
}
/// Add a recursive CTE
///
/// Use recursive CTEs for hierarchical or tree-structured data.
#[must_use]
pub fn with_recursive_cte(
mut self,
name: &str,
columns: Vec<&str>,
base_case: &str,
recursive_case: &str,
) -> Self {
if let Err(reason) = crate::query::db_sql::validate_identifier("CTE name", name) {
self.invalidate_query(reason);
}
for column in &columns {
if let Err(reason) = crate::query::db_sql::validate_identifier("CTE column", column) {
self.invalidate_query(reason);
break;
}
}
if let Err(reason) = crate::query::db_sql::validate_subquery_sql(base_case) {
self.invalidate_query(format!(
"invalid subquery for with_recursive_cte() base query: {}",
reason
));
}
if let Err(reason) = crate::query::db_sql::validate_subquery_sql(recursive_case) {
self.invalidate_query(format!(
"invalid subquery for with_recursive_cte() recursive query: {}",
reason
));
}
let full_sql = format!("{} UNION ALL {}", base_case, recursive_case);
let cte = CTE::with_columns(name, columns, full_sql).recursive();
self.ctes.push(cte);
self
}
// =========================================================================
// SOFT DELETE QUERIES
// =========================================================================
/// Include soft-deleted records in the query results
///
/// By default, soft-deleted records (where `deleted_at` is not NULL) are excluded.
/// Use this method to include them.
#[must_use]
pub fn with_trashed(mut self) -> Self {
self.include_trashed = true;
self.only_trashed = false;
self
}
/// Only return soft-deleted records
///
/// Returns only records where `deleted_at` is not NULL.
#[must_use]
pub fn only_trashed(mut self) -> Self {
self.only_trashed = true;
self.include_trashed = false;
self
}
/// Exclude soft-deleted records (default behavior)
///
/// This is the default, but can be used to explicitly exclude soft-deleted
/// records after calling `with_trashed()`.
#[must_use]
pub fn without_trashed(mut self) -> Self {
self.include_trashed = false;
self.only_trashed = false;
self
}
// =========================================================================
// SCOPES (Reusable query fragments)
// =========================================================================
/// Apply a scope function to modify the query
///
/// Scopes are reusable query fragments that can be applied to any query.
/// Use scopes to define common query patterns once and reuse them.
#[must_use]
pub fn scope<F>(self, f: F) -> Self
where
F: FnOnce(Self) -> Self,
{
f(self)
}
/// Apply a conditional scope
///
/// Only applies the scope function if the condition is true.
#[must_use]
pub fn when<F>(self, condition: bool, f: F) -> Self
where
F: FnOnce(Self) -> Self,
{
if condition { f(self) } else { self }
}
/// Apply a scope based on an Option value
///
/// If the option is Some, applies the scope function with the value.
/// If None, returns the query unchanged.
#[must_use]
pub fn when_some<T, F>(self, option: Option<T>, f: F) -> Self
where
F: FnOnce(Self, T) -> Self,
{
match option {
Some(value) => f(self, value),
None => self,
}
}
// =========================================================================
}