subquery with eq_any doesn't compile

Question

The relevant bits of my model:

table! {
    server_website {
        id -> Integer,
        server_database_id -> Nullable<Integer>,
        server_id -> Integer,
    }
}

table! {
    server_database {
        id -> Integer,
        server_id -> Integer,
    }
}

table! {
    server {
        id -> Integer,
    }
}

joinable!(server_website -> server_database (server_database_id));
allow_tables_to_appear_in_same_query!(server_website, server_database);

#[derive(Queryable, Debug, Clone, PartialEq, Eq)]
pub struct Server {
    pub id: i32,
}

#[derive(Queryable, Debug, Clone, PartialEq, Eq)]
pub struct ServerWebsite {
    pub id: i32,
    pub server_database_id: Option<i32>,
    pub server_id: i32,
}

#[derive(Queryable, Debug, Clone, PartialEq, Eq)]
pub struct ServerDatabase {
    pub id: i32,
    pub server_id: i32,
}

I'm trying to express the following working SQLite query with Diesel:

select * from server_website where server_database_id in (
    select id from server_database where server_id = 83
);

And here is my diesel query code:

use projectpadsql::schema::server::dsl as srv;
use projectpadsql::schema::server_database::dsl as db;
use projectpadsql::schema::server_website::dsl as srvw;

let database_ids_from_server = db::server_database
    .filter(db::server_id.eq(83))
    .select(db::id);
let used_websites = srvw::server_website
    .filter(srvw::server_database_id.eq_any(database_ids_from_server))
    .load::<ServerWebsite>(sql_conn)
    .unwrap();

This fails with the error:

error[E0277]: the trait bound `diesel::query_builder::SelectStatement<projectpadsql::schema::server_database::table, diesel::query_builder::select_clause::SelectClause<projectpadsql::schema::server_database::columns::id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<projectpadsql::schema::server_database::columns::server_id, diesel::expression::bound::Bound<diesel::sql_types::Integer, i32>>>>: diesel::expression::array_comparison::AsInExpression<diesel::sql_types::Nullable<diesel::sql_types::Integer>>` is not satisfied

I see that the compiler would like the inner query to have the type AsInExpression<Nullable<Integer>> which looks good to me (besides the nullable).

The inner query has more or less the type SelectStatement<server_database, SelectClause<server_database::id>>, which looks good to me since the id is Integer just like the outer query wants.

I have the feeling I'm quite close, but I can't put my finger on what the issue is.

Answer 1

I needed to use nullable() to get the types matching. Notice the db::id.nullable():

let database_ids_from_server = db::server_database
    .filter(db::server_id.eq(server_id))
    .select(db::id.nullable());
let used_websites = srvw::server_website
    .filter(srvw::server_database_id.eq_any(database_ids_from_server))
    .load::<ServerWebsite>(sql_conn)
    .unwrap();

Georg Semmler - @weiznich on the Diesel Gitter channel answered this question.