Pagination guide doesn't work on entity represented by joined tables

Question

Pagination guide doesn't work on entity represented by joined tables

nimrodkor opened this issue 5 months ago · 2 comments

nimrodkor commented 5 months ago

Setup

I have 2 tables:
Restaurants:

id
name
location

Tables:

number
restaurant_id
seat_number
is_free

The tables are marked as joinable in the generated schema.rs.

Versions

Rust: rustc 1.74.1
Diesel: 2.1.4
Database: Postgres
Operating System: mac

Feature Flags

diesel: "chrono", "serde_json"

Problem Description

I would like to paginate on free tables, but need the restaurant data as well. The model I want to paginate on:
RestaurantTable:

restaurant_id
name
location
table_number
seat_number
is_free = true

When I use the guide at https://diesel.rs/guides/extending-diesel.html, this is the code I end up with:

// pagination.rs
//! Source: <https://diesel.rs/guides/extending-diesel.html>
use diesel::pg::Pg;
use diesel::query_builder::{AstPass, Query, QueryFragment};
use diesel::query_dsl::LoadQuery;
use diesel::sql_types::Integer;
use diesel::{PgConnection, QueryId, QueryResult, RunQueryDsl};

pub trait Paginate: Sized {
    fn paginate(self, page_num: i32, page_size: i32) -> Paginated<Self>;
}

impl<T> Paginate for T {
    fn paginate(self, page_num: i32, page_size: i32) -> Paginated<Self> {
        Paginated {
            query: self,
            page_size,
            offset: page_size * page_num,
        }
    }
}

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

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(") as paged_query_with LIMIT ");
        out.push_bind_param::<Integer, _>(&self.page_size)?;
        out.push_sql(" OFFSET ");
        out.push_bind_param::<Integer, _>(&self.offset)?;
        Ok(())
    }
}

impl<T> Paginated<T> {
    pub fn load_and_count_pages<'a, U>(
        self,
        conn: &mut PgConnection,
    ) -> QueryResult<(Vec<U>, i32, bool)>
    where
        Self: LoadQuery<'a, PgConnection, (U, i32)>,
    {
        let total_queried = self.offset + self.page_size;
        let results = self.load::<(U, i32)>(conn)?;
        let total = results.get(0).map_or(0, |x| x.1);
        let records = results.into_iter().map(|x| x.0).collect();
        let has_more = total > total_queried;
        Ok((records, total, has_more))
    }
}

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

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

// db_manager.rs
let free_tables: (Vec<Restaurant, Table>, i32, bool) = restaurants::table()
            .inner_join(tables::table())
            .filter(is_free.eq(true))
            .select((Restaurant::as_select(), Table::as_select()))
            .paginate(page_num, page_size)
            .load_and_count_pages(&mut connection)?;

What are you trying to accomplish?

Paginating over an entity that represents a joined table. I've also tried to create a struct that defines the resulting fields, but it seems that it can't be Selectable without a #[diesel(table_name = "...")] annotation, and obviously a model that represents a join of 2 models doesn't have 1 table to use here

What is the expected output?

Paginated list of the tuple (Restaurant, Table).

What is the actual output?

error[E0277]: the trait bound `(diesel::expression::select_by::SelectBy<db::models::Repository, diesel::pg::Pg>, diesel::expression::select_by::SelectBy<db::models::CodeChange, diesel::pg::Pg>): diesel::util::TupleSize` is not satisfied
  --> crates/codebase_management/src/db/code_changes_db_manager.rs:72:14
   |
72 |             .load_and_count_pages(&mut connection)?;
   |              ^^^^^^^^^^^^^^^^^^^^ the trait `diesel::util::TupleSize` is not implemented for `(diesel::expression::select_by::SelectBy<db::models::Repository, diesel::pg::Pg>, diesel::expression::select_by::SelectBy<db::models::CodeChange, diesel::pg::Pg>)`
   |
   = help: the following other types implement trait `diesel::util::TupleSize`:
             (T0,)
             (T0, T1)
             (T0, T1, T2)
             (T0, T1, T2, T3)
             (T0, T1, T2, T3, T4)
             (T0, T1, T2, T3, T4, T5)
             (T0, T1, T2, T3, T4, T5, T6)
             (T0, T1, T2, T3, T4, T5, T6, T7)
           and 24 others
   = note: required for `(db::models::Repository, db::models::CodeChange)` to implement `diesel::deserialize::StaticallySizedRow<(diesel::expression::select_by::SelectBy<db::models::Repository, diesel::pg::Pg>, diesel::expression::select_by::SelectBy<db::models::CodeChange, diesel::pg::Pg>), diesel::pg::Pg>`
   = note: required for `((db::models::Repository, db::models::CodeChange), i32)` to implement `diesel::deserialize::FromStaticSqlRow<((diesel::expression::select_by::SelectBy<db::models::Repository, diesel::pg::Pg>, diesel::expression::select_by::SelectBy<db::models::CodeChange, diesel::pg::Pg>), diesel::sql_types::Integer), diesel::pg::Pg>`
   = note: required for `((db::models::Repository, db::models::CodeChange), i32)` to implement `diesel::Queryable<((diesel::expression::select_by::SelectBy<db::models::Repository, diesel::pg::Pg>, diesel::expression::select_by::SelectBy<db::models::CodeChange, diesel::pg::Pg>), diesel::sql_types::Integer), diesel::pg::Pg>`
   = note: required for `((db::models::Repository, db::models::CodeChange), i32)` to implement `diesel::deserialize::FromSqlRow<((diesel::expression::select_by::SelectBy<db::models::Repository, diesel::pg::Pg>, diesel::expression::select_by::SelectBy<db::models::CodeChange, diesel::pg::Pg>), diesel::sql_types::Integer), diesel::pg::Pg>`
   = note: required for `((SelectBy<Repository, Pg>, SelectBy<CodeChange, Pg>), Integer)` to implement `diesel::query_dsl::load_dsl::private::CompatibleType<((db::models::Repository, db::models::CodeChange), i32), diesel::pg::Pg>`
   = note: the full type name has been written to '/Users/nimrodkor/Projects/baz/platform/target/debug/deps/codebase_management-edab074a227d5339.long-type-13515196522775546583.txt'
   = note: required for `Paginated<BoxedSelectStatement<'_, (SelectBy<Repository, Pg>, SelectBy<CodeChange, Pg>), FromClause<...>, ...>>` to implement `diesel::query_dsl::LoadQuery<'_, diesel::PgConnection, ((db::models::Repository, db::models::CodeChange), i32)>`
   = note: the full type name has been written to '/Users/nimrodkor/Projects/baz/platform/target/debug/deps/codebase_management-edab074a227d5339.long-type-12037448283329689932.txt'
note: required by a bound in `central_db_schema::pagination::Paginated::<T>::load_and_count_pages`
  --> /Users/nimrodkor/Projects/baz/platform/crates/central_db_schema/src/pagination.rs:50:15
   |
45 |     pub fn load_and_count_pages<'a, U>(
   |            -------------------- required by a bound in this associated function
...
50 |         Self: LoadQuery<'a, PgConnection, (U, i32)>,
   |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `Paginated::<T>::load_and_count_pages`

Are you seeing any additional errors?

Nope

Steps to reproduce

Detailed above

Checklist

I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

This issue can be reproduced on Rust's stable channel. (Your issue will be
closed if this is not the case)
This issue can be reproduced without requiring a third party crate

Answer 1 · 2023-12-27T21:32:37.000Z

I'm open to contributing if you help me with some pointers, BTW!

Answer 2 · 2023-12-28T17:47:27.000Z

Thanks for filling this issue.

The title of the guide in question is Extending Diesel not Pagination Guide. This implies that guide want's to teach something else than "How to implement pagination with diesel". It want's to show how to extend diesel. For this a example is chosen an a simplified implementation is provided. The later point is particular important as it does not want to implement a complete pagination extension. Instead it focuses on the extension aspect.

I don't treat that as bug in diesel so I will close it as won't fix. That written: I will convert that issue to a discussion, so that someone can provide help to your specific use case there.