How to make an Where In subquery in symfony

Question

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

score 1 · Accepted Answer · 2020-01-09T19:21:11.507

1

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

edited Jan 09 '20 at 19:21

answered Jan 09 '20 at 19:11

changing it to andWhere didnt help much... can't I put an expression into where? – CKWDani Jan 09 '20 at 19:20
See updated answer $qb is $originalQuery and there is a sample with simple where clause in answer. – Jan 09 '20 at 19:24
Thanks for your help so far. Unfortunately I still recive the same Error.... with the toString method... – CKWDani Jan 09 '20 at 19:31
I put it like it is now in an new edit in the origonal post, maybee i did oversee something :) – CKWDani Jan 09 '20 at 19:33
@CKWDani stop updating your question please, and Try Like this `->where($qb->expr()->in('ues.user', $userID))` set paramaeters if needed `->setParameter();` qb is `db` instans – Jan 09 '20 at 19:36
ues is actually another table, so it now throws ```Error: 'ues' is not defined```. I addaed it to the main queryBuilder, so the subquery is not used anymore, so I think it does not know which ues I am talking about when Ad it instead of the ```in expression```. If I ad it instead of the ```eq``` expression I still remain with the same error (_toString) – CKWDani Jan 09 '20 at 19:49
are you using left join ? that error means ues table not found, see this answer as an example https://stackoverflow.com/questions/57322563/where-in-query-with-sub-query-in-doctrine-querybuilder-or-equivalent – Jan 09 '20 at 19:50
Thanks for that Idea. Didnt think about it yet :) – CKWDani Jan 09 '20 at 19:56
@CKWDani See here might help **how to create subqueries** https://stackoverflow.com/questions/10762586/how-to-convert-this-to-doctrine-2-querybuilder-format/10763358#10763358 – Jan 09 '20 at 20:10
@CKWDani glad I helped, I didnt its a query from two tables at the beginig) – Jan 09 '20 at 21:15

How to make an Where In subquery in symfony

1 Answers1