Ecto query on column values

Questions / Help
,
1

Hi all,

Trying to figure this one out. I have an example table (Postgres) :

user_id: "bob", address: "xxxxx", type: %TypeData{value: :residential}
user_id: "sue", address: "yyyyy", type: %TypeData{value: :business}
user_id: "tim", address: "zzzzz", type: %TypeData{value: :residential}
user_id: "lisa", address: "xyzxyz", type: %TypeData{value: :educational}

I want a single query that will return a count on individual types. So in this case residential: 2, business: 1, educational: 1

Currently I do something like:

Repo.all(from x in MyTable, where: not is_nil(x.user_id), select: x.type)
|> Enum.frequencies()

Is there a way to use select: count() so I can avoid the Enum.frequencies/1?

Thanks

2

You likely want count/1 along with group_by:

from(x in MyTable, 
where: not is_nil(x.user_id),
select: {x.type, count(x.type)},
group_by: x.type
) 
|> Repo.all()
|> Map.new()
2 Likes
3

Great, thanks…that worked