# Get results where group contains entries for all values in a list

Hello friends,

I have a table like so:

id key section details
1 x 1 part 1 details
2 x 2 part 2 details
3 x 3 part 3 details
4 y 2 y’s part 2 details
5 y 1 y’s part 1 details
6 y 3 y’s part 3 details

which I’ve aquired through this query

``````SELECT ra.id, ra.key, ra.id, ra.details
from things as ra
inner join (
select distinct key from things limit 50
) as ra2
on ra.key = ra2.key
order by key
``````

aka

``````  def distinct_keys(query \\ base(), limit \\ 10) do
from r in query,
select: %{key: r.key},
distinct: true,
limit: ^limit
end

def some_things(query \\ base()) do
from r in query,
join: d in subquery(distinct_keys()),
on: d.key == r.key,
select: %{id: r.id, key: r.key, section: r.section, details: r.details}
end
``````

I’d like to end up with
` [%{key: x, 1: "part 1 details", 2: "part 2 details", 3: "part 3 details"}, ... ]`

Is there a way to do this in Ecto or should I just reduce over the results? I figure doing it in the db would be faster, as currently I get some results that only have two of the three sections there at that point in time.

Dear self,

You can get all the keys where the count of the section is however many you are expecting and then search the table based on those. It is not as accurate as you’d like but it is a start.

``````  def distinct_complete(query \\ base(), limit) do
from r in query,
select: r.key,
group_by: r.key,
having: fragment("count(distinct(section)) = 3"),
limit: ^limit
end

def key_in(query) do
from r in base(),
where: r.key in subquery(query)
end
``````
2 Likes

Is the intent to filter out `things` that don’t have all the sections?

1 Like

Yes, exactly.