Run ecto query and select multiple records with two different keys

Phoenix Forum Questions / Help
1

Hi there,

I recently migrated parts of my data to Elasticsearch to improve performance. Now, I need to select a list of entries from a table using Ecto. Each entry is uniquely identified by two attributes: user_id and auction_id.

In raw SQL, the query works perfectly:

It works perfectly fine in SQL:

SELECT * FROM table WHERE (table.user_id, table.auction_id) in ((1,2), (3,4)...)

However, I’m struggling to replicate this in Ecto. I’ve tried preparing the list as tuples:

list = [{1, 2}, {3, 4}]
from(t in Table, where: {t.user_id, t.auction_id} in ^list

I’ve also tried using a list of lists (each with two elements):

list = [[1, 2], [3, 4]]
from(t in Table, where: {t.user_id, t.auction_id} in ^list

Unfortunately, neither approach works.

Is there a way to achieve this in Ecto without resorting to raw SQL? Falling back to raw SQL feels like a less-than-ideal solution.

I’d appreciate any suggestions or insights!

2

anything preventing you from doing something like:

user_list = [1, 3]
auction_list = [2, 4]

from(t in Table, where: t.user_id in ^user_list, where: t.auction_id in ^auction_list)

edit: iirc, in from ecto dsl to sql statement(at least for postgres) is usually transformed in field = ANY(...) that might messup with the sql that you want.

2 Likes
3

The table that I am querying is more or less a cross-table between auction and customer, containing some more fields. So there are many entries per auction. That’s why I need to query by auction-id and user-id

4

one thing that’s worth trying then is

list = [[1, 2], [3, 4]]
from(t in Table, where: fragment("(?, ?) in ?", t.user_id, t.auction_id, ^list))

but i’m not 100% sure it will work. otherwise i’d try using cte

1 Like
5

See Ecto.Query.API — Ecto v3.12.5 if you want to replicate your sql.