How to use Doctrine filters based on join field?

Question

I'm using ApiPlatform in one of my apps. I've just followed this https://api-platform.com/docs/core/filters/#using-doctrine-orm-filters to add an extra filter based on the logged User.

The aim is that a user can see only its own resources, and it works well because I have the id of the current logged user using TokenStorageInterface.

But how to do this when you want to filter on something else than a data stored in User entity ? I don't want to do extra SQL queries to retrieve the required value. In fact sometime when ApiPlatform retrieve a Resource, you cannot use filter because it lacks a join. So I would like to alter the query generated by ApiPlatform by adding an extra INNER JOIN.

Image those tables and related resources:

user: /api/users[/{id}]
booking: /api/bookings[/{id}]
booking_comments: /api/booking/comments[/{id}]

I want a user to retrieve only its information. With a UserAware class it's easy to do this on User and Booking because you have the userId with the logged user. But to prevent someone to read booking comments related to another user, I didn't find the right way to do whereas it would be simple to add an extra inner join on booking.id = booking_commments.booking_id WHERE booking.user_id = %s .

The table booking_comments doesn't have a user_id field, and I don't want to add it in the database. I also don't want to use a DataProvider to build a custom query (this is what I did until now and I don't think it's the current state of the art).

rugolinifr rugolinifr · Accepted Answer · 2020-08-11T13:26:17

I just discovered Doctrine filters with your issue, but in my opinion they are not suited to your needs, because Api Platform leverages its own extensions system, which is mentionned in the security page and is exactly what you want to do : filtering your result sets regarding of your current user. Besides, it gives you better control (what is the current resource ? what is the current operation ? etc.) over the filtering.

A simple example filtering the result set of all Booking and BookingComment URLs:

class BookingExtension implements QueryCollectionExtensionInterface, QueryItemExtensionInterface
{
    /** @var Security */
    private $security;

    public function __construct(Security $security)
    {
        $this->security = $security;
    }

    public function applyToCollection(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
    {
        $this->filterResultSet($queryBuilder, $resourceClass);
    }

    public function applyToItem(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, array $identifiers, string $operationName = null, array $context = [])
    {
        $this->filterResultSet($queryBuilder, $resourceClass);
    }

    private function filterResultSet(QueryBuilder $queryBuilder, string $resourceClass)
    {
            switch ($resourceClass) {
                case BookingComment::class:
                    $comment = $queryBuilder->getRootAliases()[0];
                    $booking = 'booking';
                    $queryBuilder->innerJoin("$comment.booking", $booking);
                    // do not break here !
                case Booking::class:
                    $booking = $booking ?? $queryBuilder->getRootAliases()[0];
                    $queryBuilder->innerJoin("$booking.user", 'user');
                    $queryBuilder->andWhere(':user = user');
                    $queryBuilder->setParameter(':user', $this->security->getUser());
            }
    }
}

Note that this example considers that anonymous users are forbidden.

How to use Doctrine filters based on join field?

1 Answers