Doctrine Class Table Inheritance - Eager loading sub-class references

Question

Consider the following diagram:

Where a User has many BlogPost's, an Event is a type of BlogPost with a property of Venue.

I have the following entity classes:

**
 * @ORM\Entity()
 * @ORM\Table(name="User")
 */
class User {
    ...
    /**
     * @ORM\OneToMany(targetEntity="BlogPost",mappedBy="User")
     * @ORM\JoinColumn(...)
     */
    protected $BlogPosts;
    ...
}

/**
 * @ORM\Entity()
 * @ORM\Table(name="BlogPost")
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(...)
 * @ORM\DiscriminatorMap({...})
 * ...
 */
class BlogPost {
    ...
    /**
     * @ORM\ManyToOne(targetEntity="User",inversedBy="BlogPosts")
     * @ORM\JoinColumn(...)
     */
    protected $User
    ...
}

/**
 * @ORM\Entity()
 * @ORM\Table(name="Event")
 */
class Event extends BlogPost {
    ...
    /**
     * @ORM\ManyToOne(targetEntity="Venue")
     * @ORM\JoinColumn(...)
     */
    protected $Venue;
    ...
}

/**
 * @ORM\Entity()
 * @ORM\Table(name="Venue")
 */
class Venue {
    ...
}

I'd now like to build a query that gets a given users events and eagerly loads the events venues. The closest I've got is:

$qb = $em->createQueryBuilder();
$qb
    ->select('User,'BlogPost,Venue')
    ->from('User','User')
    ->join('User.BlogPosts','BlogPost','WITH','BlogPost INSTANCE OF :Event')
    ->join('BlogPost.Venue','Venue')
    ->setParameter('Event',$em->getClassMetadata('Event'));

This gives me a rather predictable error of:

Error: Class BlogPost has no association named Venue

Removing the references to Venue I'll get all of the users Events just fine but then I'll have to rely upon lazy-loading to get the rest of the Event's properties.

I've seen suggestions that I map Events in User:

class User {
    ...
    /**
     * @ORM\OneToMany(targetEntity="BlogPost",mappedBy="User")
     * @ORM\JoinColumn(name="UserID", referenceColumnName="UserID")
     */
    protected $BlogPosts;

    /**
     * @ORM\OneToMany(targetEntity="Event",mappedBy="User")
     * @ORM\JoinColumn(name="UserID", referenceColumnName="UserID")
     */
    protected $Events;
    ...
}

Attempting this, the SQL that is generated attempts to do something like:

SELECT * FROM User 
JOIN Event ON User.id = Event.user_id
JOIN BlogPost ON BlogPost.id = Event.blog_post_id

Where user_id is a column in BlogPost and not Event and thus errors with:

Invalid column name 'user_id'

How should I be mapping Class Table Inherited references? Is there a way to achieve eager loading as I've described?

I'm aware that I've omitted an amount of detail from the classes, their mapping, and the generated SQL. Please shout if certain details would help get to the bottom of this.

Edit

This differs from Single Table Inheritance (STI) with a ManyToOne relationship as its for Class Table Inheritance rather than Single Table Inheritance. Additionally I am interested in the specifics of properties of the sub-class, imagine if in that example, Video had additional references.

Answer 1

I have found a way that does what I want but I'm sure that there must be a better way.

First write a native query:

$sql =<<<SQL
SELECT * FROM User user
JOIN BlogPost blog_post ON user.id = blog_post.UserID
JOIN Event event ON blog_post.id = event.BlogPostID
JOIN Venue venue ON event.VenueID = venue.id
WHERE user.id = ?
SQL;

This can either be run directly through $em->getConnection()->prepare() or better still using a ResultSetMapping and then through $em->createNativeQuery($sql,$rsm)

$rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata('User','user');
$rsm->addIndexBy('user','id');
$rsm->addJoinedEntityResult('event','event','user','BlogPosts');
$rsm->addJoinedEntityResult('venue','venue','event','Venue');
$rsm->addFieldResult(); // add all of the fields you are interested in

$query = $this->get_manager()->createNativeQuery($sql,$rsm);
$query->setParameter(1, $user_id);

return $query->getResult($hydration_mode);

It seems a shame that this can't be done through the QueryBuilder but as far as I can tell, it's the only way to achieve this. I'd love to be shown otherwise.