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

Questions / Help
1

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.

2

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
3

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

1 Like
4

Yes, exactly.