In Doctrine 2, when using Table Class Inheritance: how can I write my own SQL query that will return the results of a child class?

Question

The execution of your own SQL query in the inheritance hierarchy of the table classes is indicated in the examples in the documents , but I will give a corresponding 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 an example:

Please note that in the case of Inheriting table classes, the example as described above will lead to partial objects if any objects as a result are actually a user subtype. When using DQL, Doctrine automatically includes the necessary joins for this matching strategy, but with SQL it is your responsibility.

, , User, , . ?

, ( - , - ), . :

/**
 * @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;
}

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

, User RegularUser Administrator (: RegularUser, Administrator).

: "romanb" (, ) , ​​ Solr, . Solr romanb romanb, . , (.. ) , , Solr. ?

Doctrine , SQL. MySQL, , .

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();

, SQL . $rsm->addFieldResult('u', 'phone_number', 'phoneNumber'), , RegularUser ( ids , ), RegularUser .

, RegularUser?

.

Answer 1

stackoverflow , .

Native SQL- :

<?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();

, , , , RegularUser.

, - ( , ).