Join table twice

1

Hi,

I have a table1 which refers to table2 by two columns. For example:

create table posts (
   author bigint references author(id),
   editor bigint references author(id),
);

Is there a way to join the same table twice?

2

If you are looking for a simple way to do this the answer is: No that’s currently not possible with diesel, as this would require support for aliases to generate a valid query. Otherwise you would end with invalid SQL like SELECT * FROM posts INNER JOIN author ON author.id = posts.author INNER JOIN author ON author.id = posts.editor where you cannot tell to which joined table author now exactly refers.

That said, it is possible to make something like this work with the currently released diesel version, but it will require quite some work. There is this WIP PR to diesel that would add native type safe support for table aliases to diesel itself. You can write similar code in a third party crate, with the restriction that it will not have that nice integration with some diesel things. (I’ve done this for one of my own projects with basically that code). That implies that you need to write even more things manually that would be otherwise generated by some of the diesel macros, especially all those impls here. If you go down that route and hit any roadblock just ask for help here.

1 Like