Problem trait LoadQuery is not satisfied sub query when implement Pagination

[measproem]

Setup

Versions

• Rust: 1.73.0
• Diesel: 2.1.1
• Diesel_async: 0.4.1
• Database: postgres
• Operating System Linux ubuntu22.04

Feature Flags

• diesel: ["chrono", "extras", "bigdecimal"]
• diesel_async: ["postgres", "deadpool"]

Problem Description

I am new on Rust and I try to implement Pagination with PostgreSQL and DeadPool by inspired from diesel_filter it works with simple type not complex query like sub query

1. This working and satisfied type .load_and_count::<(String, String)>(&mut conn)

    let mut query = tbl_users.into_boxed();
    if let Some(roleid) = user.role_id {
        query = query.filter(crate::schema::tbl_users::role_id.nullable().eq(roleid));
    }
   
    let query_result = query
    .filter(
        crate::schema::tbl_users::deleted_at.is_null(),
    )
    .left_join(tbl_users_histories)
    .select((
        crate::schema::tbl_users::nickname,
        crate::schema::tbl_users::login_id,
   
    ))
    .paginate(Some(1))
    .per_page(Some(2))
    .load_and_count::<(String, String)>(&mut conn)
    .await.map_err(|err| {
        ServiceError::new(
            StatusCode::INTERNAL_SERVER_ERROR,
            false,
            format!("DB show() users error: {:?}", err),
            -1,
        )
    })?;
    println!("User Paging:{:#?}", query_result);
    todo!()
   

2. Not satisfied .load_and_count::<(HistoryInfo, String, String)>(&mut conn)

      let mut query = tbl_users.into_boxed();
      if let Some(roleid) = user.role_id {
      	query = query.filter(crate::schema::tbl_users::role_id.nullable().eq(roleid));
      }
   
      let query_result = query
      .filter(
      	crate::schema::tbl_users::deleted_at.is_null(),
      )
      .left_join(tbl_users_histories)
      .select((
      	HistoryInfo::as_select(),
      	crate::schema::tbl_users::nickname,
      	crate::schema::tbl_users::login_id,
   
      ))
      .paginate(Some(1))
      .per_page(Some(2))
      .load_and_count::<(HistoryInfo, String, String)>(&mut conn).await.map_err(|err| {
      	 ServiceError::new(
          	StatusCode::INTERNAL_SERVER_ERROR,
          	false,
          	format!("DB show() users error: {:?}", err),
          	-1,
      	)
      })?;
      println!("User Paging:{:#?}", query_result);
      todo!()
      
        
        #[derive(Debug, Serialize, Deserialize, Queryable, Selectable, Identifiable, Clone, PartialEq)]
        #[diesel(table_name = tbl_users_histories)]
        pub struct HistoryInfo {
            pub id              : i32,
            pub history_desc     : String,
        }
   

3. Paginated

use diesel::{
 pg::Pg, prelude::*, query_builder::*,  sql_types::BigInt,
};
use diesel_async::pooled_connection::AsyncDieselConnectionManager;
use diesel_async::{RunQueryDsl, AsyncPgConnection};
use diesel_async::methods::LoadQuery;
use std::result::Result;
use deadpool::managed::Object;

pub use diesel_filter_query::*;

#[cfg(feature = "serialize")]
pub use serialize::*;

pub const DEFAULT_PER_PAGE: i64 = 10;

#[cfg(feature = "serialize")]
pub mod serialize {
 use serde::Serialize;

 #[derive(Serialize)]
 pub struct PaginatedPayload<T>
 where
     T: Serialize,
 {
     pub data: Vec<T>,
     total: i64,
 }

 impl<T> From<(Vec<T>, i64)> for PaginatedPayload<T>
 where
     T: Serialize,
 {
     fn from(data: (Vec<T>, i64)) -> Self {
         Self {
             data: data.0,
             total: data.1,
         }
     }
 }
}

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

impl<T> Paginate for T {
 fn paginate(self, page: Option<i64>) -> Paginated<Self> {
     let page = page.unwrap_or(1);

     Paginated {
         query: self,
         per_page: DEFAULT_PER_PAGE,
         page: page,
         offset: (page - 1) * DEFAULT_PER_PAGE,
     }
 }
}

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

impl<T> Paginated<T> {
 pub fn per_page(self, per_page: Option<i64>) -> Self {
     let per_page = per_page.unwrap_or(DEFAULT_PER_PAGE);

     Paginated {
         per_page,
         offset: (self.page - 1) * per_page,
         ..self
     }
 }

 pub async fn load_and_count<'a, U>(
     self,
     conn: &mut Object<AsyncDieselConnectionManager<AsyncPgConnection>>,
 ) -> Result<(Vec<U>, i64), Box<dyn std::error::Error>>
 where
     Self: LoadQuery<'a, AsyncPgConnection, (U, i64)> + 'a,
     U: std::marker::Send,
 {
     let per_page = self.per_page;
     let results = self
         .load::<(U, i64)>(conn)
         .await
         .map_err(|err| Box::new(err) as Box<dyn std::error::Error>)?;
     let total = results.get(0).map(|x| x.1).unwrap_or(0);
     let records = results.into_iter().map(|x| x.0).collect();
     let total_pages = (total as f64 / per_page as f64).ceil() as i64;
     Ok((records, total_pages))
 }
 

}

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



//Comment to avoid connflict impl trait RunQueryDsl
// 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.per_page)?;
     out.push_sql(" OFFSET ");
     out.push_bind_param::<BigInt, _>(&self.offset)?;
     Ok(())
 }
}

pub struct PaginationOptions {
 pub per_page: i64,
 pub page: i64,
}

What are you trying to accomplish?

Make function pub async fn load_and_count with trait bound Self: LoadQuery<'a, AsyncPgConnection, (U, i64)> + 'a, satisfied complex type

What is the expected output?

What is the actual output?

error[E0277]: the trait bound `((diesel::expression::select_by::SelectBy<HistoryInfo, Pg>, diesel::sql_types::Text, diesel::sql_types::Text), diesel::sql_types::BigInt): CompatibleType<((HistoryInfo, std::string::String, std::string::String), i64), Pg>` is not satisfied
   --> src/repositories/user/user_repo.rs:141:10
    |
141 |         .load_and_count::<(HistoryInfo, String, String)>(&mut conn)
    |          ^^^^^^^^^^^^^^ the trait `CompatibleType<((HistoryInfo, std::string::String, std::string::String), i64), Pg>` is not implemented for `((diesel::expression::select_by::SelectBy<HistoryInfo, Pg>, diesel::sql_types::Text, diesel::sql_types::Text), diesel::sql_types::BigInt)`
    |
    = help: the following other types implement trait `CompatibleType<U, DB>`:
              (ST0,)
              (ST0, ST1)
              (ST0, ST1, ST2)
              (ST0, ST1, ST2, ST3)
              (ST0, ST1, ST2, ST3, ST4)
              (ST0, ST1, ST2, ST3, ST4, ST5)
              (ST0, ST1, ST2, ST3, ST4, ST5, ST6)
              (ST0, ST1, ST2, ST3, ST4, ST5, ST6, ST7)
            and 24 others
    = note: required for `Paginated<BoxedSelectStatement<'_, (SelectBy<HistoryInfo, Pg>, Text, Text), FromClause<JoinOn<..., ...>>, ...>>` to implement `diesel_async::methods::LoadQuery<'_, AsyncPgConnection, ((HistoryInfo, std::string::String, std::string::String), i64)>`
	note: required by a bound in `Paginated::<T>::load_and_count`
   --> /home/.../diesel_filter/core/src/pagination.rs:84:15
    |
79  |     pub async fn load_and_count<'a, U>(
    |                  -------------- required by a bound in this associated function
...
84  |         Self: LoadQuery<'a, AsyncPgConnection, (U, i64)> + 'a,
    |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `Paginated::<T>::load_and_count`

Are you seeing any additional errors?

No

Steps to reproduce

Checklist

• I have already looked over the issue tracker 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

[weiznich]

To repeat again what I've already written before: The issue tracker is no support forum, it's a tool for contributors to track bugs. Use the support forum for questions like this one. I've moved your question one last time, the next time it will be treated as bug report and likely closed as: That's not a bug.

As for your actual question: The error message suggests that this is just a type mismatch between what your query claims to return and what your struct expects the query to return. See the relevant documentation here. It's likely related to the fact that you try to load the result from a left joined table without marking it explicitly as nullable.

[measproem]

Thank next time will post in discussion,
I have tried a few difference approaches change from HistoryInfo to struct UserInfo example not related nullable or left join, .load_and_count::<(UserInfo, String, String)>(&mut conn) still got problem missmatch type that LoadQuery required

  ...
   .left_join(tbl_users_histories)
   .select((
   	UserInfo::as_select(),//mentioned here
   	crate::schema::tbl_users::nickname,
   	crate::schema::tbl_users::login_id,

   ))
   .paginate(Some(1))
   .per_page(Some(2))
   .load_and_count::<(UserInfo, String, String)>(&mut conn) 
   ...

   
   
   #[derive(Debug, Serialize, Deserialize, Queryable, Selectable, Identifiable, Clone, PartialEq)]
   #[diesel(table_name = tbl_users)]
   pub struct UserInfo {
		 pub id            : i32,
		 pub user_name     : String,
   }

[measproem]

I found my problem may benfit someone same me:
just fix by change to tuple of schema table column

 .select((
   	UserInfo::as_select(), //change here
        crate::schema::tbl_users::nickname,
   	crate::schema::tbl_users::login_id,

   ))

to

  .select((
   (
   	crate::schema::tbl_users::id,
   	crate::schema::tbl_users::user_name,
    ), //become
    crate::schema::tbl_users::nickname,
    crate::schema::tbl_users::login_id,
   ))