FLOP - Filter across table does not work as expected

Questions / Help
, ,
1

Using the example from the lib itself, let’s suppose I have 10 owners and each owner has 10 pets.

@derive {
Flop.Schema,
filterable: [:pet_species],
sortable: [:pet_species],
adapter_opts: [
join_fields: [
pet_species: [
binding: :pets,
field: :species,
ecto_type: :string
]
]
]
}
schema “owners” do
field :name, :string
field :email, :string

has_many :pets, Pet
end

schema “pets” do
field :name, :string
field :species, :string

belongs_to :owner, Owner
end

After setting up the join fields, I can write a query like this:

params = %{
page_size: 10,
page: 1,
filters: [%{field: :pet_species, op: :==, value: “E. africanus”}]
}

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

The expected result is that it would return 10 owners and all their associations, in this case, for each owner it would return 10 pets. But this query returns 1 owner and their 10 pets. If I sent apage_size: 5, it would return 1 owner and 5 pets.

This other query works the way I expect, returning 10 users and all their pets:

params = %{page_size: 10, page: 1, filters: [%{field: :pet_species, op: :==, value: “E. africanus”}]}

Owner
|> preload([o], pets: ^from(p in Pet))
|> limit(^page_size)
|> Flop.validate_and_run(params, for: Owner)

But this way, the filters are not applied.

How could I fix it?

2

Try to add distinct: o.id.
However, I wouldn’t use Flop for filtering in this case. I prefer writing queries myself and in this case it would use where + exists expression because usually it will be more performant and more convenient (no dances with ordering, for example) than join + distinct. Actually, Flop can use this approach under the hood, but I doubt.

1 Like