page_hunter/pagination/sqlx/
queries.rs

1use std::future::Future;
2
3use sqlx::{
4    ColumnIndex, Connection, Database, Decode, Error as SqlxError, Executor, FromRow,
5    IntoArguments, QueryBuilder, Transaction, Type, query, query_scalar,
6};
7
8#[allow(unused_imports)]
9use crate::{ErrorKind, Page, PaginationError, PaginationResult};
10
11/// Trait to paginate results from a SQL query into a [`Page`] model from database using [`sqlx`].
12pub trait SQLxPagination<DB, S>
13where
14    DB: Database,
15    for<'b> &'b mut DB::Connection: Executor<'b, Database = DB>,
16    for<'c> i64: Type<DB> + Decode<'c, DB>,
17    for<'d> DB::Arguments<'d>: IntoArguments<'d, DB>,
18    usize: ColumnIndex<<DB>::Row>,
19    S: for<'r> FromRow<'r, DB::Row> + Clone,
20{
21    /// Paginate results from a SQL query into a [`Page`] model from database using [`sqlx`].
22    /// Available for Postgres, MySQL or SQLite databases.
23    ///
24    /// ### Arguments:
25    /// - **conn**: A mutable reference to a connection to the database.
26    /// - **page**: The page index.
27    /// - **size**: The number of records per page.
28    ///
29    /// ### Returns:
30    /// A [`PaginationResult`] containing a [`Page`] model of the paginated records `S`, where `S` must implement the [`FromRow`] for given [`Database::Row`] type according to the database.
31    ///
32    /// Only available when the `sqlx` feature is enabled.
33    fn paginate(
34        &self,
35        conn: &mut DB::Connection,
36        page: usize,
37        size: usize,
38    ) -> impl Future<Output = PaginationResult<Page<S>>>;
39}
40
41impl<DB, S> SQLxPagination<DB, S> for QueryBuilder<'_, DB>
42where
43    DB: Database,
44    for<'c> &'c mut DB::Connection: Executor<'c, Database = DB>,
45    for<'d> i64: Type<DB> + Decode<'d, DB>,
46    for<'a> DB::Arguments<'a>: IntoArguments<'a, DB>,
47    usize: ColumnIndex<<DB>::Row>,
48    S: for<'r> FromRow<'r, DB::Row> + Clone,
49{
50    fn paginate(
51        &self,
52        conn: &mut DB::Connection,
53        page: usize,
54        size: usize,
55    ) -> impl Future<Output = PaginationResult<Page<S>>> {
56        let query_str: &str = self.sql();
57
58        async move {
59            let mut tx: Transaction<'_, DB> = conn.begin().await?;
60
61            let total: usize = query_scalar::<DB, i64>(&format!(
62                "SELECT count(*) from ({query_str}) as temp_table;"
63            ))
64            .fetch_one(&mut *tx)
65            .await? as usize;
66
67            let rows: Vec<DB::Row> = query::<DB>(&format!(
68                "{query_str} LIMIT {size} OFFSET {offset};",
69                offset = size * page,
70            ))
71            .fetch_all(&mut *tx)
72            .await?;
73
74            tx.commit().await?;
75
76            let items: Vec<S> = rows
77                .into_iter()
78                .map(|r| S::from_row(&r))
79                .collect::<Result<Vec<S>, SqlxError>>()?;
80
81            Page::new(&items, page, size, total)
82        }
83    }
84}
page_hunter/pagination/sqlx/queries.rs

page_hunter/pagination/sqlx/
queries.rs
