Join from inversed side

Question

given the two following intities:

<?php
    /**
     * User
     * @ORM\Entity()
     */
    class User implements AdvancedUserInterface, \Serializable, EncoderAwareInterface
    {
        /**
         * @var Vip
         * @ORM\OneToOne(targetEntity="Vip", mappedBy="user", fetch="EAGER")
         */
        protected $vip;

        // …

<?php
    /**
     * Vip
     * @ORM\Entity()
     */
    class Vip
    {
        /**
         * @ORM\id @ORM\OneToOne(targetEntity="User", inversedBy="vip", fetch="EAGER")
         * @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=false)
         */
        protected $user;

        // …

---

SHORT :
How can I do this SQL in DQL given above entities:
SELECT u.firstName, v.foo FROM User join Vip v ON v.user_id = u.id

In other words how can I retrieve 10 first users ( with their VIP infos if it exists), using DQL join in such a way that only one SQL query will be generated by Doctrine. Is that possible ?

---

Long story:
The owning side is the Vip entity because it holds the reference/foreign key to a User underneath in the database.

I am trying to retrieve all User with their Vip datas.
Using the knplabs/knp-paginator-bundle, I first set up a simple query:

$dql = "SELECT u, p FROM AppBundle:User u;

In spite of enforcing the fetch attribute as « EAGER », Vip infos where not part of the initial query. As a consequence, calling getter getVip() on each iteration from inside the twig for in loop like

{% for user in pagination %}
    {% if user.getVip() %}
        <span class="label label-warning">V.I.P</span>
    {% endif %}
{{% endfor %}}

.. caused a query to be issued on each iteration !

The Symfony dev bar shows 6 queries:

DQL documentation and says that one can use JOIN keyword. So my query became:
$dql = "SELECT u, v FROM AppBundle:User u JOIN u.vip v;

But now I get this error:

Warning: spl_object_hash() expects parameter 1 to be object, null given

Here I'm stuck, wondering how I could fetch Vip datas (or null) along with User datas, in a single query.

Answer 1

Yes, one may add associated entities to the SELECT statement.
But more precisely, one should only add relations that are really involved in the expected result , in other words, entities fetched as "EAGER".

I realized that the vip entity had another relation (oneToMany with a vehicule entity). I just want to retrieve users with their vip metas. Adding another JOIN to the query would just bring more datas since I would not use vehicules anyway (and issue extra work behind the scenes).

-> So I simply changed the fetch attribute from "EAGER" to "LAZY" in the vip OneToMany declaration.

To conclude:

Ask yourself «what are involved intities ?», should it be part of the result (do you simply need those infos).

if NO, you might turn fetch attribute to "[EXTRA_]LAZY" in the relation declaration like

/**
 * @ORM\OneToMany(targetEntity="Vehicule", mappedBy="vip", fetch="LAZY", …)
 */

protected $vehicules;

if YES you will have to select those entities in your query.

Using DQL:
SELECT u, v, w FROM AppBundle:User u LEFT JOIN u.vip v LEFT JOIN v.vehicules w

Using queryBuilder:

$repository = $em->getRepository(User::class);
$users = $repository->createQueryBuilder('u')
    ->addSelect('v')
    ->join('u.vip', 'v')
    ->addSelect('w')
    ->join('v.vehicules', 'w')
    // …

Answer 2

In other words how can I retrieve 10 first users ( with their VIP infos if it exists), using DQL join in such a way that only one SQL query will be generated by Doctrine. Is that possible ?

You should initialize all related entities using select clause to avoid additional queries when accessing to the related objects.

$repository = $em->getRepository(User::class);

$users = $repository->createQueryBuilder('u')
        ->addSelect('v') // Initialize Vip's
        ->join('u.vip', 'v')
        ->getQuery()
        ->setMaxResults(10)
        ->getResult();