I'm trying to translate the following postgres query to diesel:
WITH foo_table AS ( SELECT foo_bar_id, json AS foo FROM foo_bar WHERE key = '1' ),
bar_table AS ( SELECT foo_bar_id, json AS bar FROM foo_bar WHERE key = '2' )
SELECT foo, bar FROM foo_table INNER JOIN bar_table USING foo_bar_id
TLDR: I have a table foo_bar
with 3 columns: foo_bar_id
, json
, key
. The key
column determines whether the row is a foo
or a bar
.
I want to join two rows from the same table where they have the same foo_bar_id
, and extract the corresponding json
fields.
I'm not sure if this is even possible with diesel
's DSL, but I've tried the following, without much success:
let foo_table = select((foo_bar::foo_bar_id, foo_bar::json)).filter(foo_bar::key.eq(1));
but I don't know how to go about giving a different name to foo_bar::json
depending on the corresponding key column.
Happy to provide more details if this isn't enough, but at this point I'm not sure what more context to add.
Thanks