Extending diesel with pagination support

1

Hi, i’m following the tutorial at http://diesel.rs/guides/extending-diesel/, trying to implement pagination. However I would like to do this for sqlite. As far as i know i will need to run two queries, one to get the count of pages, and one to get the actual result. The guide mentions this might be necessary if using something other than Postgres. I’m feeling a bit lost on how to do this, in the load_and_count_pages() i guess i would like to clone the query somehow and then do .count() on it, run it and use the results, but i’m not sure how i would go about doing that.

What i’ve got so far, mostly copy-pasted:

    pub trait Paginate: Sized {
    fn paginate(self, page: i64) -> Paginated<Self>;
}

impl<T> Paginate for T {
    fn paginate(self, page: i64) -> Paginated<Self> {
        Paginated {
            query: self,
            per_page: DEFAULT_PER_PAGE,
            page,
        }
    }
}

const DEFAULT_PER_PAGE: i64 = 10;

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

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

    fn load_and_count_pages<U>(self, conn: &SqliteConnection) -> QueryResult<(Vec<U>, i64)>
    where
        Self: LoadQuery<SqliteConnection, (U, i64)>,
    {
        Ok((vec![], 0))
    }
}

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

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

impl<T> QueryFragment<Sqlite> for Paginated<T>
where
    T: QueryFragment<Sqlite>,
{
    fn walk_ast(&self, mut out: AstPass<Sqlite>) -> QueryResult<()> {
        out.push_sql("SELECT * FROM (");
        self.query.walk_ast(out.reborrow())?;
        out.push_sql(") t LIMIT ");
        out.push_bind_param::<BigInt, _>(&self.per_page)?;
        out.push_sql(" OFFSET ");
        let offset = (self.page - 1) * self.per_page;
        out.push_bind_param::<BigInt, _>(&offset)?;
        Ok(())
    }
}

If I add a trait bound like this:

fn load_and_count_pages<U>(self, conn: &SqliteConnection) -> QueryResult<(Vec<U>, i64)>
where
    Self: LoadQuery<SqliteConnection, (U, i64)> + QueryDsl,
{
    let new_query = self.count();
    Ok((vec![], 0))
}

I get the following error:

error[E0275]: overflow evaluating the requirement `<db::pagination::Paginated<T> as diesel::query_dsl::select_dsl::SelectDsl<diesel::dsl::CountStar>>::Output`
  --> src/db/pagination.rs:46:30
   |
46 |         let new_query = self.count();
   |                              ^^^^^

And I’m not sure how I would solve that. I’m guessing the problem is that I’m trying to modify a query that’s already got a select statement bound to it? Could I maybe box the query and have that help me somehow? Any help is appreciated!

Thanks in advance!

2

Hi again, I managed to solve it by using this query in walk_ast:

    out.push_sql("SELECT * FROM (");
    self.query.walk_ast(out.reborrow())?;
    out.push_sql(") INNER JOIN (SELECT COUNT(*) FROM (");
    self.query.walk_ast(out.reborrow())?;
    out.push_sql(")) LIMIT ");
    out.push_bind_param::<BigInt, _>(&self.per_page)?;
    out.push_sql(" OFFSET ");
    let offset = (self.page - 1) * self.per_page;
    out.push_bind_param::<BigInt, _>(&offset)?;

Which should work for now, however, it would be nice to know how i’m supposed to solve this if I actually need to run 2 different queries?

3

If you wanted to be able to call .count() on it, you’d need to satisfy the constraints of that method (which unfortunately gives you the error that you saw if they aren’t upheld), which is that you implement SelectDsl<CountStar>. This isn’t really reasonable to do, but you could instead do something like this:

impl<T> Paginated<T> {
    fn load_and_count_pages<U>(self, conn: &SqliteConnection) -> QueryResult<(Vec<U>, i64)>
    where
        Self: Clone + LoadQuery<SqliteConnection, U>,
        T: SelectDsl<CountStar>,
        Select<T, CountStar>: LoadQuery<SqliteConnection, i64>,