AE
Ash Elixir•2y ago
Tore

Group by and count query

Hi, Just got to start to say thanks for creating this awesome library. It seems really cool so far 🚀 I was having a bit of trouble with what seems like a simple SQL query with group by. In Ecto I would write the query like this: 
from(user in User,
  select: {user.country_code, count(user.country_code)}
  group_by: user.country_code,
)
|> Repo.all()
|> Map.new()
from(user in User,
  select: {user.country_code, count(user.country_code)}
  group_by: user.country_code,
)
|> Repo.all()
|> Map.new()
After a bit of digging, the best way I found was creating a custom action and gabbing the data_layer_query and falling back to Ecto. Is this the best/only way to go, or is there a better alternative? 
action :country_stats do
  run fn input, context ->
    {:ok, query} = 
      User
      |> Ash.Query.to_query()
      |> Ash.Query.data_layer_query()

    result =
      from(user in query,
        select: {user.country_code, count(user.country_code)},
        group_by: user.country_code,
      )
      |> Repo.all()
      |> Map.new()

    {:ok, result}
  end
end
action :country_stats do
  run fn input, context ->
    {:ok, query} = 
      User
      |> Ash.Query.to_query()
      |> Ash.Query.data_layer_query()

    result =
      from(user in query,
        select: {user.country_code, count(user.country_code)},
        group_by: user.country_code,
      )
      |> Repo.all()
      |> Map.new()

    {:ok, result}
  end
end
3 Replies
ZachDaniel
ZachDaniel•2y ago
👋 one thing you'll want to keep in mind is that Ash doesn't try to achieve feature parity in terms of the kinds of sql queries you can make. The query tools we provide are "resource-centric", and grouping by a column breaks the concept of a resource. Typically what you would want to do is one of two things: 1. a generic action as you've got there. If you're using ash_graphql, we haven't hooked up generic actions to automatically get resolvers, but that is just elbow grease waiting to be applied. 2. Find a way to represent what you want as direct-to-postgres resources. For example, if country_codes were kept in their own table, and you related users to their country code, you could do this: 
defmodule CountryCode do
  use Ash.Resource, ....

  aggregates do
    count :count_of_users, [:users]
  end
  # or
  calculations do
    # this could make the same query you're making
    calculate :count_of_users, :integer, CustomCalculation
  end
end
defmodule CountryCode do
  use Ash.Resource, ....

  aggregates do
    count :count_of_users, [:users]
  end
  # or
  calculations do
    # this could make the same query you're making
    calculate :count_of_users, :integer, CustomCalculation
  end
end
3. Create resources with manual actions. This looks very similarly to what you have already, but shapes it as its own resource. While the second options comes with many benefits, it is by no means necessary. Just a matter of trade-offs, and I personally have used all three strategies to solve this kind of problem (i.e I want some kind of metric/non-resource-centric data to expose).
Tore
ToreOP•2y ago
Thanks for explaining, that makes a lot of sense 😊 Yes, I am trying to make some different metrics, so I also found it a bit strange to have to attach it to the resource. So the third option of creating a separate Stats resource would make prefect sense. For now, I am just using Phoenix LiveView, so seems like I could even do with just a normal module. Also, I just figured I could use the resource as a schema and use it directly in an Ecto query. Just like my initial example with Ecto.
ZachDaniel
ZachDaniel•2y ago
Yep! Those are all options Each resource is also an Ecto.Schema, exactly for things like this 👍

Did you find this page helpful?