Aggregate query counts with multiple where values

Questions / Help
,
1

So I have this query that is fairly complex as it involves several joins along a materialized view (which is as fast to read as a table isn’t it?):

I have sub-filters over the query and need to display the count for each sub-query:

image
image1185×86 8.05 KB

To achieve that, I wrote the following function:

  def count_with_level(query) do
    Enum.reduce(3..8, %{}, fn level, result ->
      count =
        from(c in query, [where: c.level == ^level])
        |> Repo.aggregate(:count, :id)

      Map.put(result, level, count)
    end)
  end

to get (in the screenshot):

%{
  3: 2,
  4: 2,
  5: 1,
  6: 0,
  7: 0,
  8: 0
}

But when I look at logs, 7 almost identical queries are executed, which is desperately greedy for my search result page :worried:

I’m sure Postgres is doing some optimization behind, but still, it opens 8 pools to my DB, which triggers connection not available and request was dropped from queue after 105ms errors.

Is there a way to write this at Ecto/SQL level elegantly to make sure only one or two queries are executed?

I’m still quite bad with Ecto, especially when it goes with aggregates. :frowning_face:

Thanks for the help!

2 
from c in query,
  where: c.level in 3..8,
  select: %{
    level: c.level
    count: count()
  },
  group_by: c.level
3

Thanks for your help @hauleth !

I get syntax error before: count: what’s supposed to be in count()?

Cheers

4

Ahh, I forgot comma after c.level.

from c in query,
  where: c.level in 3..8,
  select: %{
    level: c.level, # <-- here
    count: count()
  },
  group_by: c.level

That is what happens when I write without testing.

1 Like
5

Indeed that was it! Thanks so much!!