backend/db/
pagination.rs

1//! Paginate queries.
2
3use crate::model::dto::Page;
4use diesel::pg::Pg;
5use diesel::query_builder::{AstPass, Query, QueryFragment};
6use diesel::sql_types::{BigInt, Integer};
7use diesel::{QueryId, QueryResult};
8use diesel_async::methods::LoadQuery;
9use diesel_async::{AsyncPgConnection, RunQueryDsl};
10use std::cmp::max;
11
12/// The default number of rows returned from a paginated query.
13pub const DEFAULT_PER_PAGE: i32 = 10;
14/// The minimum value for page number in a paginated query.
15/// Pages start at 1. Using a lower value would lead to nonsensical queries.
16pub const MIN_PAGE: i32 = 1;
17/// The minimum number of rows returned from a paginated query.
18pub const MIN_PER_PAGE: i32 = 1;
19
20/// An executable paginated query.
21#[derive(Debug, Clone, Copy, QueryId)]
22pub struct PaginatedQuery<T> {
23    /// Executable query.
24    query: T,
25    /// Page number to be loaded.
26    page: i32,
27    /// Number of rows loaded in the query.
28    per_page: i32,
29    /// Offset to the the first row in the query.
30    offset: i32,
31}
32
33/// A trait intended for enabling pagination in diesel's query builder.
34pub trait Paginate: Sized {
35    /// Return a paginated version of a query for a specific page number.
36    fn paginate(self, page: Option<i32>) -> PaginatedQuery<Self>;
37}
38
39impl<T> Paginate for T {
40    fn paginate(self, page: Option<i32>) -> PaginatedQuery<Self> {
41        // allow optional pages and disallow non positive pages
42        let actual_page = max(page.unwrap_or(MIN_PAGE), MIN_PAGE);
43        PaginatedQuery {
44            query: self,
45            per_page: DEFAULT_PER_PAGE,
46            page: actual_page,
47            offset: (actual_page - 1) * DEFAULT_PER_PAGE,
48        }
49    }
50}
51
52impl<T> PaginatedQuery<T> {
53    /// Set the number of rows returned by the query.
54    #[must_use]
55    pub fn per_page(self, per_page: Option<i32>) -> Self {
56        // allow optional per_page and disallow non positive per_page
57        let actual_per_page = max(per_page.unwrap_or(DEFAULT_PER_PAGE), MIN_PER_PAGE);
58        Self {
59            per_page: actual_per_page,
60            offset: (self.page - 1) * actual_per_page,
61            ..self
62        }
63    }
64
65    /// Execute the query returning one [`Page`] of rows.
66    ///
67    /// # Errors
68    /// Unknown, diesel doesn't say why it might error.
69    pub async fn load_page<'query, 'conn, U>(
70        self,
71        conn: &'conn mut AsyncPgConnection,
72    ) -> QueryResult<Page<U>>
73    where
74        T: Send,
75        U: Send,
76        Self: LoadQuery<'query, AsyncPgConnection, (U, i64)> + 'query,
77    {
78        let page = self.page;
79        let per_page = self.per_page;
80        let query_result = self.load::<(U, i64)>(conn).await?;
81        let total = query_result.get(0).map_or(0, |x| x.1);
82        let results = query_result.into_iter().map(|x| x.0).collect();
83        let extra_page = match total % i64::from(per_page) {
84            0 => 0,
85            _ => 1,
86        };
87        #[allow(clippy::cast_possible_truncation, clippy::integer_division)]
88        let total_pages = (total / i64::from(per_page) + extra_page) as i32;
89        Ok(Page {
90            results,
91            page,
92            per_page,
93            total_pages,
94        })
95    }
96}
97
98impl<T: Query> Query for PaginatedQuery<T> {
99    type SqlType = (T::SqlType, BigInt);
100}
101
102impl<T> QueryFragment<Pg> for PaginatedQuery<T>
103where
104    T: QueryFragment<Pg>,
105{
106    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, Pg>) -> QueryResult<()> {
107        out.push_sql("SELECT *, COUNT(*) OVER () FROM (");
108        self.query.walk_ast(out.reborrow())?;
109        out.push_sql(") t LIMIT ");
110        out.push_bind_param::<Integer, _>(&self.per_page)?;
111        out.push_sql(" OFFSET ");
112        out.push_bind_param::<Integer, _>(&self.offset)?;
113        Ok(())
114    }
115}