Ecto IN clauses with tuples

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.

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

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.

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.

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. ^.^;

I haven’t marked this as the solution because it only works with literal tuples. So, if you build a list of tuples that is dynamic (with values not known in compile time) it will no work.

@josevalim wrote on the Ecto mailing list:

We already support {m.col1, m.col2} in [{1, 2}, …] but the biggest issue is that users want to encode the value on the right-side of IN dynamically, and that is a much harder problem to solve.

The error from Ecto when you try to compile a dynamic list is like so:

list_of_tuples = load_from_some_place()
from row in "my_table",
  where: {row.col1, row.col2, row.col3} in ^list_of_tuples,
  select: row.col1

** (Ecto.Query.CompileError) Tuples can only be used in comparisons with literal tuples of the same size
    expanding macro: Ecto.Query.where/3