CODEWITHSUNDEEP
symfonydoctrine-ormone-to-manywhere-clausequery-builder
Sébastien
Sébastien

Reputation: 5463

doctrine query onetonmany / notIn with objects / symfony forms querybuilder

I am using symfony 2 and doctrine to prefilter a form field type 'entity' with the help of a querybuilder.

My querybuilder should return all products which the user has not already added to a list. All relations are bidirectionnal. I have products linked to userIngredients (oneToMany) each linked to one user (manyToOne)

I have come with this so far but it's not working, I get products not added by other users.

    return $this
        ->createQueryBuilder('p')
        ->leftJoin('p.userIngredients', 'i')
        ->where('i.user <> ?1')
        ->setParameter(1,$user);

1; Any clue on how to correct this ?

Alternatively, I could select the products I don't want and then reselect those who don't match but using an expression and NotIn seems to only work for strings

    $products = $this
        ->createQueryBuilder('p')
        ->leftJoin('p.userIngredients', 'i')
        ->where('i.user = ?1')
        ->setParameter(1,$user)
        ->getQuery()
        ->getResult();

    return $this
        ->createQueryBuilder('p')
        ->where($this->createQueryBuilder('p')->expr()->notIn('p', $products));

2; how could we correct this to make it work with objects ?

3; alternatively : is there a way to pass not a querybuilder but an array of results to symfony form builders ?

Upvotes: 0

Views: 765

Answers (1)

S&#233;bastien
S&#233;bastien

Reputation: 5463

I got thinks thanks to Javad: (slight modification, I'm using an array result, not dql):

$qb = $this->_em->createQueryBuilder();

$ids = $qb
    ->select('p.id')
    ->from('AppBundle:MarketPlace\Product','p','p.id')
    ->leftJoin('p.userIngredients', 'i')
    ->where('i.user = ?1')
    ->setParameter(1,$user)
    ->getQuery()
    ->getResult();

//I don't know why I couldn't directly get an array of ids otherwise... if you know how to do better directly from the query, I'm interested (getScalarResult does not make it)
$ids=array_keys($ids);

$result = $this
    ->createQueryBuilder('p')
    ->where($this->createQueryBuilder('p')->expr()->notIn('p.id', $ids));

return $result;

Upvotes: 0

Related Questions