CODEWITHSUNDEEP
symfonysymfony-3.4
SensacionRC
SensacionRC

Reputation: 615

Symfony apply criteria in relation

I have an entity called Products

<?php
class Product
{
    /**
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Product", inversedBy="list")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="id_parent", referencedColumnName="id", nullable=true)
     * })
     */
    private $parent;

    /**
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\Product", mappedBy="parent")
     */
    private $list;
    //... more properties
}

The getList method is like this:

public function getList()
{
    if($this->list !== null){
        return $this->list->getValues();
    }
}

Now, in the controller I have a problem, some times I need to filter the list by a property of the same entity and other times I need it with no filter.

I create the query with dql:

$query = "SELECT p
            FROM AppBundle:Product p
            WHERE (p.parent= 0 OR p.parent IS NULL)
            AND p.code != '' ";
$createdQuery= $this->em->createQuery($query)->setParameters($params);
$result= $createdQuery->getResult();

The products result from the query have the correct data, but if I try to print $products[0]->getList() it retrieve all the child products, and I only need to retrieve only the ones that has a p.code not a blank string.

Is there a way to all a specific criteria to apply to the full result of a query, or somehow pass a parameter to the getList() method to apply logic to that method to sometimes filter the results of the list and sometimes dont filter the list?

UPDATE

Example of the data I retrieve from database:

Product 1
{
    id : 1,
    parent : null,
    code: 'aa',
    list : [
        Product 2
        {
          id : 2,
          parent : object with the product 1,
          code: '',
          list : []
        }
    ]
  }
Product 3
{
    id : 3,
    parent : null,
    code: 'bb',
    list : []
}

Sometimes I only need to retrieve all the products,and sometimes I only need those witch are active = 1.

With the query in the example I retrieve all the products correctly, but AND p.code != '' only apply to the ones thar are in the root. I want to apply this filter to the list property but not always.

Upvotes: 1

Views: 1873

Answers (1)

theredled
theredled

Reputation: 1050

Either you can do it after having retrieved all data from the DB, or during the query.

After

The list that Doctrine provides you ($this->list) is an ArrayCollection which contains a matching method to return a filtered set of entities.

You have to pass a Criteria object to it such as this:

public function getList($myParam = null)
{
    if ($myParam) {
        $crit = Criteria::create();
        $crit->andWhere(Criteria::expr()->eq('myField', $myParam));
        return $this->list->matching($crit)->getValues();
    }
}

This will return all entities from $this->list having ->myField equal to $myParam

More infos around here: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/working-with-associations.html#filtering-collections

During

You can also directly filter associated results by modifying the query using a Join and extra conditions:

$query = "SELECT p
            FROM AppBundle:Product p
            LEFT JOIN p.list child WITH child.active = :active
            WHERE (p.parent= 0 OR p.parent IS NULL)
            AND p.code != ''";
$createdQuery= $this->em->createQuery($query)->setParameters([
  'active' => 1
]);
$result= $createdQuery->getResult();

You can also conditionally modify the query, using a QueryBuilder (you can't miss that thing if you do Doctrine) :

$queryBuilder = $this->em->createQueryBuilder()
  ->from('AppBundle:Product', 'p')
  ->where('(p.parent= 0 OR p.parent IS NULL) AND p.code != ""');

if ($onlyActive)
    $queryBuilder
        ->leftJoin('p.list', 'child', 'child.active = :active')
        ->setParameter('active', 1);

$result = $queryBuilder->getQuery()->getResult();

Upvotes: 2

Related Questions