Doctrine DQL returns multiple types of entities

Question

I have three entities: HandsetSubscription, Handset and Subscription.

The yaml of HandsetSubscription is:

App\SoBundle\Entity\HandsetSubscription:
type: entity
table: handset_subscription
manyToOne:
    handset:
        targetEntity: Handset
    subscription:
        targetEntity: Subscription                    
id:
    id:
        type: integer
        generator: { strategy: AUTO }
        options: { unsigned: true }
fields:
    amount:
        type: integer
        nullable: false
        options: { default: 0, unsigned: true  }
    discount:
        type: integer
        nullable: false
        options: { default: 0, unsigned: true  }

The query:

SELECT hs,s,h
      FROM  \App\SoBundle\Entity\HandsetSubscription hs                    
      JOIN  \App\SoBundle\Entity\Subscription        s with s.id    = hs.subscription 
                        AND s.mins  = 150 
                        AND s.mb    = 250 
                        AND s.sms   = 150
      JOIN  \App\SoBundle\Entity\Handset             h with h.id    = hs.handset ​

These are the class names of the entries retrieved:

App\SoBundle\Entity\HandsetSubscription
Proxies\__CG__\App\SoBundle\Entity\Subscription
Proxies\__CG__\App\SoBundle\Entity\Handset
App\SoBundle\Entity\HandsetSubscription
Proxies\__CG__\App\SoBundle\Entity\Handset
App\SoBundle\Entity\HandsetSubscription
Proxies\__CG__\App\SoBundle\Entity\Handset
…

I would expect to get only HandsetSubscription entities back. Why am I getting proxies of Subscription and Handset too?

By adding fetch eager to the handset and subscription mappings and removing handset and subscription from the SELECT statement in the query I would get only HandsetSubscription but I would like to do this through fetch joins, as stated in the manual (http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html#joins).

UPDATE

Quote from the link posted above:

Fetch join of the address:

<?php
$query = $em->createQuery("SELECT u, a FROM User u JOIN u.address a WHERE a.city = 'Berlin'");
$users = $query->getResult();

When Doctrine hydrates a query with fetch-join it returns the class in the FROM clause on the root level of the result array. In the previous example an array of User instances is returned and the address of each user is fetched and hydrated into the User#address variable. If you access the address Doctrine does not need to lazy load the association with another query.

Angora

Don't SELECT hs,s,h, just SELECT hs. Subscription and Handset will be available through reference getter and setter – Aphonia

This way the s and the h would be lazy loaded. I want them to be eager loaded. – Angora

I am pretty shure they won't. Don't select but keep the join. Try it out! – Aphonia

I just tried it again and it lazy loads them. They would be eager loaded if you specify this in the mapping. But I would like to be able to control this from the DQL as stated in the manual. – Angora

I added a quote from the doctrine manual about fetch-joins. As you can see the association, the address, is specified in the SELECT. – Angora

Answer 1

Big thanks goes to veonik from the #doctrine irc channel for solving this.

Instead of joining with the fully qualified names of the entities you should join with the association. So the query becomes:

SELECT hs,s,h
  FROM  \App\SoBundle\Entity\HandsetSubscription hs                    
  JOIN  hs.subscription s with s.id = hs.subscription 
                    AND s.mins  = 150 
                    AND s.mb    = 250 
                    AND s.sms   = 150
  JOIN  hs.handset h with h.id = hs.handset 

Angora

Works great. Can you please share the QueryBuilder version of that DQL? Thanks. – Cockatoo