How can I filter doctrine query by related id (Symfony 4)?

Question

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

In my Controller:

$group = $this->getDoctrine()->getRepository(class::fields)->findAll();

The output:

array:2 [▼
  0 => Fields {#120842 ▼
    -id: 3
    -name: "cat"
    -unique_id: "5a38c820ed"
    -productgroup: PersistentCollection {#120846 ▼
      -snapshot: array:1 [ …1]
      -owner: Fields {#120842}
      -association: array:20 [ …20]
      -em: EntityManager {#114768 …11}
      -backRefFieldName: "fields"
      -typeClass: ClassMetadata {#119877 …}
      -isDirty: false
      #collection: ArrayCollection {#120847 ▼
        -elements: array:1 [▼
          0 => Productgroup {#120528 ▼
            -id: 6
            -name: "Animals"
            -unique_id: "9e4ef1c46f"
            -fields: PersistentCollection {#120739 ▶}
          }
        ]
      }
      #initialized: true
    }
    -type: Type {#120923 ▶}
  }
  1 => Fields {#120924 ▼
    -id: 5
    -name: "horse"
    -unique_id: "c3890b9287"
    -productgroup: PersistentCollection {#120925 ▼
      -snapshot: []
      -owner: Fields {#120924}
      -association: array:20 [ …20]
      -em: EntityManager {#114768 …11}
      -backRefFieldName: "fields"
      -typeClass: ClassMetadata {#119877 …}
      -isDirty: false
      #collection: ArrayCollection {#120926 ▼
        -elements: []
      }
      #initialized: false
    }
    -type: Type {#120927 ▶}
  }
]

I want to filter now all $group to output only the fields that are not connected to the productgroup with the id 6. My approach:

In my controller:

$group = $this->getDoctrine()->getRepository(class:fields)->filterByColletion(6);

In my Repository:

 public function filterByColletion($id)
    {
      return $this->createQueryBuilder('p')
      ->addSelect('f')
      ->from('App\Entity\Fields', 'f')
      ->leftJoin('f.productgroup', 'productgroup')
      ->andWhere('f.productgroup != 6')
      ->getQuery()
      ->execute();
    }

The error is

Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected

As a result I expect only group contains horse.

score 1 · Accepted Answer · answered Feb 12 '19 at 13:15

1

You have ->andWhere('f.productgroup != 6') in your query, but f.productgroup is not a plain value field, but relation. You need to apply your condition to value field, so it would be something like this:

->leftJoin('f.productgroup', 'pg')
->andWhere('pg.id != 6')

I've used pg.id in this example, but you need to use name of actual value field name from Productgroup entity that you want to apply condition to.

Side note: it is better to not embed value directly into query, but pass it as parameter instead:

->leftJoin('f.productgroup', 'pg')
->andWhere('pg.id != :id')
->setParameter(':id', 6)

answered Feb 12 '19 at 13:15

Flying

4,422
2
17
25

Thank you, I tested your code, but now the output is only `[]` Totally empty array. No error message. But I expect, that in the array there is horse – peace_love Feb 12 '19 at 13:25
Since you're working with Symfony - I would recommend you to enable developer toolbar (if not yet) and take a look at "Doctrine" tab of your request. It contains log of all queries made during request along with actual SQL queries generated by Doctrine. Review actual query, maybe it will give you a hint of what goes wrong in your case – Flying Feb 12 '19 at 13:42
I checked. Cannot find this request in the list. I found out until now I have to remove `->addSelect('f')`. And if I write `->andWhere('pg.id = :id')` then it shows the array as in my example above – peace_love Feb 12 '19 at 13:53
I think `andWhere` is wrong in this case. Because I need to figure out the relation – peace_love Feb 12 '19 at 13:56
If you're using `createQueryBuilder()` from inside repository - you can omit `select()` and `from()` clauses because they're [applied automatically](https://github.com/doctrine/orm/blob/e8eaf8386de1497863cb400fbc4fddda5878db7e/lib/Doctrine/ORM/EntityRepository.php#L60-L62) in this case – Flying Feb 12 '19 at 14:01
in my database this is the table `fields`: https://i.stack.imgur.com/bWLNi.png and this is the table `productgroup`: https://i.stack.imgur.com/bRNnR.png and this is `fields_productgroup`: https://i.stack.imgur.com/jLp9M.png – peace_love Feb 12 '19 at 14:04
You want to have negative condition for N:N relationship, it is not a straightforward thing. Please refer [this article](http://www.mysqldiary.com/a-many-to-many-relationship-table-solving-the-exclude-relation-problem/) and [this question](https://stackoverflow.com/q/7713806/2633956) on SO for more information – Flying Feb 12 '19 at 14:36
Thank you, I will let you know as soon as I could figure out a solution – peace_love Feb 12 '19 at 15:02

score 1 · Answer 2 · answered Oct 24 '21 at 12:38

1

You can skip the left join by using the IDENTITY() DQL function:

            ->andWhere('IDENTITY(f.productgroup) != :productgroup_id')
            ->setParameter('productgroup_id', $id);

answered Oct 24 '21 at 12:38

Tac Tacelosky

3,165
3
27
28

How can I filter doctrine query by related id (Symfony 4)?

2 Answers2