Doctrine 2.5 Unexpected association fetch behavior

Question

I have 3 entities associated this way:

Don't worry, I've set associations using annotations, but I thought the following mix would be lighter/cleaner to expose my issue

Post
  @ORM\ManyToOne(targetEntity="User", fetch="EAGER")
  - author
User
  @ORM\OneToOne(targetEntity="Vip", mappedBy="user", fetch="EAGER")
  - vip
Vip
  # Notice that the primary key of vip is a foreign key on User primary
  @ORM\id
  @ORM\OneToOne(targetEntity="User", inversedBy="peliqan", fetch="EAGER")
  @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=false)
  - user 

As you can see, all is set to be eagerly fetched.

What do I need?

I would like to retrieve Posts sets along with both Users & Vip infos ONLY, using a single query. (see edit)

Right now, for every post entry I get one extra query:

SELECT t0.valid AS valid_1, ...
FROM vip t0
INNER JOIN user t10 ON t0.user_id = t10.id WHERE t0.user_id = ?

when:

• I execute this

  $results = $qb
      ->select('ps')
      ->leftJoin('ps.author','u')->addSelect('u')
      ->add('where', $qb->expr()->in('ps.id', $ids))
      ->getQuery()->getResult();
  

• and even while I try to enforce FETCH_EAGER mode like this

  ->getQuery()
  ->setFetchMode('AppBundle\Entity\User', 'vip', ClassMetadata::FETCH_EAGER)
  ->getResult();
  

Note:
I managed to get rid of extra query by enforcingQuery::HYDRATE_ARRAY upon getResult() call.
Queries vanished which saved a third of the initial time.
The downside here is that while retrieving association as arrays, I could not take advantage of Symfony\Component\Serializer\Annotation\Groups any more to filter entities properties and had to manually edit result set in order to remove/transform some values.

EDIT

Wilt answer is okay for the original post. I did not expose my issue the right way. I told I want to retrieve Vip infos because I thought it was a good way to get rid of the extra query I talk above. Actually I do not need Vip infos but omitting ->leftJoin('u.vip','v')->addSelect('v') makes doctrine issue the extra query which gather Vip infos! Is there a way to prevent doctrine from executing this query?

Answer 1

There are two kinds of join queries in Doctrine2:

1) Regular joins
2) Fetch joins

Check the documentation chapter 14.2.2. Joins for more details.

So if you want to fetch join vips you should addSelect and leftJoin them inside your query as follows:

$results = $qb
    ->select('ps')
    ->addSelect('u')->leftJoin('ps.author','u')
    ->addSelect('v')->leftJoin('u.vip','v')
    ->add('where', $qb->expr()->in('ps.id', $ids))
    ->getQuery()->getResult();

---

UPDATE

Update after your comment:

I thought including vip in the result set would be the best way to get rid of the extra query

You cannot get rid of the extra query because you cannot lazy load the inverse side of a one-to-one relationship. Refer also to this post for more details:

This is expected behavior. Inverse sides of one-to-one associations can not be lazy, technically. There is no foreign key on the inverse side, hence it is impossible to decide whether to proxy it or not. We must query for the associated object or join it. Note that this only affects inverse sides of single-valued associations, that is, really only the inverse side of bidirectional one-to-one associations.

• A solution could be to inverse the relationship so user becomes the owning side of the relationship. I that case you can at least lazy-load Vip inside your User entity. The lazy load problem would move to the Vip side, meaning you could not lazy-load your User in Vip any longer.

• Otherwise you could make your query return a Partial object to prevent loading of Vip, but in general you should be very careful with this approach.