Hello,
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
SELECT
staff_id,
COUNT (payment_id)
FROM
payment
GROUP BY
staff_id;
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 a.id = 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(p.id),
}
Repo.all(query)
end
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(c.id),
category_title: c.title
}
Repo.all(query)
end
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(c.id),
# 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(c.id),
category_name: p.title
# category_title: c.title
}
Repo.all(query)
end
Something along these lines should be possible:
SELECT
d.title,
e.record_count
FROM cms_changelog_category AS d
INNER JOIN (
SELECT
c.cms_changelog_category_id AS category_id,
COUNT(c.id) AS record_count
FROM cms_changelog_record AS c
WHERE
blah_blah
GROUP BY c.cms_changelog_category_id;
) AS e ON (d.id = e.category_id)
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…
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
end
create table(:cms_changelog_records) do
add :name, :string
add :category_id, :id
end
iex(1)> import Ecto.Query
Ecto.Query
iex(2)> alias Squery.{Repo}
[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(c.id)})
#Ecto.Query<from c in "cms_changelog_records", group_by: [c.category_id],
select: %{category_id: c.category_id, record_count: count(c.id)}>
iex(6)> query = from(d in "cms_changelog_categories",
...(6)> join: e in subquery(sub), on: d.id == 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(c.id)}),
on: c0.id == 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"}
]
iex(8)>
© Copyright Elixir Forum | Terms | Privacy & Cookies
