CODEWITHSUNDEEP
phpsymfonydoctrinedql
CKWDani
CKWDani

Reputation: 33

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();

Upvotes: 0

Views: 1262

Answers (1)

user1805543
user1805543

Reputation:

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

Upvotes: 1

Related Questions