How can I filter related entities with query builder by using negative conditions?

Question

My entity:

  /**
  * @ORM\ManyToMany(targetEntity="Productgroup", inversedBy="fields")
  * @ORM\JoinColumn(name="productgroup", referencedColumnName="id")
  */
  private $productgroup;

  public function getProductgroup()
  {
    return $this->productgroup;
  }

  public function setProductgroup($productgroup): self
  {
    $this->productgroup = $productgroup;

    return $this;
  }

  public function __construct()
  {
    $this->productgroup = new ArrayCollection();
  }

This is the output:

array:2 [▼
  0 => Fields {#7534 ▼
    -id: 3
    -name: "cat"
    -unique_id: "5a38c820ed"
    -productgroup: PersistentCollection {#7538 ▼
      -snapshot: array:1 [ …1]
      -owner: Fields {#7534}
      -association: array:20 [ …20]
      -em: EntityManager {#2889 …11}
      -backRefFieldName: "fields"
      -typeClass: ClassMetadata {#6568 …}
      -isDirty: false
      #collection: ArrayCollection {#7539 ▼
        -elements: array:1 [▼
          0 => Productgroup {#7220 ▼
            -id: 6
            -name: "Animals"
            -unique_id: "9e4ef1c46f"
            -fields: PersistentCollection {#7431 ▶}
          }
        ]
      }
      #initialized: true
    }
    -type: Type {#7615 ▶}
  }
  1 => Fields {#7616 ▼
    -id: 5
    -name: "horse"
    -unique_id: "c3890b9287"
    -productgroup: PersistentCollection {#7617 ▼
      -snapshot: []
      -owner: Fields {#7616}
      -association: array:20 [ …20]
      -em: EntityManager {#2889 …11}
      -backRefFieldName: "fields"
      -typeClass: ClassMetadata {#6568 …}
      -isDirty: false
      #collection: ArrayCollection {#7618 ▼
        -elements: []
      }
      #initialized: false
    }
    -type: Type {#7619 ▶}
  }
]

What I am trying to do is remove all arrays, that do not have a relation in with the producgroup id 6.

Until now I could figure out how I can remove all arrays that do NOT have a relation with productgroup id 6:

The Controller:

$group = $this->getDoctrine()->getRepository($EntityName)->filterByColletion(6);

And the Repository:

  public function filterByColletion($id)
    {
      return $this->createQueryBuilder('f')
      ->leftJoin('f.productgroup', 'pg')
      ->where('pg.id = :id')
      ->setParameter(':id', 6)
      ->getQuery()
      ->execute();
    }

The result is:

array:1 [▼
  0 => Fields {#7534 ▼
    -id: 3
    -name: "cat"
    -unique_id: "5a38c820ed"
    -productgroup: PersistentCollection {#7538 ▼
      -snapshot: array:1 [ …1]
      -owner: Fields {#7534}
      -association: array:20 [ …20]
      -em: EntityManager {#2889 …11}
      -backRefFieldName: "fields"
      -typeClass: ClassMetadata {#6568 …}
      -isDirty: false
      #collection: ArrayCollection {#7539 ▼
        -elements: array:1 [▼
          0 => Productgroup {#7220 ▼
            -id: 6
            -name: "Animals"
            -unique_id: "9e4ef1c46f"
            -fields: PersistentCollection {#7431 ▶}
          }
        ]
      }
      #initialized: true
    }
    -type: Type {#7615 ▼
      +__isInitialized__: true
      -id: 3
      -name: "password"
      -unique_id: "2ef6e55a1d"
      -label: "password"
       …2
    }
  }
]

But I need it exactly the other way around. So I tried:

   public function filterByColletion($id)
    {
      return $this->createQueryBuilder('f')
      ->leftJoin('f.productgroup', 'pg')
      ->where('pg.id != :id')
      ->setParameter(':id', 6)
      ->getQuery()
      ->execute();
    }

But this is giving me an empty array as output and not as expected the field with the name horse.

I also tried another approach, but this did not work out ether:

public function filterByColletion($id)
{
  $em = $this->getEntityManager();
  $qb  = $this->_em->createQueryBuilder();

  return $this->createQueryBuilder('f')
  ->leftJoin('f.productgroup', 'pg')
  ->where($qb->expr()->notIn('pg.id',6))
  ->getQuery()
  ->execute();
}

score 1 · Accepted Answer · answered Feb 12 '19 at 16:41

1

I believe you are using expr improperly try using this way:

$qb->expr()->notIn('f.productgroup', [6])

You can read more here How to use WHERE IN with Doctrine 2

answered Feb 12 '19 at 16:41

Vytenis Ščiukas

473
2
9

I tested your suggestion and I get an error `[Semantical Error] line 0, col 70 near 'productgroup': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.` – peace_love Feb 12 '19 at 16:48
Have you tried ```->where(":id NOT MEMBER OF f.productgroup")```? Where id can entity id or whole entity in your case 6. – Vytenis Ščiukas Feb 12 '19 at 17:30

How can I filter related entities with query builder by using negative conditions?

1 Answers1