SQL 'IN' operator

[JojiiOfficial]

I came across the problem that I have a list of IDs I want to exclude from a result. So I'd like to build a query with diesel which results in something like this: SELECT * FROM users WHERE id NOT IN (1,3,9) and returns all users except those with id 1, 3 or 9.

[weiznich]

Could you elaborate why https://docs.diesel.rs/1.4.x/diesel/expression_methods/trait.ExpressionMethods.html#method.ne_all does not fit your needs?

[vdods]

I'm trying to figure out how to write a diesel query using the SQL IN operator, where the operand for IN is not trivially expressible as a finite, list, but rather is fully general as in: https://www.w3schools.com/sql/sql_in.asp

Looking through the QueryDsl docs ( https://docs.diesel.rs/diesel/query_dsl/trait.QueryDsl.html ), it's unclear if this exists, or if so which method should be used.

In particular, I want to be able to also use the NOT IN operator.

Thanks in advance!

[weiznich]

Could you elaborate why .eq_any and .ne_all do not fit your needs?

[vdods]

Thanks for your reply. It does meet my needs, but where I got tripped up was that the examples made it look like you could only pass in fixed iterables to eq_any/ne_all, instead of full select statements.

For others' reference, what worked for me looked like:

// Only import the schemas themselves so that it's clear what's part of each table's DSL and what isn't.
// I find that not doing this makes the code too cryptic to read later.
use schema::{table_1, table_A};
// Form the query.
let f = table_1::dsl::table_1.filter(
    table_1::dsl::column_1.eq_any(
        table_A::table.select(table_A::dsl::column_A)
    )
);
// Do whatever else with the query, like load the results.
let results = f.load::<ResultType>(db_conn)?;

[weiznich]

The documentation of the master branch has a corresponding example for the usage of subqueries. So this will be more clear with the next release. If there is anything else to improve here, please submit a PR.