toasty_driver_integration_suite/tests/
paginate_multi_column.rs

1//! Test keyset pagination over a multi-column `order_by`.
2//!
3//! The cursor must be compared lexicographically: a row that ties the cursor
4//! on a leading sort column but is beyond it on a later column belongs on the
5//! next page. These are gated on `sql` because SQL drivers rewrite the cursor
6//! into a WHERE filter; NoSQL drivers use a driver-level cursor instead.
7
8use crate::prelude::*;
9use toasty::{
10    Executor,
11    stmt::{IntoStatement, Page},
12};
13use toasty_core::stmt::{self, Value};
14
15fn cursor_len(cursor: Option<&Value>) -> usize {
16    let Some(Value::Record(cursor)) = cursor else {
17        panic!("expected a record cursor");
18    };
19    cursor.fields.len()
20}
21
22#[driver_test(requires(and(sql, backward_pagination)))]
23pub async fn paginate_first_page_has_no_previous_page(test: &mut Test) -> Result<()> {
24    #[derive(Debug, toasty::Model)]
25    struct Item {
26        #[key]
27        id: i64,
28    }
29
30    let mut db = test.setup_db(models!(Item)).await;
31    toasty::create!(Item::[{ id: 1 }, { id: 2 }])
32        .exec(&mut db)
33        .await?;
34
35    let first: Page<Item> = Item::all()
36        .order_by(Item::fields().id().asc())
37        .paginate(1)
38        .exec(&mut db)
39        .await?;
40
41    assert!(!first.has_prev());
42    assert!(first.prev(&mut db).await?.is_none());
43
44    Ok(())
45}
46
47// The outer query never references the CTE, so `cte_unreferenced` gates out
48// drivers that cannot prepare a statement whose eliminated CTE holds
49// placeholders.
50#[driver_test(requires(and(sql, backward_pagination, cte_unreferenced)))]
51pub async fn paginate_first_page_ignores_subquery_cursor(test: &mut Test) -> Result<()> {
52    #[derive(Debug, toasty::Model)]
53    struct Item {
54        #[key]
55        id: i64,
56    }
57
58    let mut db = test.setup_db(models!(Item)).await;
59    toasty::create!(Item::[{ id: 1 }, { id: 2 }])
60        .exec(&mut db)
61        .await?;
62
63    let mut inner = Item::all()
64        .order_by(Item::fields().id().asc())
65        .select((Item::fields().id(), Item::fields().id()))
66        .into_statement()
67        .into_untyped()
68        .into_query_unwrap();
69    inner.limit = Some(stmt::Limit::Cursor(stmt::LimitCursor {
70        page_size: stmt::Value::from(2_i64).into(),
71        after: Some(stmt::Value::from(0_i64).into()),
72    }));
73
74    let mut statement = Item::all()
75        .order_by(Item::fields().id().asc())
76        .into_statement()
77        .into_untyped();
78
79    let outer = statement.as_query_mut().unwrap();
80    outer.limit = Some(stmt::Limit::Cursor(stmt::LimitCursor {
81        page_size: stmt::Value::from(1_i64).into(),
82        after: None,
83    }));
84    outer.with = Some(stmt::With {
85        ctes: vec![stmt::Cte { query: inner }],
86    });
87
88    let response = db.exec_untyped(statement).await?;
89    assert!(response.prev_cursor.is_none());
90
91    Ok(())
92}
93
94#[driver_test(requires(and(sql, backward_pagination)))]
95pub async fn paginate_first_page_ignores_nested_statement_cursor(test: &mut Test) -> Result<()> {
96    #[derive(Debug, toasty::Model)]
97    struct Item {
98        #[key]
99        id: i64,
100    }
101
102    let mut db = test.setup_db(models!(Item)).await;
103    toasty::create!(Item::[{ id: 1 }, { id: 2 }])
104        .exec(&mut db)
105        .await?;
106
107    let mut nested = Item::all()
108        .order_by(Item::fields().id().asc())
109        .into_statement()
110        .into_untyped();
111    nested.as_query_mut().unwrap().limit = Some(stmt::Limit::Cursor(stmt::LimitCursor {
112        page_size: stmt::Value::from(1_i64).into(),
113        after: Some(stmt::Value::from(0_i64).into()),
114    }));
115
116    let mut statement = Item::all()
117        .order_by(Item::fields().id().asc())
118        .select((Item::fields().id(), Item::fields().id()))
119        .into_statement()
120        .into_untyped();
121    let outer = statement.as_query_mut().unwrap();
122    outer.limit = Some(stmt::Limit::Cursor(stmt::LimitCursor {
123        page_size: stmt::Value::from(1_i64).into(),
124        after: None,
125    }));
126
127    let stmt::Expr::Record(returning) = outer.returning_mut_unwrap().as_project_mut_unwrap() else {
128        panic!("expected a record projection");
129    };
130    returning.fields[1] = stmt::Expr::stmt(nested);
131
132    let response = db.exec_untyped(statement).await?;
133    assert!(response.prev_cursor.is_none());
134
135    Ok(())
136}
137
138#[driver_test(requires(sql))]
139pub async fn paginate_single_non_unique_column(test: &mut Test) -> Result<()> {
140    #[derive(Debug, toasty::Model)]
141    struct Item {
142        #[key]
143        id: i64,
144        group: i64,
145    }
146
147    let mut db = test.setup_db(models!(Item)).await;
148    toasty::create!(Item::[
149        { id: 1, group: 1 },
150        { id: 2, group: 1 },
151        { id: 3, group: 1 },
152        { id: 4, group: 1 },
153    ])
154    .exec(&mut db)
155    .await?;
156
157    let first: Page<Item> = Item::all()
158        .order_by(Item::fields().group().asc())
159        .paginate(2)
160        .exec(&mut db)
161        .await?;
162    assert_struct!(first.items, [{ id: 1 }, { id: 2 }]);
163    assert_eq!(cursor_len(first.next_cursor.as_ref()), 2);
164
165    let second = first.next(&mut db).await?.unwrap();
166    assert_struct!(second.items, [{ id: 3 }, { id: 4 }]);
167
168    let first_again = second.prev(&mut db).await?.unwrap();
169    assert_struct!(first_again.items, [{ id: 1 }, { id: 2 }]);
170
171    Ok(())
172}
173
174#[driver_test(requires(sql))]
175pub async fn paginate_non_unique_column_with_newtype_primary_key(test: &mut Test) -> Result<()> {
176    #[derive(Debug, toasty::Embed)]
177    struct ItemId(i64);
178
179    #[derive(Debug, toasty::Model)]
180    struct Item {
181        #[key]
182        id: ItemId,
183        group: i64,
184    }
185
186    let mut db = test.setup_db(models!(Item)).await;
187    toasty::create!(Item::[
188        { id: ItemId(1), group: 1 },
189        { id: ItemId(2), group: 1 },
190        { id: ItemId(3), group: 1 },
191    ])
192    .exec(&mut db)
193    .await?;
194
195    let first: Page<Item> = Item::all()
196        .order_by(Item::fields().group().asc())
197        .paginate(2)
198        .exec(&mut db)
199        .await?;
200    assert_eq!(
201        first.items.iter().map(|item| item.id.0).collect::<Vec<_>>(),
202        [1, 2]
203    );
204    assert_eq!(cursor_len(first.next_cursor.as_ref()), 2);
205
206    let second = first.next(&mut db).await?.unwrap();
207    assert_eq!(
208        second
209            .items
210            .iter()
211            .map(|item| item.id.0)
212            .collect::<Vec<_>>(),
213        [3]
214    );
215
216    Ok(())
217}
218
219#[driver_test(requires(sql))]
220pub async fn paginate_accepts_cursor_without_hidden_primary_key(test: &mut Test) -> Result<()> {
221    #[derive(Debug, toasty::Model)]
222    struct Item {
223        #[key]
224        id: i64,
225        group: i64,
226    }
227
228    let mut db = test.setup_db(models!(Item)).await;
229    toasty::create!(Item::[
230        { id: 1, group: 1 },
231        { id: 2, group: 1 },
232        { id: 3, group: 1 },
233    ])
234    .exec(&mut db)
235    .await?;
236
237    let first: Page<Item> = Item::all()
238        .order_by(Item::fields().group().asc())
239        .paginate(2)
240        .after(0_i64)
241        .exec(&mut db)
242        .await?;
243    assert_struct!(first.items, [{ id: 1 }, { id: 2 }]);
244    assert_eq!(cursor_len(first.next_cursor.as_ref()), 2);
245
246    let second = first.next(&mut db).await?.unwrap();
247    assert_struct!(second.items, [{ id: 3 }]);
248
249    Ok(())
250}
251
252#[driver_test(requires(sql))]
253pub async fn paginate_unique_non_nullable_column_needs_no_tiebreaker(
254    test: &mut Test,
255) -> Result<()> {
256    #[derive(Debug, toasty::Model)]
257    struct Item {
258        #[key]
259        id: i64,
260        #[unique]
261        rank: i64,
262    }
263
264    let mut db = test.setup_db(models!(Item)).await;
265    toasty::create!(Item::[
266        { id: 1, rank: 10 },
267        { id: 2, rank: 20 },
268        { id: 3, rank: 30 },
269    ])
270    .exec(&mut db)
271    .await?;
272
273    let page: Page<Item> = Item::all()
274        .order_by(Item::fields().rank().asc())
275        .paginate(2)
276        .exec(&mut db)
277        .await?;
278    assert_eq!(cursor_len(page.next_cursor.as_ref()), 1);
279
280    Ok(())
281}
282
283#[driver_test(requires(sql))]
284pub async fn paginate_composite_unique_non_nullable_order_needs_no_tiebreaker(
285    test: &mut Test,
286) -> Result<()> {
287    #[derive(Debug, toasty::Model)]
288    #[unique(group, rank)]
289    struct Item {
290        #[key]
291        id: i64,
292        group: i64,
293        rank: i64,
294    }
295
296    let mut db = test.setup_db(models!(Item)).await;
297    toasty::create!(Item::[
298        { id: 1, group: 1, rank: 10 },
299        { id: 2, group: 1, rank: 20 },
300        { id: 3, group: 2, rank: 10 },
301    ])
302    .exec(&mut db)
303    .await?;
304
305    let page: Page<Item> = Item::all()
306        .order_by((Item::fields().group().asc(), Item::fields().rank().asc()))
307        .paginate(2)
308        .exec(&mut db)
309        .await?;
310    assert_eq!(cursor_len(page.next_cursor.as_ref()), 2);
311
312    Ok(())
313}
314
315#[driver_test(requires(sql))]
316pub async fn paginate_nullable_unique_column_uses_primary_key(test: &mut Test) -> Result<()> {
317    #[derive(Debug, toasty::Model)]
318    struct Item {
319        #[key]
320        id: i64,
321        #[unique]
322        rank: Option<i64>,
323    }
324
325    let mut db = test.setup_db(models!(Item)).await;
326    toasty::create!(Item::[
327        { id: 1, rank: None },
328        { id: 2, rank: None },
329        { id: 3, rank: Some(10) },
330        { id: 4, rank: Some(20) },
331    ])
332    .exec(&mut db)
333    .await?;
334
335    let mut page: Page<Item> = Item::all()
336        .order_by(Item::fields().rank().asc())
337        .paginate(1)
338        .exec(&mut db)
339        .await?;
340    assert_eq!(cursor_len(page.next_cursor.as_ref()), 2);
341
342    let mut ids = Vec::new();
343    loop {
344        ids.extend(page.iter().map(|item| item.id));
345        let Some(next) = page.next(&mut db).await? else {
346            break;
347        };
348        page = next;
349    }
350    ids.sort_unstable();
351    assert_eq!(ids, [1, 2, 3, 4]);
352
353    Ok(())
354}
355
356#[driver_test(requires(sql))]
357pub async fn paginate_multi_column_equal_leading_values(test: &mut Test) -> Result<()> {
358    #[derive(Debug, toasty::Model)]
359    struct Item {
360        #[key]
361        id: i64,
362        group: i64,
363    }
364
365    let mut db = test.setup_db(models!(Item)).await;
366
367    // All rows share the same leading sort value.
368    toasty::create!(Item::[
369        { id: 1, group: 1 },
370        { id: 2, group: 1 },
371        { id: 3, group: 1 },
372    ])
373    .exec(&mut db)
374    .await?;
375
376    let page: Page<Item> = Item::all()
377        .order_by((Item::fields().group().asc(), Item::fields().id().asc()))
378        .paginate(2)
379        .exec(&mut db)
380        .await?;
381    assert_struct!(page.items, [{ id: 1 }, { id: 2 }]);
382
383    let page: Page<Item> = page.next(&mut db).await?.unwrap();
384    assert_struct!(page.items, [{ id: 3 }]);
385
386    Ok(())
387}
388
389#[driver_test(requires(sql))]
390pub async fn paginate_multi_column_mixed_directions(test: &mut Test) -> Result<()> {
391    #[derive(Debug, toasty::Model)]
392    struct Item {
393        #[key]
394        id: i64,
395        group: i64,
396    }
397
398    let mut db = test.setup_db(models!(Item)).await;
399
400    toasty::create!(Item::[
401        { id: 1, group: 1 },
402        { id: 2, group: 1 },
403        { id: 3, group: 2 },
404        { id: 4, group: 2 },
405    ])
406    .exec(&mut db)
407    .await?;
408
409    // group desc, id asc: 3, 4, 1, 2
410    let page: Page<Item> = Item::all()
411        .order_by((Item::fields().group().desc(), Item::fields().id().asc()))
412        .paginate(2)
413        .exec(&mut db)
414        .await?;
415    assert_struct!(page.items, [{ id: 3 }, { id: 4 }]);
416
417    let page: Page<Item> = page.next(&mut db).await?.unwrap();
418    assert_struct!(page.items, [{ id: 1 }, { id: 2 }]);
419
420    Ok(())
421}
toasty_driver_integration_suite/tests/paginate_multi_column.rs

toasty_driver_integration_suite/tests/
paginate_multi_column.rs
