Array not in array for doctrine query

Question

I need to use a condition like array is not in array. I have array which one has two element for search. For example

(3,1) NOT IN ((2,3),(1,3),(1,32))

it's work as SQL query in phpmyadmin. But not worked as doctrine query.

$em = $this->getDoctrine()->getManager(); 

$qb = $em->getRepository('FangoUserBundle:User')
        ->createQueryBuilder('user')  
        ->leftJoin('user.collabInvitationTarget', 'invite')
        ->where('(:currentUserId, user.id) NOT IN (:inviteArr)')
        ->setParameter('currentUserId', $this->getUser()->getId())
        ->setParameter('inviteArr', $invites, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);

An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 304: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','") in FangoCollabaignBundle:Default:index.html.twig at line 54.

Any advice ?

I think the problem comes from your WHERE. What are you expecting from this condition exactly ? — Aurelien, May 12 '18 at 22:54
`(array) [currentUserId, user.id]` should not in `(array) [:inviteArr]` — Mehmet S., May 12 '18 at 22:59

score 1 · Answer 1 · answered May 12 '18 at 23:13

This might not be the final answer, but there are a few things to correct here.

While it's tolerated in basic SQL, query should be between double quotes, and string should be between simple quotes. Not the opposite.
From your code, I'm guessing that your custom query is in the controller. It would be best to move it into the entity repository, withing a new function, and then call this function in your controller.
It's odd that you don't get an error for createQueryBuilder() as it doesn't exists in this context.
Avoid alias that could be mistaken for entity names.
Your query is wrong around the where() I think.

Taking in account that you applied point 2, this is how I would try to do it.

public function getNameYourFunction($user, $invites) {
    $em=$this->getEntityManager();
    $qb=$em->createQueryBuilder()

    $qb->select("u")
       ->from(User::class, "u")
       ->leftJoin("u.collabInvitationTarget", "i")
       ->where($qb->expr()->notin("u.id", "(:inviteArrA)"))
       ->andWhere($qb->expr()->notin(":currentUserId", "(:inviteArrB)"))
       ->setParameters(array(
           'currentUserId'=>$user->getId(),
           'inviteArrA'=>implode(',', $invites),
           'inviteArrB'=>implode(',', $invites),
       ));
}

You will notice that I'm using User::class.
Make sure to import it in your repository by adding this line :
use FangoUserBundle\Entity\User; (may be different usein your case)

This is what I would do based on your code. Tell us if it solved your problem.

No it's not a true solution but thank you for time and you give to me an idea. I made a trick for search array and haystack array. I concat the element with underscore like 3_2 and (3_1, 4_2) and looking for only one element. It's works for now. — Mehmet S., May 12 '18 at 23:46

Array not in array for doctrine query

1 Answers1

Linked