Syntax for composing Ecto queries with virtual/aggregate columns?

Phoenix Forum Questions / Help
,
1

Happy Friday, would love to query the elixir forum hive mind to get some help on an ecto problem that’s been bothering me today…

I have the following base query for my Fic model…

   def summary_query(query) do
    from f in query,
         left_join: r in assoc(f, :reviews),
         group_by: f.id,
         preload: [
           :submitter,
           :genres,
           reviews: :submitter,
         ],
         select: %{
           f |
           review_count: fragment("count(?) as review_count", r.id),
           review_avg: fragment("coalesce(?::float, 0) as review_avg", avg(r.rating))
         }
  end

I’d like to compose my summary query with some sorting and filtering queries. These sorting and filtering queries need to use the computed review_count and review_avg columns…

Example: Fic |> summary_query |> at_least_ten_reviews_query

Unfortunately, when I try to do either this…
from f in query, where: f.review_avg > 10

Or…
from f in query, where: fragment("review_avg") > 10

I get errors, either saying that “field review_avg in where is a virtual field in schema Ficdb.Fanfics.Fanfic in query:” or “column “review_avg” does not exist”

What’s the correct syntax for working with these virtual/aggregate columns? Thx!

2

It’s more a SQL thing than Ecto, but to filter by aggregate fields you need to use “having”, not “where”

https://hexdocs.pm/ecto/Ecto.Query.html#having/3

3 Likes
3

Thx! Good catch

4

I had a similar problem, and the cause was that I needed to ensure the alias was being added in a subquery, not a composed query:

q = from s in Schema,
  select: %{s | computed_field: "whatever"}

from s in subquery(q),
  where: s.computed_field == "whatever