In Doctrine 2 when using Class Table Inheritance: how can one write a Native SQL Query that will return results from a child class?

Question

Doing a Native SQL Query on a class table inheritance hierarchy is mentioned in the examples in the docs but I will produce the relevant example here:

<?php

use Doctrine\ORM\Query\ResultSetMapping;

// Equivalent DQL query: "select u from User u where u.name=?1"
// User is a mapped base class for other classes. User owns no associations.
$rsm = new ResultSetMapping;
$rsm->addEntityResult('User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'name', 'name');
$rsm->addMetaResult('u', 'discr', 'discr'); // discriminator column
$rsm->setDiscriminatorColumn('u', 'discr');

$query = $this->_em->createNativeQuery('SELECT id, name, discr FROM users WHERE name = ?', $rsm);
$query->setParameter(1, 'romanb');

$users = $query->getResult();

From the example:

Note that in the case of Class Table Inheritance, an example as above would result in partial objects if any objects in the result are actually a subtype of User. When using DQL, Doctrine automatically includes the necessary joins for this mapping strategy but with native SQL it is your responsibility.

The problem with the above query is that while it will return fields from the parent class User if you are looking to return fields from a child class that above example does not return them. How do we do this?

Before I go any further I have taken the time to create some example classes (one a parent, the other a child), that should work for the above example query. Let me provide them for you:

/**
 * @ORM\Table(name="users",
 * @ORM\Entity
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="discr", type="string")
 * @ORM\DiscriminatorMap({
 *  "regular_user" = "RegularUser",
 *  "administrator" = "Administrator",
 * })
 */
class User
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(name="name", type="string")
     */
    protected $name;
}

And:

/**
 * @ORM\Table(name="regular_users",
 * @ORM\Entity
 */
class RegularUser extends User
{
    /**
     * @ORM\Column(name="phone_number", type="string")
     */
    protected $phoneNumber;
}

So we have a parent class User with two child classes RegularUser and Administrator (note: I only provided the one child class RegularUser but I did provide the discriminator mapping for child class Administrator).

Consider this: Instead of querying the database for a user with name 'romanb' (like at the top example) we would like to use a fulltext search engine like Solr to index the database. We query Solr for romanb and it returns an array of user ids of users with a name like romanb ordered by relevance. With these ordered id's we would like to query the database and return a list of regular users, fully hydrated (i.e. including their phone number) and ordered in the same order as the id's that Solr returned. How can we do this?

Doctrine does not support custom ordering by as it is not supported by all SQL engines. It is supported by MySQL which happens to be the database that we are using.

Consider the following Native SQL Query:

$rsm = new ResultSetMapping;
$rsm->addEntityResult('User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'name', 'name');
$rsm->addMetaResult('u', 'discr', 'discr');
$rsm->setDiscriminatorColumn('u', 'discr');
$rsm->addFieldResult('u', 'phone_number', 'phoneNumber'); // How do we map this result?

// Returned by Solr
$userIds = array(22, 3, 88, 109, 12);

$sql = <<<SQL
  SELECT u.id, u.name, u.discr, r.phone_number
  FROM users u
  INNER JOIN regular_users r ON users.id = regular_users.id
  WHERE u.id IN (?)
  ORDER BY FIELD(u.id, ?); # Custom SQL logic that will return an ordered set
SQL;

$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $userIds);
$query->setParameter(2, $userIds);

$users = $query->getResult();

That above query will fail with an error though the SQL is fine. If we commented out $rsm->addFieldResult('u', 'phone_number', 'phoneNumber') it would work and a RegularUser object would be returned in the results (because all ids belong to regular users and we defined the discriminator column in the result set mapping) but none of those RegularUser objects would contain a phone number.

Is there anyway to do a query that will return a fully hydrated RegularUser object?

Thanks for reading.

Answer 1

Sometimes the good thing about writing out questions on stackoverflow is that it helps you think through the question better and that can help you come to a solution.

Here is a working Native SQL query for the above:

<?php

$rsm = new ResultSetMapping;
$rsm->addEntityResult('RegularUser', 'r');
$rsm->addFieldResult('r', 'id', 'id');
$rsm->addFieldResult('r', 'name', 'name');
$rsm->addFieldResult('r', 'phone_number', 'phoneNumber');

$userIds = array(22, 3, 88, 109, 12);

$sql = <<<SQL
  SELECT r.id, u.name, r.phone_number
  FROM regular_users r
  INNER JOIN users u ON r.id = u.id
  WHERE u.id IN (?)
  ORDER BY FIELD(u.id, ?); # Custom SQL logic that will return an ordered set
SQL;

$query = $this->_em->createNativeQuery($sql, $rsm);
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $userIds);
$query->setParameter(2, $userIds);

$users = $query->getResult();

Basically there is no need to involve the discriminator map assuming you know the ids you are querying for are of class RegularUser.

I hope this helps someone else (edge case I know).