JOIN and query_as!

[alucryd]

How do you handle joined rows with the query_as! syntax? I tried specifying a tuple but it doesn't work and I didn't find any example anywhere.

[abonander]

@alucryd can you give an example of what you're trying to do?

[alucryd]

Sure thing, this is the select I'm talking about: https://github.com/alucryd/oxyromon/blob/a3b7b1ff10473a7ac9c689166dafb502616435b8/src/database.rs#L593

Had to narrow the select to return only the roms columns, or else query_as! would complain because there were more columns than fields in the struct. Also scratched my head for a while when using the non macro version because the returned Rom struct contained fields from the joined games table which wasn't obvious at first.

So how do you tell sqlx that the returned rows contain 2 structs? diesel uses a tuple in this case but I could't find the sqlx equivalent.

[Kruptein]

I'm in the process of trying to figure this out as well, based on the examples in #1014 I think the following is supposed to work:

struct CombinedStruct {
    a_result: A,
    b_result: B,
}

query_as!(CombinedStruct,
    r#"SELECT a as "a_result!: A", b as "b_result!: B"
       FROM first_table as a INNER JOIN second_table as b ON a.some_col = b.other_col"#
).fetch_all(conn).await?;

This assumes the A and B structs are derived with sqlx::Type. You can also change a as " with something like ((a.col1, a.col2) as "

[jplatte]

@Kruptein what errors are you running into with the above? Just the one from #1031 or something else too?

[Kruptein]

I get

implementation of `sqlx::Decode` is not general enough
`std::option::Option<std::string::String>` must implement `sqlx::Decode<'0, Postgres>`, for any lifetime `'0`...
...but `std::option::Option<std::string::String>` actually implements `sqlx::Decode<'1, Postgres>`, for some specific lifetime `'1`

which seems to just be the same Option decode problem.

My struct also gets flagged with a warning "unnecessary braces around function argument", if I add the sqlx::Type derive, but unsure if that's a problem

[jplatte]

My struct also gets flagged with a warning "unnecessary braces around function argument", if I add the sqlx::Type derive, but unsure if that's a problem

Yeah, that's also kind of a bug in the macros. Maybe adding #[automatically_derived] will be enough to fix it. Worst case the generated code will have to disable that lint.