CODEWITHSUNDEEP
rustrust-diesel
Arshia001
Arshia001

Reputation: 1872

How to make a `SELECT .... WHERE EXISTS` query with diesel?

Assuming I have a master entity (let's have it be a user) and a detail entity (let's go with adresses), how can I filter the master entity over values from its details?

Going with the user/address example, let's say we want to find all users who have an address in Berlin. In SQL, the query would probably be something like this:

SELECT * FROM user AS u
WHERE EXISTS(
    SELECT 0 FROM address AS a
    WHERE a.user_id = u.id
        AND a.city = 'Berlin'
)

To make this query in SQL, one has to use an alias (user AS u) and use it later in the a.user_id = u.id part. Is it possible to do something similar in diesel?

EDIT: I'm using diesel 2.0, rust 1.64 and here's the relevant section from schema.rs:

diesel::table! {
    addresses (id) {
        id -> Int4,
        user_id -> Int4,
        city -> Nullable<Varchar>,
    }
}

diesel::table! {
    users (id) {
        id -> Int4,
        name -> Varchar,
    }
}

diesel::joinable!(addresses -> users (user_id));

diesel::allow_tables_to_appear_in_same_query!(
    addresses,
    users,
);

Upvotes: 0

Views: 1971

Answers (1)

weiznich
weiznich

Reputation: 3435

For this specific query the usage of an alias is not required as each table appears exactly once in the query. Generally speaking: diesel does provide an alias! macro, which allows to define table aliases for later use.

As for the corresponding query: Such queries are more or less literally translated to diesel dsl using the provided functions:

let sub_query = addresses::table
    .select(0.into_sql::<diesel::sql_types::Integer>())
    .filter(addresses::user_id.eq(users::id))
    .filter(addresses::city.eq("Berlin"));
let result = users::table.filter(diesel::dsl::exists(sub_query)).load::<CompatibleType>(&mut conn)?;

Upvotes: 1

Related Questions