"where in" filter on multiple column values

[Simon Jesenko]

I am trying to obtain all rows from a table that match any of the given n-column values, e.g. in raw sql,

select * from tbl

where (tbl.col_a, tbl.col_b) in (('val1', 1), ('val2', 2), ('val2', 1))

Does Ecto/postgrex currently support queries like this? I was trying to accomplish this in Ecto 2.0RC5 by using fragment and any postgresql function:

from tbl in MyProj.Tbl,

where:

fragment("(?, ?) = any(?)", tbl.col_a, tbl.col_b, ^[{"val1, 1"}, {"val2", 2}, {"val2", 1}])

However it fails with 

     ** (FunctionClauseError) no function clause matching in :lists.zip/2

       (postgrex) lib/postgrex/extensions/record.ex:21: Postgrex.Extensions.Record.encode_record/3

       (postgrex) lib/postgrex/extensions/array.ex:54: anonymous fn/3 in Postgrex.Extensions.Array.encode_array/4

       (elixir) lib/enum.ex:1151: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3

       (postgrex) lib/postgrex/extensions/array.ex:50: Postgrex.Extensions.Array.encode_array/4

       (postgrex) lib/postgrex/extensions/array.ex:22: Postgrex.Extensions.Array.encode_array/3

Is there an alternative / suggested approach how to handle scenarios like this? I could in principle add multiple "where" statements for each {col_a, col_b}, however in this case new query would be prepared for each different combination of parameters...

[José Valim]

Currently not supported, please open up an issue. For now, your best option is to build the query by hand and use Ecto.Adapters.SQL.query.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/ca3f6037-f701-4972-bb44-21a5f90dea35%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Simon Jesenko]

José, thank you for quick answer! I opened an issue on github: https://github.com/elixir-lang/ecto/issues/1457.

You received this message because you are subscribed to a topic in the Google Groups "elixir-ecto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/elixir-ecto/FJBAq9raGAc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4LUchpJE9C-y9BVt9kmTtrcrxM2JLTUuE6L0kAdbBx29Q%40mail.gmail.com.