ocre/query.rs
1//! A small, explicit SQL builder for one table: conditions with bound
2//! parameters, order, limits, grouping, and the statements to count, check,
3//! pluck, update or delete the matching rows. Pure Rust: nothing runs until a
4//! terminal method (`all`, `first`, `count`, ... in `runtime::query`) sends
5//! the built SQL to D1.
6
7use std::marker::PhantomData;
8
9use serde::{Deserialize, Serialize};
10
11use crate::{IntoParam, Page, Param, Statement};
12
13/// Sort direction for [`Query::order_by`], read from a query string as `asc` or `desc`.
14///
15/// Deserializes from `"asc"`/`"desc"` (any case), so a handler can take it
16/// straight from `?direction=desc`; the column must still come from your code
17/// (see [`Query::order_by`]).
18///
19/// # Examples
20///
21/// ```
22/// use ocre::Direction;
23///
24/// let direction: Direction = serde_json::from_str(r#""desc""#).unwrap();
25/// assert_eq!(direction, Direction::Desc);
26/// assert_eq!(direction.as_sql(), "DESC");
27/// assert_eq!(Direction::default(), Direction::Asc);
28/// ```
29#[derive(Debug, Clone, Copy, Default, PartialEq, Eq, Deserialize, Serialize)]
30#[serde(rename_all = "lowercase")]
31pub enum Direction {
32 /// Smallest first (`ASC`).
33 #[default]
34 #[serde(alias = "ASC", alias = "Asc")]
35 Asc,
36 /// Largest first (`DESC`).
37 #[serde(alias = "DESC", alias = "Desc")]
38 Desc,
39}
40
41impl Direction {
42 /// `ASC` or `DESC`.
43 ///
44 /// # Examples
45 ///
46 /// ```
47 /// assert_eq!(ocre::Direction::Asc.as_sql(), "ASC");
48 /// ```
49 pub fn as_sql(self) -> &'static str {
50 match self {
51 Self::Asc => "ASC",
52 Self::Desc => "DESC",
53 }
54 }
55}
56
57/// The non-generic state of a [`Query`]: SQL fragments using bare `?`
58/// placeholders, numbered `?1, ?2...` when a statement is built.
59#[derive(Debug, Clone, Default)]
60struct Parts {
61 table: &'static str,
62 select: Option<String>,
63 distinct: bool,
64 joins: Vec<&'static str>,
65 conditions: Vec<String>,
66 params: Vec<Param>,
67 group: Option<&'static str>,
68 having: Vec<String>,
69 having_params: Vec<Param>,
70 order: Vec<String>,
71 order_params: Vec<Param>,
72 limit: Option<i64>,
73 offset: Option<i64>,
74}
75
76/// A `SELECT` on one table, built step by step, whose values are always bound parameters.
77///
78/// Generated models start every query with `query()` (e.g.
79/// `post::query()`, which is `Query::table("posts")`), so the row type is
80/// known: [`all`](Self::all) returns `Vec<Post>`. Scopes are plain functions
81/// taking and returning a `Query` (see [`scope`](Self::scope)).
82///
83/// - **Values** (`eq`, `is_in`, `contains`...) are always bound as
84/// parameters, never written into the SQL.
85/// - **Column names and SQL fragments** are `&'static str`: they come from
86/// your code, so a request cannot inject SQL through them. To sort by a
87/// column the user picks, `match` their input onto a fixed column name.
88/// - Conditions combine with `AND`; [`any`](Self::any) groups conditions with
89/// `OR`, [`not`](Self::not) negates a group.
90/// - Raw fragments ([`where_sql`](Self::where_sql), [`having`](Self::having))
91/// use bare `?` placeholders; the builder numbers every placeholder
92/// `?1, ?2...` in the final SQL, in order.
93///
94/// Building costs nothing on the free plan. Running it reads the rows D1
95/// scans (see [`Db`](crate::Db)): filter and order on indexed columns, and
96/// bound lists with [`limit`](Self::limit) or [`page`](Self::page).
97///
98/// # Examples
99///
100/// ```
101/// use ocre::{Direction, Page, Query, params};
102///
103/// #[derive(serde::Deserialize)]
104/// struct Post {
105/// id: i64,
106/// title: String,
107/// }
108///
109/// let query: Query<Post> = Query::table("posts")
110/// .eq("published", true)
111/// .any(|q| q.contains("title", "rust").contains("body", "rust"))
112/// .order_by("created_at", Direction::Desc)
113/// .page(Page { limit: 20, offset: 40 });
114/// let stmt = query.to_statement();
115/// assert_eq!(
116/// stmt.sql,
117/// "SELECT * FROM posts WHERE published = ?1 AND (title LIKE ?2 ESCAPE '\\' OR body LIKE ?3 ESCAPE '\\') \
118/// ORDER BY created_at DESC LIMIT ?4 OFFSET ?5"
119/// );
120/// assert_eq!(stmt.params, params![true, "%rust%", "%rust%", 20, 40]);
121/// ```
122///
123/// Running it, in a handler or a model function:
124///
125/// ```no_run
126/// # use ocre::{Ctx, Query, Result};
127/// # #[derive(serde::Deserialize)] struct Post { id: i64 }
128/// async fn published(ctx: &Ctx) -> Result<Vec<Post>> {
129/// Query::table("posts").eq("published", true).order_desc("id").limit(20).all(&ctx.db()?).await
130/// }
131/// ```
132pub struct Query<T> {
133 parts: Parts,
134 row: PhantomData<fn() -> T>,
135}
136
137impl<T> Clone for Query<T> {
138 fn clone(&self) -> Self {
139 Self { parts: self.parts.clone(), row: PhantomData }
140 }
141}
142
143impl<T> std::fmt::Debug for Query<T> {
144 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
145 let stmt = self.parts.select_statement();
146 f.debug_struct("Query").field("sql", &stmt.sql).field("params", &stmt.params).finish()
147 }
148}
149
150impl<T> Query<T> {
151 /// Starts a query on `table`: `SELECT * FROM <table>`.
152 ///
153 /// # Examples
154 ///
155 /// ```
156 /// let query: ocre::Query<serde_json::Value> = ocre::Query::table("posts");
157 /// assert_eq!(query.to_statement().sql, "SELECT * FROM posts");
158 /// ```
159 pub fn table(table: &'static str) -> Self {
160 Self { parts: Parts { table, ..Parts::default() }, row: PhantomData }
161 }
162
163 /// Applies a scope: `f(self)`. Scopes are plain functions, so they chain
164 /// like Rails scopes and take arguments like any function.
165 ///
166 /// # Examples
167 ///
168 /// ```
169 /// use ocre::Query;
170 ///
171 /// # struct Post;
172 /// fn published(query: Query<Post>) -> Query<Post> {
173 /// query.eq("published", true)
174 /// }
175 /// fn by_author(query: Query<Post>, author_id: i64) -> Query<Post> {
176 /// query.eq("author_id", author_id)
177 /// }
178 ///
179 /// let query = Query::<Post>::table("posts").scope(published).scope(|q| by_author(q, 7));
180 /// assert_eq!(query.to_statement().sql, "SELECT * FROM posts WHERE published = ?1 AND author_id = ?2");
181 /// ```
182 pub fn scope(self, f: impl FnOnce(Self) -> Self) -> Self {
183 f(self)
184 }
185
186 /// Selects `columns` (an SQL select list) instead of `*`; the rows become `U`.
187 ///
188 /// The row type changes because the columns do: read them into a struct
189 /// with matching field names (use `AS` for expressions).
190 ///
191 /// # Examples
192 ///
193 /// ```
194 /// use ocre::Query;
195 ///
196 /// #[derive(serde::Deserialize)]
197 /// struct Title {
198 /// title: String,
199 /// }
200 ///
201 /// let query: Query<Title> = Query::<()>::table("posts").select("title");
202 /// assert_eq!(query.to_statement().sql, "SELECT title FROM posts");
203 /// ```
204 pub fn select<U>(mut self, columns: &'static str) -> Query<U> {
205 self.parts.select = Some(columns.to_owned());
206 Query { parts: self.parts, row: PhantomData }
207 }
208
209 /// `SELECT DISTINCT`: drops duplicate rows.
210 ///
211 /// # Examples
212 ///
213 /// ```
214 /// use ocre::Query;
215 ///
216 /// let query: Query<()> = Query::<()>::table("posts").select("author_id").distinct();
217 /// assert_eq!(query.to_statement().sql, "SELECT DISTINCT author_id FROM posts");
218 /// ```
219 pub fn distinct(mut self) -> Self {
220 self.parts.distinct = true;
221 self
222 }
223
224 /// Adds a join clause, written in full: `JOIN ...` or `LEFT JOIN ...`.
225 ///
226 /// Select the columns you need with [`select`](Self::select) (`SELECT *`
227 /// would mix both tables' columns) and qualify ambiguous names.
228 ///
229 /// # Examples
230 ///
231 /// ```
232 /// use ocre::Query;
233 ///
234 /// let query: Query<()> = Query::<()>::table("posts")
235 /// .select("posts.*")
236 /// .join("JOIN taggings ON taggings.post_id = posts.id")
237 /// .eq("taggings.tag_id", 3);
238 /// assert_eq!(
239 /// query.to_statement().sql,
240 /// "SELECT posts.* FROM posts JOIN taggings ON taggings.post_id = posts.id WHERE taggings.tag_id = ?1"
241 /// );
242 /// ```
243 pub fn join(mut self, clause: &'static str) -> Self {
244 self.parts.joins.push(clause);
245 self
246 }
247
248 /// `column = value`. A `None` value matches nothing (SQL `= NULL`); use
249 /// [`is_null`](Self::is_null) for missing values.
250 ///
251 /// # Examples
252 ///
253 /// ```
254 /// use ocre::{Query, params};
255 ///
256 /// let stmt = Query::<()>::table("users").eq("email", "ada@example.com").to_statement();
257 /// assert_eq!(stmt.sql, "SELECT * FROM users WHERE email = ?1");
258 /// assert_eq!(stmt.params, params!["ada@example.com"]);
259 /// ```
260 pub fn eq(mut self, column: &'static str, value: impl IntoParam) -> Self {
261 self.parts.compare(column, "=", value.into_param());
262 self
263 }
264
265 /// `column != value`.
266 ///
267 /// # Examples
268 ///
269 /// ```
270 /// let stmt = ocre::Query::<()>::table("posts").ne("status", "archived").to_statement();
271 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE status != ?1");
272 /// ```
273 pub fn ne(mut self, column: &'static str, value: impl IntoParam) -> Self {
274 self.parts.compare(column, "!=", value.into_param());
275 self
276 }
277
278 /// `column > value`.
279 ///
280 /// # Examples
281 ///
282 /// ```
283 /// let stmt = ocre::Query::<()>::table("posts").gt("id", 100).to_statement();
284 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE id > ?1");
285 /// ```
286 pub fn gt(mut self, column: &'static str, value: impl IntoParam) -> Self {
287 self.parts.compare(column, ">", value.into_param());
288 self
289 }
290
291 /// `column >= value`.
292 ///
293 /// # Examples
294 ///
295 /// ```
296 /// let stmt = ocre::Query::<()>::table("posts").gte("created_at", "2026-01-01").to_statement();
297 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE created_at >= ?1");
298 /// ```
299 pub fn gte(mut self, column: &'static str, value: impl IntoParam) -> Self {
300 self.parts.compare(column, ">=", value.into_param());
301 self
302 }
303
304 /// `column < value`.
305 ///
306 /// # Examples
307 ///
308 /// ```
309 /// let stmt = ocre::Query::<()>::table("posts").lt("views", 10).to_statement();
310 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE views < ?1");
311 /// ```
312 pub fn lt(mut self, column: &'static str, value: impl IntoParam) -> Self {
313 self.parts.compare(column, "<", value.into_param());
314 self
315 }
316
317 /// `column <= value`.
318 ///
319 /// # Examples
320 ///
321 /// ```
322 /// let stmt = ocre::Query::<()>::table("posts").lte("views", 10).to_statement();
323 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE views <= ?1");
324 /// ```
325 pub fn lte(mut self, column: &'static str, value: impl IntoParam) -> Self {
326 self.parts.compare(column, "<=", value.into_param());
327 self
328 }
329
330 /// `column BETWEEN low AND high`, both bounds included. For a date range
331 /// with an optional bound, use [`gte`](Self::gte)/[`lt`](Self::lt) under
332 /// `if let Some(..)`.
333 ///
334 /// # Examples
335 ///
336 /// ```
337 /// let stmt = ocre::Query::<()>::table("events").between("starts_on", "2026-01-01", "2026-12-31").to_statement();
338 /// assert_eq!(stmt.sql, "SELECT * FROM events WHERE starts_on BETWEEN ?1 AND ?2");
339 /// ```
340 pub fn between(mut self, column: &'static str, low: impl IntoParam, high: impl IntoParam) -> Self {
341 self.parts.push_condition(format!("{column} BETWEEN ? AND ?"), vec![low.into_param(), high.into_param()]);
342 self
343 }
344
345 /// `column IN (?, ?, ...)`. An empty list matches no row (`0`), like
346 /// Rails' `where(id: [])`. D1 binds at most 100 parameters per query.
347 ///
348 /// # Examples
349 ///
350 /// ```
351 /// use ocre::Query;
352 ///
353 /// let stmt = Query::<()>::table("posts").is_in("id", [1, 2, 3]).to_statement();
354 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE id IN (?1, ?2, ?3)");
355 /// let none = Query::<()>::table("posts").is_in("id", Vec::<i64>::new()).to_statement();
356 /// assert_eq!(none.sql, "SELECT * FROM posts WHERE 0");
357 /// ```
358 pub fn is_in<V: IntoParam>(mut self, column: &'static str, values: impl IntoIterator<Item = V>) -> Self {
359 self.parts.list(column, "IN", values.into_iter().map(IntoParam::into_param).collect());
360 self
361 }
362
363 /// `column NOT IN (?, ?, ...)`. An empty list excludes nothing.
364 ///
365 /// # Examples
366 ///
367 /// ```
368 /// let stmt = ocre::Query::<()>::table("posts").not_in("status", ["draft", "archived"]).to_statement();
369 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE status NOT IN (?1, ?2)");
370 /// ```
371 pub fn not_in<V: IntoParam>(mut self, column: &'static str, values: impl IntoIterator<Item = V>) -> Self {
372 self.parts.list(column, "NOT IN", values.into_iter().map(IntoParam::into_param).collect());
373 self
374 }
375
376 /// `column IS NULL`.
377 ///
378 /// # Examples
379 ///
380 /// ```
381 /// let stmt = ocre::Query::<()>::table("posts").is_null("deleted_at").to_statement();
382 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE deleted_at IS NULL");
383 /// ```
384 pub fn is_null(mut self, column: &'static str) -> Self {
385 self.parts.push_condition(format!("{column} IS NULL"), vec![]);
386 self
387 }
388
389 /// `column IS NOT NULL`.
390 ///
391 /// # Examples
392 ///
393 /// ```
394 /// let stmt = ocre::Query::<()>::table("posts").is_not_null("published_at").to_statement();
395 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE published_at IS NOT NULL");
396 /// ```
397 pub fn is_not_null(mut self, column: &'static str) -> Self {
398 self.parts.push_condition(format!("{column} IS NOT NULL"), vec![]);
399 self
400 }
401
402 /// `column LIKE pattern`, with the pattern as given: `%` and `_` are
403 /// wildcards. For user input, use [`contains`](Self::contains),
404 /// [`starts_with`](Self::starts_with) or [`ends_with`](Self::ends_with),
405 /// which escape them. SQLite's `LIKE` ignores ASCII case.
406 ///
407 /// # Examples
408 ///
409 /// ```
410 /// let stmt = ocre::Query::<()>::table("posts").like("slug", "2026-%").to_statement();
411 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE slug LIKE ?1");
412 /// ```
413 pub fn like(mut self, column: &'static str, pattern: impl IntoParam) -> Self {
414 self.parts.compare(column, "LIKE", pattern.into_param());
415 self
416 }
417
418 /// `column NOT LIKE pattern` (wildcards as given, see [`like`](Self::like)).
419 ///
420 /// # Examples
421 ///
422 /// ```
423 /// let stmt = ocre::Query::<()>::table("users").not_like("email", "%@example.com").to_statement();
424 /// assert_eq!(stmt.sql, "SELECT * FROM users WHERE email NOT LIKE ?1");
425 /// ```
426 pub fn not_like(mut self, column: &'static str, pattern: impl IntoParam) -> Self {
427 self.parts.compare(column, "NOT LIKE", pattern.into_param());
428 self
429 }
430
431 /// `column` contains `text` (case-insensitive for ASCII), with `%`, `_`
432 /// and `\` in `text` matched literally (see [`escape_like`]).
433 ///
434 /// # Examples
435 ///
436 /// ```
437 /// use ocre::{Query, params};
438 ///
439 /// let stmt = Query::<()>::table("posts").contains("title", "100%").to_statement();
440 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE title LIKE ?1 ESCAPE '\\'");
441 /// assert_eq!(stmt.params, params!["%100\\%%"]);
442 /// ```
443 pub fn contains(mut self, column: &'static str, text: &str) -> Self {
444 self.parts.escaped_like(column, format!("%{}%", escape_like(text)));
445 self
446 }
447
448 /// `column` starts with `text` (wildcards escaped, see [`contains`](Self::contains)).
449 ///
450 /// # Examples
451 ///
452 /// ```
453 /// let stmt = ocre::Query::<()>::table("users").starts_with("name", "Ad").to_statement();
454 /// assert_eq!(stmt.params, ocre::params!["Ad%"]);
455 /// ```
456 pub fn starts_with(mut self, column: &'static str, text: &str) -> Self {
457 self.parts.escaped_like(column, format!("{}%", escape_like(text)));
458 self
459 }
460
461 /// `column` ends with `text` (wildcards escaped, see [`contains`](Self::contains)).
462 ///
463 /// # Examples
464 ///
465 /// ```
466 /// let stmt = ocre::Query::<()>::table("users").ends_with("email", "@example.com").to_statement();
467 /// assert_eq!(stmt.params, ocre::params!["%@example.com"]);
468 /// ```
469 pub fn ends_with(mut self, column: &'static str, text: &str) -> Self {
470 self.parts.escaped_like(column, format!("%{}", escape_like(text)));
471 self
472 }
473
474 /// Adds a raw SQL condition with bare `?` placeholders, bound to `params` in order.
475 ///
476 /// The fragment is wrapped in parentheses, so an `OR` inside stays
477 /// grouped. Use it for anything the other methods do not cover: SQL
478 /// functions, subqueries (`EXISTS (...)` for "has at least one
479 /// comment"), date arithmetic.
480 ///
481 /// # Examples
482 ///
483 /// ```
484 /// use ocre::{Query, params};
485 ///
486 /// let stmt = Query::<()>::table("posts")
487 /// .eq("published", true)
488 /// .where_sql("created_at > datetime('now', ?)", params!["-7 days"])
489 /// .where_sql("EXISTS (SELECT 1 FROM comments WHERE comments.post_id = posts.id)", params![])
490 /// .to_statement();
491 /// assert_eq!(
492 /// stmt.sql,
493 /// "SELECT * FROM posts WHERE published = ?1 AND (created_at > datetime('now', ?2)) \
494 /// AND (EXISTS (SELECT 1 FROM comments WHERE comments.post_id = posts.id))"
495 /// );
496 /// ```
497 pub fn where_sql(mut self, fragment: &'static str, params: Vec<Param>) -> Self {
498 self.parts.push_condition(format!("({fragment})"), params);
499 self
500 }
501
502 /// Keeps rows with at least one row in `table` pointing to them through
503 /// `foreign_key` (Rails' `where.associated` on a has-many side).
504 ///
505 /// Writes `EXISTS (SELECT 1 FROM <table> WHERE <table>.<foreign_key> = <this table>.id)`,
506 /// which stops at the first child: with an index on the foreign key
507 /// (generated for every `references` field) it reads one child row per
508 /// parent. For the belongs-to side, test the column itself:
509 /// `is_not_null("author_id")`.
510 ///
511 /// # Examples
512 ///
513 /// ```
514 /// let stmt = ocre::Query::<()>::table("posts").where_associated("comments", "post_id").to_statement();
515 /// assert_eq!(
516 /// stmt.sql,
517 /// "SELECT * FROM posts WHERE EXISTS (SELECT 1 FROM comments WHERE comments.post_id = posts.id)"
518 /// );
519 /// ```
520 pub fn where_associated(mut self, table: &'static str, foreign_key: &'static str) -> Self {
521 let condition = self.parts.child_exists(table, foreign_key);
522 self.parts.push_condition(condition, vec![]);
523 self
524 }
525
526 /// Keeps rows that no row of `table` points to through `foreign_key`
527 /// (Rails' `where.missing` on a has-many side): posts without comments.
528 ///
529 /// The belongs-to side is `is_null("author_id")`.
530 ///
531 /// # Examples
532 ///
533 /// ```
534 /// let stmt = ocre::Query::<()>::table("posts").where_missing("comments", "post_id").to_statement();
535 /// assert_eq!(
536 /// stmt.sql,
537 /// "SELECT * FROM posts WHERE NOT EXISTS (SELECT 1 FROM comments WHERE comments.post_id = posts.id)"
538 /// );
539 /// ```
540 pub fn where_missing(mut self, table: &'static str, foreign_key: &'static str) -> Self {
541 let condition = format!("NOT {}", self.parts.child_exists(table, foreign_key));
542 self.parts.push_condition(condition, vec![]);
543 self
544 }
545
546 /// Filters `column` between two optional bounds, like Loco's `DateRangeBuilder`.
547 ///
548 /// Both bounds: `BETWEEN from AND to` (inclusive). One bound: strictly
549 /// after `from` (`>`) or strictly before `to` (`<`). No bound: no
550 /// condition. Made for `?from=&to=` filters, whose values arrive as
551 /// `Option`s; works on any comparable column (dates stored as ISO text
552 /// compare correctly).
553 ///
554 /// # Examples
555 ///
556 /// ```
557 /// use ocre::{Query, params};
558 ///
559 /// let both = Query::<()>::table("posts").date_range("created_at", Some("2026-01-01"), Some("2026-01-31"));
560 /// assert_eq!(both.to_statement().sql, "SELECT * FROM posts WHERE created_at BETWEEN ?1 AND ?2");
561 /// let from = Query::<()>::table("posts").date_range("created_at", Some("2026-01-01"), None);
562 /// assert_eq!(from.to_statement().sql, "SELECT * FROM posts WHERE created_at > ?1");
563 /// let to = Query::<()>::table("posts").date_range("created_at", None, Some("2026-01-31"));
564 /// assert_eq!(to.to_statement().sql, "SELECT * FROM posts WHERE created_at < ?1");
565 /// let none = Query::<()>::table("posts").date_range::<&str>("created_at", None, None);
566 /// assert_eq!(none.to_statement().sql, "SELECT * FROM posts");
567 /// ```
568 pub fn date_range<V: IntoParam>(self, column: &'static str, from: Option<V>, to: Option<V>) -> Self {
569 match (from, to) {
570 (Some(from), Some(to)) => self.between(column, from, to),
571 (Some(from), None) => self.gt(column, from),
572 (None, Some(to)) => self.lt(column, to),
573 (None, None) => self,
574 }
575 }
576
577 /// Removes every condition added so far (Rails' `unscope(:where)`);
578 /// chain new ones after it for Rails' `rewhere`.
579 ///
580 /// Useful to reuse a scoped query (a model's `query()` that hides
581 /// soft-deleted rows, say) without its conditions. Joins, order and
582 /// limits stay.
583 ///
584 /// # Examples
585 ///
586 /// ```
587 /// let visible = ocre::Query::<()>::table("posts").is_null("deleted_at").order_desc("id");
588 /// let stmt = visible.unscope_where().eq("author_id", 3).to_statement();
589 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE author_id = ?1 ORDER BY id DESC");
590 /// ```
591 pub fn unscope_where(mut self) -> Self {
592 self.parts.conditions.clear();
593 self.parts.params.clear();
594 self
595 }
596
597 /// Removes the limit and offset set so far (Rails' `unscope(:limit, :offset)`).
598 ///
599 /// # Examples
600 ///
601 /// ```
602 /// let stmt = ocre::Query::<()>::table("posts").limit(10).offset(20).unscope_limit().to_statement();
603 /// assert_eq!(stmt.sql, "SELECT * FROM posts");
604 /// ```
605 pub fn unscope_limit(mut self) -> Self {
606 self.parts.limit = None;
607 self.parts.offset = None;
608 self
609 }
610
611 /// Reverses the order (Rails' `reverse_order`): `ASC` terms become
612 /// `DESC` and back; terms without a direction ([`order_in`](Self::order_in),
613 /// a bare [`order_sql`](Self::order_sql)) get `DESC`. Without any order,
614 /// sorts by `<table>.id DESC`.
615 ///
616 /// Write raw terms with `NULLS FIRST/LAST` in full instead: they cannot
617 /// be flipped by appending a direction.
618 ///
619 /// # Examples
620 ///
621 /// ```
622 /// use ocre::Query;
623 ///
624 /// let stmt = Query::<()>::table("posts").order_asc("title").order_desc("id").reverse_order().to_statement();
625 /// assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY title DESC, id ASC");
626 /// assert_eq!(Query::<()>::table("posts").reverse_order().to_statement().sql, "SELECT * FROM posts ORDER BY posts.id DESC");
627 /// ```
628 pub fn reverse_order(mut self) -> Self {
629 self.parts.reverse_order();
630 self
631 }
632
633 /// Matches rows meeting at least one of the conditions `f` adds: `(a OR b ...)`.
634 ///
635 /// `f` receives an empty query to add conditions to; its table, order and
636 /// limits are ignored. No condition added: nothing changes.
637 ///
638 /// # Examples
639 ///
640 /// ```
641 /// let stmt = ocre::Query::<()>::table("posts")
642 /// .eq("published", true)
643 /// .any(|q| q.eq("author_id", 1).is_null("author_id"))
644 /// .to_statement();
645 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE published = ?1 AND (author_id = ?2 OR author_id IS NULL)");
646 /// ```
647 pub fn any(mut self, f: impl FnOnce(Self) -> Self) -> Self {
648 let group = f(Self::table(self.parts.table)).parts;
649 self.parts.group(group, " OR ", "");
650 self
651 }
652
653 /// Matches rows that do not meet all the conditions `f` adds: `NOT (a AND b ...)`.
654 ///
655 /// # Examples
656 ///
657 /// ```
658 /// let stmt = ocre::Query::<()>::table("posts").not(|q| q.eq("status", "draft").eq("author_id", 3)).to_statement();
659 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE NOT (status = ?1 AND author_id = ?2)");
660 /// ```
661 pub fn not(mut self, f: impl FnOnce(Self) -> Self) -> Self {
662 let group = f(Self::table(self.parts.table)).parts;
663 self.parts.group(group, " AND ", "NOT ");
664 self
665 }
666
667 /// Matches no row (`WHERE 0`), like Rails' `none`: a scope can return
668 /// it when a filter makes the result empty. D1 still runs the query, but
669 /// reads no row.
670 ///
671 /// # Examples
672 ///
673 /// ```
674 /// let stmt = ocre::Query::<()>::table("posts").none().to_statement();
675 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE 0");
676 /// ```
677 pub fn none(mut self) -> Self {
678 self.parts.push_condition("0".to_owned(), vec![]);
679 self
680 }
681
682 /// `GROUP BY columns`; combine with [`select`](Self::select) for the
683 /// aggregates and [`having`](Self::having) to filter groups.
684 ///
685 /// # Examples
686 ///
687 /// ```
688 /// use ocre::{Query, params};
689 ///
690 /// #[derive(serde::Deserialize)]
691 /// struct PerAuthor {
692 /// author_id: i64,
693 /// count: i64,
694 /// }
695 ///
696 /// let query: Query<PerAuthor> = Query::<()>::table("posts")
697 /// .select("author_id, COUNT(*) AS count")
698 /// .group_by("author_id")
699 /// .having("COUNT(*) >= ?", params![5])
700 /// .order_desc("count");
701 /// assert_eq!(
702 /// query.to_statement().sql,
703 /// "SELECT author_id, COUNT(*) AS count FROM posts GROUP BY author_id HAVING (COUNT(*) >= ?1) ORDER BY count DESC"
704 /// );
705 /// ```
706 pub fn group_by(mut self, columns: &'static str) -> Self {
707 self.parts.group = Some(columns);
708 self
709 }
710
711 /// Adds a `HAVING` condition (raw SQL with bare `?` placeholders) on the groups.
712 ///
713 /// See [`group_by`](Self::group_by).
714 ///
715 /// # Examples
716 ///
717 /// ```
718 /// let stmt = ocre::Query::<()>::table("posts").group_by("author_id").having("COUNT(*) > ?", ocre::params![1]).to_statement();
719 /// assert!(stmt.sql.ends_with("HAVING (COUNT(*) > ?1)"));
720 /// ```
721 pub fn having(mut self, fragment: &'static str, params: Vec<Param>) -> Self {
722 self.parts.having.push(format!("({fragment})"));
723 self.parts.having_params.extend(params);
724 self
725 }
726
727 /// `ORDER BY column ASC`, after any order already set.
728 ///
729 /// # Examples
730 ///
731 /// ```
732 /// let stmt = ocre::Query::<()>::table("posts").order_asc("title").order_desc("id").to_statement();
733 /// assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY title ASC, id DESC");
734 /// ```
735 pub fn order_asc(self, column: &'static str) -> Self {
736 self.order_by(column, Direction::Asc)
737 }
738
739 /// `ORDER BY column DESC`, after any order already set.
740 ///
741 /// # Examples
742 ///
743 /// ```
744 /// let stmt = ocre::Query::<()>::table("posts").order_desc("id").to_statement();
745 /// assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY id DESC");
746 /// ```
747 pub fn order_desc(self, column: &'static str) -> Self {
748 self.order_by(column, Direction::Desc)
749 }
750
751 /// `ORDER BY column <direction>`, after any order already set.
752 ///
753 /// The direction may come from the request; the column may not, so map
754 /// the user's choice onto a fixed name.
755 ///
756 /// # Examples
757 ///
758 /// ```
759 /// use ocre::{Direction, Query};
760 ///
761 /// // `sort` and `direction` from `?sort=title&direction=desc`.
762 /// let (sort, direction) = ("title", Direction::Desc);
763 /// let column = match sort {
764 /// "title" => "title",
765 /// _ => "created_at",
766 /// };
767 /// let stmt = Query::<()>::table("posts").order_by(column, direction).to_statement();
768 /// assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY title DESC");
769 /// ```
770 pub fn order_by(mut self, column: &'static str, direction: Direction) -> Self {
771 self.parts.order.push(format!("{column} {}", direction.as_sql()));
772 self
773 }
774
775 /// Orders `column` by an explicit list of values (Rails' `in_order_of`):
776 /// rows whose value is not listed come last.
777 ///
778 /// # Examples
779 ///
780 /// ```
781 /// let stmt = ocre::Query::<()>::table("tasks").order_in("status", ["urgent", "open"]).to_statement();
782 /// assert_eq!(stmt.sql, "SELECT * FROM tasks ORDER BY CASE status WHEN ?1 THEN 0 WHEN ?2 THEN 1 ELSE 2 END");
783 /// ```
784 pub fn order_in<V: IntoParam>(mut self, column: &'static str, values: impl IntoIterator<Item = V>) -> Self {
785 self.parts.order_in(column, values.into_iter().map(IntoParam::into_param).collect());
786 self
787 }
788
789 /// Adds a raw `ORDER BY` term, e.g. `lower(title)` or `published_at DESC NULLS LAST`.
790 ///
791 /// # Examples
792 ///
793 /// ```
794 /// let stmt = ocre::Query::<()>::table("posts").order_sql("lower(title) ASC").to_statement();
795 /// assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY lower(title) ASC");
796 /// ```
797 pub fn order_sql(mut self, term: &'static str) -> Self {
798 self.parts.order.push(term.to_owned());
799 self
800 }
801
802 /// Removes the order set so far (Rails' `reorder` when followed by a new order).
803 ///
804 /// # Examples
805 ///
806 /// ```
807 /// let stmt = ocre::Query::<()>::table("posts").order_desc("id").reorder().order_asc("title").to_statement();
808 /// assert_eq!(stmt.sql, "SELECT * FROM posts ORDER BY title ASC");
809 /// ```
810 pub fn reorder(mut self) -> Self {
811 self.parts.order.clear();
812 self.parts.order_params.clear();
813 self
814 }
815
816 /// Returns at most `limit` rows.
817 ///
818 /// # Examples
819 ///
820 /// ```
821 /// let stmt = ocre::Query::<()>::table("posts").limit(5).to_statement();
822 /// assert_eq!(stmt.sql, "SELECT * FROM posts LIMIT ?1");
823 /// ```
824 pub fn limit(mut self, limit: i64) -> Self {
825 self.parts.limit = Some(limit);
826 self
827 }
828
829 /// Skips the first `offset` rows.
830 ///
831 /// # Examples
832 ///
833 /// ```
834 /// let stmt = ocre::Query::<()>::table("posts").offset(10).to_statement();
835 /// assert_eq!(stmt.sql, "SELECT * FROM posts LIMIT -1 OFFSET ?1");
836 /// ```
837 pub fn offset(mut self, offset: i64) -> Self {
838 self.parts.offset = Some(offset);
839 self
840 }
841
842 /// Limit and offset from a [`Page`] (`?limit=&offset=`).
843 ///
844 /// # Examples
845 ///
846 /// ```
847 /// use ocre::{Page, Query, params};
848 ///
849 /// let stmt = Query::<()>::table("posts").page(Page { limit: 20, offset: 40 }).to_statement();
850 /// assert_eq!(stmt.sql, "SELECT * FROM posts LIMIT ?1 OFFSET ?2");
851 /// assert_eq!(stmt.params, params![20, 40]);
852 /// ```
853 pub fn page(self, page: Page) -> Self {
854 self.limit(page.limit).offset(page.offset)
855 }
856
857 /// `EXPLAIN QUERY PLAN` of the `SELECT` (Rails' `explain`): how SQLite
858 /// finds the rows, e.g. `SEARCH posts USING INDEX index_posts_on_author_id (author_id=?)`
859 /// or a full `SCAN posts`.
860 ///
861 /// Run it with [`explain`](Self::explain), or print the SQL and run it with
862 /// `ocre sql "EXPLAIN QUERY PLAN ..."` on the local database.
863 ///
864 /// # Examples
865 ///
866 /// ```
867 /// let stmt = ocre::Query::<()>::table("posts").eq("author_id", 3).explain_statement();
868 /// assert_eq!(stmt.sql, "EXPLAIN QUERY PLAN SELECT * FROM posts WHERE author_id = ?1");
869 /// ```
870 pub fn explain_statement(&self) -> Statement {
871 let stmt = self.parts.select_statement();
872 Statement { sql: format!("EXPLAIN QUERY PLAN {}", stmt.sql), params: stmt.params }
873 }
874
875 /// Walks the matching rows in batches of `size`, ordered by id (Rails'
876 /// `find_in_batches` / `in_batches`, and `find_each` with a loop over each batch).
877 ///
878 /// `id` reads a row's primary key (`|post| post.id`): each batch starts
879 /// after the last id of the previous one (keyset pagination: `WHERE id >
880 /// last ORDER BY id LIMIT size`), so batches stay cheap however far they
881 /// go, unlike `OFFSET`. The query's own order and limits are replaced;
882 /// for Rails' `start:` / `finish:` add `gte("id", start)` / `lte("id", finish)`.
883 /// [`Batches::next`] runs one batch.
884 ///
885 /// # Free plan
886 ///
887 /// Each batch is one query reading `size` rows. A Worker invocation may
888 /// run 50 D1 queries on the free plan and has 10 ms of CPU: walk large
889 /// tables from a job or scheduled task, a few batches per invocation
890 /// (keep [`Batches::after`] to resume in the next one).
891 ///
892 /// # Examples
893 ///
894 /// ```
895 /// use ocre::Query;
896 ///
897 /// #[derive(serde::Deserialize)]
898 /// struct Post {
899 /// id: i64,
900 /// }
901 ///
902 /// let mut batches = Query::<Post>::table("posts").eq("published", true).batches(500, |post| post.id);
903 /// assert_eq!(
904 /// batches.statement().sql,
905 /// "SELECT * FROM posts WHERE published = ?1 ORDER BY posts.id ASC LIMIT ?2"
906 /// );
907 /// batches.advance(&[Post { id: 7 }, Post { id: 9 }]);
908 /// assert_eq!(batches.after(), Some(9));
909 /// assert_eq!(
910 /// batches.statement().sql,
911 /// "SELECT * FROM posts WHERE published = ?1 AND posts.id > ?2 ORDER BY posts.id ASC LIMIT ?3"
912 /// );
913 /// ```
914 pub fn batches(self, size: i64, id: fn(&T) -> i64) -> Batches<T> {
915 Batches { query: self, size: size.max(1), id, after: None, done: false }
916 }
917
918 /// The `SELECT` statement, with placeholders numbered `?1, ?2...`.
919 ///
920 /// Terminal methods ([`all`](Self::all), [`first`](Self::first)...) run
921 /// it; use it directly for [`Db::batch`](crate::Db::batch) or logging.
922 ///
923 /// # Examples
924 ///
925 /// ```
926 /// let stmt = ocre::Query::<()>::table("posts").eq("id", 1).to_statement();
927 /// assert_eq!(stmt.sql, "SELECT * FROM posts WHERE id = ?1");
928 /// ```
929 pub fn to_statement(&self) -> Statement {
930 self.parts.select_statement()
931 }
932
933 /// `SELECT COUNT(*) AS count` of the matching rows, ignoring order, limit
934 /// and offset. With [`group_by`](Self::group_by) or
935 /// [`distinct`](Self::distinct), counts the groups or distinct rows.
936 ///
937 /// # Examples
938 ///
939 /// ```
940 /// use ocre::Query;
941 ///
942 /// let query = Query::<()>::table("posts").eq("published", true).order_desc("id").limit(10);
943 /// assert_eq!(query.count_statement().sql, "SELECT COUNT(*) AS count FROM posts WHERE published = ?1");
944 /// let authors = Query::<()>::table("posts").select::<()>("author_id").distinct();
945 /// assert_eq!(
946 /// authors.count_statement().sql,
947 /// "SELECT COUNT(*) AS count FROM (SELECT DISTINCT author_id FROM posts)"
948 /// );
949 /// ```
950 pub fn count_statement(&self) -> Statement {
951 self.parts.count_statement()
952 }
953
954 /// `SELECT 1 ... LIMIT 1`: whether any row matches, stopping at the first.
955 ///
956 /// # Examples
957 ///
958 /// ```
959 /// let stmt = ocre::Query::<()>::table("users").eq("email", "a@b.co").exists_statement();
960 /// assert_eq!(stmt.sql, "SELECT 1 FROM users WHERE email = ?1 LIMIT 1");
961 /// ```
962 pub fn exists_statement(&self) -> Statement {
963 self.parts.exists_statement()
964 }
965
966 /// `SELECT <expression> AS value`, keeping conditions, order and limits:
967 /// one column of every matching row (Rails' `pluck`), or with an
968 /// aggregate expression (`SUM(price)`) the calculation over the matching
969 /// rows (order and limits are dropped then, as they do not apply).
970 ///
971 /// # Examples
972 ///
973 /// ```
974 /// use ocre::Query;
975 ///
976 /// let query = Query::<()>::table("posts").eq("published", true).order_desc("id").limit(3);
977 /// assert_eq!(
978 /// query.value_statement("title").sql,
979 /// "SELECT title AS value FROM posts WHERE published = ?1 ORDER BY id DESC LIMIT ?2"
980 /// );
981 /// assert_eq!(query.aggregate_statement("SUM(views)").sql, "SELECT SUM(views) AS value FROM posts WHERE published = ?1");
982 /// ```
983 pub fn value_statement(&self, expression: &'static str) -> Statement {
984 self.parts.value_statement(expression, false)
985 }
986
987 /// `SELECT <aggregate> AS value` over the matching rows, without order or limits.
988 ///
989 /// See [`value_statement`](Self::value_statement).
990 ///
991 /// # Examples
992 ///
993 /// ```
994 /// let stmt = ocre::Query::<()>::table("products").aggregate_statement("MAX(price)");
995 /// assert_eq!(stmt.sql, "SELECT MAX(price) AS value FROM products");
996 /// ```
997 pub fn aggregate_statement(&self, expression: &'static str) -> Statement {
998 self.parts.value_statement(expression, true)
999 }
1000
1001 /// `UPDATE <table> SET ... WHERE ...` on the matching rows (Rails'
1002 /// `update_all`): no validation, no `updated_at` change unless listed.
1003 ///
1004 /// Joins, order and limits are not part of the statement.
1005 ///
1006 /// # Examples
1007 ///
1008 /// ```
1009 /// use ocre::{IntoParam, Query, params};
1010 ///
1011 /// let stmt = Query::<()>::table("posts")
1012 /// .eq("author_id", 3)
1013 /// .update_statement(vec![("published", false.into_param()), ("updated_at", "2026-09-29 10:00:00".into_param())]);
1014 /// assert_eq!(stmt.sql, "UPDATE posts SET published = ?1, updated_at = ?2 WHERE author_id = ?3");
1015 /// assert_eq!(stmt.params, params![false, "2026-09-29 10:00:00", 3]);
1016 /// ```
1017 pub fn update_statement(&self, sets: Vec<(&'static str, Param)>) -> Statement {
1018 self.parts.update_statement(sets)
1019 }
1020
1021 /// `DELETE FROM <table> WHERE ...` on the matching rows (Rails' `delete_all`).
1022 ///
1023 /// Joins, order and limits are not part of the statement. Without any
1024 /// condition, it deletes every row of the table.
1025 ///
1026 /// # Examples
1027 ///
1028 /// ```
1029 /// let stmt = ocre::Query::<()>::table("sessions").lt("expires_at", 1_790_000_000).delete_statement();
1030 /// assert_eq!(stmt.sql, "DELETE FROM sessions WHERE expires_at < ?1");
1031 /// ```
1032 pub fn delete_statement(&self) -> Statement {
1033 self.parts.delete_statement()
1034 }
1035}
1036
1037impl Parts {
1038 fn push_condition(&mut self, sql: String, params: Vec<Param>) {
1039 self.conditions.push(sql);
1040 self.params.extend(params);
1041 }
1042
1043 fn compare(&mut self, column: &str, operator: &str, value: Param) {
1044 self.push_condition(format!("{column} {operator} ?"), vec![value]);
1045 }
1046
1047 fn escaped_like(&mut self, column: &str, pattern: String) {
1048 self.push_condition(format!("{column} LIKE ? ESCAPE '\\'"), vec![pattern.into_param()]);
1049 }
1050
1051 fn list(&mut self, column: &str, operator: &str, values: Vec<Param>) {
1052 match (values.is_empty(), operator) {
1053 (true, "IN") => self.push_condition("0".to_owned(), vec![]),
1054 (true, _) => {}
1055 (false, _) => {
1056 let placeholders = vec!["?"; values.len()].join(", ");
1057 self.push_condition(format!("{column} {operator} ({placeholders})"), values);
1058 }
1059 }
1060 }
1061
1062 fn group(&mut self, group: Parts, joiner: &str, prefix: &str) {
1063 if group.conditions.is_empty() {
1064 return;
1065 }
1066 self.push_condition(format!("{prefix}({})", group.conditions.join(joiner)), group.params);
1067 }
1068
1069 fn order_in(&mut self, column: &str, values: Vec<Param>) {
1070 let mut term = format!("CASE {column}");
1071 for i in 0..values.len() {
1072 term.push_str(&format!(" WHEN ? THEN {i}"));
1073 }
1074 term.push_str(&format!(" ELSE {} END", values.len()));
1075 self.order.push(term);
1076 self.order_params.extend(values);
1077 }
1078
1079 /// `FROM <table> <joins> WHERE ...`, with its parameters.
1080 fn source(&self, sql: &mut String, params: &mut Vec<Param>) {
1081 sql.push_str(" FROM ");
1082 sql.push_str(self.table);
1083 for join in &self.joins {
1084 sql.push(' ');
1085 sql.push_str(join);
1086 }
1087 self.where_clause(sql, params);
1088 }
1089
1090 fn where_clause(&self, sql: &mut String, params: &mut Vec<Param>) {
1091 if !self.conditions.is_empty() {
1092 sql.push_str(" WHERE ");
1093 sql.push_str(&self.conditions.join(" AND "));
1094 params.extend(self.params.iter().cloned());
1095 }
1096 }
1097
1098 /// `GROUP BY` and `HAVING`.
1099 fn grouping(&self, sql: &mut String, params: &mut Vec<Param>) {
1100 if let Some(group) = self.group {
1101 sql.push_str(" GROUP BY ");
1102 sql.push_str(group);
1103 }
1104 if !self.having.is_empty() {
1105 sql.push_str(" HAVING ");
1106 sql.push_str(&self.having.join(" AND "));
1107 params.extend(self.having_params.iter().cloned());
1108 }
1109 }
1110
1111 /// `ORDER BY`, `LIMIT` and `OFFSET`.
1112 fn ordering(&self, sql: &mut String, params: &mut Vec<Param>) {
1113 if !self.order.is_empty() {
1114 sql.push_str(" ORDER BY ");
1115 sql.push_str(&self.order.join(", "));
1116 params.extend(self.order_params.iter().cloned());
1117 }
1118 match (self.limit, self.offset) {
1119 (Some(limit), Some(offset)) => {
1120 sql.push_str(" LIMIT ? OFFSET ?");
1121 params.extend([limit.into_param(), offset.into_param()]);
1122 }
1123 (Some(limit), None) => {
1124 sql.push_str(" LIMIT ?");
1125 params.push(limit.into_param());
1126 }
1127 (None, Some(offset)) => {
1128 sql.push_str(" LIMIT -1 OFFSET ?");
1129 params.push(offset.into_param());
1130 }
1131 (None, None) => {}
1132 }
1133 }
1134
1135 fn select_head(&self, columns: &str) -> String {
1136 let distinct = if self.distinct { "DISTINCT " } else { "" };
1137 format!("SELECT {distinct}{columns}")
1138 }
1139
1140 fn select_statement(&self) -> Statement {
1141 let mut params = Vec::new();
1142 let mut sql = self.select_head(self.select.as_deref().unwrap_or("*"));
1143 self.source(&mut sql, &mut params);
1144 self.grouping(&mut sql, &mut params);
1145 self.ordering(&mut sql, &mut params);
1146 numbered(sql, params)
1147 }
1148
1149 fn count_statement(&self) -> Statement {
1150 let mut params = Vec::new();
1151 if self.group.is_some() || self.distinct {
1152 let mut inner = self.select_head(self.select.as_deref().unwrap_or("*"));
1153 self.source(&mut inner, &mut params);
1154 self.grouping(&mut inner, &mut params);
1155 return numbered(format!("SELECT COUNT(*) AS count FROM ({inner})"), params);
1156 }
1157 let mut sql = "SELECT COUNT(*) AS count".to_owned();
1158 self.source(&mut sql, &mut params);
1159 numbered(sql, params)
1160 }
1161
1162 fn exists_statement(&self) -> Statement {
1163 let mut params = Vec::new();
1164 let mut sql = "SELECT 1".to_owned();
1165 self.source(&mut sql, &mut params);
1166 self.grouping(&mut sql, &mut params);
1167 sql.push_str(" LIMIT 1");
1168 numbered(sql, params)
1169 }
1170
1171 fn value_statement(&self, expression: &str, aggregate: bool) -> Statement {
1172 let mut params = Vec::new();
1173 let mut sql = self.select_head(&format!("{expression} AS value"));
1174 self.source(&mut sql, &mut params);
1175 self.grouping(&mut sql, &mut params);
1176 if !aggregate {
1177 self.ordering(&mut sql, &mut params);
1178 }
1179 numbered(sql, params)
1180 }
1181
1182 fn update_statement(&self, sets: Vec<(&'static str, Param)>) -> Statement {
1183 let mut params = Vec::with_capacity(sets.len() + self.params.len());
1184 let mut assignments = Vec::with_capacity(sets.len());
1185 for (column, value) in sets {
1186 assignments.push(format!("{column} = ?"));
1187 params.push(value);
1188 }
1189 let mut sql = format!("UPDATE {} SET {}", self.table, assignments.join(", "));
1190 self.where_clause(&mut sql, &mut params);
1191 numbered(sql, params)
1192 }
1193
1194 fn delete_statement(&self) -> Statement {
1195 let mut params = Vec::new();
1196 let mut sql = format!("DELETE FROM {}", self.table);
1197 self.where_clause(&mut sql, &mut params);
1198 numbered(sql, params)
1199 }
1200
1201 /// `EXISTS (SELECT 1 FROM <table> WHERE <table>.<fk> = <self.table>.id)`.
1202 fn child_exists(&self, table: &str, foreign_key: &str) -> String {
1203 format!("EXISTS (SELECT 1 FROM {table} WHERE {table}.{foreign_key} = {}.id)", self.table)
1204 }
1205
1206 fn reverse_order(&mut self) {
1207 if self.order.is_empty() {
1208 self.order.push(format!("{}.id DESC", self.table));
1209 return;
1210 }
1211 for term in &mut self.order {
1212 if let Some(column) = term.strip_suffix(" ASC") {
1213 *term = format!("{column} DESC");
1214 } else if let Some(column) = term.strip_suffix(" DESC") {
1215 *term = format!("{column} ASC");
1216 } else {
1217 term.push_str(" DESC");
1218 }
1219 }
1220 }
1221
1222 /// The next batch of [`Batches`]: after `after` by `<table>.id`, in id order.
1223 fn batch(&mut self, after: Option<i64>, size: i64) {
1224 let id = format!("{}.id", self.table);
1225 if let Some(after) = after {
1226 self.compare(&id, ">", after.into_param());
1227 }
1228 self.order = vec![format!("{id} ASC")];
1229 self.order_params.clear();
1230 self.limit = Some(size);
1231 self.offset = None;
1232 }
1233}
1234
1235/// Numbers the bare `?` placeholders of `sql` as `?1, ?2...`, skipping
1236/// quoted strings and identifiers.
1237fn numbered(sql: String, params: Vec<Param>) -> Statement {
1238 let mut out = String::with_capacity(sql.len() + 2 * params.len());
1239 let mut next = 1;
1240 let mut quote: Option<char> = None;
1241 let mut chars = sql.chars().peekable();
1242 while let Some(c) = chars.next() {
1243 out.push(c);
1244 match (quote, c) {
1245 (Some(q), c) if c == q => quote = None,
1246 (Some(_), _) => {}
1247 (None, '\'' | '"') => quote = Some(c),
1248 (None, '?') if !chars.peek().is_some_and(char::is_ascii_digit) => {
1249 out.push_str(&next.to_string());
1250 next += 1;
1251 }
1252 (None, _) => {}
1253 }
1254 }
1255 Statement::new(out, params)
1256}
1257
1258/// Escapes `%`, `_` and `\` so `text` matches literally in a `LIKE ... ESCAPE '\'`
1259/// pattern (Rails' `sanitize_sql_like`).
1260///
1261/// [`Query::contains`], [`starts_with`](Query::starts_with) and
1262/// [`ends_with`](Query::ends_with) call it; use it for hand-written SQL.
1263///
1264/// # Examples
1265///
1266/// ```
1267/// use ocre::{escape_like, params};
1268///
1269/// assert_eq!(escape_like("50%_off\\"), "50\\%\\_off\\\\");
1270/// let pattern = format!("%{}%", escape_like("50%"));
1271/// let sql = "SELECT * FROM products WHERE name LIKE ?1 ESCAPE '\\'";
1272/// # let _ = (sql, params![pattern]);
1273/// ```
1274pub fn escape_like(text: &str) -> String {
1275 let mut out = String::with_capacity(text.len());
1276 for c in text.chars() {
1277 if matches!(c, '%' | '_' | '\\') {
1278 out.push('\\');
1279 }
1280 out.push(c);
1281 }
1282 out
1283}
1284
1285/// A page of rows plus the total, for pagination links and JSON envelopes.
1286///
1287/// [`Query::paginate`] builds one with two queries (the rows and
1288/// `COUNT(*)`). Serializes as
1289/// `{"items": [...], "total": 42, "limit": 20, "offset": 0}`.
1290///
1291/// # Free plan
1292///
1293/// The count reads every matching row once more: on large tables, prefer
1294/// plain "next page" links without a total ([`Page::next`](crate::Page)
1295/// after [`Query::all`]) or cache the total.
1296///
1297/// # Examples
1298///
1299/// ```
1300/// use ocre::{Page, Paginated};
1301///
1302/// let page = Paginated { items: vec!["a", "b"], total: 5, limit: 2, offset: 2 };
1303/// assert_eq!(page.current_page(), 2);
1304/// assert_eq!(page.total_pages(), 3);
1305/// assert!(page.has_next() && page.has_previous());
1306/// assert_eq!(page.next_page(), Some(Page { limit: 2, offset: 4 }));
1307/// assert_eq!(page.previous_page(), Some(Page { limit: 2, offset: 0 }));
1308/// assert_eq!(serde_json::to_string(&page).unwrap(), r#"{"items":["a","b"],"total":5,"limit":2,"offset":2}"#);
1309/// ```
1310#[derive(Debug, Clone, PartialEq, Serialize)]
1311pub struct Paginated<T> {
1312 /// The rows of this page.
1313 pub items: Vec<T>,
1314 /// Rows matching the query, all pages together.
1315 pub total: i64,
1316 /// Rows per page.
1317 pub limit: i64,
1318 /// Rows skipped before this page.
1319 pub offset: i64,
1320}
1321
1322impl<T> Paginated<T> {
1323 /// 1-based number of this page.
1324 ///
1325 /// # Examples
1326 ///
1327 /// ```
1328 /// let page = ocre::Paginated::<()> { items: vec![], total: 0, limit: 10, offset: 0 };
1329 /// assert_eq!(page.current_page(), 1);
1330 /// ```
1331 pub fn current_page(&self) -> i64 {
1332 self.offset / self.limit.max(1) + 1
1333 }
1334
1335 /// Number of pages (at least 1, even with no row).
1336 ///
1337 /// # Examples
1338 ///
1339 /// ```
1340 /// let page = ocre::Paginated::<()> { items: vec![], total: 21, limit: 10, offset: 0 };
1341 /// assert_eq!(page.total_pages(), 3);
1342 /// ```
1343 pub fn total_pages(&self) -> i64 {
1344 ((self.total + self.limit - 1) / self.limit.max(1)).max(1)
1345 }
1346
1347 /// Whether rows follow this page.
1348 ///
1349 /// # Examples
1350 ///
1351 /// ```
1352 /// let page = ocre::Paginated::<()> { items: vec![], total: 10, limit: 10, offset: 0 };
1353 /// assert!(!page.has_next());
1354 /// ```
1355 pub fn has_next(&self) -> bool {
1356 self.offset + self.limit < self.total
1357 }
1358
1359 /// Whether rows come before this page.
1360 ///
1361 /// # Examples
1362 ///
1363 /// ```
1364 /// let page = ocre::Paginated::<()> { items: vec![], total: 10, limit: 10, offset: 0 };
1365 /// assert!(!page.has_previous());
1366 /// ```
1367 pub fn has_previous(&self) -> bool {
1368 self.offset > 0
1369 }
1370
1371 /// The next page, if any.
1372 ///
1373 /// # Examples
1374 ///
1375 /// ```
1376 /// let page = ocre::Paginated::<()> { items: vec![], total: 3, limit: 2, offset: 2 };
1377 /// assert_eq!(page.next_page(), None);
1378 /// ```
1379 pub fn next_page(&self) -> Option<Page> {
1380 self.has_next().then_some(Page { limit: self.limit, offset: self.offset + self.limit })
1381 }
1382
1383 /// The previous page, if any (never a negative offset).
1384 ///
1385 /// # Examples
1386 ///
1387 /// ```
1388 /// let page = ocre::Paginated::<()> { items: vec![], total: 30, limit: 10, offset: 5 };
1389 /// assert_eq!(page.previous_page(), Some(ocre::Page { limit: 10, offset: 0 }));
1390 /// ```
1391 pub fn previous_page(&self) -> Option<Page> {
1392 self.has_previous().then_some(Page { limit: self.limit, offset: (self.offset - self.limit).max(0) })
1393 }
1394
1395 /// Converts the items, keeping the counts (e.g. rows into view structs).
1396 ///
1397 /// # Examples
1398 ///
1399 /// ```
1400 /// let page = ocre::Paginated { items: vec![1, 2], total: 2, limit: 10, offset: 0 };
1401 /// assert_eq!(page.map(|n| n * 10).items, [10, 20]);
1402 /// ```
1403 pub fn map<U>(self, f: impl FnMut(T) -> U) -> Paginated<U> {
1404 Paginated {
1405 items: self.items.into_iter().map(f).collect(),
1406 total: self.total,
1407 limit: self.limit,
1408 offset: self.offset,
1409 }
1410 }
1411}
1412
1413/// Batches of rows by increasing id, from [`Query::batches`]: Rails'
1414/// `find_in_batches` without holding a cursor open.
1415///
1416/// [`next`](Self::next) runs one query and returns the next batch, or
1417/// `None` when every row was read. [`after`](Self::after) is the last id
1418/// seen: store it (in a job's arguments, in KV) to resume later with
1419/// [`resume_after`](Self::resume_after).
1420///
1421/// # Examples
1422///
1423/// ```no_run
1424/// use ocre::{Ctx, Query, Result};
1425///
1426/// #[derive(serde::Deserialize)]
1427/// struct User {
1428/// id: i64,
1429/// email: String,
1430/// }
1431///
1432/// // A scheduled task: at most 4 batches (4 queries) per run.
1433/// async fn send_digests(ctx: &Ctx, resume: Option<i64>) -> Result<Option<i64>> {
1434/// let db = ctx.db()?;
1435/// let mut batches = Query::<User>::table("users").batches(100, |user| user.id).resume_after(resume);
1436/// for _ in 0..4 {
1437/// let Some(users) = batches.next(&db).await? else { return Ok(None) };
1438/// for user in users {
1439/// // find_each: one row at a time.
1440/// let _ = user.email;
1441/// }
1442/// }
1443/// Ok(batches.after())
1444/// }
1445/// ```
1446pub struct Batches<T> {
1447 query: Query<T>,
1448 size: i64,
1449 id: fn(&T) -> i64,
1450 after: Option<i64>,
1451 done: bool,
1452}
1453
1454impl<T> Batches<T> {
1455 /// Starts after `id` (Rails' `start:`, exclusive), or from the first row with `None`.
1456 ///
1457 /// # Examples
1458 ///
1459 /// ```
1460 /// # #[derive(serde::Deserialize)] struct Post { id: i64 }
1461 /// let batches = ocre::Query::<Post>::table("posts").batches(100, |p| p.id).resume_after(Some(41));
1462 /// assert_eq!(batches.after(), Some(41));
1463 /// assert!(batches.statement().sql.contains("posts.id > ?1"));
1464 /// ```
1465 pub fn resume_after(mut self, id: Option<i64>) -> Self {
1466 self.after = id;
1467 self
1468 }
1469
1470 /// The last id read so far (`None` before the first batch).
1471 ///
1472 /// # Examples
1473 ///
1474 /// ```
1475 /// # #[derive(serde::Deserialize)] struct Post { id: i64 }
1476 /// assert_eq!(ocre::Query::<Post>::table("posts").batches(10, |p| p.id).after(), None);
1477 /// ```
1478 pub fn after(&self) -> Option<i64> {
1479 self.after
1480 }
1481
1482 /// Whether the last batch was shorter than the batch size: no row is left.
1483 ///
1484 /// # Examples
1485 ///
1486 /// ```
1487 /// # #[derive(serde::Deserialize)] struct Post { id: i64 }
1488 /// let mut batches = ocre::Query::<Post>::table("posts").batches(2, |p| p.id);
1489 /// batches.advance(&[Post { id: 1 }]);
1490 /// assert!(batches.is_done());
1491 /// ```
1492 pub fn is_done(&self) -> bool {
1493 self.done
1494 }
1495
1496 /// The `SELECT` of the next batch.
1497 ///
1498 /// # Examples
1499 ///
1500 /// ```
1501 /// # #[derive(serde::Deserialize)] struct Post { id: i64 }
1502 /// let batches = ocre::Query::<Post>::table("posts").limit(3).batches(10, |p| p.id);
1503 /// assert_eq!(batches.statement().sql, "SELECT * FROM posts ORDER BY posts.id ASC LIMIT ?1");
1504 /// ```
1505 pub fn statement(&self) -> Statement {
1506 let mut parts = self.query.parts.clone();
1507 parts.batch(self.after, self.size);
1508 parts.select_statement()
1509 }
1510
1511 /// Records a batch just read: its last id, and whether it was the last batch.
1512 ///
1513 /// [`next`](Self::next) calls it; call it yourself after running
1514 /// [`statement`](Self::statement) through [`Db::all`](crate::Db::all).
1515 ///
1516 /// # Examples
1517 ///
1518 /// ```
1519 /// # #[derive(serde::Deserialize)] struct Post { id: i64 }
1520 /// let mut batches = ocre::Query::<Post>::table("posts").batches(2, |p| p.id);
1521 /// batches.advance(&[Post { id: 3 }, Post { id: 5 }]);
1522 /// assert_eq!((batches.after(), batches.is_done()), (Some(5), false));
1523 /// ```
1524 pub fn advance(&mut self, rows: &[T]) {
1525 if let Some(last) = rows.last() {
1526 self.after = Some((self.id)(last));
1527 }
1528 self.done = (rows.len() as i64) < self.size;
1529 }
1530}
1531
1532#[cfg(test)]
1533#[path = "../tests/query.rs"]
1534mod tests;