Replies: 5 comments 4 replies
-
|
The error message is not 100% unambiguous here. As for the actual error: Without having a complete minimal example (I'm sorry but I do currently not have the capacity to dig through large projects) I can only guess, but possibly you need to provide information for the sql types of each column as explained in the documentation As for support for lateral joins: PRs are welcome |
Beta Was this translation helpful? Give feedback.
-
|
From having another look at this:
|
Beta Was this translation helpful? Give feedback.
-
|
Thanks for your reply. I tried to use a subquery in the select expression like you suggested, but postgres doesnt allow it: select
*,
array(
select * from article where article.instance_id = instance.id order by article.title limit 2
)
from instance;
ERROR: subquery must return only one column
LINE 3: array(Maybe Im missing something, but lateral join seems to be the best option. To get it working I replaced use of diesel(embed) with this: #[derive(Clone, Debug, Serialize, Deserialize, PartialEq)]
#[derive(Queryable, QueryableByName)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct InstanceView {
#[diesel(sql_type = instance::SqlType)]
pub instance: DbInstance,
#[diesel(sql_type = diesel::sql_types::Array<article::SqlType>)]
pub articles: Vec<DbArticle>,
}Which results in the following error: build output
So something is still wrong with the sql_type, but I cant tell how to fix it. The printed db values exactly match the Rust struct, and deserialization should be handled by |
Beta Was this translation helpful? Give feedback.
-
Note that this query is now different to the query you've posted above. That one above did literally just use a single field from the subquery.
This assumption is wrong, as In the end it boils down to:
|
Beta Was this translation helpful? Give feedback.
-
|
Sorry I was testing different things and copied it wrong. Ive now changed the view to use #[derive(Clone, Debug, Serialize, Deserialize, PartialEq)]
#[derive(Queryable, QueryableByName)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct InstanceView {
#[diesel(sql_type = instance::SqlType)]
pub instance: DbInstance,
#[diesel(sql_type = Array<diesel::pg::sql_types::Record<article::SqlType>>)]
pub articles: Vec<DbArticle>,
}And also added the impl you mentioned: impl FromSql<diesel::pg::sql_types::Record<article::SqlType>, Pg> for DbArticle {
fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
let res: (
ArticleId,
String,
String,
ObjectId<DbArticle>,
InstanceId,
bool,
bool,
bool,
DateTime<Utc>,
) = FromSql::<diesel::pg::sql_types::Record<article::SqlType>, Pg>::from_sql(bytes)?;
Ok(DbArticle {
id: res.0,
title: res.1,
text: res.2,
ap_id: res.3,
instance_id: res.4,
local: res.5,
protected: res.6,
approved: res.7,
published: res.8,
})
}
}However Im still missing something as it throws another error: build output
I tried to write the select parameters manually as Edit: I also tried to |
Beta Was this translation helpful? Give feedback.
-
|
That's still not what I meant above: #[derive(Clone, Debug, Serialize, Deserialize, PartialEq)]
#[derive(Queryable, QueryableByName)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct InstanceView {
#[diesel(sql_type = instance::SqlType)]
pub instance: DbInstance,
#[diesel(sql_type = Array<diesel::pg::sql_types::Record<article::SqlType>>)]
pub articles: Vec<DbArticle>,
}Note that for the |
Beta Was this translation helpful? Give feedback.
-
|
In the string passed to sql_query, you might need to use If sql_query doesn't work, then create a struct that implements Query, QueryFragment, and QueryId. With this solution, |
Beta Was this translation helpful? Give feedback.
-
You could make this a bit more robust by adding a From time to time I think about the possibility to basically add a generator for this kind of construct to diesel-cli, by basically connecting to the database, getting the necessary type details from there and generating the relevant code based on that. If someone is interested on working on this I can share more details on that idea. |
Beta Was this translation helpful? Give feedback.
-
|
Got back to this now and still cant get it working. I dont want to mess with this more and will go with multiple separate queries for now. Anyway I added the code to an example in case one of you wants to get it working and possibly add it as an example in the repo. Thanks for the help! |
Beta Was this translation helpful? Give feedback.
-
I have the following sql structure:
schema.rs
And the following structs:
Now I want to write an SQL query which selects a list of instances, together with a limited number of articles from each instance:
Unfortunately lateral joins are not supported in diesel so I use sql_query:
Unfortunately this results in an error
build output
The error message suggests adding
.select(InstanceView::as_select())to the query but that only gives a different error:build output
The error seems to indicate that diesel expects
InstanceViewto refer to an actual database table, but that doesnt make sense here. And I want to avoid implementing these traits manually. How can I get this query working?The code is open source, you can try it with:
Beta Was this translation helpful? Give feedback.
All reactions