How do you supply a vector of values to a custom query for use in an in operator?`

[robot-head]

I'm trying to do a find_by_statement on a model, but my query contains something like:

`select column from table where column in (1,2,3)'

If I try to implement it as such, it does not compile:

query.sql:

SELECT column
FROM table
WHERE column IN $1

let possible_values = vec![1,2,3];
let model = Model::find_by_statement(Statement::from_sql_and_values(
    DatabaseBackend::Postgres,
    include_str!("query.sql"),
    vec![
        possible_values.into()
    ],
))

the trait bound `sea_orm::Value: From<Vec<u32>>` is not satisfied

Alternatively, if I try it as such:

let possible_values = vec![1,2,3];
let model = Model::find_by_statement(Statement::from_sql_and_values(
    DatabaseBackend::Postgres,
    include_str!("query.sql"),
    vec![
        Value::from(format!("({})", possible_values.iter().join(",")))
    ],
))

I get a query error as the possible values become single quoted and interpreted as a string.

[billy1624]

Hey @robot-head, you can use the is_in method. It's just a wrapper method of sea-query's method

• https://docs.rs/sea-orm/latest/sea_orm/entity/prelude/trait.ColumnTrait.html#method.is_in

[robot-head]

Right, but I want to do this in a custom query instead. I've simplified the query for the bug/example, but it's actually a quite complex query with subselects etc that I do not wish to express using the ORM.

[billy1624]

I think you need to write raw sql completely, i.e. write the values into sql directly instead of binding it

[robot-head]

I think you need to write raw sql completely, i.e. write the values into sql directly instead of binding it

That's what I was afraid of, and wanted to avoid doing so I was protected from sql injection etc.

It looks like sea-query supports this in the Value enum through a postgres-arrays feature flag.

Could we do similar for sea-orm?

https://github.com/SeaQL/sea-query/blob/master/src/value.rs#L76-L78

[billy1624]

As a temporary solution, you can do

SELECT column
FROM table
WHERE column IN ($1, $2, $3)

let possible_values = vec![1i32, 2i32, 3i32];
let model = Model::find_by_statement(Statement::from_sql_and_values(
    DatabaseBackend::Postgres,
    include_str!("query.sql"),
    possible_values.into_iter().map(Into::into),
))

[robot-head]

Well, unfortunately the size of the array is variable so that won't work.

[billy1624]

I wonder how complex is your query in query.sql? Making it difficult to build it with sea-query

[tyt2y3]

I think your best bet is to build the sub-query portion of the SQL using sea-query and inject that into your bigger SQL (using a $REPLACE_ME or anything like that).
I think sea-query has facilities to replace a bunch of ? into numbered $N.

[robot-head]

Will give that a try