I am trying to implement custom filters on a table that has a jsonb column metadata using Flop. as per @woylie suggestions trying to use custom fields
Following the docs for custom fields came up with below code.
when trying to lookup metadata_is_active getting error
Postgrex expected a binary, got true. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
<url>?filters[0][field]=name&filters[0][value]=Expert&filters[1][field]=whatsapp_no&filters[1][value]=88827271111&filters[2][field]=metadata_is_active&filters[2][value]=true
for metadata_owner it throws no error just returns empty result even on values that should match
<url>?filters[0][field]=name&filters[0][value]=Expert&filters[1][field]=whatsapp_no&filters[1][value]=88827271111&filters[2][field]=metadata_owner&filters[2][value]=abc
I feel my noobness with dynamic and fragmented queries is probably where I am wrong.
organization.ex
@derive {
Flop.Schema,
filterable: [:name, :whatsapp_no, :metadata_is_active, :metadata_owner],
sortable: [:name, :whatsapp_no, :inserted_at],
max_limit: 100,
default_limit: 50,
adapter_opts: [
custom_fields: [
metadata_is_active: [
filter: {OrganizationMetadataFilters, :metadata, []},
ecto_type: :boolean
],
metadata_owner: [
filter: {OrganizationMetadataFilters, :metadata, []},
ecto_type: :string
]
]
]
}
schema "organizations" do
field :name, :string
field :whatsapp_no, :string
embeds_one :metadata, OrganizationMetadata
organization_metadata_filters.ex
defmodule Maverick.Organizations.OrganizationMetadataFilters do
@moduledoc """
Maverick.Organizations.MetadataFilters
"""
import Ecto.Query
def metadata(query, %Flop.Filter{field: name, value: value, op: op} = flop_filter, _) do
metadata_field = field(name)
metadata_value = value(name, value)
expr =
dynamic(
[r],
fragment(
"metadata->>'?'",
field(r, ^metadata_field)
)
)
case metadata_value do
{:ok, query_value} ->
IO.inspect(flop_filter, label: "Flop filter")
IO.inspect(metadata_field, label: "Metadata field")
IO.inspect(query_value, label: "Metadata value")
conditions =
case op do
:== -> dynamic([r], ^expr == ^query_value)
:!= -> dynamic([r], ^expr != ^query_value)
:> -> dynamic([r], ^expr > ^query_value)
:< -> dynamic([r], ^expr < ^query_value)
:>= -> dynamic([r], ^expr >= ^query_value)
:<= -> dynamic([r], ^expr <= ^query_value)
end
IO.inspect(conditions, label: "conditions")
where(query, ^conditions)
:error ->
IO.inspect("Error casting value #{value} for #{name}")
query
end
end
def field(:metadata_is_active), do: :is_active
def field(:metadata_owner), do: :owner
def value(:metadata_is_active, value), do: Ecto.Type.cast(:boolean, value)
def value(:metadata_owner, value), do: Ecto.Type.cast(:string, value)
end
