fraiseql_core/runtime/aggregation/
where_clause.rs

1//! Parameterized WHERE and HAVING clause SQL generation.
2
3use super::{
4    AggregationSqlGenerator, DatabaseType, FactTableMetadata, FraiseQLError, Result,
5    ValidatedHavingCondition, WhereClause, WhereOperator, to_snake_case,
6};
7
8impl AggregationSqlGenerator {
9    /// Convert a [`WhereClause`] AST to parameterized SQL, appending bind values to `params`.
10    pub(super) fn where_clause_to_sql_parameterized(
11        &self,
12        clause: &WhereClause,
13        metadata: &FactTableMetadata,
14        params: &mut Vec<serde_json::Value>,
15    ) -> Result<String> {
16        match clause {
17            WhereClause::Field {
18                path,
19                operator,
20                value,
21            } => {
22                let field_name = &path[0];
23                let is_denormalized =
24                    metadata.denormalized_filters.iter().any(|f| f.name == *field_name);
25                if is_denormalized {
26                    self.generate_direct_column_where_parameterized(
27                        field_name, "", operator, value, params,
28                    )
29                } else {
30                    let jsonb_column = &metadata.dimensions.name;
31                    self.generate_jsonb_where_parameterized(
32                        jsonb_column,
33                        path,
34                        operator,
35                        value,
36                        params,
37                    )
38                }
39            },
40            WhereClause::And(clauses) => {
41                let conditions: Vec<String> = clauses
42                    .iter()
43                    .map(|c| self.where_clause_to_sql_parameterized(c, metadata, params))
44                    .collect::<Result<Vec<_>>>()?;
45                Ok(format!("({})", conditions.join(" AND ")))
46            },
47            WhereClause::Or(clauses) => {
48                let conditions: Vec<String> = clauses
49                    .iter()
50                    .map(|c| self.where_clause_to_sql_parameterized(c, metadata, params))
51                    .collect::<Result<Vec<_>>>()?;
52                Ok(format!("({})", conditions.join(" OR ")))
53            },
54            WhereClause::Not(inner) => {
55                let s = self.where_clause_to_sql_parameterized(inner, metadata, params)?;
56                Ok(format!("NOT ({s})"))
57            },
58            WhereClause::NativeField {
59                column,
60                pg_cast,
61                operator,
62                value,
63            } => {
64                // Direct column reference (no JSONB extraction) for native SQL columns.
65                // Use quote_identifier for dialect-correct quoting (MySQL backticks, SQL
66                // Server brackets, PG/SQLite double-quotes).
67                let col_ref = self.quote_identifier(column);
68                // An ltree operator casts its own operand (`::ltree`, `::lquery`, …); the
69                // column's cast suffix below would double it.
70                if let Some(sql) = self.ltree_where(&col_ref, operator, value, params)? {
71                    return Ok(sql);
72                }
73                self.generate_direct_column_where_parameterized(
74                    &col_ref, pg_cast, operator, value, params,
75                )
76            },
77            // Declared field types steer the JSONB-extraction casts in the
78            // generic WHERE generator; this path builds its own SQL from the
79            // fact-table metadata, so the annotation is transparent.
80            WhereClause::Typed { inner, .. } => {
81                self.where_clause_to_sql_parameterized(inner, metadata, params)
82            },
83            WhereClause::InHierarchy { context, inner } => {
84                self.node_id_where(context, inner, metadata, params)
85            },
86            // Reason: non_exhaustive requires catch-all for cross-crate matches
87            _ => Err(crate::FraiseQLError::Validation {
88                message: "Unknown WhereClause variant".to_string(),
89                path:    None,
90            }),
91        }
92    }
93
94    /// Parameterized WHERE for a denormalized (direct column) filter.
95    ///
96    /// `pg_cast` is the column's PostgreSQL type (`""` for none). Every aggregate parameter
97    /// is bound as text, so a compared value is cast from text to it (`$1::text::int4`), in
98    /// a scalar comparison and in each `IN` element alike: a bare `$1::int4` would type the
99    /// parameter `int4` and have its text bytes decoded as a binary integer (#1231).
100    pub(super) fn generate_direct_column_where_parameterized(
101        &self,
102        field: &str,
103        pg_cast: &str,
104        operator: &WhereOperator,
105        value: &serde_json::Value,
106        params: &mut Vec<serde_json::Value>,
107    ) -> Result<String> {
108        if let Some(test) = null_test(operator, value)? {
109            return Ok(format!("{field} {test}"));
110        }
111        if let Some(sql) = self.ltree_where(field, operator, value, params)? {
112            return Ok(sql);
113        }
114
115        let op_sql = self.operator_to_sql(operator)?;
116
117        if matches!(operator, WhereOperator::In | WhereOperator::Nin) {
118            let arr = value.as_array().ok_or_else(|| {
119                FraiseQLError::validation("IN/NOT IN operators require array values")
120            })?;
121            let phs: Vec<String> =
122                arr.iter().map(|v| self.emit_cast_param(v, pg_cast, params)).collect();
123            return Ok(format!("{field} {op_sql} ({})", phs.join(", ")));
124        }
125
126        if matches!(
127            operator,
128            WhereOperator::Contains
129                | WhereOperator::Startswith
130                | WhereOperator::Endswith
131                | WhereOperator::Like
132        ) {
133            let s = value
134                .as_str()
135                .ok_or_else(|| FraiseQLError::validation("LIKE operators require string values"))?;
136            let (ph, needs_escape) = self.emit_like_pattern_param(operator, s, params);
137            return if needs_escape {
138                Ok(format!("{field} {op_sql} {ph} ESCAPE '!'"))
139            } else {
140                Ok(format!("{field} {op_sql} {ph}"))
141            };
142        }
143
144        if operator.is_case_insensitive() {
145            let s = value.as_str().ok_or_else(|| {
146                FraiseQLError::validation("Case-insensitive operators require string values")
147            })?;
148            return self.generate_case_insensitive_where_parameterized(field, operator, s, params);
149        }
150
151        let ph = self.emit_cast_param(value, pg_cast, params);
152        Ok(format!("{field} {op_sql} {ph}"))
153    }
154
155    /// A placeholder for `value`, cast to `pg_cast` through text on PostgreSQL. The value is
156    /// bound as its text form (a JSON number or boolean as its literal), which is what the
157    /// cast reads; an empty `pg_cast`, or another dialect, binds it unchanged.
158    fn emit_cast_param(
159        &self,
160        value: &serde_json::Value,
161        pg_cast: &str,
162        params: &mut Vec<serde_json::Value>,
163    ) -> String {
164        if pg_cast.is_empty() || self.database_type != DatabaseType::PostgreSQL {
165            return self.emit_value_param(value, params);
166        }
167        let text = match value {
168            serde_json::Value::Number(n) => serde_json::Value::String(n.to_string()),
169            serde_json::Value::Bool(b) => serde_json::Value::String(b.to_string()),
170            other => other.clone(),
171        };
172        let ph = self.emit_value_param(&text, params);
173        format!("{ph}::text::{pg_cast}")
174    }
175
176    /// Parameterized WHERE for a JSONB dimension field.
177    pub(super) fn generate_jsonb_where_parameterized(
178        &self,
179        jsonb_column: &str,
180        path: &[String],
181        operator: &WhereOperator,
182        value: &serde_json::Value,
183        params: &mut Vec<serde_json::Value>,
184    ) -> Result<String> {
185        let field_path = &path[0];
186        let db_field_path = to_snake_case(field_path);
187        let jsonb_extract = self.jsonb_extract_sql(jsonb_column, &db_field_path);
188
189        if let Some(test) = null_test(operator, value)? {
190            return Ok(format!("{jsonb_extract} {test}"));
191        }
192        if let Some(sql) = self.ltree_where(&jsonb_extract, operator, value, params)? {
193            return Ok(sql);
194        }
195
196        let op_sql = self.operator_to_sql(operator)?;
197
198        if operator.is_case_insensitive() {
199            let s = value.as_str().ok_or_else(|| {
200                FraiseQLError::validation("Case-insensitive operators require string values")
201            })?;
202            return self.generate_case_insensitive_where_parameterized(
203                &jsonb_extract,
204                operator,
205                s,
206                params,
207            );
208        }
209
210        if matches!(operator, WhereOperator::In | WhereOperator::Nin) {
211            let arr = value.as_array().ok_or_else(|| {
212                FraiseQLError::validation("IN/NOT IN operators require array values")
213            })?;
214            let phs: Vec<String> = arr.iter().map(|v| self.emit_value_param(v, params)).collect();
215            return Ok(format!("{jsonb_extract} {op_sql} ({})", phs.join(", ")));
216        }
217
218        if matches!(
219            operator,
220            WhereOperator::Contains | WhereOperator::Startswith | WhereOperator::Endswith
221        ) {
222            let s = value
223                .as_str()
224                .ok_or_else(|| FraiseQLError::validation("LIKE operators require string values"))?;
225            // needs_escape is always true for semantic LIKE operators (Contains etc.)
226            let (ph, _) = self.emit_like_pattern_param(operator, s, params);
227            return Ok(format!("{jsonb_extract} {op_sql} {ph} ESCAPE '!'"));
228        }
229
230        let ph = self.emit_value_param(value, params);
231        Ok(format!("{jsonb_extract} {op_sql} {ph}"))
232    }
233
234    /// SQL for a node-id filter on a path column (#1498): the path is compared against
235    /// the node's own path, read from the declared hierarchy's table.
236    fn node_id_where(
237        &self,
238        context: &fraiseql_db::where_generator::HierarchyContext,
239        inner: &WhereClause,
240        metadata: &FactTableMetadata,
241        params: &mut Vec<serde_json::Value>,
242    ) -> Result<String> {
243        use fraiseql_db::{PostgresDialect, SqlDialect};
244
245        let (lhs, operator, value) = match inner {
246            WhereClause::NativeField {
247                column,
248                operator,
249                value,
250                ..
251            } => (self.quote_identifier(column), operator, value),
252            WhereClause::Field {
253                path,
254                operator,
255                value,
256            } if metadata.denormalized_filters.iter().any(|f| Some(&f.name) == path.first()) => {
257                (self.quote_identifier(&path[0]), operator, value)
258            },
259            other => {
260                return Err(FraiseQLError::validation(format!(
261                    "a node-id filter applies to a denormalized path column, not {other:?}"
262                )));
263            },
264        };
265        let pg_op = match operator {
266            WhereOperator::DescendantOfId => "<@",
267            WhereOperator::AncestorOfId => "@>",
268            other => {
269                return Err(FraiseQLError::validation(format!(
270                    "{other:?} is not a node-id operator"
271                )));
272            },
273        };
274        let dialect = match self.database_type {
275            DatabaseType::PostgreSQL => PostgresDialect,
276        };
277        // The hierarchy table's `id` is a UUID; an uncast text parameter against it is
278        // rejected by the driver's binary bind (the #1396 lowering does the same).
279        let ph = self.emit_value_param(value, params);
280        let ph = dialect.cast_native_param(&ph, "uuid");
281        dialect
282            .ltree_id_subquery_sql(
283                pg_op,
284                &lhs,
285                &context.table,
286                &context.path_column,
287                context.fk_column.as_deref(),
288                &ph,
289            )
290            .map_err(|e| FraiseQLError::validation(e.to_string()))
291    }
292
293    /// SQL for an ltree operator on `lhs` (a column or a JSONB extraction), or `None` for
294    /// any other operator.
295    ///
296    /// The SQL is the PostgreSQL dialect's, as the main WHERE generator emits it. This
297    /// generator implemented no ltree operator, so `descendantOf` became `=` and later
298    /// was refused (#1460).
299    fn ltree_where(
300        &self,
301        lhs: &str,
302        operator: &WhereOperator,
303        value: &serde_json::Value,
304        params: &mut Vec<serde_json::Value>,
305    ) -> Result<Option<String>> {
306        use fraiseql_db::{PostgresDialect, SqlDialect};
307
308        let dialect = match self.database_type {
309            DatabaseType::PostgreSQL => PostgresDialect,
310        };
311        let binary = |pg_op: &str, rhs_type: &str, params: &mut Vec<serde_json::Value>| {
312            let ph = self.emit_value_param(value, params);
313            dialect.ltree_binary_sql(pg_op, lhs, &ph, rhs_type)
314        };
315        let depth = |op: &str, params: &mut Vec<serde_json::Value>| {
316            let ph = self.emit_value_param(value, params);
317            dialect.ltree_depth_sql(op, lhs, &ph)
318        };
319        let list = |cast: &str, params: &mut Vec<serde_json::Value>| -> Result<Vec<String>> {
320            let items = value.as_array().filter(|a| !a.is_empty()).ok_or_else(|| {
321                FraiseQLError::validation(format!("{operator:?} requires a non-empty array"))
322            })?;
323            Ok(items
324                .iter()
325                .map(|v| format!("{}::{cast}", self.emit_value_param(v, params)))
326                .collect())
327        };
328        let sql = match operator {
329            WhereOperator::AncestorOf => binary("@>", "ltree", params),
330            WhereOperator::DescendantOf => binary("<@", "ltree", params),
331            WhereOperator::MatchesLquery => binary("~", "lquery", params),
332            WhereOperator::MatchesLtxtquery => binary("@", "ltxtquery", params),
333            WhereOperator::MatchesAnyLquery => {
334                dialect.ltree_any_lquery_sql(lhs, &list("lquery", params)?)
335            },
336            WhereOperator::Lca => dialect.ltree_lca_sql(lhs, &list("ltree", params)?),
337            WhereOperator::DepthEq => depth("=", params),
338            WhereOperator::DepthNeq => depth("!=", params),
339            WhereOperator::DepthGt => depth(">", params),
340            WhereOperator::DepthGte => depth(">=", params),
341            WhereOperator::DepthLt => depth("<", params),
342            WhereOperator::DepthLte => depth("<=", params),
343            WhereOperator::DescendantOfId | WhereOperator::AncestorOfId => {
344                return Err(FraiseQLError::validation(format!(
345                    "{operator:?} resolves a node id through a declared hierarchy, which a \
346                     fact-table aggregate filter does not support; filter on the path with \
347                     descendant_of / ancestor_of"
348                )));
349            },
350            _ => return Ok(None),
351        };
352        sql.map(Some).map_err(|e| FraiseQLError::validation(e.to_string()))
353    }
354
355    /// Parameterized case-insensitive WHERE (ILIKE for PostgreSQL, `UPPER()` for others).
356    pub(super) fn generate_case_insensitive_where_parameterized(
357        &self,
358        column: &str,
359        operator: &WhereOperator,
360        value_str: &str,
361        params: &mut Vec<serde_json::Value>,
362    ) -> Result<String> {
363        let op = self.operator_to_sql(operator)?;
364        if self.database_type == DatabaseType::PostgreSQL {
365            let (ph, needs_escape) = self.emit_like_pattern_param(operator, value_str, params);
366            Ok(if needs_escape {
367                format!("{column} {op} {ph} ESCAPE '!'")
368            } else {
369                format!("{column} {op} {ph}")
370            })
371        } else {
372            let upper = value_str.to_uppercase();
373            let (ph, needs_escape) = self.emit_like_pattern_param(operator, &upper, params);
374            Ok(if needs_escape {
375                format!("UPPER({column}) LIKE {ph} ESCAPE '!'")
376            } else {
377                format!("UPPER({column}) LIKE {ph}")
378            })
379        }
380    }
381
382    /// Build a parameterized `WHERE …` clause, or an empty string if the clause is empty.
383    ///
384    /// # Errors
385    ///
386    /// Returns an error if WHERE clause generation fails.
387    pub fn build_where_clause_parameterized(
388        &self,
389        where_clause: &WhereClause,
390        metadata: &FactTableMetadata,
391        params: &mut Vec<serde_json::Value>,
392    ) -> Result<String> {
393        if where_clause.is_empty() {
394            return Ok(String::new());
395        }
396        let cond = self.where_clause_to_sql_parameterized(where_clause, metadata, params)?;
397        Ok(format!("WHERE {cond}"))
398    }
399
400    /// Build a parameterized `HAVING …` clause.
401    ///
402    /// # Errors
403    ///
404    /// Returns an error if HAVING clause generation fails.
405    pub(super) fn build_having_clause_parameterized(
406        &self,
407        having_conditions: &[ValidatedHavingCondition],
408        params: &mut Vec<serde_json::Value>,
409    ) -> Result<String> {
410        if having_conditions.is_empty() {
411            return Ok(String::new());
412        }
413        let mut conditions = Vec::new();
414        for condition in having_conditions {
415            let aggregate_sql = self.aggregate_expression_to_sql(&condition.aggregate)?;
416            let operator_sql = condition.operator.sql_operator();
417            let value_sql = self.emit_value_param(&condition.value, params);
418            conditions.push(format!("{aggregate_sql} {operator_sql} {value_sql}"));
419        }
420        Ok(format!("HAVING {}", conditions.join(" AND ")))
421    }
422}
423
424/// `IS NULL` / `IS NOT NULL` for a null-test operator, following its operand; `None` for any
425/// other operator.
426///
427/// The operand decides, as in the main WHERE generator (#828): `isnull: false` asks for the
428/// rows that are NOT null. This path used to emit `IS NULL` whatever the operand said, and sent
429/// `IsNotNull` through the operator table, where it became `=` (#1460).
430fn null_test(operator: &WhereOperator, value: &serde_json::Value) -> Result<Option<&'static str>> {
431    let negated = match operator {
432        WhereOperator::IsNull => false,
433        WhereOperator::IsNotNull => true,
434        _ => return Ok(None),
435    };
436    let asserted = match value {
437        serde_json::Value::Bool(b) => *b,
438        serde_json::Value::Null => true,
439        other => {
440            return Err(FraiseQLError::validation(format!(
441                "{operator:?} takes a boolean operand, got {other}"
442            )));
443        },
444    };
445    Ok(Some(if asserted == negated {
446        "IS NOT NULL"
447    } else {
448        "IS NULL"
449    }))
450}
fraiseql_core/runtime/aggregation/where_clause.rs

fraiseql_core/runtime/aggregation/
where_clause.rs
