Anyway to count empty associations for calculated values when filtering?

[ah0y]

So following https://hexdocs.pm/flop/Flop.Schema.html#module-filtering-by-calculated-values-with-subqueries I was almost able to get filtering working with calculated values.

The problem I'm running into now is that since I'm using a left lateral join (as opposed to an inner lateral join), I can't count nil as a 0, thus not allowing me to search for counts of "0".

    join(
      query,
      :left_lateral,
      [user],
      comment_count in subquery(
        from(comment in Comment,
          # where: parent_as(:user).id == comment.user_id,
          group_by: comment.id,
          select: %{user_id: comment.user_id, count: count(comment.id)},
          # having: count(comment.id) == 1
        )
      ),
      on: user.id == comment_count.matchmaker_id,
      as: :comment_count
    )

[ah0y]

Figured this out using custom filters and having:

@derive {
  Flop.Schema,
  filterable: [
    :pet_count_eq
  ],
  sortable: [...],
  adapter_opts: [
    custom_fields: [
      pet_count_eq: [
        filter: {CustomFilters, :count_filter, [source: :pets]},
        ecto_type: :integer,
        operators: [:==]
      ]
    ]
  ]
}

schema "owners" do

  field :pet_count, :integer, virtual: true

  timestamps()
end

query =
  from(owner in Owner,
    left_join: pets in assoc(owner, :pets),
    as: :pets,
    group_by: [pets.id, owner.id],
    select_merge: %{
      pet_count: count(pets.id)
    }
  )

Flop.validate_and_run(query, params, for: Owner)

defmodule CustomFilters do
  import Ecto.Query

  def count_filter(query, %Flop.Filter{value: count, op: op}, opts) do
    source = Keyword.fetch!(opts, :source)

    position = named_binding_position(query, source)

    case op do
      :== -> having(query, [{t, position}], count(field(t, :id)) == ^count)
      :<= -> having(query, [{t, position}], count(field(t, :id)) <= ^count)
      :<= -> having(query, [{t, position}], count(field(t, :id)) >= ^count)
      _ -> query
    end
  end

  defp named_binding_position(query, binding) do
    query.aliases
    |> Map.get(binding)
  end
end