Ecto IN clauses with tuples

Hello people!

I have a requirement to perform a select query with IN clause using tuples. I am using PostgreSQL. Example:

select * from my_table where (col1, col2, col3) in ((1, 2, 3), (4, 5, 6), (7, 8, 9));

This is valid SQL that we can’t, currently, express with Ecto. Tried with several interpolation techniques but was not successful. The list of values is dynamic in content and length (can’t use a fragment here).

We are falling back to a Repo.query! (making sure this is not a public query filled with user provided data) but would like to know if anyone can think of a way to keep it in Ecto.Query API (mostly for the added readability and security).

Thanks in advance!

1 Like

I suspect part of the challenge may be that the row type like (a, b, c) is an anonymous type in Postgres. And therefore interpolating may require typing on both the postgres and elixir sides (this is speculation on my part). I don’t have a repo handy right now to even see how Ecto handle select * from table where (a,b,c) in (1,2,3) type queries.

Another angle might be to try an = ANY type query which in most cases is semantically the same as an in query. But it would allow you to provide an elixir list as a parameter which may help make some progress.

I appreciate these are more questions, not answers.

1 Like

Is it possible to write the query differently? where col1 in (1,2,3) and col2 in (4,5,6) ?

I thought about that but I can’t convince myself it is the exact same result. Other than that, I want to use an index with col1, col2 and col3 and that would not use this index.

I thought that tuples (col1, col2, col3) is a standard SQL type though I may be mistaken. It is incredibly difficult to find a SQL specification.

I’ll look into any! Thanks a lot :slight_smile:

That isn’t logically equivalent. Suppose you have WHERE (col1, col2) IN ((1, 10), (2, 20)). If you do WHERE col1 in (1, 2) AND col2 IN (10, 20) that would allow a row that had col1 as 1 and col2 as 20, which would not have been allowed by the first query.

@victorolinasc I’ve had some success using unnest and JOIN for here’s an example:

    SELECT p.result_order as result_order, si.*
      FROM unnest($1::text[], $2::timestamp[], $3::timestamp[], $4::integer[])
      AS p(slug, starts_at, ends_at, result_order)
    LEFT JOIN sensors AS s ON s.slug = p.slug
    LEFT JOIN sensor_installations AS si
      ON s.id = si.sensor_id
      AND (si.activated_at, COALESCE(si.deactivated_at, p.ends_at + INTERVAL '5 minutes')) OVERLAPS
          (p.starts_at, p.ends_at)
    ORDER BY p.result_order ASC

The nice thing is that you get to at least use parameterized queries instead of interpolation, and if you join on multiple columns it should be perfectly able to use compound indices. The downside of course is that you’re still writing raw SQL. This is a pretty old query for us, you might be able to write the unnest as raw SQL but then use that as a subquery in a regular ecto query. Not sure.

1 Like

Wow! Didn’t know unnest… Will try it out too.

Thanks!

I am about to open an issue on Ecto because I think tuples are a standard type in SQL and, therefore, should be supported. I know this might be tricky and huge to implement and might be used only once in a lifetime but is really one of those things that any workaround seems quite troublesome.

I would love Ecto to support tuples as well, too many times have I needed workarounds for lacking them…

You can express this in Ecto easily:

from row in "my_table",
  where: {row.col1, row.col2, row.col3} in [{1, 2, 3}, {4, 5, 6}, {7, 8, 9}],
  select: row.col1

Will do exactly what you want.

1 Like

Hmm, it works now? It didn’t work when I first wrote this code (admittedly quite a while ago). I should update it, would make it faster than my workaround. ^.^;