Pagination with diesel_async

[romch007]

I've implemented pagination based on https://github.com/diesel-rs/diesel/blob/master/examples/postgres/advanced-blog-cli/src/pagination.rs, but I want to switch to diesel_async now and I'm having trouble migrating the code.

The sync version of the pagination mechanism:

use diesel::pg::Pg;
use diesel::prelude::*;
use diesel::query_builder::*;
use diesel::query_dsl::methods::LoadQuery;
use diesel::sql_types::BigInt;
use serde::Deserialize;
use serde::Serialize;

pub trait Paginate: Sized {
    fn paginate(self, params: &PaginationParams) -> Paginated<Self>;
}

impl<T> Paginate for T {
    fn paginate(self, params: &PaginationParams) -> Paginated<Self> {
        Paginated {
            query: self,
            limit: params.limit,
            offset: (params.page - 1) * params.limit,
        }
    }
}

#[derive(Debug, Clone, Copy, QueryId)]
pub struct Paginated<T> {
    query: T,
    limit: i64,
    offset: i64,
}

impl<T> Paginated<T> {
    pub fn load_and_count_total<'a, U>(self, conn: &mut PgConnection) -> QueryResult<(Vec<U>, i64)>
    where
        Self: LoadQuery<'a, PgConnection, (U, i64)>,
    {
        let results = self.load::<(U, i64)>(conn)?;
        let total = results.first().map_or(0, |x| x.1);
        let records = results.into_iter().map(|x| x.0).collect();
        Ok((records, total))
    }
}

impl<T: Query> Query for Paginated<T> {
    type SqlType = (T::SqlType, BigInt);
}

impl<T> RunQueryDsl<PgConnection> for Paginated<T> {}

impl<T> QueryFragment<Pg> for Paginated<T>
where
    T: QueryFragment<Pg>,
{
    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, Pg>) -> QueryResult<()> {
        out.push_sql("SELECT *, COUNT(*) OVER () FROM (");
        self.query.walk_ast(out.reborrow())?;
        out.push_sql(") t LIMIT ");
        out.push_bind_param::<BigInt, _>(&self.limit)?;
        out.push_sql(" OFFSET ");
        out.push_bind_param::<BigInt, _>(&self.offset)?;
        Ok(())
    }
}

My attempt to migrate to diesel_async:

use diesel::pg::Pg;
use diesel::prelude::*;
use diesel::query_builder::*;
use diesel::query_dsl::methods::LoadQuery;
use diesel::sql_types::BigInt;
use diesel_async::pg::AsyncPgConnection;
use serde::{Deserialize, Serialize};

pub trait Paginate: Sized {
    fn paginate(self, params: &PaginationParams) -> Paginated<Self>;
}

impl<T> Paginate for T {
    fn paginate(self, params: &PaginationParams) -> Paginated<Self> {
        Paginated {
            query: self,
            limit: params.limit,
            offset: (params.page - 1) * params.limit,
        }
    }
}

#[derive(Debug, Clone, Copy, QueryId)]
pub struct Paginated<T> {
    query: T,
    limit: i64,
    offset: i64,
}

impl<T> Paginated<T> {
    pub async fn load_and_count_total<'a, U>(
        self,
        conn: &mut AsyncPgConnection,
    ) -> QueryResult<(Vec<U>, i64)>
    where
        Self: LoadQuery<'a, AsyncPgConnection, (U, i64)>,
    {
        let results = self.load::<(U, i64)>(conn).await?;
        let total = results.first().map_or(0, |x| x.1);
        let records = results.into_iter().map(|x| x.0).collect();
        Ok((records, total))
    }
}

impl<T: Query> Query for Paginated<T> {
    type SqlType = (T::SqlType, BigInt);
}

// impl<T> RunQueryDsl<AsyncPgConnection> for Paginated<T> {}

impl<T> QueryFragment<Pg> for Paginated<T>
where
    T: QueryFragment<Pg>,
{
    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, Pg>) -> QueryResult<()> {
        out.push_sql("SELECT *, COUNT(*) OVER () FROM (");
        self.query.walk_ast(out.reborrow())?;
        out.push_sql(") t LIMIT ");
        out.push_bind_param::<BigInt, _>(&self.limit)?;
        out.push_sql(" OFFSET ");
        out.push_bind_param::<BigInt, _>(&self.offset)?;
        Ok(())
    }
}

The compiler throws the trait bound 'AsyncPgConnection: LoadConnection' is not satisfied and I'm stuck now.
Any help would be appreciated.

[weiznich]

That impl is nearly correct. The main remaining problem is that you import diesel::query_dsl::methods::LoadQuery (which offers a sync trait) instead of diesel_async::methods::LoadQuery (which is the async counter part). If you change that import the compiler will also complain about a few missing additional trait bounds for the load_and_count_total function. You need to add a U: Send +'a bound there and a T: 'a bound as well. After that it should just work.