Search code examples
phpsymfonydoctrinedql

How to make an Where In subquery in symfony


I'm trying to do this SQL query with Doctrine QueryBuilder:

SELECT * FROM events WHERE NOT id in (SELECT event_id FROM ues WHERE user_id = $userID)

The UserEventStatus has foreign keys from User and event, as well as an integer for status. I now want to query all events that dont have an entry in UserEventStatus from an particular User. My function for this in the EventRepository looks like this:

    public function getUnReactedEvents(int $userID){
        $expr = $this->getEntityManager()->getExpressionBuilder();
        $originalQuery = $this->createQueryBuilder('e');
        $subquery= $this->createQueryBuilder('b');
        $originalQuery->where(
            $expr->not(
                $expr->in(
                    'e.id',
                    $subquery
                        ->select('ues.user')
                        ->from('App/Entity/UserEventStatus', "ues")
                        ->where(
                            $expr->eq('ues.user', $userID)
                    )
                )
            )
        );
        return $originalQuery->getQuery()->getResult();

    }

But i get an error that says: Error: Method Doctrine\Common\Collections\ArrayCollection::__toString() must not throw an exception, caught ErrorException: Catchable Fatal Error: Object of class Doctrine\ORM\EntityManager could not be converted to string (500 Internal Server Error) Can anyone help me or point me to right point in the docs? Cause i failed to find something that describes my problem. And another thing is, that I don't know if its possible, but it would be nice. Can I somehow make direct Object requests? I mean not with the string App/Entity/UserEventStatus but with something like UserEventStatus::class or something. Thanks for your help in advance. :)

EDIT: It has to be $originalQuery->getQuery()->getResult() of course. If its like it was with $subquery instead i recive [Semantical Error] line I0, col 41 near 'App/Entity/UserEventStatus': Error: Class 'App' is not defined. (500 Internal Server Error)

Second EDIT:

        $expr = $this->getEntityManager()->getExpressionBuilder();
        $queryBuilder = $this->createQueryBuilder('e');

        $subquery= $this->createQueryBuilder('b')
            ->select('ues.user')
            ->from('UserEventStatus', "ues")
            ->add('where', $expr->eq('ues.user', $userID));

        $originalQueryExpression = $expr->not($expr->in('e.id', $subquery));

        $queryBuilder->add('where', $originalQueryExpression);

        return $queryBuilder->getQuery()->getResult();

Third EDIT: Thanks to @Dilek I made it work with a JOIN. This is the final Query:

        $queryBuilder = $this->createQueryBuilder('e')
            ->leftJoin('App\Entity\UserEventStatus', 'ues', 'WITH', 'ues.user=:userID')
            ->setParameter('userID', $userID)
            ->where($expr->orX($expr->not(
                $expr->eq('e.id','ues.event')
            ),
                $expr->not($expr->eq('ues.user', $userID)))
            );


        return $queryBuilder->getQuery()->getResult();

Solution

  • Building AND WHERE into a Query

    public function search($term)
    {
        return $this->createQueryBuilder('cat')
            ->andWhere('cat.name = :searchTerm')
            ->setParameter('searchTerm', $term)
            ->getQuery()
            ->execute();
    }
    

    simple is: ->where('cat.name = :searchTerm')

    UPDATE :

    I think you need to use where in

    $qb->add('where', $qb->expr()->in('ues.user', $userID));
    

    And WHERE Or WHERE