Currently I’m facing a problem with left_join.
I’m trying so many thing and can’t figure out how to do that correctly.
The case is:
I have some of books in my Postgresql database. I need to get last chapter for every book. Important thing, need information about Null value in book chapter. That’s why I decided use left join.
My models and schema:
#[derive(Associations, Identifiable, Queryable,
PartialEq, Debug, Deserialize, Serialize)]
#[diesel(belongs_to(User))]
#[diesel(table_name = books)]
pub struct Book {
pub id: i32,
pub user_id: i32,
pub title: String,
pub book_description: String,
pub normalize_book_title: String,
pub book_image: Option<String>,
pub publish_day: chrono::NaiveDateTime,
}
#[derive(Associations, Identifiable, Queryable,
PartialEq, Debug, Deserialize, Serialize)]
#[diesel(belongs_to(Book))]
#[diesel(table_name = book_chapters)]
pub struct BookChapter {
pub id: i32,
pub book_id: i32,
pub title: String,
pub normalize_title: String,
pub body: String,
pub description: String,
pub publish_day: chrono::NaiveDateTime,
}
diesel::table! {
books (id) {
id -> Int4,
user_id -> Int4,
title -> Varchar,
book_description -> Varchar,
normalize_book_title -> Varchar,
book_image -> Nullable<Varchar>,
publish_day -> Timestamp,
}
}
diesel::table! {
book_chapters (id) {
id -> Int4,
book_id -> Int4,
title -> Varchar,
normalize_title -> Varchar,
body -> Text,
description -> Varchar,
publish_day -> Timestamp,
}
}
diesel::joinable!(book_chapters -> books (book_id));
diesel::allow_tables_to_appear_in_same_query!(books, book_chapters);
For me models looking good.
My code in Actix:
pub fn get_all_books_with_last_chapters_left_join(db_connection: &mut PgConnection) -> Result<Vec<(String, String)>, DieselError> {
let result = books::table.left_join(book_chapters::table)
.select((book::title.nullable(), chapter::title.nullable()))
.load(db_connection);
result
}
I have handling an exception in different place.
when I’m trying run my code is raising an exception:
error[E0277]: the trait bound `(std::string::String, std::string::String): FromStaticSqlRow<(diesel::sql_types::Nullable<diesel::sql_types::Text>, diesel::sql_types::Nullable<diesel::sql_types::Text>), Pg>` is not satisfied
--> database/src/database_manager.rs:126:27
|
126 | .load(db_connection);
| ---- ^^^^^^^^^^^^^ the trait `FromStaticSqlRow<(diesel::sql_types::Nullable<diesel::sql_types::Text>, diesel::sql_types::Nullable<diesel::sql_types::Text>), Pg>` is not implemented for `(std::string::String, std::string::String)`
| |
| required by a bound introduced by this call
|
= help: the following other types implement trait `FromStaticSqlRow<ST, DB>`:
<(T0,) as FromStaticSqlRow<(ST0,), __DB>>
<(T1, T0) as FromStaticSqlRow<(ST1, ST0), __DB>>
<(T1, T2, T0) as FromStaticSqlRow<(ST1, ST2, ST0), __DB>>
<(T1, T2, T3, T0) as FromStaticSqlRow<(ST1, ST2, ST3, ST0), __DB>>
<(T1, T2, T3, T4, T0) as FromStaticSqlRow<(ST1, ST2, ST3, ST4, ST0), __DB>>
<(T1, T2, T3, T4, T5, T0) as FromStaticSqlRow<(ST1, ST2, ST3, ST4, ST5, ST0), __DB>>
<(T1, T2, T3, T4, T5, T6, T0) as FromStaticSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, ST0), __DB>>
<(T1, T2, T3, T4, T5, T6, T7, T0) as FromStaticSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, ST7, ST0), __DB>>
and 24 others
= note: required for `(std::string::String, std::string::String)` to implement `diesel::Queryable<(diesel::sql_types::Nullable<diesel::sql_types::Text>, diesel::sql_types::Nullable<diesel::sql_types::Text>), Pg>`
= note: required for `(std::string::String, std::string::String)` to implement `FromSqlRow<(diesel::sql_types::Nullable<diesel::sql_types::Text>, diesel::sql_types::Nullable<diesel::sql_types::Text>), Pg>`
= note: required for `(diesel::sql_types::Nullable<diesel::sql_types::Text>, diesel::sql_types::Nullable<diesel::sql_types::Text>)` to implement `load_dsl::private::CompatibleType<(std::string::String, std::string::String), Pg>`
= note: required for `SelectStatement<FromClause<JoinOn<Join<table, table, LeftOuter>, Grouped<Eq<Nullable<book_id>, Nullable<id>>>>>, SelectClause<(Nullable<title>, Nullable<title>)>>` to implement `LoadQuery<'_, PgConnection, (std::string::String, std::string::String)>`
= note: the full type name has been written to '/home/redbeardman/Development/ActixWeb/blog_site/target/debug/deps/database-ab8567b4f90f5964.long-type-17256427855449653182.txt'
note: required by a bound in `diesel::RunQueryDsl::load`
--> /home/redbeardman/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-2.0.3/src/query_dsl/mod.rs:1499:15
|
1499 | Self: LoadQuery<'query, Conn, U>,
| ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::load`
For more information about this error, try `rustc --explain E0277`.
I found here one question related to left_join, but it didn’t help me to solve my problem.
How can I fix this?
I was trying inner_join and it work well. My working inner_join:
pub fn get_all_books_with_last_chapters(db_connection: &mut PgConnection) -> Result<Vec<(String, String)>,
DieselError> {
let result: Result<Vec<(String, String)>,
diesel::result::Error> = books::table.inner_join(book_chapters::table)
.select((book::title, chapter::title))
.load(db_connection);
result
}
>Solution :
This looks to be a simple type mismatch. You’ve specified the types in .select should be .nullable() which means the Diesel-SQL type for those columns are Nullable<Text> but you are only loading them as Strings which are not compatible; What is the value if its NULL?
From the query, you should always have a book::title but may not have a chapter::title. So your select and types should reflect that. Try this:
pub fn get_all_books_with_last_chapters_left_join(
db_connection: &mut PgConnection,
) -> Result<Vec<(String, Option<String>)>, DieselError> {
// ^^^^^^^^^^^^^^
let result = books::table
.left_join(book_chapters::table)
.select((book::title, chapter::title.nullable()))
// ^^^^^^^^^^^
.load(db_connection);
result
}