Doctrine2 in ZF2 - DQL gives different result than findOneBy method

Question

I have this entity:

/**
 * User state.
 *
 * @ORM\Entity(repositoryClass="User\Repository\StateRepository")
 * @ORM\Table(name="user_state")
 */
class State implements StateInterface
{
    /** ONE-TO-ONE BIDIRECTIONAL, OWNING SIDE
     * @var User
     * @ORM\Id
     * @ORM\OneToOne(targetEntity="User\Entity\User", inversedBy="state")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     */
    protected $user;

    /** ONE-TO-ONE UNIDIRECTIONAL
     * @var \Application\Entity\State
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Application\Entity\State", fetch="EAGER")
     * @ORM\JoinColumn(name="state_id", referencedColumnName="id")
     */
    protected $state;

    public function __construct(User $user, \Application\Entity\State $state)
    {
        $this->user = $user;
        $this->state = $state;
    }

    /********/
    /* USER */
    /********/
    /**
     * Get user
     *
     * @return User
     */
    public function getUser()
    {
        return $this->user;
    }

    /**
     * Set user
     *
     * @param User $user
     *
     * @return Email
     */
    public function setUser(User $user)
    {
        $this->user = $user;

        return $this;
    }

    /**********/
    /* STATUS */
    /**********/
    /**
     * Get state
     *
     * @return \Application\Entity\State
     */
    public function getState()
    {
        return $this->state;
    }

    /**
     * Set state
     *
     * @param \Application\Entity\State $state
     *
     * @return State
     */
    public function setState(\Application\Entity\State $state)
    {
        $this->state = $state;

        return $this;
    }

    /******/
    /* ID */
    /******/
    /**
     * Get id.
     *
     * @return int
     */
    public function getId()
    {
        return $this->state->getId();
    }

    /**
     * Set id.
     *
     * @param int $id
     *
     * @return State
     */
    public function setId($id)
    {
        $this->state->setId((int) $id);

        return $this;
    }

    /********/
    /* NAME */
    /********/
    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->state->getName();
    }

    /**
     * Set name
     *
     * @param string $name
     *
     * @return State
     */
    public function setName($name)
    {
        $this->state->setName((string) $name);

        return $this;
    }
}

And to fetch the entity I do this in my custom repository

return self::findOneBy($params);

Which results in this query:

SELECT t0.user_id AS user_id1, t0.state_id AS state_id2, t3.id AS id4, t3.name AS name5 FROM user_state t0 LEFT JOIN state t3 ON t0.state_id = t3.id WHERE t0.user_id = ? LIMIT 1

Perfect! Exactly what I wanted and the related entity is eagerly fetched because of the fetch="EAGER" on the relationship. Now I want to achieve the same with DQL, so i do this:

$query = $this->_em->createQuery("SELECT us, s FROM 'User\Entity\State' us LEFT JOIN us.state s WHERE us.user = :user_id");
$query->setParameters($params);
$query->setFetchMode('User\Entity\State', 'state', ClassMetadata::FETCH_EAGER);
return $query->getOneOrNullResult();

I get an error message with: Call to a member function getId() on a non-object Apparently the related entity is not properly loaded. When I check manually the $state turns out to be NULL

If I ommit the s in the query like this:

$query = $this->_em->createQuery("SELECT us FROM 'User\Entity\State' us LEFT JOIN us.state s WHERE us.user = :user_id");

Then it returns the result exactly how I want it, but it results in two queries (an additional query to get the related state entity), which obviously makes sense because the query is not regarded as a fetch join any longer, so it lazy loads the related entity.

How can I get the same result (one query, one result) as I got with using the findOneBy method but than with using DQL?

Bascomb

This question might be helpful – Aghast

@Aghast I tried LEFT JOIN but no difference, still throws Call to a member function getId() on a non-object – Bascomb

@Aghast I updated my question to a LEFT JOIN in the query. – Bascomb

I just updated my Doctrine2 ORM to version 2.5 and now it is working :) – Bascomb

Answer 1

I just updated my Doctrine2 ORM to version 2.5 and now it is working :)

The Doctrine elements inside my current composer.json file look like this:

"doctrine/doctrine-module":"dev-master",
"doctrine/doctrine-orm-module": "dev-master",
"doctrine/dbal": "2.5.*@dev",
"doctrine/orm": "2.5.*@dev",

Bascomb