How can I read and count items of category well?

Please see the blow image:


For example, I have 4 categories and I need to load 4 their title and count each Items of category, at last , I want to create like the picture concerned.

what is way best for me which has performance and speed?

black text = category title
red circle = number of posts


 COUNT (payment_id)


1 Like

Here is how I query users with their comments count in a sub query

=> select id, name, count as number_of_comments from users as a join (select count(id) as count, user_id from comments group by user_id) as b on = b.user_id;
 id |  name   | number_of_comments 
  1 | user_1  |                 21
  2 | user_2  |                 21
  3 | user_3  |                 13
  4 | user_4  |                 17
  5 | user_5  |                 18
  6 | user_6  |                 20
  7 | user_7  |                 27
  8 | user_8  |                 24
  9 | user_9  |                 17
 10 | user_10 |                 22
(10 rows)

You could change users to your categories, and comments to your items.

	def load_all_record_by_category() do
		query = from p in ChangelogRecord,
				group_by: p.version,
				select: %{
				 	category_title: p.title,
	 				record_count: count(,

but I have an error :

** (Postgrex.Error) ERROR 42803 (grouping_error): column "c0.title" must appear in the GROUP BY clause or be used in an aggregate function
    (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

I need after counting and it shows me , now print all item in the category.

	def load_all_record_by_category(category_alias_link) do
		query = from p in ChangelogCategory,
				join: c in assoc(p, :cms_changelog_record),
				group_by: c.version,
				where: p.seo_alias_link == ^category_alias_link,
				select: %{
				 	category_version: c.version,
	 				record_count: count(,

					category_title: c.title

but , didn’t work for me , I think to create 2 query to show things which I mean, don’t I?

by the way , I used category id relation in record instead of label name not its name !!!

like this :

query = from p in ChangelogCategory,
				join: c in assoc(p, :cms_changelog_record),
				group_by: c.cms_changelog_category_id,
				where: p.seo_alias_link == ^category_alias_link,
				select: %{
				 	category_version: c.cms_changelog_category_id,
	 				record_count: count(,

					# category_title: c.title

my outputs :

[%{category_version: "52b7a8d7-57b4-4deb-9636-6f6e0dca6dab", record_count: 15}]

how do I print category name instead of its ID?

I think my second problem fixer is this :

	def load_all_record_by_category(category_alias_link) do
		query = from p in ChangelogCategory,
				join: c in assoc(p, :cms_changelog_record),
				group_by: [c.cms_changelog_category_id, p.title],
				where: p.seo_alias_link == ^category_alias_link,
				select: %{
				 	category_version: c.cms_changelog_category_id,
	 				record_count: count(,
					category_name: p.title

					# category_title: c.title

Something along these lines should be possible:

FROM cms_changelog_category AS d
    c.cms_changelog_category_id AS category_id,
    COUNT( AS record_count
  FROM cms_changelog_record AS c
  GROUP BY c.cms_changelog_category_id;
) AS e ON ( = e.category_id)
1 Like

unfortunately, I don’t understand raw sql code, may you write the code like Ecto?

After years of SQL, Ecto looks absolutely dyslexic to me and I know SQL way better than Ecto.
I’ll always see the solution in SQL first - translating to Ecto is an extra layer of indirection.

And at the psql prompt I find it much easier to play with SQL. Ecto requires more infrastructure.

Edit: Ecto.Query.subquery/2 may be the direction …


Heh, that’s what I always do. I write the SQL first then ‘translate’ it to Ecto so it gets Typed properly… ^.^;

/me wonders if anyone has thought about a SQL->Ecto transformer…

1 Like

Now, you suggest me that I leave the code and learn Postgresql like sql command , don’t I ?

Ecto is actually a pretty thin layer on top of SQL. It is always good to write the query out in SQL first (I like to test in PGAdmin3 personally) then convert it, that way you know it works.

    create table(:cms_changelog_categories) do
      add :title, :string
    create table(:cms_changelog_records) do
      add :name, :string
      add :category_id, :id
iex(1)> import Ecto.Query
iex(2)> alias Squery.{Repo}
iex(3)>   Repo.insert_all("cms_changelog_categories",[
...(3)>     [title: "First"],
...(3)>     [title: "Second"],
...(3)>     [title: "Third"]
...(3)>   ])

12:15:59.835 [debug] QUERY OK db=7.7ms
INSERT INTO "cms_changelog_categories" ("title") VALUES ($1),($2),($3) ["First", "Second", "Third"]
{3, nil}
iex(4)>   Repo.insert_all("cms_changelog_records",[
...(4)>     [name: "One", category_id: 1],
...(4)>     [name: "Two", category_id: 2],
...(4)>     [name: "Two", category_id: 2],
...(4)>     [name: "Three", category_id: 3],
...(4)>     [name: "Three", category_id: 3],
...(4)>     [name: "Three", category_id: 3]
...(4)>   ])

12:16:15.459 [debug] QUERY OK db=8.4ms
INSERT INTO "cms_changelog_records" ("category_id","name") VALUES ($1,$2),($3,$4),($5,$6),($7,$8),($9,$10),($11,$12) [1, "One", 2, "Two", 2, "Two", 3, "Three", 3, "Three", 3, "Three"]
{6, nil}
iex(5)> sub = from(c in "cms_changelog_records",
...(5)>   group_by: c.category_id,
...(5)>   select: %{category_id: c.category_id, record_count: count(})
#Ecto.Query<from c in "cms_changelog_records", group_by: [c.category_id],
 select: %{category_id: c.category_id, record_count: count(}>
iex(6)> query = from(d in "cms_changelog_categories",
...(6)>   join: e in subquery(sub), on: == e.category_id,
...(6)>   select: %{title: d.title, record_count: e.record_count})
#Ecto.Query<from c0 in "cms_changelog_categories",
 join: c1 in subquery(from c in "cms_changelog_records",
  group_by: [c.category_id],
  select: %{category_id: c.category_id, record_count: count(}),
 on: == c1.category_id,
 select: %{title: c0.title, record_count: c1.record_count}>
iex(7)> Repo.all(query)

12:16:44.436 [debug] QUERY OK source="cms_changelog_categories" db=1.7ms
SELECT c0."title", s1."record_count" FROM "cms_changelog_categories" AS c0 INNER JOIN (SELECT c0."category_id" AS "category_id", count(c0."id") AS "record_count" FROM "cms_changelog_records" AS c0 GROUP BY c0."category_id") AS s1 ON c0."id" = s1."category_id" []
  %{record_count: 1, title: "First"},
  %{record_count: 2, title: "Second"},
  %{record_count: 3, title: "Third"}