我的锈柴油分页代码出了什么问题

Question

我想在使用防锈柴油diesel = { version = "1.4.7", features = ["postgres","32-column-tables"] }时进行分页查询，这是我的分页代码：

use diesel::prelude::*;
use diesel::query_dsl::methods::LoadQuery;
use diesel::query_builder::{QueryFragment, Query, AstPass};
use diesel::pg::Pg;
use diesel::sql_types::BigInt;
use diesel::QueryId;
pub trait PaginateForQueryFragment: Sized {
fn paginate(self, page: i64) -> Paginated<Self>;
}
impl<T> PaginateForQueryFragment for T
where T: QueryFragment<Pg>{
fn paginate(self, page: i64) -> Paginated<Self> {
Paginated {
query: self,
per_page: 10,
page,
is_sub_query: true,
}
}
}
#[derive(Debug, Clone, Copy, QueryId)]
pub struct Paginated<T> {
query: T,
page: i64,
per_page: i64,
is_sub_query: bool
}
impl<T> Paginated<T> {
pub fn per_page(self, per_page: i64) -> Self {
Paginated { per_page, ..self }
}

pub 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 = self.load::<(U, i64)>(conn)?;
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);
}
impl<T> RunQueryDsl<PgConnection> for Paginated<T> {}

impl<T> QueryFragment<Pg> for Paginated<T>
where
T: QueryFragment<Pg>,
{
fn walk_ast(&self, mut out: AstPass<Pg>) -> QueryResult<()> {
out.push_sql("SELECT *, COUNT(*) OVER () FROM ");
if self.is_sub_query {
out.push_sql("(");
}
self.query.walk_ast(out.reborrow())?;
if self.is_sub_query {
out.push_sql(")");
}
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(())
}
}

#[derive(Debug, Clone, Copy, QueryId)]
pub struct QuerySourceToQueryFragment<T> {
query_source: T,
}
impl<FC, T> QueryFragment<Pg> for QuerySourceToQueryFragment<T>
where
FC: QueryFragment<Pg>,
T: QuerySource<FromClause=FC>,
{
fn walk_ast(&self, mut out: AstPass<Pg>) -> QueryResult<()> {
self.query_source.from_clause().walk_ast(out.reborrow())?;
Ok(())
}
}
pub trait PaginateForQuerySource: Sized {
fn paginate(self, page: i64) -> Paginated<QuerySourceToQueryFragment<Self>>;
}
impl<T> PaginateForQuerySource for T
where T: QuerySource {
fn paginate(self, page: i64) -> Paginated<QuerySourceToQueryFragment<Self>> {
Paginated {
query: QuerySourceToQueryFragment {query_source: self},
per_page: 10,
page,
is_sub_query: false, 
}
}
}

然后我在单元测试中进行分页查询，如下所示：

#[cfg(test)]
mod test {
use std::env;
use diesel::{Connection, ExpressionMethods, PgConnection, QueryDsl, RunQueryDsl};
use rust_wheel::common::query::pagination::PaginateForQuerySource;
use crate::model::diesel::rhythm::rhythm_schema::favorites::dsl::favorites;
use crate::model::diesel::rhythm::rhythm_schema::favorites::like_status;
use crate::models::Favorites;
#[test]
fn page_test(){
use crate::model::diesel::rhythm::rhythm_schema::favorites::dsl::*;
use rust_wheel::common::query::pagination::{PaginateForQueryFragment, PaginateForQuerySource};
let conn = establish_music_connection();
let query = favorites
.filter(like_status.eq(1))
.paginate(1)
.per_page(10)
.load::<Favorites>(&conn)
.expect("query fav failed");
println!("{:?}", 1);
}
pub fn establish_music_connection() -> PgConnection {
let database_url = std::env::var("MUSIC_DATABASE_URL").expect("MUSIC_DATABASE_URL must be set");
PgConnection::establish(&database_url).expect(&format!("Error connecting to {}", database_url))
}
}

显示如下错误：

error[E0277]: the trait bound `(i64, std::option::Option<i64>, i64, i64, i64, std::string::String, i32, i32, i64, i32, std::option::Option<i32>, std::option::Option<i32>): Queryable<((BigInt, diesel::sql_types::Nullable<BigInt>, BigInt, BigInt, BigInt, Text, Integer, Integer, BigInt, Integer, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Integer>), BigInt), Pg>` is not satisfied
--> src/test/app/music/fav/fav_music.rs:21:14
|
21   |             .load::<Favorites>(&conn)
|              ^^^^ the trait `Queryable<((BigInt, diesel::sql_types::Nullable<BigInt>, BigInt, BigInt, BigInt, Text, Integer, Integer, BigInt, Integer, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Integer>), BigInt), Pg>` is not implemented for `(i64, std::option::Option<i64>, i64, i64, i64, std::string::String, i32, i32, i64, i32, std::option::Option<i32>, std::option::Option<i32>)`
|
= help: the following implementations were found:
<(A, B, C, D, E, F, G, H, I, J, K, L) as Queryable<(SA, SB, SC, SD, SE, SF, SG, SH, SI, SJ, SK, SL), __DB>>
<(A, B, C, D, E, F, G, H, I, J, K, L) as Queryable<diesel::sql_types::Record<(SA, SB, SC, SD, SE, SF, SG, SH, SI, SJ, SK, SL)>, Pg>>
note: required because of the requirements on the impl of `Queryable<((BigInt, diesel::sql_types::Nullable<BigInt>, BigInt, BigInt, BigInt, Text, Integer, Integer, BigInt, Integer, diesel::sql_types::Nullable<Integer>, diesel::sql_types::Nullable<Integer>), BigInt), Pg>` for `Favorites`
--> src/models.rs:14:22
|
14   | #[derive( Serialize, Queryable, Deserialize,Default)]
|                      ^^^^^^^^^
15   | // #[table_name = "favorites"]
16   | pub struct Favorites {
|            ^^^^^^^^^
= note: required because of the requirements on the impl of `LoadQuery<PgConnection, Favorites>` for `Paginated<diesel::query_builder::SelectStatement<rhythm_schema::favorites::table, query_builder::select_clause::DefaultSelectClause, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<rhythm_schema::favorites::columns::like_status, diesel::expression::bound::Bound<Integer, i32>>>>>`
note: required by a bound in `load`
--> /Users/dolphin/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-1.4.8/src/query_dsl/mod.rs:1238:15
|
1238 |         Self: LoadQuery<Conn, U>,
|               ^^^^^^^^^^^^^^^^^^ required by this bound in `load`
= note: this error originates in the derive macro `Queryable` (in Nightly builds, run with -Z macro-backtrace for more info)

当我删除单元测试代码中的分页行时，它工作得很好。无法成功查询数据库中的数据。所以我认为我的分页代码出了问题。我读了代码，但不知道哪里出了问题，我应该怎么做才能解决这个问题？这是我的Favorites定义：

#[derive( Serialize, Queryable, Deserialize,Default)]
// #[table_name = "favorites"]
pub struct Favorites {
pub id: i64,
pub song_id: Option<i64>,
pub created_time: i64,
pub updated_time: i64,
pub user_id: i64,
pub source_id: String,
pub like_status: i32,
pub source: i32,
pub playlist_id: i64,
pub play_count: i32,
pub fetched_download_url: Option<i32>,
pub downloaded: Option<i32>
}

这是PostgreSQL 13:中的DDL表

-- Drop table
-- DROP TABLE public.favorites;
CREATE TABLE public.favorites (
id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
song_id int8 NULL,
created_time int8 NOT NULL,
updated_time int8 NOT NULL,
user_id int8 NOT NULL,
source_id varchar NOT NULL,
like_status int4 NOT NULL,
"source" int4 NOT NULL,
playlist_id int8 NOT NULL,
play_count int4 NOT NULL DEFAULT 1,
fetched_download_url int4 NULL DEFAULT 0,
downloaded int4 NULL DEFAULT 0,
CONSTRAINT favorites_id_seq_pk PRIMARY KEY (id),
CONSTRAINT unique_idx UNIQUE (source_id, user_id)
);
CREATE UNIQUE INDEX fav_uniq_idx ON public.favorites USING btree (source_id, user_id);

Answer 1

分页代码将查询从一个返回Favorites转换为一个返回的(Favorites, i64)。它通过SELECT *, COUNT(*)添加一列以跟踪总计数。

您应该使用提供的.load_and_count_pages()而不是.load()，或者您可以使用.select()只获得原始列。如果您不需要计数，只需要限制和偏移量，则可以修改代码以避免添加列。