Unable to combine query using dsl::any.

Setup

Versions

Rust:
**Diesel: 1.4.2
**Database: Postgres
**Operating System MacOS 10.14.5

Feature Flags

**diesel: postgres, r2d2

Problem Description

Given the following code:

// schema.rs
table! {
    orgs (id) {
        id -> Int4,
        name -> Varchar,
    }
}

table! {
    users (id) {
        id -> Int4,
        username -> Varchar,
        firstname -> Varchar,
        lastname -> Varchar,
        email -> Varchar,
        orgs -> Array<Int4>,
    }
}

allow_tables_to_appear_in_same_query!(
    orgs,
    users,
);

// Models

// models.org.rs
#[derive(Serialize, Queryable)]
pub struct Org {
  pub id: i32,
  pub name: String,
}

// models.user.rs
#[derive(Serialize, Queryable)]
pub struct User {
  pub id: i32,
  pub username: String,
  pub firstname: String,
  pub lastname: String,
  pub email: String,
  pub orgs: Vec<i32>,
}

// Code
use crate::schema::{ orgs, users };

use orgs::{ table as OrgTable, dsl as OrgQuery };
use users::{ table as UserTable, dsl as UserQuery };

...in function
    let q = UserTable
      .find(path.user_id)
      .select(UserQuery::orgs);

    OrgTable
      .filter(OrgQuery::id.eq_any(q))
      .get_results::<Org>(conn)

What are you trying to accomplish?

I would like to get all the orgs that belong to the orgs array of a particular user.

What is the expected output?

I should see a vec of orgs.

FWIW, I've chatted briefly with George Semmler (@weiznich) on Gitter, and he believes this should be working.

What is the actual output?

no output.

Are you seeing any additional errors?

This results in an error in the .filter(OrgQuery::id.eq_any(q)) line of:

the trait bound `diesel::query_builder::SelectStatement<schema::users::table, diesel::query_builder::select_clause::SelectClause<schema::users::columns::orgs>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<schema::users::columns::id, diesel::expression::bound::Bound<diesel::sql_types::Integer, i32>>>>: diesel::expression::array_comparison::AsInExpression<diesel::sql_types::Integer>` is not satisfied

Steps to reproduce

Checklist

[x] I have already looked over the issue tracker for similar issues.
[x] This issue can be reproduced on Rust's stable channel. (Your issue will be closed if this is not the case)

The behavior here is correct. The query you wrote is id IN (query returning int4[]), which would error at runtime with:

ERROR:  42883: operator does not exist: integer = integer[]
LINE 1: select 1 in (select array[1,2,3]);

I'd expect = ANY((query)) to work here, but PG doesn't appear to recognize the second set of parenthesis as turning a query into a single value (which is unfortunate, since the corresponding Diesel query would compile...)

Something like this would probably work for you: .filter(array(OrgQuery::id).is_contained_by(q.single_value())). You could also use unnest:

sql_function!(fn unnest<T>(x: Array<T>) -> T);

// ...
.select(unnest(UserQuery::orgs))

I'm closing this, as there doesn't appear to be a bug here. If you need additional help, please ask in Gitter or Discourse.

diesel-rs / diesel