I have a schema “category” that has multiple “sizes”.
I want to count as a virtual column in the category the sizes total.
As sql this would be the query:
select c.id, c.name, (select count(s.id) from ordering_sizes s where s.category_id = c.id) as sizes_count
from ordering_categories c
In Ecto I am trying to implement this query like this:
query = from c in OrderingCategory,
select: %{sizes_count: fragment(“select count(s.id) from ordering_sizes s where s.category_id = ?”, c.id), id: c.id, name: c.name},
where: c.categorizable_id == ^menu.id
This does not work. I receive this error:
(RuntimeError) the functions in Ecto.Query.API should not be invoked directly, they serve for documentation purposes only.
2 Likes
I tried another approach:
I am doing a left join and group by to count the sizes:
query = from c in OrderingCategory,
left_join: s in assoc(c, :sizes),
select: %{sizes_count: count(s.id), id: c.id, name: c.name, description: c.description},
where: c.categorizable_id == ^menu.id,
where: c.categorizable_type == “Ordering::Menu”,
where: c.active == true,
group_by: c.id
The only problem I have is that the select fields are dynamically and I do not know then at query time.
I want to send an array of fields and to concat to that the virtual field resolved as count(s.id)
requested_columns = […]
I tried do do it like this but it does not work:
query = from c in OrderingCategory,
left_join: s in assoc(c, :sizes),
select: [count(s.id) | ^requested_columns]
2 Likes
