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
– Aurelien, Commented May 12, 2018 at 22:54
(array) [currentUserId, user.id] should not in (array) [:inviteArr] — Mehmet S.
– Mehmet S., Commented May 12, 2018 at 22:59

Preciel · Accepted Answer · 2018-05-12 23:13:06Z

1

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.

answered May 12, 2018 at 23:13

Preciel

2,8773 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mehmet S. Over a year ago

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.

Collectives™ on Stack Overflow

Array not in array for doctrine query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related