Trying to do a hstore select / group by with ecto using fragments

Questions / Help
1

Hi all!

I’m sorry if this had been asked before but I couldn’t find anything that really helped me.

I’m trying to perform a Ecto query using a hstore field to do the grouping, Given the following Service schema:

     add :id, :uuid, primary_key: true
     add :data, :hstore

I’m trying to run a query which would group my entries by a specific key of data, in this case data->plan_id, and count how many are there for every type.
This isn’t a problem if I hard code the hstore’s key into the query like this:

Service |> select([s], {fragment("data->'plan_id'"), count(s.id)}) |> group_by(fragment("data->'plan_id'")) |> Repo.all
-> 
[
  {"6e5c70d8-15e6-464b-9286-690cdd8d823e", 28},
  {"da13d4ac-fcd4-4f5a-aac3-55ddfb9ebea6", 248},
  {"f589dc20-3eaf-4cd6-9c8f-fb28972565d6", 648},
  {"b570dec0-3c18-4307-93bf-4f9031bbf511", 14}
]

But I’m planning to do this more flexible allowing the user to select which key of data they want to use for doing the group_by…But I’m unable to make this work with fragments and variables:

data_key = "plan_id"
Service |> select([s], {fragment("data->'?'", ^data_key), count(s.id)}) |> group_by(fragment("data->'?'", ^data_key)) |> Repo.all
** (Postgrex.Error) ERROR 42803 (grouping_error) column "s0.data" must appear in the GROUP BY clause or be used in an aggregate function

    query: SELECT data->'$1', count(s0."id") FROM "services" AS s0 GROUP BY data->'$2'
    (ecto_sql) lib/ecto/adapters/sql.ex:624: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql) lib/ecto/adapters/sql.ex:557: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3

From my limited English I get the feeling that it is asking me to add the data column as a whole to the group_by, which seemed weird to me…

Thanks in advanced for any help!

2

Welcome to the community @dkenzox!

Trying to see the difference with the generated SQL might help. You can:

query =
  Service
  |> select([s], {fragment("data->'plan_id'"), count(s.id)})
  |> group_by(fragment("data->'plan_id'"))

Ecto.Adapters.SQL.to_sql(:all, Repo, query)
3

Thanks for your answer @aptinio
I tried that piece of code for both queries and I still don’t see anything wrong with the one with variables:

query = Service |> select([s], {fragment("data->'plan_id'"), count(s.id)}) |> group_by(fragment("data->'plan_id'"))
->  {"SELECT data->'plan_id', count(s0.\"id\") FROM \"services\" AS s0 GROUP BY data->'plan_id'",
 []}

And

query = Service |> select([s], {fragment("data->'?'", ^data_key), count(s.id)}) |> group_by(fragment("data->'?'", ^data_key))
-> {"SELECT data->'$1', count(s0.\"id\") FROM \"services\" AS s0 GROUP BY data->'$2'",
 ["plan_id", "plan_id"]}