CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Łukasz D. Tulikowski
Łukasz D. Tulikowski

Reputation: 1503

Doctrine returning only one result which possibly violating where statement

I have two entities Item and Property with OneToMany and ManyToOne relations.

Item.php

/**
 * @ORM\Table(name="Item")
 * @ORM\Entity
 * @ORM\HasLifecycleCallbacks
 * @ORM\Entity(repositoryClass="AppBundle\Repository\ItemRepository")
 * @ORM\ChangeTrackingPolicy("NOTIFY")
 *
 * @ExclusionPolicy("all")
 */
class Item
    ...
    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\Property", mappedBy="item")
     */
    protected $itemProperties;   

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="createdTs", type="datetime")
     *
     * @Expose
     */
    protected $createdTs;
    ...

Property.php

/**
 * @ORM\Table()
 * @ORM\HasLifecycleCallbacks
 * @ORM\Entity(repositoryClass="AppBundle\Repository\PropertyRepository")
 */
class Property
    ...
    /**
     * @var string
     *
     * @ORM\Column(name="type", type="string", length=255)
     */
    private $type;

    /**
     * @var \AppBundle\Entity\Item
     *
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Item", inversedBy="itemProperties")
     */
    private $item;
    ...

I want get all items older than $date which have no property entities where type has value invisible.

I build a query like this:

/**
 * @param \Datetime $date
 * @return array
 */
public function getVisibleItemsOlderThan(\Datetime $date)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('i')
        ->from('AppBundle:Item', 'i')
        ->join('i.properties', 'p')
        ->where($qb->expr()->lt('i.createdTs', ':date'))
        ->andWhere('p.type <> :type')
        ->setParameters([
            'date' => $date,
            'type' => 'invisible'
        ]);
    return $qb->getQuery()->getResult();
}

This query returning me one Item, which properties has field where value of type is invisible ignoring rest of items in database.

Upvotes: 1

Views: 539

Answers (1)

Ryan
Ryan

Reputation: 5016

I'm fairly sure this will work, although there may be a better way.

/**
 * @param \Datetime $date
 * @return array
 */
public function getVisibleItemsOlderThan(\Datetime $date)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('i')
        ->from('AppBundle:Item', 'i')
        ->leftJoin('i.properties', 'p', 'WITH', 'p.type = :type')
        ->where($qb->expr()->lt('i.createdTs', ':date'))
        ->andWhere('p.id IS NULL')
        ->setParameters([
            'date' => $date,
            'type' => 'invisible'
        ]);
    return $qb->getQuery()->getResult();
}

You may find andWhere('i.properties IS EMPTY') works here too.

Upvotes: 1

Related Questions