Apply Numeric Filter to Parent and Children Relation Items

[ahmed-bhs]

Question:

I'm trying to retrieve products that are associated with at least one non-archived inventory using API Platform Boolean/Numeric Filter. Currently, my query looks like this: /api/products?inventoryProducts.isArchived=1. However, I would like the filter to return the products and also affect the product.inventoryProducts relationship. As it stands, the filter returns all associated inventories. Except that I would like the relationship to be filtered.

Here's my Product class:

class Product
{
    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Inventory\InventoryProduct", mappedBy="product", cascade={"remove", "persist"})
     */
    private $inventoryProducts;

}

And this is my InventoryProduct class:

class InventoryProduct 
{
    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Catalog\Product", inversedBy="inventoryProducts")
     */
    private $product;

    /**
     * @ORM\Column(type="boolean")
     */
    private $isArchived = false;
}

Any help or suggestions would be greatly appreciated. Cheers!"

[monitaurus]

Have you try to implement a custom filter? (see documentation)

For a Doctrine filter, by implementing the method filterProperty, you can access the QueryBuilder, and then building the filtering as you wish. Here, for example, making a join and adding a where condition on isArchived.

Resulting in something like this (untested pseudo code):

protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, Operation $operation = null, array $context = []): void
{
    // ...
    $alias = $queryBuilder->getRootAliases()[0];
    $inventoryAlias = $queryNameGenerator->generateJoinAlias('inventoryProduct');

    $queryBuilder->leftJoin("$alias.inventoryProducts", $inventoryAlias);
    $queryBuilder->andWhere("$inventoryAlias.isArchived = :isArchived");
    $queryBuilder->setParameter('isArchived', false);
}

[ahmed-bhs]

I retrieved my products associated with non-archived inventoryProducts. However, when I try to access any product using product->getInventoryProduct(), it returns both archived and non-archived inventories. I should only get non archive inventories.

[monitaurus]

With the custom filter, you may not need to access $product->getInventoryProduct(), as the filter's goal is only to add stuff to the query that API Platform will run into the database. Unless you're working with a custom StateProvider (or StateProcessor).

Where did you try to call the getInventoryProduct method?
If you've got some code snippets to share, it'll help getting the gist of this.