This might be somewhat related to Sorting by a join field in a query with Flop not working, but I wasn’t able to follow the author’s solution

Context

I have two schemas: one for assets and another for issues.

# asset.ex
@derive {
  Flop.Schema,
  filterable: [:name],
  sortable: [:updated_at, :ongoing_issues_count],
  adapter_opts: [
    join_fields: [
      ongoing_issues_count: [
        binding: :ongoing_issues_count,
        field: :count,
        ecto_type: :integer
      ]
    ]
  ]
}

schema "assets" do
  field :name, :string
  
  has_many :issues, Issue
end

# issue.ex
schema "issues" do
  field :title, :string
  field :description, :string
  field :status, Ecto.Enum, values: @supported_statuses, default: :ongoing
end

Intentions

I’m trying to do two things within the Flop table that lists all my assets:

1. show how many issues with status==:ongoing are associated to each asset, and
2. allow users to sort the table in increasing/decreasing order based on how many issues with status==:ongoing are associated to each asset

I was able to accomplish 1. by defining the @derive Flop.Schema in the “assets.ex” file:

I’m struggling to understand why I can’t sort by the ongoing_issues_count join field though

Here’s the list_assets function I’m currently working with:

def list_assets(params) do
    ongoing_issues_count_query =
      Issue
      |> where([issue], parent_as(:asset).id == issue.asset_id)
      |> where([issue], issue.status == :ongoing)
      |> select([issue], %{count: count(issue)})

    query =
      Asset
      |> from(as: :asset)
      |> join(
        :left_lateral,
        [asset],
        ongoing_issues_count in subquery(ongoing_issues_count_query),
        as: :ongoing_issues_count
      )
      |> select([asset, ongoing_issues_count], %{
        asset: asset,
        ongoing_issues_count: ongoing_issues_count
      })

    Flop.validate_and_run(query, params, for: Asset)
  end

Thanks in advance to anyone who reads this!