How to implement this pattern in diesel

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 :grin:

From QueryDsl in diesel::prelude - Rust youll need to use alias! too

1 Like

Ahh alias! looks like it's the missing piece. Thanks! :smile: