Tore Posts - Answer Overflow

Tore

•Created by Tore on 8/25/2023 in #support

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

5 replies

Gaming

Programming