Schema configuration

Minimal configuration

defmodule Pet do
  use Ecto.Schema

  @derive {
    Flop.Schema,
    filterable: [:name, :species],
    sortable: [:name, :age]
  }

  schema "pets" do
    field :name, :string
    field :age, :integer
    field :species, :string
  end
end

Options

Limit

@derive {
  Flop.Schema,
  filterable: [:name, :species],
  sortable: [:name, :age],
  max_limit: 100,
  default_limit: 50
}

Order

@derive {
  Flop.Schema,
  filterable: [:name, :species],
  sortable: [:name, :age],
  default_order: %{
    order_by: [:name, :age],
    order_directions: [:asc, :desc]
  }
}

Tiebreaker

Appended to every order Flop applies. Defaults to the primary key, ascending.

@derive {
  Flop.Schema,
  filterable: [:name, :species],
  sortable: [:name, :age],
  tiebreaker: {:primary_key, :desc}
}

Pagination types

@derive {
  Flop.Schema,
  filterable: [:name, :species],
  sortable: [:name, :age],
  pagination_types: [:first, :last],
  default_pagination_type: :first
}

Schema

defmodule Owner do
  use Ecto.Schema

  @derive {
    Flop.Schema,
    filterable: [:name],
    sortable: [:name, :pet_count],
    adapter_opts: [
      alias_fields: [:pet_count]
    ]
  }

  schema "owners" do
    field :name, :string
    has_many :pets, Pet
  end
end

Query

params = %{order_by: [:pet_count]}

Owner
|> join(:left, [o], p in assoc(o, :pets), as: :pets)
|> group_by([o], o.id)
|> select(
  [o, pets: p],
  {o.id, p.id |> count() |> selected_as(:pet_count)}
)
|> Flop.validate_and_run(params, for: Owner)

Schema

defmodule User do
  use Ecto.Schema

  @derive {
    Flop.Schema,
    filterable: [:full_name],
    sortable: [:full_name],
    adapter_opts: [
      compound_fields: [
        full_name: [:family_name, :given_name]
      ]
    ]
  }

  schema "users" do
    field :family_name, :string
    field :given_name, :string
  end
end

Query

params = %{
  filters: [
    %{field: :full_name, op: :ilike_and, value: "pea"}
  ]
}

Flop.validate_and_run(User, params, for: User)

Schema

Owner

defmodule Owner do
  use Ecto.Schema

  @derive {
    Flop.Schema,
    filterable: [:name, :pet_age],
    sortable: [:name],
    adapter_opts: [
      join_fields: [
        pet_age: [
          binding: :pets,
          field: :age,
          ecto_type: :integer
        ]
      ]
    ]
  }

  schema "owners" do
    field :name, :string
    has_many :pets, Pet
  end
end

Pet

defmodule Pet do
  use Ecto.Schema

  schema "pets" do
    field :age, :integer
    belongs_to :owner, Owner
  end
end

Query

Only filtering or sorting

params = %{
  filters: [
    %{field: :pet_age, op: :==, value: 8}
  ]
}

Owner
|> join(:left, [o], p in assoc(o, :pets), as: :pets)
|> Flop.validate_and_run(params, for: Owner)

With preload

Owner
|> join(:left, [o], p in assoc(o, :pets), as: :pets)
|> preload([pets: p], pets: p)
|> Flop.validate_and_run(params, for: Owner)

Schema

Pet

defmodule Pet do
  use Ecto.Schema

  @derive {
    Flop.Schema,
    filterable: [:name, :owner_city],
    sortable: [:name, :owner_city],
    adapter_opts: [
      join_fields: [
        owner_city: [
          binding: :address,
          field: :city,
          ecto_type: :string,
          # only needed with cursor pagination when sorting
          # by the join field, so that Flop can find the
          # cursor value in the returned struct;
          # defaults to [binding, field]
          path: [:owner, :address, :city]
        ]
      ]
    ]
  }

  schema "pets" do
    field :name, :string
    belongs_to :owner, Owner
  end
end

Owner

defmodule Owner do
  use Ecto.Schema

  schema "owners" do
    field :name, :string
    belongs_to :address, Address
  end
end

Address

defmodule Address do
  use Ecto.Schema

  schema "addresses" do
    field :city, :string
  end
end

Query with preload

params = %{order_by: [:owner_city]}

Pet
|> join(:left, [p], o in assoc(p, :owner), as: :owner)
|> join(:left, [owner: o], a in assoc(o, :address), as: :address)
|> preload([owner: o, address: a], owner: {o, address: a})
|> Flop.validate_and_run(params, for: Pet)

Schema

Owner

defmodule Owner do
  use Ecto.Schema

  @derive {
    Flop.Schema,
    filterable: [:name, :pet_count],
    sortable: [:name, :pet_count],
    adapter_opts: [
      join_fields: [
        pet_count: [
          binding: :pet_count,
          field: :count,
          ecto_type: :integer
        ]
      ]
    ]
  }

  schema "owners" do
    field :name, :string
    has_many :pets, Pet
  end
end

Pet

defmodule Pet do
  use Ecto.Schema

  schema "pets" do
    field :age, :integer
    belongs_to :owner, Owner
  end
end

Query

params = %{filters: [%{field: :pet_count, op: :>, value: 2}]}

pet_count_query =
  Pet
  |> where([p], parent_as(:owner).id == p.owner_id)
  |> select([p], %{count: count(p)})

q =
  Owner
  |> from(as: :owner)
  |> join(:inner_lateral, [o], p in subquery(pet_count_query),
    on: true,
    as: :pet_count
  )
  |> Flop.validate_and_run(params, for: Owner)

Schema

defmodule Pet do
  use Ecto.Schema

  @derive {
    Flop.Schema,
    filterable: [:name, :human_age],
    sortable: [:name, :human_age],
    adapter_opts: [
      custom_fields: [
        human_age: [
          filter: {CustomFields, :human_age_filter, []},
          field_dynamic: {CustomFields, :human_age, []},
          ecto_type: :integer
        ]
      ]
    ]
  }

  schema "pets" do
    field :name, :string
    field :age, :integer
  end
end

Custom field functions

defmodule CustomFields do
  import Ecto.Query

  # for ordering
  def human_age(_opts) do
    dynamic([p], fragment("? * 7", p.age))
  end

  # for filtering
  def human_age_filter(q, %Flop.Filter{value: value, op: op}, _) do
    case Ecto.Type.cast(:integer, value) do
      {:ok, human_years} ->
        value_in_dog_years = round(human_years / 7)

        case op do
          :== -> where(q, [p], p.age == ^value_in_dog_years)
          :!= -> where(q, [p], p.age != ^value_in_dog_years)
          :> -> where(q, [p], p.age > ^value_in_dog_years)
          :< -> where(q, [p], p.age < ^value_in_dog_years)
          :>= -> where(q, [p], p.age >= ^value_in_dog_years)
          :<= -> where(q, [p], p.age <= ^value_in_dog_years)
        end

      :error ->
        # cannot cast filter value, ignore
        q
    end
  end
end

Query

params = %{
  filters: [
    %{field: :human_age, op: :==, value: 30}
  ],
  order_by: [:human_age]
}

Flop.validate_and_run(Pet, params, for: Pet)

Operators

custom_fields: [
  human_age: [
    filter: {CustomFields, :human_age_filter, []},
    ecto_type: :integer,
    operators: [:==, :<=, :>=]
  ]
]

Takes precedence over the operators derived from ecto_type.

Bindings

custom_fields: [
  full_text: [
    filter: {CustomFilters, :full_text, []},
    ecto_type: :string,
    bindings: [:owner]
  ]
]

Flop.with_named_bindings/4 adds these bindings when the field is used.

Filter value casting

Join and custom fields need ecto_type. It determines the allowed operators and how filter values are cast.

# plain type
ecto_type: :string

# array
ecto_type: {:array, :string}

# custom Ecto type
ecto_type: MyApp.Mood

# parameterized type
ecto_type: Ecto.ParameterizedType.init(Ecto.Enum, values: [:one, :two])

# shorthand for the above
ecto_type: {:ecto_enum, [:one, :two]}

# type of a field of another schema
ecto_type: {:from_schema, MyApp.Pet, :mood}

A custom Ecto type used in a filter or a cursor has to return the database type name from type/0, since Flop casts those values with Ecto.Query.API.type/2.

Compile-time dependencies

Naming a module in @derive makes it a compile-time dependency. Build the module name without an alias to avoid that.

@pet Module.concat(["MyApp", "Pet"])

@derive {
  Flop.Schema,
  filterable: [:pet_mood],
  sortable: [],
  adapter_opts: [
    join_fields: [
      pet_mood: [
        binding: :pets,
        field: :mood,
        ecto_type: {:from_schema, @pet, :mood}
      ]
    ]
  ]
}

Cursor values

Cursor pagination needs an order clause. For a join field, Flop reads the value from the returned row by following path, which defaults to [binding, field].

join_fields: [
  owner_city: [
    binding: :address,
    field: :city,
    ecto_type: :string,
    path: [:owner, :address, :city]
  ]
]

For any other select shape, pass a cursor_value_func.

Flop.validate_and_run(query, params,
  for: Pet,
  cursor_value_func: fn %{pet: pet}, order_by ->
    Map.take(pet, order_by)
  end
)