Show children count (Total) in table view

[io2]

Discovered backpex on ElixirForum a few weeks ago, took it for a spin and very quickly became a convert. I appreciate it is still early days but my my have we needed something like this in the phoenix/elixir world for a while. 💯

Thanks for contributing this much needed resource 🥇

Right, I find myself now trying to use backpex to add an admin dashboard into a more complex application and find i need to display a column showing counts of children has_many, embeds_many, many_to_many e.t.c objects.

Can't for the life of me figure out how to do so and haven't been to locate documentation or sample use case.

Please help?

[io2]

additional context ...

def fields do
      ....
      school_students: %{
        module: Backpex.Fields.HasMany,
        label: "Students",
        select: dynamic([school_students: s], fragment("count(?)", s.id))
      },
     ....
end

returns

UndefinedFunctionError at GET /_admin/schools
function nil.__struct__/0 is undefined

[Flo0807]

Hey, one way to achieve your requirement is to use the item_query function to join the associations and assign the count to a virtual field.

Suppose you have a school schema like this with a virtual field named students_count

schema "school" do
  field :name, :string
  has_many :students, Student

  # add the following virtual field
  field :students_count, :integer, virtual: true
end

you can use the item_query function in your LiveResource to modify the query executed by Backpex (see corresponding docs: https://hexdocs.pm/backpex/item-query.html) and assign the students count to the virtual field

@impl Backpex.LiveResource
def item_query(query, _live_action, _assigns) do
  query
  |> join(:left, [school], student in Student, on: student.school_id == school.id)
  |> group_by([school, _student], school.id)
  |> select_merge([_school, student], %{students_count: count(student.id)})
end

You are now able to display the students count with a number field like this:

def fields do
  [
    students_count: %{
      module: Backpex.Fields.Number,
      label: "Students Count",
      readonly: true,
      orderable: false
    }
  ]
end

Note that you should set orderable to false. Otherwise, an error is thrown because the ordering happens at database level and students_count does not exist there.

If you have the requirement to make the count orderable, please let me know! We might be able to find a way to do that, too.

I hope this helps @io2 🙏

[io2]

Wow, thanks for the quick response @Flo0807 mighty appreciated.

The above works, however I am curious what if i have multiple counts to display? Sticking with the example above

School	Students	Tutors	Lesson
St Paul Elementary	251	29	4099

Seems we'd very quickly end up with n+1 joins and a very complicated lookingitem_query function.

What other ways to achieve this are there?

Thanks in advance

[Flo0807]

Another way would be using a HasMany field and overwriting the render function with the count:

def fields do
  [
    students_count: %{
      module: Backpex.Fields.HasMany,
      label: "Students Count",
      orderable: false,
      readonly: true,
      render: fn assigns ->
        assigns = assign(assigns, :count, Enum.count(assigns.value))

        ~H"""
        <span>
          <%= @count %>
        </span>
        """
      end
    },
  ]
end

You might want to extract the markup into a dedicated component.

[io2]

You sir, are a Legend! Works a treat. Thank you 💯