Сontrolling Lazy loading and filtering SoftDeletable entities in Doctrine and API Platform

API Platform version(s) affected: 3.2.25 Symfony version(s) affected: 6.4 Doctrine ORM version(s) affected: 2.x

Description: Hello!

We are working on a project using Symfony (6.4) with Doctrine ORM and API Platform (3.2.25). We need to implement SoftDeletable functionality, considering specific requirements. One of the key tasks is filtering related entities by the deletedAt field so that deleted entities are either excluded or included based on the context.

Current Implementation and Issues: Using EagerLoadingExtension:

We partially solved the problem using a custom EagerLoadingExtension by adding specific conditions for ManyToOne relations. This allowed us to include deleted entities in the service field, which is eagerly loaded. However, when Doctrine switches to Lazy loading (e.g., for Tracks->Places), this solution does not apply, and we lose control over filtering deleted entities. Issues with Lazy Loading:

In API Platform, when the nesting level exceeds three, Doctrine automatically switches to Lazy loading. In this scenario, our EagerLoadingExtension does not work, and the deletedAt filter is not correctly applied.

Attempt to Use SQLFilter:

We tried using SQLFilter to filter entities by deletedAt. However, SQLFilter only provides two parameters — $targetEntity and $targetAlias, which is insufficient for understanding the context of the query. We cannot determine how and for what purpose the query was triggered, making it challenging to control Lazy loading through this mechanism. Extensions do not work on Lazy relations:

We found that Extensions in API Platform are not applied to Lazy relations, which creates a problem when we cannot control the loading of such relations. Attempt to Force fetch=EAGER:

We attempted to force fetch=EAGER on all relevant relations. However, this did not result in the expected merging of queries into a single large query, leaving the problem with loading and filtering Lazy relations unresolved. JSON Example:

Here is the JSON structure we want to achieve:

Service (ManyToOne): Deleted entities should be displayed in the service field (controlled via EagerLoadingExtension). Tracks->Places (ToMany): Only non-deleted entities should be displayed in the places field (filtered by deletedAt IS NULL).

Example JSON:

{
  "cargo": {
    "id": 1,
    "name": "Electronics Shipment",
    "description": "A shipment of various electronic devices",
    "service": {
      "id": 5,
      "name": "Express Delivery",
      "provider": "FastShip Inc.",
      "deletedAt": "2024-08-10T10:00:00Z"
    },
    "tracks": [
      {
        "id": 10,
        "status": "In Transit",
        "estimatedArrival": "2024-08-25T15:30:00Z",
        "places": [
          {
            "id": 100,
            "name": "Warehouse A",
            "location": "New York, NY",
            "arrivalTime": "2024-08-18T08:00:00Z",
            "deletedAt": null
          },
          {
            "id": 101,
            "name": "Distribution Center B",
            "location": "Chicago, IL",
            "arrivalTime": "2024-08-20T12:00:00Z",
            "deletedAt": null
          }
        ]
      }
    ]
  }
}

Example Entities:

#[ORM\Entity]
class Cargo
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string')]
    private string $name;

    #[ORM\Column(type: 'text', nullable: true)]
    private ?string $description;

    #[ORM\ManyToOne(targetEntity: Service::class)]
    #[ORM\JoinColumn(nullable: false)]
    private ?Service $service = null;

    #[ORM\OneToMany(targetEntity: Track::class, mappedBy: 'cargo', cascade: ['persist', 'remove'], fetch: 'LAZY')]
    private Collection $tracks;

    public function __construct()
    {
        $this->tracks = new ArrayCollection();
    }

    // Getters and setters
}

#[ORM\Entity]
class Track
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string')]
    private string $status;

    #[ORM\Column(type: 'datetime', nullable: true)]
    private ?\DateTimeInterface $estimatedArrival;

    #[ORM\ManyToOne(targetEntity: Cargo::class, inversedBy: 'tracks')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Cargo $cargo = null;

    #[ORM\OneToMany(targetEntity: Place::class, mappedBy: 'track', cascade: ['persist', 'remove'], fetch: 'LAZY')]
    private Collection $places;

    public function __construct()
    {
        $this->places = new ArrayCollection();
    }

    // Getters and setters
}

#[ORM\Entity]
class Place
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string')]
    private string $name;

    #[ORM\Column(type: 'string')]
    private string $location;

    #[ORM\Column(type: 'datetime', nullable: true)]
    private ?\DateTimeInterface $arrivalTime;

    #[ORM\Column(type: 'datetime', nullable: true)]
    private ?\DateTimeInterface $deletedAt = null;

    #[ORM\ManyToOne(targetEntity: Track::class, inversedBy: 'places')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Track $track = null;

    // Getters and setters
}

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class Service
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string')]
    private string $name;

    #[ORM\Column(type: 'string')]
    private string $provider;

    #[ORM\Column(type: 'datetime', nullable: true)]
    private ?\DateTimeInterface $deletedAt = null;

    // Getters and setters
}

api-platform / core

Сontrolling Lazy loading and filtering SoftDeletable entities in Doctrine and API Platform #6522