Skip to main content

sea_orm/executor/
cursor.rs

1use crate::{
2    ConnectionTrait, DbErr, EntityTrait, FromQueryResult, Identity, IdentityOf, IntoIdentity,
3    PartialModelTrait, PrimaryKeyToColumn, QuerySelect, Select, SelectModel, SelectThree,
4    SelectThreeModel, SelectTwo, SelectTwoModel, SelectorTrait, Topology,
5};
6use sea_query::{
7    Condition, DynIden, Expr, ExprTrait, IntoValueTuple, Order, SeaRc, SelectStatement, SimpleExpr,
8    Value, ValueTuple,
9};
10use std::marker::PhantomData;
11use strum::IntoEnumIterator as Iterable;
12
13#[cfg(feature = "with-json")]
14use crate::JsonValue;
15
16/// Keyset (cursor) pagination over an ordered query.
17///
18/// Unlike [`Paginator`](crate::Paginator) which uses `LIMIT` / `OFFSET`,
19/// `Cursor` paginates by filtering on the configured order column(s) — fast
20/// and stable across concurrent inserts. Build one with
21/// [`Select::cursor_by`](crate::Select::cursor_by) (or
22/// [`CursorTrait`]-aware helpers on other selects), then drive it with
23/// [`before`](Self::before) / [`after`](Self::after) /
24/// [`first`](Self::first) / [`last`](Self::last) before calling
25/// `.all(db)` / `.one(db)`.
26#[derive(Debug, Clone)]
27pub struct Cursor<S>
28where
29    S: SelectorTrait,
30{
31    query: SelectStatement,
32    table: DynIden,
33    order_columns: Identity,
34    secondary_order_by: Vec<(DynIden, Identity)>,
35    first: Option<u64>,
36    last: Option<u64>,
37    before: Option<ValueTuple>,
38    after: Option<ValueTuple>,
39    sort_asc: bool,
40    is_result_reversed: bool,
41    phantom: PhantomData<S>,
42}
43
44impl<S> Cursor<S>
45where
46    S: SelectorTrait,
47{
48    pub(crate) fn new<C>(query: SelectStatement, table: DynIden, order_columns: C) -> Self
49    where
50        C: IntoIdentity,
51    {
52        Self {
53            query,
54            table,
55            order_columns: order_columns.into_identity(),
56            secondary_order_by: Default::default(),
57            last: None,
58            first: None,
59            after: None,
60            before: None,
61            sort_asc: true,
62            is_result_reversed: false,
63            phantom: PhantomData,
64        }
65    }
66
67    /// Filter paginated result with corresponding column less than the input value
68    pub fn before<V>(&mut self, values: V) -> &mut Self
69    where
70        V: IntoValueTuple,
71    {
72        self.before = Some(values.into_value_tuple());
73        self
74    }
75
76    /// Filter paginated result with corresponding column greater than the input value
77    pub fn after<V>(&mut self, values: V) -> &mut Self
78    where
79        V: IntoValueTuple,
80    {
81        self.after = Some(values.into_value_tuple());
82        self
83    }
84
85    fn apply_filters(&mut self) -> Result<&mut Self, DbErr> {
86        if let Some(values) = self.after.clone() {
87            if self.order_columns.arity() != values.arity() {
88                return Err(DbErr::KeyArityMismatch {
89                    expected: self.order_columns.arity() as u8,
90                    received: values.arity() as u8,
91                });
92            }
93            let condition = self.apply_filter(values, |c, v| {
94                let exp = Expr::col((self.table.clone(), c.clone()));
95                if self.sort_asc { exp.gt(v) } else { exp.lt(v) }
96            });
97            self.query.cond_where(condition);
98        }
99
100        if let Some(values) = self.before.clone() {
101            if self.order_columns.arity() != values.arity() {
102                return Err(DbErr::KeyArityMismatch {
103                    expected: self.order_columns.arity() as u8,
104                    received: values.arity() as u8,
105                });
106            }
107            let condition = self.apply_filter(values, |c, v| {
108                let exp = Expr::col((self.table.clone(), c.clone()));
109                if self.sort_asc { exp.lt(v) } else { exp.gt(v) }
110            });
111            self.query.cond_where(condition);
112        }
113
114        Ok(self)
115    }
116
117    fn apply_filter<F>(&self, values: ValueTuple, f: F) -> Condition
118    where
119        F: Fn(&DynIden, Value) -> SimpleExpr,
120    {
121        match (&self.order_columns, values) {
122            (Identity::Unary(c1), ValueTuple::One(v1)) => Condition::all().add(f(c1, v1)),
123            (Identity::Binary(c1, c2), ValueTuple::Two(v1, v2)) => Condition::any()
124                .add(
125                    Condition::all()
126                        .add(Expr::col((self.table.clone(), c1.clone())).eq(v1.clone()))
127                        .add(f(c2, v2)),
128                )
129                .add(f(c1, v1)),
130            (Identity::Ternary(c1, c2, c3), ValueTuple::Three(v1, v2, v3)) => Condition::any()
131                .add(
132                    Condition::all()
133                        .add(Expr::col((self.table.clone(), c1.clone())).eq(v1.clone()))
134                        .add(Expr::col((self.table.clone(), c2.clone())).eq(v2.clone()))
135                        .add(f(c3, v3)),
136                )
137                .add(
138                    Condition::all()
139                        .add(Expr::col((self.table.clone(), c1.clone())).eq(v1.clone()))
140                        .add(f(c2, v2)),
141                )
142                .add(f(c1, v1)),
143            (Identity::Many(col_vec), ValueTuple::Many(val_vec))
144                if col_vec.len() == val_vec.len() =>
145            {
146                // The length of `col_vec` and `val_vec` should be equal and is denoted by "n".
147                //
148                // The elements of `col_vec` and `val_vec` are denoted by:
149                //   - `col_vec`: "col_1", "col_2", ..., "col_n-1", "col_n"
150                //   - `val_vec`: "val_1", "val_2", ..., "val_n-1", "val_n"
151                //
152                // The general form of the where condition should have "n" number of inner-AND-condition chained by an outer-OR-condition.
153                // The "n"-th inner-AND-condition should have exactly "n" number of column value expressions,
154                // to construct the expression we take the first "n" number of column and value from the respected vector.
155                //   - if it's not the last element, then we construct a "col_1 = val_1" equal expression
156                //   - otherwise, for the last element, we should construct a "col_n > val_n" greater than or "col_n < val_n" less than expression.
157                // i.e.
158                // WHERE
159                //   (col_1 = val_1 AND col_2 = val_2 AND ... AND col_n > val_n)
160                //   OR (col_1 = val_1 AND col_2 = val_2 AND ... AND col_n-1 > val_n-1)
161                //   OR (col_1 = val_1 AND col_2 = val_2 AND ... AND col_n-2 > val_n-2)
162                //   OR ...
163                //   OR (col_1 = val_1 AND col_2 > val_2)
164                //   OR (col_1 > val_1)
165
166                // Counting from 1 to "n" (inclusive) but in reverse, i.e. n, n-1, ..., 2, 1
167                (1..=col_vec.len())
168                    .rev()
169                    .fold(Condition::any(), |cond_any, n| {
170                        // Construct the inner-AND-condition
171                        let inner_cond_all =
172                            // Take the first "n" elements from the column and value vector respectively
173                            col_vec.iter().zip(val_vec.iter()).enumerate().take(n).fold(
174                                Condition::all(),
175                                |inner_cond_all, (i, (col, val))| {
176                                    let val = val.clone();
177                                    // Construct a equal expression,
178                                    // except for the last one being greater than or less than expression
179                                    let expr = if i != (n - 1) {
180                                        Expr::col((self.table.clone(), col.clone()))
181                                            .eq(val)
182                                    } else {
183                                        f(col, val)
184                                    };
185                                    // Chain it with AND operator
186                                    inner_cond_all.add(expr)
187                                },
188                            );
189                        // Chain inner-AND-condition with OR operator
190                        cond_any.add(inner_cond_all)
191                    })
192            }
193            _ => unreachable!("checked by caller"),
194        }
195    }
196
197    /// Use ascending sort order
198    pub fn asc(&mut self) -> &mut Self {
199        self.sort_asc = true;
200        self
201    }
202
203    /// Use descending sort order
204    pub fn desc(&mut self) -> &mut Self {
205        self.sort_asc = false;
206        self
207    }
208
209    /// Limit result set to only first N rows in ascending order of the order by column
210    pub fn first(&mut self, num_rows: u64) -> &mut Self {
211        self.last = None;
212        self.first = Some(num_rows);
213        self
214    }
215
216    /// Limit result set to only last N rows in ascending order of the order by column
217    pub fn last(&mut self, num_rows: u64) -> &mut Self {
218        self.first = None;
219        self.last = Some(num_rows);
220        self
221    }
222
223    fn resolve_sort_order(&mut self) -> Order {
224        let should_reverse_order = self.last.is_some();
225        self.is_result_reversed = should_reverse_order;
226
227        if (self.sort_asc && !should_reverse_order) || (!self.sort_asc && should_reverse_order) {
228            Order::Asc
229        } else {
230            Order::Desc
231        }
232    }
233
234    fn apply_limit(&mut self) -> &mut Self {
235        if let Some(num_rows) = self.first {
236            self.query.limit(num_rows);
237        } else if let Some(num_rows) = self.last {
238            self.query.limit(num_rows);
239        }
240
241        self
242    }
243
244    fn apply_order_by(&mut self) -> &mut Self {
245        self.query.clear_order_by();
246        let ord = self.resolve_sort_order();
247
248        let query = &mut self.query;
249        let order = |query: &mut SelectStatement, col: &DynIden| {
250            query.order_by((self.table.clone(), col.clone()), ord.clone());
251        };
252        match &self.order_columns {
253            Identity::Unary(c1) => {
254                order(query, c1);
255            }
256            Identity::Binary(c1, c2) => {
257                order(query, c1);
258                order(query, c2);
259            }
260            Identity::Ternary(c1, c2, c3) => {
261                order(query, c1);
262                order(query, c2);
263                order(query, c3);
264            }
265            Identity::Many(vec) => {
266                for col in vec.iter() {
267                    order(query, col);
268                }
269            }
270        }
271
272        for (tbl, col) in self.secondary_order_by.iter().cloned() {
273            if let Identity::Unary(c1) = col {
274                query.order_by((tbl, c1), ord.clone());
275            }
276        }
277
278        self
279    }
280
281    /// Fetch the paginated result
282    pub fn all<C>(&mut self, db: &C) -> Result<Vec<S::Item>, DbErr>
283    where
284        C: ConnectionTrait,
285    {
286        self.apply_limit();
287        self.apply_order_by();
288        self.apply_filters()?;
289
290        let rows = db.query_all(&self.query)?;
291        let mut buffer = Vec::with_capacity(rows.len());
292        for row in rows.into_iter() {
293            buffer.push(S::from_raw_query_result(row)?);
294        }
295        if self.is_result_reversed {
296            buffer.reverse()
297        }
298        Ok(buffer)
299    }
300
301    /// Construct a [Cursor] that fetch any custom struct
302    pub fn into_model<M>(self) -> Cursor<SelectModel<M>>
303    where
304        M: FromQueryResult,
305    {
306        Cursor {
307            query: self.query,
308            table: self.table,
309            order_columns: self.order_columns,
310            secondary_order_by: self.secondary_order_by,
311            last: self.last,
312            first: self.first,
313            after: self.after,
314            before: self.before,
315            sort_asc: self.sort_asc,
316            is_result_reversed: self.is_result_reversed,
317            phantom: PhantomData,
318        }
319    }
320
321    /// Restrict the cursor's projection to a [`PartialModelTrait`] type `M`,
322    /// returning a [`Cursor`] that yields `M` instead of the full model.
323    pub fn into_partial_model<M>(self) -> Cursor<SelectModel<M>>
324    where
325        M: PartialModelTrait,
326    {
327        M::select_cols(QuerySelect::select_only(self)).into_model::<M>()
328    }
329
330    /// Construct a [Cursor] that fetch JSON value
331    #[cfg(feature = "with-json")]
332    pub fn into_json(self) -> Cursor<SelectModel<JsonValue>> {
333        Cursor {
334            query: self.query,
335            table: self.table,
336            order_columns: self.order_columns,
337            secondary_order_by: self.secondary_order_by,
338            last: self.last,
339            first: self.first,
340            after: self.after,
341            before: self.before,
342            sort_asc: self.sort_asc,
343            is_result_reversed: self.is_result_reversed,
344            phantom: PhantomData,
345        }
346    }
347
348    /// Set the cursor ordering for another table when dealing with SelectTwo
349    pub fn set_secondary_order_by(&mut self, tbl_col: Vec<(DynIden, Identity)>) -> &mut Self {
350        self.secondary_order_by = tbl_col;
351        self
352    }
353}
354
355impl<S> QuerySelect for Cursor<S>
356where
357    S: SelectorTrait,
358{
359    type QueryStatement = SelectStatement;
360
361    fn query(&mut self) -> &mut SelectStatement {
362        &mut self.query
363    }
364}
365
366/// Extension trait that adds `.cursor_by(...)` to anything that can be
367/// paginated with a keyset cursor — implemented for [`Select`],
368/// [`SelectTwo`], [`SelectThree`].
369pub trait CursorTrait {
370    /// The [`SelectorTrait`] used to materialise each row of the cursor.
371    type Selector: SelectorTrait;
372}
373
374impl<E, M> CursorTrait for Select<E>
375where
376    E: EntityTrait<Model = M>,
377    M: FromQueryResult + Sized,
378{
379    type Selector = SelectModel<M>;
380}
381
382impl<E, M> Select<E>
383where
384    E: EntityTrait<Model = M>,
385    M: FromQueryResult + Sized,
386{
387    /// Convert into a cursor
388    pub fn cursor_by<C>(self, order_columns: C) -> Cursor<SelectModel<M>>
389    where
390        C: IntoIdentity,
391    {
392        Cursor::new(self.query, SeaRc::new(E::default()), order_columns)
393    }
394}
395
396impl<E, F, M, N> CursorTrait for SelectTwo<E, F>
397where
398    E: EntityTrait<Model = M>,
399    F: EntityTrait<Model = N>,
400    M: FromQueryResult + Sized,
401    N: FromQueryResult + Sized,
402{
403    type Selector = SelectTwoModel<M, N>;
404}
405
406impl<E, F, G, M, N, O, TOP> CursorTrait for SelectThree<E, F, G, TOP>
407where
408    E: EntityTrait<Model = M>,
409    F: EntityTrait<Model = N>,
410    G: EntityTrait<Model = O>,
411    M: FromQueryResult + Sized,
412    N: FromQueryResult + Sized,
413    O: FromQueryResult + Sized,
414    TOP: Topology,
415{
416    type Selector = SelectThreeModel<M, N, O>;
417}
418
419impl<E, F, M, N> SelectTwo<E, F>
420where
421    E: EntityTrait<Model = M>,
422    F: EntityTrait<Model = N>,
423    M: FromQueryResult + Sized,
424    N: FromQueryResult + Sized,
425{
426    /// Convert into a cursor using column of first entity
427    pub fn cursor_by<C>(self, order_columns: C) -> Cursor<SelectTwoModel<M, N>>
428    where
429        C: IdentityOf<E>,
430    {
431        let primary_keys: Vec<(DynIden, Identity)> = <F::PrimaryKey as Iterable>::iter()
432            .map(|pk| {
433                (
434                    SeaRc::new(F::default()),
435                    Identity::Unary(SeaRc::new(pk.into_column())),
436                )
437            })
438            .collect();
439        let mut cursor = Cursor::new(
440            self.query,
441            SeaRc::new(E::default()),
442            order_columns.identity_of(),
443        );
444        cursor.set_secondary_order_by(primary_keys);
445        cursor
446    }
447
448    /// Convert into a cursor using column of second entity
449    pub fn cursor_by_other<C>(self, order_columns: C) -> Cursor<SelectTwoModel<M, N>>
450    where
451        C: IdentityOf<F>,
452    {
453        let primary_keys: Vec<(DynIden, Identity)> = <E::PrimaryKey as Iterable>::iter()
454            .map(|pk| {
455                (
456                    SeaRc::new(E::default()),
457                    Identity::Unary(SeaRc::new(pk.into_column())),
458                )
459            })
460            .collect();
461        let mut cursor = Cursor::new(
462            self.query,
463            SeaRc::new(F::default()),
464            order_columns.identity_of(),
465        );
466        cursor.set_secondary_order_by(primary_keys);
467        cursor
468    }
469}
470
471impl<E, F, G, M, N, O, TOP> SelectThree<E, F, G, TOP>
472where
473    E: EntityTrait<Model = M>,
474    F: EntityTrait<Model = N>,
475    G: EntityTrait<Model = O>,
476    M: FromQueryResult + Sized,
477    N: FromQueryResult + Sized,
478    O: FromQueryResult + Sized,
479    TOP: Topology,
480{
481    /// Convert into a cursor using column of first entity
482    pub fn cursor_by<C>(self, order_columns: C) -> Cursor<SelectThreeModel<M, N, O>>
483    where
484        C: IdentityOf<E>,
485    {
486        let mut cursor = Cursor::new(
487            self.query,
488            SeaRc::new(E::default()),
489            order_columns.identity_of(),
490        );
491        {
492            let primary_keys: Vec<(DynIden, Identity)> = <F::PrimaryKey as Iterable>::iter()
493                .map(|pk| {
494                    (
495                        SeaRc::new(F::default()),
496                        Identity::Unary(SeaRc::new(pk.into_column())),
497                    )
498                })
499                .collect();
500            cursor.set_secondary_order_by(primary_keys);
501        }
502        {
503            let primary_keys: Vec<(DynIden, Identity)> = <G::PrimaryKey as Iterable>::iter()
504                .map(|pk| {
505                    (
506                        SeaRc::new(G::default()),
507                        Identity::Unary(SeaRc::new(pk.into_column())),
508                    )
509                })
510                .collect();
511            cursor.set_secondary_order_by(primary_keys);
512        }
513        cursor
514    }
515}
516
517#[cfg(test)]
518#[cfg(feature = "mock")]
519mod tests {
520    use crate::entity::prelude::*;
521    use crate::tests_cfg::*;
522    use crate::{DbBackend, MockDatabase, Statement, Transaction};
523    use pretty_assertions::assert_eq;
524
525    #[test]
526    fn first_2_before_10() -> Result<(), DbErr> {
527        use fruit::*;
528
529        let models = [
530            Model {
531                id: 1,
532                name: "Blueberry".into(),
533                cake_id: Some(1),
534            },
535            Model {
536                id: 2,
537                name: "Raspberry".into(),
538                cake_id: Some(1),
539            },
540        ];
541
542        let db = MockDatabase::new(DbBackend::Postgres)
543            .append_query_results([models.clone()])
544            .into_connection();
545
546        assert_eq!(
547            Entity::find()
548                .cursor_by(Column::Id)
549                .before(10)
550                .first(2)
551                .all(&db)?,
552            models
553        );
554
555        assert_eq!(
556            db.into_transaction_log(),
557            [Transaction::many([Statement::from_sql_and_values(
558                DbBackend::Postgres,
559                [
560                    r#"SELECT "fruit"."id", "fruit"."name", "fruit"."cake_id""#,
561                    r#"FROM "fruit""#,
562                    r#"WHERE "fruit"."id" < $1"#,
563                    r#"ORDER BY "fruit"."id" ASC"#,
564                    r#"LIMIT $2"#,
565                ]
566                .join(" ")
567                .as_str(),
568                [10_i32.into(), 2_u64.into()]
569            ),])]
570        );
571
572        Ok(())
573    }
574
575    #[test]
576    fn last_2_after_10_desc() -> Result<(), DbErr> {
577        use fruit::*;
578
579        let mut models = [
580            Model {
581                id: 1,
582                name: "Blueberry".into(),
583                cake_id: Some(1),
584            },
585            Model {
586                id: 2,
587                name: "Raspberry".into(),
588                cake_id: Some(1),
589            },
590        ];
591
592        let db = MockDatabase::new(DbBackend::Postgres)
593            .append_query_results([models.clone()])
594            .into_connection();
595
596        models.reverse();
597
598        assert_eq!(
599            Entity::find()
600                .cursor_by(Column::Id)
601                .after(10)
602                .last(2)
603                .desc()
604                .all(&db)?,
605            models
606        );
607
608        assert_eq!(
609            db.into_transaction_log(),
610            [Transaction::many([Statement::from_sql_and_values(
611                DbBackend::Postgres,
612                [
613                    r#"SELECT "fruit"."id", "fruit"."name", "fruit"."cake_id""#,
614                    r#"FROM "fruit""#,
615                    r#"WHERE "fruit"."id" < $1"#,
616                    r#"ORDER BY "fruit"."id" ASC"#,
617                    r#"LIMIT $2"#,
618                ]
619                .join(" ")
620                .as_str(),
621                [10_i32.into(), 2_u64.into()]
622            ),])]
623        );
624
625        Ok(())
626    }
627
628    #[test]
629    fn first_2_before_10_also_related_select() -> Result<(), DbErr> {
630        let models = [
631            (
632                cake::Model {
633                    id: 1,
634                    name: "Blueberry Cheese Cake".into(),
635                },
636                Some(fruit::Model {
637                    id: 9,
638                    name: "Blueberry".into(),
639                    cake_id: Some(1),
640                }),
641            ),
642            (
643                cake::Model {
644                    id: 2,
645                    name: "Raspberry Cheese Cake".into(),
646                },
647                Some(fruit::Model {
648                    id: 10,
649                    name: "Raspberry".into(),
650                    cake_id: Some(1),
651                }),
652            ),
653        ];
654
655        let db = MockDatabase::new(DbBackend::Postgres)
656            .append_query_results([models.clone()])
657            .into_connection();
658
659        assert_eq!(
660            cake::Entity::find()
661                .find_also_related(Fruit)
662                .cursor_by(cake::Column::Id)
663                .before(10)
664                .first(2)
665                .all(&db)?,
666            models
667        );
668
669        assert_eq!(
670            db.into_transaction_log(),
671            [Transaction::many([Statement::from_sql_and_values(
672                DbBackend::Postgres,
673                [
674                    r#"SELECT "cake"."id" AS "A_id", "cake"."name" AS "A_name","#,
675                    r#""fruit"."id" AS "B_id", "fruit"."name" AS "B_name", "fruit"."cake_id" AS "B_cake_id""#,
676                    r#"FROM "cake""#,
677                    r#"LEFT JOIN "fruit" ON "cake"."id" = "fruit"."cake_id""#,
678                    r#"WHERE "cake"."id" < $1"#,
679                    r#"ORDER BY "cake"."id" ASC, "fruit"."id" ASC LIMIT $2"#,
680                ]
681                .join(" ")
682                .as_str(),
683                [10_i32.into(), 2_u64.into()]
684            ),])]
685        );
686
687        Ok(())
688    }
689
690    #[test]
691    fn last_2_after_10_also_related_select_desc() -> Result<(), DbErr> {
692        let mut models = [
693            (
694                cake::Model {
695                    id: 2,
696                    name: "Raspberry Cheese Cake".into(),
697                },
698                Some(fruit::Model {
699                    id: 10,
700                    name: "Raspberry".into(),
701                    cake_id: Some(1),
702                }),
703            ),
704            (
705                cake::Model {
706                    id: 1,
707                    name: "Blueberry Cheese Cake".into(),
708                },
709                Some(fruit::Model {
710                    id: 9,
711                    name: "Blueberry".into(),
712                    cake_id: Some(1),
713                }),
714            ),
715        ];
716
717        let db = MockDatabase::new(DbBackend::Postgres)
718            .append_query_results([models.clone()])
719            .into_connection();
720
721        models.reverse();
722
723        assert_eq!(
724            cake::Entity::find()
725                .find_also_related(Fruit)
726                .cursor_by(cake::Column::Id)
727                .after(10)
728                .last(2)
729                .desc()
730                .all(&db)?,
731            models
732        );
733
734        assert_eq!(
735            db.into_transaction_log(),
736            [Transaction::many([Statement::from_sql_and_values(
737                DbBackend::Postgres,
738                [
739                    r#"SELECT "cake"."id" AS "A_id", "cake"."name" AS "A_name","#,
740                    r#""fruit"."id" AS "B_id", "fruit"."name" AS "B_name", "fruit"."cake_id" AS "B_cake_id""#,
741                    r#"FROM "cake""#,
742                    r#"LEFT JOIN "fruit" ON "cake"."id" = "fruit"."cake_id""#,
743                    r#"WHERE "cake"."id" < $1"#,
744                    r#"ORDER BY "cake"."id" ASC, "fruit"."id" ASC LIMIT $2"#,
745                ]
746                .join(" ")
747                .as_str(),
748                [10_i32.into(), 2_u64.into()]
749            ),])]
750        );
751
752        Ok(())
753    }
754
755    #[test]
756    fn first_2_before_10_also_related_select_cursor_other() -> Result<(), DbErr> {
757        let models = [(
758            cake::Model {
759                id: 1,
760                name: "Blueberry Cheese Cake".into(),
761            },
762            Some(fruit::Model {
763                id: 9,
764                name: "Blueberry".into(),
765                cake_id: Some(1),
766            }),
767        )];
768
769        let db = MockDatabase::new(DbBackend::Postgres)
770            .append_query_results([models.clone()])
771            .into_connection();
772
773        assert_eq!(
774            cake::Entity::find()
775                .find_also_related(Fruit)
776                .cursor_by_other(fruit::Column::Id)
777                .before(10)
778                .first(2)
779                .all(&db)?,
780            models
781        );
782
783        assert_eq!(
784            db.into_transaction_log(),
785            [Transaction::many([Statement::from_sql_and_values(
786                DbBackend::Postgres,
787                [
788                    r#"SELECT "cake"."id" AS "A_id", "cake"."name" AS "A_name","#,
789                    r#""fruit"."id" AS "B_id", "fruit"."name" AS "B_name", "fruit"."cake_id" AS "B_cake_id""#,
790                    r#"FROM "cake""#,
791                    r#"LEFT JOIN "fruit" ON "cake"."id" = "fruit"."cake_id""#,
792                    r#"WHERE "fruit"."id" < $1"#,
793                    r#"ORDER BY "fruit"."id" ASC, "cake"."id" ASC LIMIT $2"#,
794                ]
795                .join(" ")
796                .as_str(),
797                [10_i32.into(), 2_u64.into()]
798            ),])]
799        );
800
801        Ok(())
802    }
803
804    #[test]
805    fn last_2_after_10_also_related_select_cursor_other_desc() -> Result<(), DbErr> {
806        let models = [(
807            cake::Model {
808                id: 1,
809                name: "Blueberry Cheese Cake".into(),
810            },
811            Some(fruit::Model {
812                id: 9,
813                name: "Blueberry".into(),
814                cake_id: Some(1),
815            }),
816        )];
817
818        let db = MockDatabase::new(DbBackend::Postgres)
819            .append_query_results([models.clone()])
820            .into_connection();
821
822        assert_eq!(
823            cake::Entity::find()
824                .find_also_related(Fruit)
825                .cursor_by_other(fruit::Column::Id)
826                .after(10)
827                .last(2)
828                .desc()
829                .all(&db)?,
830            models
831        );
832
833        assert_eq!(
834            db.into_transaction_log(),
835            [Transaction::many([Statement::from_sql_and_values(
836                DbBackend::Postgres,
837                [
838                    r#"SELECT "cake"."id" AS "A_id", "cake"."name" AS "A_name","#,
839                    r#""fruit"."id" AS "B_id", "fruit"."name" AS "B_name", "fruit"."cake_id" AS "B_cake_id""#,
840                    r#"FROM "cake""#,
841                    r#"LEFT JOIN "fruit" ON "cake"."id" = "fruit"."cake_id""#,
842                    r#"WHERE "fruit"."id" < $1"#,
843                    r#"ORDER BY "fruit"."id" ASC, "cake"."id" ASC LIMIT $2"#,
844                ]
845                .join(" ")
846                .as_str(),
847                [10_i32.into(), 2_u64.into()]
848            ),])]
849        );
850
851        Ok(())
852    }
853
854    #[test]
855    fn first_2_before_10_also_linked_select() -> Result<(), DbErr> {
856        let models = [
857            (
858                cake::Model {
859                    id: 1,
860                    name: "Blueberry Cheese Cake".into(),
861                },
862                Some(vendor::Model {
863                    id: 9,
864                    name: "Blueberry".into(),
865                }),
866            ),
867            (
868                cake::Model {
869                    id: 2,
870                    name: "Raspberry Cheese Cake".into(),
871                },
872                Some(vendor::Model {
873                    id: 10,
874                    name: "Raspberry".into(),
875                }),
876            ),
877        ];
878
879        let db = MockDatabase::new(DbBackend::Postgres)
880            .append_query_results([models.clone()])
881            .into_connection();
882
883        assert_eq!(
884            cake::Entity::find()
885                .find_also_linked(entity_linked::CakeToFillingVendor)
886                .cursor_by(cake::Column::Id)
887                .before(10)
888                .first(2)
889                .all(&db)?,
890            models
891        );
892
893        assert_eq!(
894            db.into_transaction_log(),
895            [Transaction::many([Statement::from_sql_and_values(
896                DbBackend::Postgres,
897                [
898                    r#"SELECT "cake"."id" AS "A_id", "cake"."name" AS "A_name","#,
899                    r#""r2"."id" AS "B_id", "r2"."name" AS "B_name""#,
900                    r#"FROM "cake""#,
901                    r#"LEFT JOIN "cake_filling" AS "r0" ON "cake"."id" = "r0"."cake_id""#,
902                    r#"LEFT JOIN "filling" AS "r1" ON "r0"."filling_id" = "r1"."id""#,
903                    r#"LEFT JOIN "vendor" AS "r2" ON "r1"."vendor_id" = "r2"."id""#,
904                    r#"WHERE "cake"."id" < $1 ORDER BY "cake"."id" ASC, "vendor"."id" ASC LIMIT $2"#,
905                ]
906                .join(" ")
907                .as_str(),
908                [10_i32.into(), 2_u64.into()]
909            ),])]
910        );
911
912        Ok(())
913    }
914
915    #[test]
916    fn last_2_after_10_also_linked_select_desc() -> Result<(), DbErr> {
917        let mut models = [
918            (
919                cake::Model {
920                    id: 2,
921                    name: "Raspberry Cheese Cake".into(),
922                },
923                Some(vendor::Model {
924                    id: 10,
925                    name: "Raspberry".into(),
926                }),
927            ),
928            (
929                cake::Model {
930                    id: 1,
931                    name: "Blueberry Cheese Cake".into(),
932                },
933                Some(vendor::Model {
934                    id: 9,
935                    name: "Blueberry".into(),
936                }),
937            ),
938        ];
939
940        let db = MockDatabase::new(DbBackend::Postgres)
941            .append_query_results([models.clone()])
942            .into_connection();
943
944        models.reverse();
945
946        assert_eq!(
947            cake::Entity::find()
948                .find_also_linked(entity_linked::CakeToFillingVendor)
949                .cursor_by(cake::Column::Id)
950                .after(10)
951                .last(2)
952                .desc()
953                .all(&db)?,
954            models
955        );
956
957        assert_eq!(
958            db.into_transaction_log(),
959            [Transaction::many([Statement::from_sql_and_values(
960                DbBackend::Postgres,
961                [
962                    r#"SELECT "cake"."id" AS "A_id", "cake"."name" AS "A_name","#,
963                    r#""r2"."id" AS "B_id", "r2"."name" AS "B_name""#,
964                    r#"FROM "cake""#,
965                    r#"LEFT JOIN "cake_filling" AS "r0" ON "cake"."id" = "r0"."cake_id""#,
966                    r#"LEFT JOIN "filling" AS "r1" ON "r0"."filling_id" = "r1"."id""#,
967                    r#"LEFT JOIN "vendor" AS "r2" ON "r1"."vendor_id" = "r2"."id""#,
968                    r#"WHERE "cake"."id" < $1 ORDER BY "cake"."id" ASC, "vendor"."id" ASC LIMIT $2"#,
969                ]
970                .join(" ")
971                .as_str(),
972                [10_i32.into(), 2_u64.into()]
973            ),])]
974        );
975
976        Ok(())
977    }
978
979    #[test]
980    fn first_2_before_10_also_linked_select_cursor_other() -> Result<(), DbErr> {
981        let models = [(
982            cake::Model {
983                id: 1,
984                name: "Blueberry Cheese Cake".into(),
985            },
986            Some(vendor::Model {
987                id: 9,
988                name: "Blueberry".into(),
989            }),
990        )];
991
992        let db = MockDatabase::new(DbBackend::Postgres)
993            .append_query_results([models.clone()])
994            .into_connection();
995
996        assert_eq!(
997            cake::Entity::find()
998                .find_also_linked(entity_linked::CakeToFillingVendor)
999                .cursor_by_other(vendor::Column::Id)
1000                .before(10)
1001                .first(2)
1002                .all(&db)?,
1003            models
1004        );
1005
1006        assert_eq!(
1007            db.into_transaction_log(),
1008            [Transaction::many([Statement::from_sql_and_values(
1009                DbBackend::Postgres,
1010                [
1011                    r#"SELECT "cake"."id" AS "A_id", "cake"."name" AS "A_name","#,
1012                    r#""r2"."id" AS "B_id", "r2"."name" AS "B_name""#,
1013                    r#"FROM "cake""#,
1014                    r#"LEFT JOIN "cake_filling" AS "r0" ON "cake"."id" = "r0"."cake_id""#,
1015                    r#"LEFT JOIN "filling" AS "r1" ON "r0"."filling_id" = "r1"."id""#,
1016                    r#"LEFT JOIN "vendor" AS "r2" ON "r1"."vendor_id" = "r2"."id""#,
1017                    r#"WHERE "vendor"."id" < $1 ORDER BY "vendor"."id" ASC, "cake"."id" ASC LIMIT $2"#,
1018                ]
1019                .join(" ")
1020                .as_str(),
1021                [10_i32.into(), 2_u64.into()]
1022            ),])]
1023        );
1024
1025        Ok(())
1026    }
1027
1028    #[test]
1029    fn last_2_after_10_also_linked_select_cursor_other_desc() -> Result<(), DbErr> {
1030        let mut models = [(
1031            cake::Model {
1032                id: 1,
1033                name: "Blueberry Cheese Cake".into(),
1034            },
1035            Some(vendor::Model {
1036                id: 9,
1037                name: "Blueberry".into(),
1038            }),
1039        )];
1040
1041        let db = MockDatabase::new(DbBackend::Postgres)
1042            .append_query_results([models.clone()])
1043            .into_connection();
1044
1045        models.reverse();
1046
1047        assert_eq!(
1048            cake::Entity::find()
1049                .find_also_linked(entity_linked::CakeToFillingVendor)
1050                .cursor_by_other(vendor::Column::Id)
1051                .after(10)
1052                .last(2)
1053                .desc()
1054                .all(&db)?,
1055            models
1056        );
1057
1058        assert_eq!(
1059            db.into_transaction_log(),
1060            [Transaction::many([Statement::from_sql_and_values(
1061                DbBackend::Postgres,
1062                [
1063                    r#"SELECT "cake"."id" AS "A_id", "cake"."name" AS "A_name","#,
1064                    r#""r2"."id" AS "B_id", "r2"."name" AS "B_name""#,
1065                    r#"FROM "cake""#,
1066                    r#"LEFT JOIN "cake_filling" AS "r0" ON "cake"."id" = "r0"."cake_id""#,
1067                    r#"LEFT JOIN "filling" AS "r1" ON "r0"."filling_id" = "r1"."id""#,
1068                    r#"LEFT JOIN "vendor" AS "r2" ON "r1"."vendor_id" = "r2"."id""#,
1069                    r#"WHERE "vendor"."id" < $1 ORDER BY "vendor"."id" ASC, "cake"."id" ASC LIMIT $2"#,
1070                ]
1071                .join(" ")
1072                .as_str(),
1073                [10_i32.into(), 2_u64.into()]
1074            ),])]
1075        );
1076
1077        Ok(())
1078    }
1079
1080    #[test]
1081    fn last_2_after_10() -> Result<(), DbErr> {
1082        use fruit::*;
1083
1084        let db = MockDatabase::new(DbBackend::Postgres)
1085            .append_query_results([[
1086                Model {
1087                    id: 22,
1088                    name: "Raspberry".into(),
1089                    cake_id: Some(1),
1090                },
1091                Model {
1092                    id: 21,
1093                    name: "Blueberry".into(),
1094                    cake_id: Some(1),
1095                },
1096            ]])
1097            .into_connection();
1098
1099        assert_eq!(
1100            Entity::find()
1101                .cursor_by(Column::Id)
1102                .after(10)
1103                .last(2)
1104                .all(&db)?,
1105            [
1106                Model {
1107                    id: 21,
1108                    name: "Blueberry".into(),
1109                    cake_id: Some(1),
1110                },
1111                Model {
1112                    id: 22,
1113                    name: "Raspberry".into(),
1114                    cake_id: Some(1),
1115                },
1116            ]
1117        );
1118
1119        assert_eq!(
1120            db.into_transaction_log(),
1121            [Transaction::many([Statement::from_sql_and_values(
1122                DbBackend::Postgres,
1123                [
1124                    r#"SELECT "fruit"."id", "fruit"."name", "fruit"."cake_id""#,
1125                    r#"FROM "fruit""#,
1126                    r#"WHERE "fruit"."id" > $1"#,
1127                    r#"ORDER BY "fruit"."id" DESC"#,
1128                    r#"LIMIT $2"#,
1129                ]
1130                .join(" ")
1131                .as_str(),
1132                [10_i32.into(), 2_u64.into()]
1133            ),])]
1134        );
1135
1136        Ok(())
1137    }
1138
1139    #[test]
1140    fn first_2_before_10_desc() -> Result<(), DbErr> {
1141        use fruit::*;
1142
1143        let models = [
1144            Model {
1145                id: 22,
1146                name: "Raspberry".into(),
1147                cake_id: Some(1),
1148            },
1149            Model {
1150                id: 21,
1151                name: "Blueberry".into(),
1152                cake_id: Some(1),
1153            },
1154        ];
1155
1156        let db = MockDatabase::new(DbBackend::Postgres)
1157            .append_query_results([models.clone()])
1158            .into_connection();
1159
1160        assert_eq!(
1161            Entity::find()
1162                .cursor_by(Column::Id)
1163                .before(10)
1164                .first(2)
1165                .desc()
1166                .all(&db)?,
1167            models
1168        );
1169
1170        assert_eq!(
1171            db.into_transaction_log(),
1172            [Transaction::many([Statement::from_sql_and_values(
1173                DbBackend::Postgres,
1174                [
1175                    r#"SELECT "fruit"."id", "fruit"."name", "fruit"."cake_id""#,
1176                    r#"FROM "fruit""#,
1177                    r#"WHERE "fruit"."id" > $1"#,
1178                    r#"ORDER BY "fruit"."id" DESC"#,
1179                    r#"LIMIT $2"#,
1180                ]
1181                .join(" ")
1182                .as_str(),
1183                [10_i32.into(), 2_u64.into()]
1184            ),])]
1185        );
1186
1187        Ok(())
1188    }
1189
1190    #[test]
1191    fn last_2_after_25_before_30() -> Result<(), DbErr> {
1192        use fruit::*;
1193
1194        let db = MockDatabase::new(DbBackend::Postgres)
1195            .append_query_results([[
1196                Model {
1197                    id: 27,
1198                    name: "Raspberry".into(),
1199                    cake_id: Some(1),
1200                },
1201                Model {
1202                    id: 26,
1203                    name: "Blueberry".into(),
1204                    cake_id: Some(1),
1205                },
1206            ]])
1207            .into_connection();
1208
1209        assert_eq!(
1210            Entity::find()
1211                .cursor_by(Column::Id)
1212                .after(25)
1213                .before(30)
1214                .last(2)
1215                .all(&db)?,
1216            [
1217                Model {
1218                    id: 26,
1219                    name: "Blueberry".into(),
1220                    cake_id: Some(1),
1221                },
1222                Model {
1223                    id: 27,
1224                    name: "Raspberry".into(),
1225                    cake_id: Some(1),
1226                },
1227            ]
1228        );
1229
1230        assert_eq!(
1231            db.into_transaction_log(),
1232            [Transaction::many([Statement::from_sql_and_values(
1233                DbBackend::Postgres,
1234                [
1235                    r#"SELECT "fruit"."id", "fruit"."name", "fruit"."cake_id""#,
1236                    r#"FROM "fruit""#,
1237                    r#"WHERE "fruit"."id" > $1"#,
1238                    r#"AND "fruit"."id" < $2"#,
1239                    r#"ORDER BY "fruit"."id" DESC"#,
1240                    r#"LIMIT $3"#,
1241                ]
1242                .join(" ")
1243                .as_str(),
1244                [25_i32.into(), 30_i32.into(), 2_u64.into()]
1245            ),])]
1246        );
1247
1248        Ok(())
1249    }
1250
1251    #[test]
1252    fn first_2_after_30_before_25_desc() -> Result<(), DbErr> {
1253        use fruit::*;
1254
1255        let models = [
1256            Model {
1257                id: 27,
1258                name: "Raspberry".into(),
1259                cake_id: Some(1),
1260            },
1261            Model {
1262                id: 26,
1263                name: "Blueberry".into(),
1264                cake_id: Some(1),
1265            },
1266        ];
1267
1268        let db = MockDatabase::new(DbBackend::Postgres)
1269            .append_query_results([models.clone()])
1270            .into_connection();
1271
1272        assert_eq!(
1273            Entity::find()
1274                .cursor_by(Column::Id)
1275                .after(30)
1276                .before(25)
1277                .first(2)
1278                .desc()
1279                .all(&db)?,
1280            models
1281        );
1282
1283        assert_eq!(
1284            db.into_transaction_log(),
1285            [Transaction::many([Statement::from_sql_and_values(
1286                DbBackend::Postgres,
1287                [
1288                    r#"SELECT "fruit"."id", "fruit"."name", "fruit"."cake_id""#,
1289                    r#"FROM "fruit""#,
1290                    r#"WHERE "fruit"."id" < $1"#,
1291                    r#"AND "fruit"."id" > $2"#,
1292                    r#"ORDER BY "fruit"."id" DESC"#,
1293                    r#"LIMIT $3"#,
1294                ]
1295                .join(" ")
1296                .as_str(),
1297                [30_i32.into(), 25_i32.into(), 2_u64.into()]
1298            ),])]
1299        );
1300
1301        Ok(())
1302    }
1303
1304    mod test_entity {
1305        use crate as sea_orm;
1306        use crate::entity::prelude::*;
1307
1308        #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
1309        #[sea_orm(table_name = "example")]
1310        pub struct Model {
1311            #[sea_orm(primary_key)]
1312            pub id: i32,
1313            #[sea_orm(primary_key)]
1314            pub category: String,
1315        }
1316
1317        #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
1318        pub enum Relation {}
1319
1320        impl ActiveModelBehavior for ActiveModel {}
1321    }
1322
1323    mod xyz_entity {
1324        use crate as sea_orm;
1325        use crate::entity::prelude::*;
1326
1327        #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
1328        #[sea_orm(table_name = "m")]
1329        pub struct Model {
1330            #[sea_orm(primary_key)]
1331            pub x: i32,
1332            #[sea_orm(primary_key)]
1333            pub y: String,
1334            #[sea_orm(primary_key)]
1335            pub z: i64,
1336        }
1337
1338        #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
1339        pub enum Relation {}
1340
1341        impl ActiveModelBehavior for ActiveModel {}
1342    }
1343
1344    #[test]
1345    fn composite_keys_1() -> Result<(), DbErr> {
1346        use test_entity::*;
1347
1348        let db = MockDatabase::new(DbBackend::Postgres)
1349            .append_query_results([[Model {
1350                id: 1,
1351                category: "CAT".into(),
1352            }]])
1353            .into_connection();
1354
1355        assert!(
1356            !Entity::find()
1357                .cursor_by((Column::Category, Column::Id))
1358                .first(3)
1359                .all(&db)?
1360                .is_empty()
1361        );
1362
1363        assert_eq!(
1364            db.into_transaction_log(),
1365            [Transaction::many([Statement::from_sql_and_values(
1366                DbBackend::Postgres,
1367                [
1368                    r#"SELECT "example"."id", "example"."category""#,
1369                    r#"FROM "example""#,
1370                    r#"ORDER BY "example"."category" ASC, "example"."id" ASC"#,
1371                    r#"LIMIT $1"#,
1372                ]
1373                .join(" ")
1374                .as_str(),
1375                [3_u64.into()]
1376            ),])]
1377        );
1378
1379        Ok(())
1380    }
1381
1382    #[test]
1383    fn composite_keys_1_desc() -> Result<(), DbErr> {
1384        use test_entity::*;
1385
1386        let db = MockDatabase::new(DbBackend::Postgres)
1387            .append_query_results([[Model {
1388                id: 1,
1389                category: "CAT".into(),
1390            }]])
1391            .into_connection();
1392
1393        assert!(
1394            !Entity::find()
1395                .cursor_by((Column::Category, Column::Id))
1396                .last(3)
1397                .desc()
1398                .all(&db)?
1399                .is_empty()
1400        );
1401
1402        assert_eq!(
1403            db.into_transaction_log(),
1404            [Transaction::many([Statement::from_sql_and_values(
1405                DbBackend::Postgres,
1406                [
1407                    r#"SELECT "example"."id", "example"."category""#,
1408                    r#"FROM "example""#,
1409                    r#"ORDER BY "example"."category" ASC, "example"."id" ASC"#,
1410                    r#"LIMIT $1"#,
1411                ]
1412                .join(" ")
1413                .as_str(),
1414                [3_u64.into()]
1415            ),])]
1416        );
1417
1418        Ok(())
1419    }
1420
1421    #[test]
1422    fn composite_keys_2() -> Result<(), DbErr> {
1423        use test_entity::*;
1424
1425        let db = MockDatabase::new(DbBackend::Postgres)
1426            .append_query_results([[Model {
1427                id: 1,
1428                category: "CAT".into(),
1429            }]])
1430            .into_connection();
1431
1432        assert!(
1433            !Entity::find()
1434                .cursor_by((Column::Category, Column::Id))
1435                .after(("A".to_owned(), 2))
1436                .first(3)
1437                .all(&db)?
1438                .is_empty()
1439        );
1440
1441        assert_eq!(
1442            db.into_transaction_log(),
1443            [Transaction::many([Statement::from_sql_and_values(
1444                DbBackend::Postgres,
1445                [
1446                    r#"SELECT "example"."id", "example"."category""#,
1447                    r#"FROM "example""#,
1448                    r#"WHERE ("example"."category" = $1 AND "example"."id" > $2)"#,
1449                    r#"OR "example"."category" > $3"#,
1450                    r#"ORDER BY "example"."category" ASC, "example"."id" ASC"#,
1451                    r#"LIMIT $4"#,
1452                ]
1453                .join(" ")
1454                .as_str(),
1455                [
1456                    "A".to_string().into(),
1457                    2i32.into(),
1458                    "A".to_string().into(),
1459                    3_u64.into(),
1460                ]
1461            )])]
1462        );
1463
1464        Ok(())
1465    }
1466
1467    #[test]
1468    fn composite_keys_error() -> Result<(), DbErr> {
1469        use test_entity::*;
1470
1471        let db = MockDatabase::new(DbBackend::Postgres)
1472            .append_query_results([[Model {
1473                id: 1,
1474                category: "CAT".into(),
1475            }]])
1476            .into_connection();
1477
1478        let result = Entity::find()
1479            .cursor_by((Column::Category, Column::Id))
1480            .after("A".to_owned())
1481            .first(3)
1482            .all(&db);
1483
1484        assert!(matches!(
1485            result,
1486            Err(DbErr::KeyArityMismatch {
1487                expected: 2,
1488                received: 1,
1489            })
1490        ));
1491
1492        Ok(())
1493    }
1494
1495    #[test]
1496    fn composite_keys_2_desc() -> Result<(), DbErr> {
1497        use test_entity::*;
1498
1499        let db = MockDatabase::new(DbBackend::Postgres)
1500            .append_query_results([[Model {
1501                id: 1,
1502                category: "CAT".into(),
1503            }]])
1504            .into_connection();
1505
1506        assert!(
1507            !Entity::find()
1508                .cursor_by((Column::Category, Column::Id))
1509                .before(("A".to_owned(), 2))
1510                .last(3)
1511                .desc()
1512                .all(&db)?
1513                .is_empty()
1514        );
1515
1516        assert_eq!(
1517            db.into_transaction_log(),
1518            [Transaction::many([Statement::from_sql_and_values(
1519                DbBackend::Postgres,
1520                [
1521                    r#"SELECT "example"."id", "example"."category""#,
1522                    r#"FROM "example""#,
1523                    r#"WHERE ("example"."category" = $1 AND "example"."id" > $2)"#,
1524                    r#"OR "example"."category" > $3"#,
1525                    r#"ORDER BY "example"."category" ASC, "example"."id" ASC"#,
1526                    r#"LIMIT $4"#,
1527                ]
1528                .join(" ")
1529                .as_str(),
1530                [
1531                    "A".to_string().into(),
1532                    2i32.into(),
1533                    "A".to_string().into(),
1534                    3_u64.into(),
1535                ]
1536            )])]
1537        );
1538
1539        Ok(())
1540    }
1541
1542    #[test]
1543    fn composite_keys_3() -> Result<(), DbErr> {
1544        use test_entity::*;
1545
1546        let db = MockDatabase::new(DbBackend::Postgres)
1547            .append_query_results([[Model {
1548                id: 1,
1549                category: "CAT".into(),
1550            }]])
1551            .into_connection();
1552
1553        assert!(
1554            !Entity::find()
1555                .cursor_by((Column::Category, Column::Id))
1556                .before(("A".to_owned(), 2))
1557                .last(3)
1558                .all(&db)?
1559                .is_empty()
1560        );
1561
1562        assert_eq!(
1563            db.into_transaction_log(),
1564            [Transaction::many([Statement::from_sql_and_values(
1565                DbBackend::Postgres,
1566                [
1567                    r#"SELECT "example"."id", "example"."category""#,
1568                    r#"FROM "example""#,
1569                    r#"WHERE ("example"."category" = $1 AND "example"."id" < $2)"#,
1570                    r#"OR "example"."category" < $3"#,
1571                    r#"ORDER BY "example"."category" DESC, "example"."id" DESC"#,
1572                    r#"LIMIT $4"#,
1573                ]
1574                .join(" ")
1575                .as_str(),
1576                [
1577                    "A".to_string().into(),
1578                    2i32.into(),
1579                    "A".to_string().into(),
1580                    3_u64.into(),
1581                ]
1582            )])]
1583        );
1584
1585        Ok(())
1586    }
1587
1588    #[test]
1589    fn composite_keys_3_desc() -> Result<(), DbErr> {
1590        use test_entity::*;
1591
1592        let db = MockDatabase::new(DbBackend::Postgres)
1593            .append_query_results([[Model {
1594                id: 1,
1595                category: "CAT".into(),
1596            }]])
1597            .into_connection();
1598
1599        assert!(
1600            !Entity::find()
1601                .cursor_by((Column::Category, Column::Id))
1602                .after(("A".to_owned(), 2))
1603                .first(3)
1604                .desc()
1605                .all(&db)?
1606                .is_empty()
1607        );
1608
1609        assert_eq!(
1610            db.into_transaction_log(),
1611            [Transaction::many([Statement::from_sql_and_values(
1612                DbBackend::Postgres,
1613                [
1614                    r#"SELECT "example"."id", "example"."category""#,
1615                    r#"FROM "example""#,
1616                    r#"WHERE ("example"."category" = $1 AND "example"."id" < $2)"#,
1617                    r#"OR "example"."category" < $3"#,
1618                    r#"ORDER BY "example"."category" DESC, "example"."id" DESC"#,
1619                    r#"LIMIT $4"#,
1620                ]
1621                .join(" ")
1622                .as_str(),
1623                [
1624                    "A".to_string().into(),
1625                    2i32.into(),
1626                    "A".to_string().into(),
1627                    3_u64.into(),
1628                ]
1629            )])]
1630        );
1631
1632        Ok(())
1633    }
1634
1635    #[test]
1636    fn composite_keys_4() -> Result<(), DbErr> {
1637        use xyz_entity::*;
1638
1639        let db = MockDatabase::new(DbBackend::Postgres)
1640            .append_query_results([[Model {
1641                x: 'x' as i32,
1642                y: "y".into(),
1643                z: 'z' as i64,
1644            }]])
1645            .into_connection();
1646
1647        assert!(
1648            !Entity::find()
1649                .cursor_by((Column::X, Column::Y, Column::Z))
1650                .first(4)
1651                .all(&db)?
1652                .is_empty()
1653        );
1654
1655        assert_eq!(
1656            db.into_transaction_log(),
1657            [Transaction::many([Statement::from_sql_and_values(
1658                DbBackend::Postgres,
1659                [
1660                    r#"SELECT "m"."x", "m"."y", "m"."z""#,
1661                    r#"FROM "m""#,
1662                    r#"ORDER BY "m"."x" ASC, "m"."y" ASC, "m"."z" ASC"#,
1663                    r#"LIMIT $1"#,
1664                ]
1665                .join(" ")
1666                .as_str(),
1667                [4_u64.into()]
1668            ),])]
1669        );
1670
1671        Ok(())
1672    }
1673
1674    #[test]
1675    fn composite_keys_4_desc() -> Result<(), DbErr> {
1676        use xyz_entity::*;
1677
1678        let db = MockDatabase::new(DbBackend::Postgres)
1679            .append_query_results([[Model {
1680                x: 'x' as i32,
1681                y: "y".into(),
1682                z: 'z' as i64,
1683            }]])
1684            .into_connection();
1685
1686        assert!(
1687            !Entity::find()
1688                .cursor_by((Column::X, Column::Y, Column::Z))
1689                .last(4)
1690                .desc()
1691                .all(&db)?
1692                .is_empty()
1693        );
1694
1695        assert_eq!(
1696            db.into_transaction_log(),
1697            [Transaction::many([Statement::from_sql_and_values(
1698                DbBackend::Postgres,
1699                [
1700                    r#"SELECT "m"."x", "m"."y", "m"."z""#,
1701                    r#"FROM "m""#,
1702                    r#"ORDER BY "m"."x" ASC, "m"."y" ASC, "m"."z" ASC"#,
1703                    r#"LIMIT $1"#,
1704                ]
1705                .join(" ")
1706                .as_str(),
1707                [4_u64.into()]
1708            ),])]
1709        );
1710
1711        Ok(())
1712    }
1713
1714    #[test]
1715    fn composite_keys_5() -> Result<(), DbErr> {
1716        use xyz_entity::*;
1717
1718        let db = MockDatabase::new(DbBackend::Postgres)
1719            .append_query_results([[Model {
1720                x: 'x' as i32,
1721                y: "y".into(),
1722                z: 'z' as i64,
1723            }]])
1724            .into_connection();
1725
1726        assert!(
1727            !Entity::find()
1728                .cursor_by((Column::X, Column::Y, Column::Z))
1729                .after(('x' as i32, "y".to_owned(), 'z' as i64))
1730                .first(4)
1731                .all(&db)?
1732                .is_empty()
1733        );
1734
1735        assert_eq!(
1736            db.into_transaction_log(),
1737            [Transaction::many([Statement::from_sql_and_values(
1738                DbBackend::Postgres,
1739                [
1740                    r#"SELECT "m"."x", "m"."y", "m"."z""#,
1741                    r#"FROM "m""#,
1742                    r#"WHERE ("m"."x" = $1 AND "m"."y" = $2 AND "m"."z" > $3)"#,
1743                    r#"OR ("m"."x" = $4 AND "m"."y" > $5)"#,
1744                    r#"OR "m"."x" > $6"#,
1745                    r#"ORDER BY "m"."x" ASC, "m"."y" ASC, "m"."z" ASC"#,
1746                    r#"LIMIT $7"#,
1747                ]
1748                .join(" ")
1749                .as_str(),
1750                [
1751                    ('x' as i32).into(),
1752                    "y".into(),
1753                    ('z' as i64).into(),
1754                    ('x' as i32).into(),
1755                    "y".into(),
1756                    ('x' as i32).into(),
1757                    4_u64.into(),
1758                ]
1759            ),])]
1760        );
1761
1762        Ok(())
1763    }
1764
1765    #[test]
1766    fn composite_keys_5_desc() -> Result<(), DbErr> {
1767        use xyz_entity::*;
1768
1769        let db = MockDatabase::new(DbBackend::Postgres)
1770            .append_query_results([[Model {
1771                x: 'x' as i32,
1772                y: "y".into(),
1773                z: 'z' as i64,
1774            }]])
1775            .into_connection();
1776
1777        assert!(
1778            !Entity::find()
1779                .cursor_by((Column::X, Column::Y, Column::Z))
1780                .before(('x' as i32, "y".to_owned(), 'z' as i64))
1781                .last(4)
1782                .desc()
1783                .all(&db)?
1784                .is_empty()
1785        );
1786
1787        assert_eq!(
1788            db.into_transaction_log(),
1789            [Transaction::many([Statement::from_sql_and_values(
1790                DbBackend::Postgres,
1791                [
1792                    r#"SELECT "m"."x", "m"."y", "m"."z""#,
1793                    r#"FROM "m""#,
1794                    r#"WHERE ("m"."x" = $1 AND "m"."y" = $2 AND "m"."z" > $3)"#,
1795                    r#"OR ("m"."x" = $4 AND "m"."y" > $5)"#,
1796                    r#"OR "m"."x" > $6"#,
1797                    r#"ORDER BY "m"."x" ASC, "m"."y" ASC, "m"."z" ASC"#,
1798                    r#"LIMIT $7"#,
1799                ]
1800                .join(" ")
1801                .as_str(),
1802                [
1803                    ('x' as i32).into(),
1804                    "y".into(),
1805                    ('z' as i64).into(),
1806                    ('x' as i32).into(),
1807                    "y".into(),
1808                    ('x' as i32).into(),
1809                    4_u64.into(),
1810                ]
1811            ),])]
1812        );
1813
1814        Ok(())
1815    }
1816
1817    mod composite_entity {
1818        use crate as sea_orm;
1819        use crate::entity::prelude::*;
1820
1821        #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
1822        #[sea_orm(table_name = "t")]
1823        pub struct Model {
1824            #[sea_orm(primary_key)]
1825            pub col_1: String,
1826            #[sea_orm(primary_key)]
1827            pub col_2: String,
1828            #[sea_orm(primary_key)]
1829            pub col_3: String,
1830            #[sea_orm(primary_key)]
1831            pub col_4: String,
1832            #[sea_orm(primary_key)]
1833            pub col_5: String,
1834            #[sea_orm(primary_key)]
1835            pub col_6: String,
1836            #[sea_orm(primary_key)]
1837            pub col_7: String,
1838            #[sea_orm(primary_key)]
1839            pub col_8: String,
1840            #[sea_orm(primary_key)]
1841            pub col_9: String,
1842            #[sea_orm(primary_key)]
1843            pub col_10: String,
1844            #[sea_orm(primary_key)]
1845            pub col_11: String,
1846            #[sea_orm(primary_key)]
1847            pub col_12: String,
1848        }
1849
1850        #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
1851        pub enum Relation {}
1852
1853        impl ActiveModelBehavior for ActiveModel {}
1854    }
1855
1856    #[test]
1857    fn cursor_by_many() -> Result<(), DbErr> {
1858        use composite_entity::*;
1859
1860        let base_sql = [
1861            r#"SELECT "t"."col_1", "t"."col_2", "t"."col_3", "t"."col_4", "t"."col_5", "t"."col_6", "t"."col_7", "t"."col_8", "t"."col_9", "t"."col_10", "t"."col_11", "t"."col_12""#,
1862            r#"FROM "t" WHERE"#,
1863        ].join(" ");
1864
1865        assert_eq!(
1866            DbBackend::Postgres.build(&
1867                Entity::find()
1868                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4))
1869                .after(("val_1", "val_2", "val_3", "val_4")).apply_limit().apply_order_by().apply_filters()?.query
1870            ).to_string(),
1871            format!("{base_sql} {}", [
1872                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" > 'val_4')"#,
1873                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" > 'val_3')"#,
1874                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" > 'val_2')"#,
1875                r#"OR "t"."col_1" > 'val_1'"#,
1876                r#"ORDER BY "t"."col_1" ASC, "t"."col_2" ASC, "t"."col_3" ASC, "t"."col_4" ASC"#,
1877            ].join(" "))
1878        );
1879
1880        assert_eq!(
1881            DbBackend::Postgres.build(&
1882                Entity::find()
1883                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5))
1884                .after(("val_1", "val_2", "val_3", "val_4", "val_5")).apply_limit().apply_order_by().apply_filters()?
1885                .query
1886            ).to_string(),
1887            format!("{base_sql} {}", [
1888                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" > 'val_5')"#,
1889                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" > 'val_4')"#,
1890                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" > 'val_3')"#,
1891                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" > 'val_2')"#,
1892                r#"OR "t"."col_1" > 'val_1'"#,
1893                r#"ORDER BY "t"."col_1" ASC, "t"."col_2" ASC, "t"."col_3" ASC, "t"."col_4" ASC, "t"."col_5" ASC"#,
1894            ].join(" "))
1895        );
1896
1897        assert_eq!(
1898            DbBackend::Postgres.build(&
1899                Entity::find()
1900                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5, Column::Col6))
1901                .after(("val_1", "val_2", "val_3", "val_4", "val_5", "val_6")).apply_limit().apply_order_by().apply_filters()?
1902                .query
1903            ).to_string(),
1904            format!("{base_sql} {}", [
1905                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" > 'val_6')"#,
1906                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" > 'val_5')"#,
1907                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" > 'val_4')"#,
1908                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" > 'val_3')"#,
1909                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" > 'val_2')"#,
1910                r#"OR "t"."col_1" > 'val_1'"#,
1911                r#"ORDER BY "t"."col_1" ASC, "t"."col_2" ASC, "t"."col_3" ASC, "t"."col_4" ASC, "t"."col_5" ASC, "t"."col_6" ASC"#,
1912            ].join(" "))
1913        );
1914
1915        assert_eq!(
1916            DbBackend::Postgres.build(&
1917                Entity::find()
1918                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5, Column::Col6, Column::Col7))
1919                .before(("val_1", "val_2", "val_3", "val_4", "val_5", "val_6", "val_7")).apply_limit().apply_order_by().apply_filters()?
1920                .query
1921            ).to_string(),
1922            format!("{base_sql} {}", [
1923                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" < 'val_7')"#,
1924                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" < 'val_6')"#,
1925                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" < 'val_5')"#,
1926                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" < 'val_4')"#,
1927                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" < 'val_3')"#,
1928                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" < 'val_2')"#,
1929                r#"OR "t"."col_1" < 'val_1'"#,
1930                r#"ORDER BY "t"."col_1" ASC, "t"."col_2" ASC, "t"."col_3" ASC, "t"."col_4" ASC, "t"."col_5" ASC, "t"."col_6" ASC, "t"."col_7" ASC"#,
1931            ].join(" "))
1932        );
1933
1934        assert_eq!(
1935            DbBackend::Postgres.build(&
1936                Entity::find()
1937                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5, Column::Col6, Column::Col7, Column::Col8))
1938                .before(("val_1", "val_2", "val_3", "val_4", "val_5", "val_6", "val_7", "val_8")).apply_limit().apply_order_by().apply_filters()?
1939                .query
1940            ).to_string(),
1941            format!("{base_sql} {}", [
1942                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" = 'val_7' AND "t"."col_8" < 'val_8')"#,
1943                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" < 'val_7')"#,
1944                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" < 'val_6')"#,
1945                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" < 'val_5')"#,
1946                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" < 'val_4')"#,
1947                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" < 'val_3')"#,
1948                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" < 'val_2')"#,
1949                r#"OR "t"."col_1" < 'val_1'"#,
1950                r#"ORDER BY "t"."col_1" ASC, "t"."col_2" ASC, "t"."col_3" ASC, "t"."col_4" ASC, "t"."col_5" ASC, "t"."col_6" ASC, "t"."col_7" ASC, "t"."col_8" ASC"#,
1951            ].join(" "))
1952        );
1953
1954        assert_eq!(
1955            DbBackend::Postgres.build(&
1956                Entity::find()
1957                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5, Column::Col6, Column::Col7, Column::Col8, Column::Col9))
1958                .before(("val_1", "val_2", "val_3", "val_4", "val_5", "val_6", "val_7", "val_8", "val_9")).apply_limit().apply_order_by().apply_filters()?
1959                .query
1960            ).to_string(),
1961            format!("{base_sql} {}", [
1962                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" = 'val_7' AND "t"."col_8" = 'val_8' AND "t"."col_9" < 'val_9')"#,
1963                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" = 'val_7' AND "t"."col_8" < 'val_8')"#,
1964                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" < 'val_7')"#,
1965                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" < 'val_6')"#,
1966                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" < 'val_5')"#,
1967                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" < 'val_4')"#,
1968                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" < 'val_3')"#,
1969                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" < 'val_2')"#,
1970                r#"OR "t"."col_1" < 'val_1'"#,
1971                r#"ORDER BY "t"."col_1" ASC, "t"."col_2" ASC, "t"."col_3" ASC, "t"."col_4" ASC, "t"."col_5" ASC, "t"."col_6" ASC, "t"."col_7" ASC, "t"."col_8" ASC, "t"."col_9" ASC"#,
1972            ].join(" "))
1973        );
1974
1975        Ok(())
1976    }
1977
1978    #[test]
1979    fn cursor_by_many_desc() -> Result<(), DbErr> {
1980        use composite_entity::*;
1981
1982        let base_sql = [
1983            r#"SELECT "t"."col_1", "t"."col_2", "t"."col_3", "t"."col_4", "t"."col_5", "t"."col_6", "t"."col_7", "t"."col_8", "t"."col_9", "t"."col_10", "t"."col_11", "t"."col_12""#,
1984            r#"FROM "t" WHERE"#,
1985        ].join(" ");
1986
1987        assert_eq!(
1988            DbBackend::Postgres.build(&
1989                Entity::find()
1990                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4))
1991                .before(("val_1", "val_2", "val_3", "val_4")).desc().apply_limit().apply_order_by().apply_filters()?.query
1992            ).to_string(),
1993            format!("{base_sql} {}", [
1994                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" > 'val_4')"#,
1995                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" > 'val_3')"#,
1996                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" > 'val_2')"#,
1997                r#"OR "t"."col_1" > 'val_1'"#,
1998                r#"ORDER BY "t"."col_1" DESC, "t"."col_2" DESC, "t"."col_3" DESC, "t"."col_4" DESC"#,
1999            ].join(" "))
2000        );
2001
2002        assert_eq!(
2003            DbBackend::Postgres.build(&
2004                Entity::find()
2005                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5))
2006                .before(("val_1", "val_2", "val_3", "val_4", "val_5")).desc().apply_limit().apply_order_by().apply_filters()?
2007                .query
2008            ).to_string(),
2009            format!("{base_sql} {}", [
2010                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" > 'val_5')"#,
2011                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" > 'val_4')"#,
2012                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" > 'val_3')"#,
2013                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" > 'val_2')"#,
2014                r#"OR "t"."col_1" > 'val_1'"#,
2015                r#"ORDER BY "t"."col_1" DESC, "t"."col_2" DESC, "t"."col_3" DESC, "t"."col_4" DESC, "t"."col_5" DESC"#,
2016            ].join(" "))
2017        );
2018
2019        assert_eq!(
2020            DbBackend::Postgres.build(&
2021                Entity::find()
2022                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5, Column::Col6))
2023                .before(("val_1", "val_2", "val_3", "val_4", "val_5", "val_6")).desc().apply_limit().apply_order_by().apply_filters()?
2024                .query
2025            ).to_string(),
2026            format!("{base_sql} {}", [
2027                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" > 'val_6')"#,
2028                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" > 'val_5')"#,
2029                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" > 'val_4')"#,
2030                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" > 'val_3')"#,
2031                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" > 'val_2')"#,
2032                r#"OR "t"."col_1" > 'val_1'"#,
2033                r#"ORDER BY "t"."col_1" DESC, "t"."col_2" DESC, "t"."col_3" DESC, "t"."col_4" DESC, "t"."col_5" DESC, "t"."col_6" DESC"#,
2034            ].join(" "))
2035        );
2036
2037        assert_eq!(
2038            DbBackend::Postgres.build(&
2039                Entity::find()
2040                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5, Column::Col6, Column::Col7))
2041                .after(("val_1", "val_2", "val_3", "val_4", "val_5", "val_6", "val_7")).desc().apply_limit().apply_order_by().apply_filters()?
2042                .query
2043            ).to_string(),
2044            format!("{base_sql} {}", [
2045                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" < 'val_7')"#,
2046                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" < 'val_6')"#,
2047                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" < 'val_5')"#,
2048                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" < 'val_4')"#,
2049                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" < 'val_3')"#,
2050                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" < 'val_2')"#,
2051                r#"OR "t"."col_1" < 'val_1'"#,
2052                r#"ORDER BY "t"."col_1" DESC, "t"."col_2" DESC, "t"."col_3" DESC, "t"."col_4" DESC, "t"."col_5" DESC, "t"."col_6" DESC, "t"."col_7" DESC"#,
2053            ].join(" "))
2054        );
2055
2056        assert_eq!(
2057            DbBackend::Postgres.build(&
2058                Entity::find()
2059                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5, Column::Col6, Column::Col7, Column::Col8))
2060                .after(("val_1", "val_2", "val_3", "val_4", "val_5", "val_6", "val_7", "val_8")).desc().apply_limit().apply_order_by().apply_filters()?
2061                .query
2062            ).to_string(),
2063            format!("{base_sql} {}", [
2064                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" = 'val_7' AND "t"."col_8" < 'val_8')"#,
2065                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" < 'val_7')"#,
2066                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" < 'val_6')"#,
2067                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" < 'val_5')"#,
2068                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" < 'val_4')"#,
2069                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" < 'val_3')"#,
2070                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" < 'val_2')"#,
2071                r#"OR "t"."col_1" < 'val_1'"#,
2072                r#"ORDER BY "t"."col_1" DESC, "t"."col_2" DESC, "t"."col_3" DESC, "t"."col_4" DESC, "t"."col_5" DESC, "t"."col_6" DESC, "t"."col_7" DESC, "t"."col_8" DESC"#,
2073            ].join(" "))
2074        );
2075
2076        assert_eq!(
2077            DbBackend::Postgres.build(&
2078                Entity::find()
2079                .cursor_by((Column::Col1, Column::Col2, Column::Col3, Column::Col4, Column::Col5, Column::Col6, Column::Col7, Column::Col8, Column::Col9))
2080                .after(("val_1", "val_2", "val_3", "val_4", "val_5", "val_6", "val_7", "val_8", "val_9")).desc().apply_limit().apply_order_by().apply_filters()?
2081                .query
2082            ).to_string(),
2083            format!("{base_sql} {}", [
2084                r#"("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" = 'val_7' AND "t"."col_8" = 'val_8' AND "t"."col_9" < 'val_9')"#,
2085                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" = 'val_7' AND "t"."col_8" < 'val_8')"#,
2086                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" = 'val_6' AND "t"."col_7" < 'val_7')"#,
2087                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" = 'val_5' AND "t"."col_6" < 'val_6')"#,
2088                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" = 'val_4' AND "t"."col_5" < 'val_5')"#,
2089                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" = 'val_3' AND "t"."col_4" < 'val_4')"#,
2090                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" = 'val_2' AND "t"."col_3" < 'val_3')"#,
2091                r#"OR ("t"."col_1" = 'val_1' AND "t"."col_2" < 'val_2')"#,
2092                r#"OR "t"."col_1" < 'val_1'"#,
2093                r#"ORDER BY "t"."col_1" DESC, "t"."col_2" DESC, "t"."col_3" DESC, "t"."col_4" DESC, "t"."col_5" DESC, "t"."col_6" DESC, "t"."col_7" DESC, "t"."col_8" DESC, "t"."col_9" DESC"#,
2094            ].join(" "))
2095        );
2096
2097        Ok(())
2098    }
2099
2100    mod test_base_entity {
2101        use crate as sea_orm;
2102        use crate::entity::prelude::*;
2103
2104        #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
2105        #[sea_orm(table_name = "base")]
2106        pub struct Model {
2107            #[sea_orm(primary_key)]
2108            pub id: i32,
2109            #[sea_orm(primary_key)]
2110            pub name: String,
2111        }
2112
2113        #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
2114        pub enum Relation {
2115            #[sea_orm(has_many = "super::test_related_entity::Entity")]
2116            TestRelatedEntity,
2117        }
2118
2119        impl Related<super::test_related_entity::Entity> for Entity {
2120            fn to() -> RelationDef {
2121                Relation::TestRelatedEntity.def()
2122            }
2123        }
2124
2125        impl ActiveModelBehavior for ActiveModel {}
2126    }
2127
2128    mod test_related_entity {
2129        use super::test_base_entity;
2130        use crate as sea_orm;
2131        use crate::entity::prelude::*;
2132
2133        #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
2134        #[sea_orm(table_name = "related")]
2135        pub struct Model {
2136            #[sea_orm(primary_key)]
2137            pub id: i32,
2138            #[sea_orm(primary_key)]
2139            pub name: String,
2140            pub test_id: i32,
2141        }
2142
2143        #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
2144        pub enum Relation {
2145            #[sea_orm(
2146                belongs_to = "test_base_entity::Entity",
2147                from = "Column::TestId",
2148                to = "super::test_base_entity::Column::Id"
2149            )]
2150            TestBaseEntity,
2151        }
2152
2153        impl Related<super::test_base_entity::Entity> for Entity {
2154            fn to() -> RelationDef {
2155                Relation::TestBaseEntity.def()
2156            }
2157        }
2158
2159        impl ActiveModelBehavior for ActiveModel {}
2160    }
2161
2162    #[test]
2163    fn related_composite_keys_1() -> Result<(), DbErr> {
2164        let db = MockDatabase::new(DbBackend::Postgres)
2165            .append_query_results([[(
2166                test_base_entity::Model {
2167                    id: 1,
2168                    name: "CAT".into(),
2169                },
2170                test_related_entity::Model {
2171                    id: 1,
2172                    name: "CATE".into(),
2173                    test_id: 1,
2174                },
2175            )]])
2176            .into_connection();
2177
2178        assert!(
2179            !test_base_entity::Entity::find()
2180                .find_also_related(test_related_entity::Entity)
2181                .cursor_by((test_base_entity::Column::Id, test_base_entity::Column::Name))
2182                .first(1)
2183                .all(&db)?
2184                .is_empty()
2185        );
2186
2187        assert_eq!(
2188            db.into_transaction_log(),
2189            [Transaction::many([Statement::from_sql_and_values(
2190                DbBackend::Postgres,
2191                [
2192                    r#"SELECT "base"."id" AS "A_id", "base"."name" AS "A_name","#,
2193                    r#""related"."id" AS "B_id", "related"."name" AS "B_name", "related"."test_id" AS "B_test_id""#,
2194                    r#"FROM "base""#,
2195                    r#"LEFT JOIN "related" ON "base"."id" = "related"."test_id""#,
2196                    r#"ORDER BY "base"."id" ASC, "base"."name" ASC, "related"."id" ASC, "related"."name" ASC LIMIT $1"#,
2197                ]
2198                .join(" ")
2199                .as_str(),
2200                [1_u64.into()]
2201            ),])]
2202        );
2203
2204        Ok(())
2205    }
2206
2207    #[test]
2208    fn related_composite_keys_1_desc() -> Result<(), DbErr> {
2209        let db = MockDatabase::new(DbBackend::Postgres)
2210            .append_query_results([[(
2211                test_base_entity::Model {
2212                    id: 1,
2213                    name: "CAT".into(),
2214                },
2215                test_related_entity::Model {
2216                    id: 1,
2217                    name: "CATE".into(),
2218                    test_id: 1,
2219                },
2220            )]])
2221            .into_connection();
2222
2223        assert!(
2224            !test_base_entity::Entity::find()
2225                .find_also_related(test_related_entity::Entity)
2226                .cursor_by((test_base_entity::Column::Id, test_base_entity::Column::Name))
2227                .last(1)
2228                .desc()
2229                .all(&db)?
2230                .is_empty()
2231        );
2232
2233        assert_eq!(
2234            db.into_transaction_log(),
2235            [Transaction::many([Statement::from_sql_and_values(
2236                DbBackend::Postgres,
2237                [
2238                    r#"SELECT "base"."id" AS "A_id", "base"."name" AS "A_name","#,
2239                    r#""related"."id" AS "B_id", "related"."name" AS "B_name", "related"."test_id" AS "B_test_id""#,
2240                    r#"FROM "base""#,
2241                    r#"LEFT JOIN "related" ON "base"."id" = "related"."test_id""#,
2242                    r#"ORDER BY "base"."id" ASC, "base"."name" ASC, "related"."id" ASC, "related"."name" ASC LIMIT $1"#,
2243                ]
2244                .join(" ")
2245                .as_str(),
2246                [1_u64.into()]
2247            ),])]
2248        );
2249
2250        Ok(())
2251    }
2252
2253    #[test]
2254    fn related_composite_keys_2() -> Result<(), DbErr> {
2255        let db = MockDatabase::new(DbBackend::Postgres)
2256            .append_query_results([[(
2257                test_base_entity::Model {
2258                    id: 1,
2259                    name: "CAT".into(),
2260                },
2261                test_related_entity::Model {
2262                    id: 1,
2263                    name: "CATE".into(),
2264                    test_id: 1,
2265                },
2266            )]])
2267            .into_connection();
2268
2269        assert!(
2270            !test_base_entity::Entity::find()
2271                .find_also_related(test_related_entity::Entity)
2272                .cursor_by((test_base_entity::Column::Id, test_base_entity::Column::Name))
2273                .after((1, "C".to_string()))
2274                .first(2)
2275                .all(&db)?
2276                .is_empty()
2277        );
2278
2279        assert_eq!(
2280            db.into_transaction_log(),
2281            [Transaction::many([Statement::from_sql_and_values(
2282                DbBackend::Postgres,
2283                [
2284                    r#"SELECT "base"."id" AS "A_id", "base"."name" AS "A_name","#,
2285                    r#""related"."id" AS "B_id", "related"."name" AS "B_name", "related"."test_id" AS "B_test_id""#,
2286                    r#"FROM "base""#,
2287                    r#"LEFT JOIN "related" ON "base"."id" = "related"."test_id""#,
2288                    r#"WHERE ("base"."id" = $1 AND "base"."name" > $2) OR "base"."id" > $3"#,
2289                    r#"ORDER BY "base"."id" ASC, "base"."name" ASC, "related"."id" ASC, "related"."name" ASC LIMIT $4"#,
2290                ]
2291                .join(" ")
2292                .as_str(),
2293                [
2294                    1_i32.into(),
2295                    "C".into(),
2296                    1_i32.into(),
2297                    2_u64.into(),
2298                ]
2299            ),])]
2300        );
2301
2302        Ok(())
2303    }
2304
2305    #[test]
2306    fn related_composite_keys_2_desc() -> Result<(), DbErr> {
2307        let db = MockDatabase::new(DbBackend::Postgres)
2308            .append_query_results([[(
2309                test_base_entity::Model {
2310                    id: 1,
2311                    name: "CAT".into(),
2312                },
2313                test_related_entity::Model {
2314                    id: 1,
2315                    name: "CATE".into(),
2316                    test_id: 1,
2317                },
2318            )]])
2319            .into_connection();
2320
2321        assert!(
2322            !test_base_entity::Entity::find()
2323                .find_also_related(test_related_entity::Entity)
2324                .cursor_by((test_base_entity::Column::Id, test_base_entity::Column::Name))
2325                .before((1, "C".to_string()))
2326                .last(2)
2327                .desc()
2328                .all(&db)?
2329                .is_empty()
2330        );
2331
2332        assert_eq!(
2333            db.into_transaction_log(),
2334            [Transaction::many([Statement::from_sql_and_values(
2335                DbBackend::Postgres,
2336                [
2337                    r#"SELECT "base"."id" AS "A_id", "base"."name" AS "A_name","#,
2338                    r#""related"."id" AS "B_id", "related"."name" AS "B_name", "related"."test_id" AS "B_test_id""#,
2339                    r#"FROM "base""#,
2340                    r#"LEFT JOIN "related" ON "base"."id" = "related"."test_id""#,
2341                    r#"WHERE ("base"."id" = $1 AND "base"."name" > $2) OR "base"."id" > $3"#,
2342                    r#"ORDER BY "base"."id" ASC, "base"."name" ASC, "related"."id" ASC, "related"."name" ASC LIMIT $4"#,
2343                ]
2344                .join(" ")
2345                .as_str(),
2346                [
2347                    1_i32.into(),
2348                    "C".into(),
2349                    1_i32.into(),
2350                    2_u64.into(),
2351                ]
2352            ),])]
2353        );
2354
2355        Ok(())
2356    }
2357
2358    #[test]
2359    fn related_composite_keys_3() -> Result<(), DbErr> {
2360        let db = MockDatabase::new(DbBackend::Postgres)
2361            .append_query_results([[(
2362                test_base_entity::Model {
2363                    id: 1,
2364                    name: "CAT".into(),
2365                },
2366                test_related_entity::Model {
2367                    id: 1,
2368                    name: "CATE".into(),
2369                    test_id: 1,
2370                },
2371            )]])
2372            .into_connection();
2373
2374        assert!(
2375            !test_base_entity::Entity::find()
2376                .find_also_related(test_related_entity::Entity)
2377                .cursor_by_other((
2378                    test_related_entity::Column::Id,
2379                    test_related_entity::Column::Name
2380                ))
2381                .after((1, "CAT".to_string()))
2382                .first(2)
2383                .all(&db)?
2384                .is_empty()
2385        );
2386
2387        assert_eq!(
2388            db.into_transaction_log(),
2389            [Transaction::many([Statement::from_sql_and_values(
2390                DbBackend::Postgres,
2391                [
2392                    r#"SELECT "base"."id" AS "A_id", "base"."name" AS "A_name","#,
2393                    r#""related"."id" AS "B_id", "related"."name" AS "B_name", "related"."test_id" AS "B_test_id""#,
2394                    r#"FROM "base""#,
2395                    r#"LEFT JOIN "related" ON "base"."id" = "related"."test_id""#,
2396                    r#"WHERE ("related"."id" = $1 AND "related"."name" > $2) OR "related"."id" > $3"#,
2397                    r#"ORDER BY "related"."id" ASC, "related"."name" ASC, "base"."id" ASC, "base"."name" ASC LIMIT $4"#,
2398                ]
2399                .join(" ")
2400                .as_str(),
2401                [
2402                    1_i32.into(),
2403                    "CAT".into(),
2404                    1_i32.into(),
2405                    2_u64.into(),
2406                ]
2407            ),])]
2408        );
2409
2410        Ok(())
2411    }
2412
2413    #[test]
2414    fn related_composite_keys_3_desc() -> Result<(), DbErr> {
2415        let db = MockDatabase::new(DbBackend::Postgres)
2416            .append_query_results([[(
2417                test_base_entity::Model {
2418                    id: 1,
2419                    name: "CAT".into(),
2420                },
2421                test_related_entity::Model {
2422                    id: 1,
2423                    name: "CATE".into(),
2424                    test_id: 1,
2425                },
2426            )]])
2427            .into_connection();
2428
2429        assert!(
2430            !test_base_entity::Entity::find()
2431                .find_also_related(test_related_entity::Entity)
2432                .cursor_by_other((
2433                    test_related_entity::Column::Id,
2434                    test_related_entity::Column::Name
2435                ))
2436                .before((1, "CAT".to_string()))
2437                .last(2)
2438                .desc()
2439                .all(&db)?
2440                .is_empty()
2441        );
2442
2443        assert_eq!(
2444            db.into_transaction_log(),
2445            [Transaction::many([Statement::from_sql_and_values(
2446                DbBackend::Postgres,
2447                [
2448                    r#"SELECT "base"."id" AS "A_id", "base"."name" AS "A_name","#,
2449                    r#""related"."id" AS "B_id", "related"."name" AS "B_name", "related"."test_id" AS "B_test_id""#,
2450                    r#"FROM "base""#,
2451                    r#"LEFT JOIN "related" ON "base"."id" = "related"."test_id""#,
2452                    r#"WHERE ("related"."id" = $1 AND "related"."name" > $2) OR "related"."id" > $3"#,
2453                    r#"ORDER BY "related"."id" ASC, "related"."name" ASC, "base"."id" ASC, "base"."name" ASC LIMIT $4"#,
2454                ]
2455                .join(" ")
2456                .as_str(),
2457                [
2458                    1_i32.into(),
2459                    "CAT".into(),
2460                    1_i32.into(),
2461                    2_u64.into(),
2462                ]
2463            ),])]
2464        );
2465
2466        Ok(())
2467    }
2468}