Struggling to use a Flop join_field for sorting

Questions / Help
,
1

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.

Assets 
# 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
Issues 
# 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:

image

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

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!

2

figured it out - i had one obvious bug, and one smaller mistake.

the smaller mistake was with how i had constructed the ongoing_issues_count_query. I ended up converting it to:

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

with the outer query being:

query =
      Asset
      |> from(as: :asset)
      |> join(
        :left_lateral,
        [asset],
        ongoing_issues_count in subquery(ongoing_issues_count_query),
        as: :ongoing_issues_count,
        on: ongoing_issues_count.asset_id == asset.id
      )
      |> select([asset, ongoing_issues_count], %{
        asset: asset,
        ongoing_issues_count: coalesce(ongoing_issues_count.count, 0)
      })

the coalesce is there to ensure that null values are converted to 0.

the bigger bug was that my Flop table had

  <:col :let={%{asset: asset, ongoing_issues_count: ongoing_issues_count}} label="Ongoing issues">

instead of

  <:col :let={%{asset: asset, ongoing_issues_count: ongoing_issues_count}} label="Ongoing issues" field={:ongoing_issues_count}>
3 Likes