Filtering and Sorting in nested embedded schemas

[Niedman]

I have an Ecto.Schema that contains a json field within(payment). That field is and embedded_schema that contains another another embedded_schema as they are not persisted in different tables

I was trying to use Flop for filtering withing nested schemas, but from what i understood we would need a query that joins and because they are only persisted in memory i don't think this will work.

So my question is: is there a way to filter using Flop inside json fields?

Follows an extract of my code for "visualization"

defmodule Charges do
  use Ecto.Schema
  import Ecto.Changeset

  @derive {
    Flop.Schema,
    filterable: [:amount, :created, :status, :card_present_brand, :card_not_present_brand],
    sortable: [:amount, :created, :status],
    adapter_opts: [
      join_fields: [
        card_present_brand: [
          binding: :payment,
          field: :brand,
          path: [
            :payment,
            :card_present
          ]
        ],
        card_not_present_brand: [
          binding: :payment,
          field: :brand,
          path: [
            :payment,
            :card_not_present
          ]
        ]
      ]
    ]
  }

  @primary_key false
  schema "charge" do
    field :id, :string, primary_key: true
    field :amount, :integer
    field :created, :naive_datetime
    field :status, :string

    embeds_one(:payment, Payment)
 
  end
end

defmodule Payment do
  use Ecto.Schema
  import Ecto.Changeset

  embedded_schema do
    field(:type, :string)

    embeds_one(:card_present, CardPresent)
  end

end

defmodule CardPresent do
  use Ecto.Schema
  import Ecto.Changeset

  embedded_schema do
    field(:brand, :string)
  end

end

[woylie]

You can configure a custom field and use a fragment with one of the Postgres JSON operators.

Alternatively, you can add a lateral join in which you select the value using the same fragment and operator as you would use in the custom field, give the binding an name, and configure a join field. The advantage of this option is that Flop can apply any operator here, while with the custom field, you have to implement each operator you want to support. The Postgres query optimizer should be able to optimize the lateral join away. Check the query plan to confirm.

[Niedman]

Hey @woylie,

Thanks for the help! I actually didn't understood or see tbh the concept of custom field. Once you mentioned it, i went through the documentation and worked like a breeze!

Thanksa lot!