How to write multiple explicit inner_joins with Diesel?

user_19889388 0
Viewed 57

I am currently working on an api using actix_web and diesel in rust. Now to my question; I am trying to make an sql request like this one:

SELECT * FROM customers c
JOIN users u ON u.name = c.customer_no
JOIN address a ON c.fk_address = a.id
WHERE a.name='10715';

There is an actual documentation for multiple joins using diesel but just for implicit joins, which can be found here:

https://docs.diesel.rs/diesel/query_dsl/trait.QueryDsl.html#method.inner_join

Whats the difference between explicit and implicit?

Implicit defines the relationsship via a macro:

users.inner_join(posts)

Explicit defines the relationsship in the query statement:

users.inner_join(posts::table.on(posts::id.eq(user::id)))

Does anyone know how to make a request using multiple explicit joins:

this works:

users.inner_join(posts.inner_join(comments))

this doesn't:

users.inner_join(
    posts::table.on(posts::id.eq(user::id))
        .inner_join(comments::table.on(comments::id.eq(post::id)))
)
rust-diesel rust
1 Answers

Your initial SQL query would be written with Diesel like this (complete example):

customers::table
    .inner_join(users::table.on(users::name.eq(customers::customer_no)))
    .inner_join(address::table.on(address::id.eq(customers::fk_address)))
    .filter(users::name.eq("10715"));

You may be wary of this because the documentation on .inner_join() warns against chained vs nested JOINs being different:

Diesel expects multi-table joins to be semantically grouped based on the relationships. For example, users.inner_join(posts.inner_join(comments)) is not the same as users.inner_join(posts).inner_join(comments). The first would deserialize into (User, (Post, Comment)) and generate the following SQL:

SELECT * FROM users
    INNER JOIN posts ON posts.user_id = users.id
    INNER JOIN comments ON comments.post_id = posts.id

While the second query would deserialize into (User, Post, Comment) and generate the following SQL:

SELECT * FROM users
    INNER JOIN posts ON posts.user_id = users.id
    INNER JOIN comments ON comments.user_id = users.id

However, the issue being presented is only a problem because of the implicit ON clause generated by the joinable! macro. The query will default to joining comments to users if they are chained instead of joining comments to posts as one might expect. That is not a problem when you explicitly provide the ON clause; there is no ambiguity to what you want since you are writing the JOIN criteria yourself.

You can always use debug_query to print out the generated SQL if you have any doubt.

user_21891300
Related