Implementing pager

[tyoc213]

Hi there, trying to get a hang of rust and diesel, have tried to follow the extending query with the paginator example, you can find my current code which throws

error[E0277]: the trait bound `<PaginatedResult<T> as LimitDsl>::Output: ToSql<_, Pg>` is not satisfied
  --> src/bin/extending_pager.rs:22:29
   |
22 |         out.push_bind_param(&self.limit(self.per_page))?;
   |                             ^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `ToSql<_, Pg>` is not implemented for `<PaginatedResult<T> as LimitDsl>::Output`
   |
   = help: the following implementations were found:
             <&'a T as ToSql<A, DB>>

I paste the code here for easy access on current state, the full code is there if you want to test locally.

//use diesel::backend::{BindCollector, QueryBuilder};
// use diesel::impl_query_id; // deprecated since 1.1.0
use diesel::pg::Pg;
use diesel::query_builder::{AsQuery, AstPass, Query, QueryFragment};
use diesel::query_dsl::limit_dsl::LimitDsl;
use diesel::sql_types::BigInt;
use diesel::{dsl, Expression};
use diesel::{PgConnection, QueryDsl, QueryResult, RunQueryDsl};
use diesel::{QueryId, Queryable};
use diesel_demo::schema::posts;

// Implement `QueryFragment`
impl<T> QueryFragment<Pg> for PaginatedResult<T>
where
    T: QueryFragment<Pg>,
    PaginatedResult<T>: LimitDsl,
{
    fn walk_ast(&self, mut out: AstPass<Pg>) -> QueryResult<()> {
        out.push_sql("select *, count(*) over () from (");
        self.query.walk_ast(out.reborrow())?;
        out.push_sql(") LIMIT ");
        out.push_bind_param(&self.limit(self.per_page))?;
        out.push_sql(" OFFSET ");
        out.push_bind_param::<BigInt, _>(&self.offset())?;
        Ok(())
    }
}

// Whenever you implement `QueryFragment` you need to implement `QueryId`
impl<T: Query> Query for PaginatedResult<T> {
    type SqlType = (T::SqlType, BigInt);
}

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

// FIXME: maybe implement this? but not this way?
// impl<T> QueryDsl for Paginated<T> {}
impl<T: LimitDsl + LimitDsl<Output = T>> LimitDsl for PaginatedResult<T> {
    type Output = PaginatedResult<dsl::Limit<T>>;

    fn limit(self, limit: i64) -> Self::Output {
        limit
    }
}

// Using `trait Paginate` to implement all that
pub trait Paginate: AsQuery + Sized {
    fn paginate(self, page: i64) -> PaginatedResult<Self::Query> {
        PaginatedResult {
            query: self.as_query(),
            page,
            per_page: DEFAULT_PER_PAGE,
        }
    }
}

impl<T: AsQuery> Paginate for T {}
const DEFAULT_PER_PAGE: i64 = 10;

#[derive(Queryable, QueryId, Debug)]
pub struct PaginatedResult<T> {
    query: T,
    page: i64,
    per_page: i64,
}

impl<T> PaginatedResult<T> {
    pub fn per_page(self, per_page: i64) -> Self {
        PaginatedResult { per_page, ..self }
    }
}

fn main() {
    let second = posts::table.paginate(3).per_page(2);
    println!("count hay {:?}", &second);
}

[weiznich]

There are multiple issues with your code:

• push_bind_paramter expects a rust value that should be bound to a specific location in your query, not a query fragment as its returned by your call to limit. Use walk_ast to add a query fragment to the current query
• The query you construct in your query fragment implementation does not make much sense syntax wise, as limit only accepts a integer value and not a complete sub query as "argument"
• Your LimitDsl implementation does not match the defined return type. Either adjust the return type to match the implementation or vice versa.

Given these points its really hard to understand what exactly you want to achieve. Therefore I cannot offer any concrete help here. Maybe have another look at the corresponding guide? Each part of the exemplary implementation is explained there in detail.

[tyoc213]

I will say that the guide is a little outdated, for example uses impl_query_id which I believe it is deprecated since 1.1.0.

Yeah, will take a look again at the guide, because afaik I followed what is there, at less for the first implementation, now it has "evolved" to this.

Notes:

typo in guide:

impl<T> Paginated<T> {

I want to paginate a query... or any query I do in the future.

[tyoc213]

OK, finally I got it implemented using the deprecated impl_query_id and changing a little the walk_ast

fn walk_ast(&self, mut out: AstPass<Pg>) -> QueryResult<()> {
        out.push_sql("SELECT *, COUNT(*) OVER () FROM (");
        self.query.walk_ast(out.reborrow())?;
        out.push_sql(") as internal LIMIT ");
        out.push_bind_param::<BigInt, _>(&self.per_page)?;
        out.push_sql(" OFFSET ");
        out.push_bind_param::<BigInt, _>(&self.page)?;
        Ok(())
    }

But retriving the results with

fn main() {
    let paginated_query = posts::table.paginate(3).per_page(2);
    println!("count hay {:?}", paginated_query);
    let conn = establish_connection();
    let e: Result<Vec<Post>, _> = paginated_query.get_results(&conn);
    println!("execution {:?}", e);
}

I get this

error[E0277]: the trait bound `(i32, String, String, bool, Option<SystemTime>, Option<i32>, Option<SystemTime>): Queryable<((Integer, Text, Text, Bool, diesel::sql_types::Nullable<Timestamp>, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Timestamp>), BigInt), Pg>` is not satisfied
  --> src/bin/extending_pager.rs:71:42
   |
71 |     let e: Result<Vec<Post>, _> = second.get_results(&conn);
   |                                          ^^^^^^^^^^^ the trait `Queryable<((Integer, Text, Text, Bool, diesel::sql_types::Nullable<Timestamp>, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Timestamp>), BigInt), Pg>` is not implemented for `(i32, String, String, bool, Option<SystemTime>, Option<i32>, Option<SystemTime>)`
   |
   = help: the following implementations were found:
             <(A, B, C, D, E, F, G) as Queryable<(SA, SB, SC, SD, SE, SF, SG), __DB>>
             <(A, B, C, D, E, F, G) as Queryable<Record<(SA, SB, SC, SD, SE, SF, SG)>, Pg>>
   = note: required because of the requirements on the impl of `Queryable<((Integer, Text, Text, Bool, diesel::sql_types::Nullable<Timestamp>, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Timestamp>), BigInt), Pg>` for `Post`
   = note: required because of the requirements on the impl of `LoadQuery<PgConnection, Post>` for `PaginatedQuery<SelectStatement<table>>`

If I got all fixed and running I can send later a PR to update the guide.

[tyoc213]

Gotcha

//use diesel::backend::{BindCollector, QueryBuilder};
//use diesel::impl_query_id; // deprecated since 1.1.0
use diesel::pg::Pg;
use diesel::query_builder::{AsQuery, AstPass, Query, QueryFragment};
use diesel::query_dsl::LoadQuery;
use diesel::sql_types::BigInt;
use diesel::{PgConnection, QueryResult, RunQueryDsl};
use diesel::{QueryId, Queryable};
use diesel_demo::establish_connection;
use diesel_demo::models::Post;
use diesel_demo::schema::posts;

// Implement `QueryFragment`
impl<T> QueryFragment<Pg> for PaginatedQuery<T>
where
    T: QueryFragment<Pg>,
{
    fn walk_ast(&self, mut out: AstPass<Pg>) -> QueryResult<()> {
        out.push_sql("SELECT *, COUNT(*) OVER () FROM (");
        self.query.walk_ast(out.reborrow())?;
        out.push_sql(") as internal LIMIT ");
        out.push_bind_param::<BigInt, _>(&self.per_page)?;
        out.push_sql(" OFFSET ");
        out.push_bind_param::<BigInt, _>(&self.page)?;
        Ok(())
    }
}

// impl_query_id!(PaginatedQuery<T>);

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

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

pub trait Paginate: AsQuery + Sized {
    fn paginate(self, page: i64) -> PaginatedQuery<Self::Query> {
        PaginatedQuery {
            query: self.as_query(),
            page,
            per_page: DEFAULT_PER_PAGE,
        }
    }
}

impl<T: AsQuery> Paginate for T {}

const DEFAULT_PER_PAGE: i64 = 10;

#[derive(Debug, QueryId)]
pub struct PaginatedQuery<T> {
    query: T,
    page: i64,
    per_page: i64,
}

impl<T> PaginatedQuery<T> {
    pub fn per_page(self, per_page: i64) -> Self {
        PaginatedQuery { per_page, ..self }
    }

    fn load_and_count_pages<U>(self, conn: &PgConnection) -> QueryResult<(Vec<U>, i64)>
    where
        Self: LoadQuery<PgConnection, (U, i64)>,
    {
        let per_page = self.per_page;
        let results: Vec<(U, i64)> = self.load(conn)?;
        let total = results.get(0).map(|(_, total)| total).unwrap_or(&0);
        let total = f64::from(*total as i32);
        let records: Vec<U> = results.into_iter().map(|(record, _)| record).collect();
        let total_pages = (total / per_page as f64).ceil() as i64;
        Ok((records, total_pages))
    }
}

fn main() {
    let conn = establish_connection();
    let results: Vec<(Post, i64)> = posts::table
        .paginate(3)
        .per_page(25)
        .get_results(&conn)
        .expect("error");
    println!("{:?}", results);

    let paginated_query = posts::table.paginate(3).per_page(2);
    let records: (Vec<Post>, i64) = paginated_query.load_and_count_pages(&conn).expect("error");
    println!("*** records {:?}", records.0);
    println!("*** pages {:?}", records.1);
}

Sended PR for update the guide and some typos.