Get best of the week

Doctrine ResultSetMapping with Examples

Doctrine ORM provides the developer with convenient means of fetching data. This is a powerful DQL for working in an object-oriented manner, and a convenient Query Builder , simple and intuitive to use. They cover most of the needs, but sometimes it becomes necessary to use SQL queries that are optimized or specific to a particular DBMS. To work with query results in code, an understanding of how mapping in Doctrine works is important.



The Doctrine ORM is based on the Data Mapper pattern , which isolates the relational representation from the object representation and converts the data between them. One of the key components of this process is the ResultSetMapping object , which describes how to transform query results from the relational model to the object one. Doctrine always uses ResultSetMapping to represent the query results, but usually this object is created on the basis of annotations or yaml, xml configs, it remains hidden from the eyes of the developer, therefore not everyone knows about its capabilities.

For examples of working with ResultSetMapping, I will use MySQL and employees sample database .

DB structure


Let us describe the entities Department, Employee, Salary, with which we will continue to work further.

Deparment
<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Departments
 *
 * @ORM\Table(name="departments", uniqueConstraints={@ORM\UniqueConstraint(name="dept_name", columns={"dept_name"})})
 * @ORM\Entity
 */
class Department
{
    /**
     * @var string
     *
     * @ORM\Column(name="dept_no", type="string", length=4, nullable=false, options={"fixed"=true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $deptNo;

    /**
     * @var string
     *
     * @ORM\Column(name="dept_name", type="string", length=40, nullable=false)
     */
    private $deptName;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Employee", mappedBy="deptNo")
     */
    private $empNo;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->empNo = new \Doctrine\Common\Collections\ArrayCollection();
    }
}


Employee

<? php

namespace App \ Entity;

use Doctrine \ ORM \ Mapping as ORM;

/ **
* Employees
*
*ORM\ Table (name = "employees")
*ORM\Entity
*/
class Employee
{
/**
* var int
*
* ORM\Column(name=«emp_no», type=«integer», nullable=false)
* ORM\Id
* ORM\GeneratedValue(strategy=«IDENTITY»)
*/
private $empNo;

/**
* var \DateTime
*
* ORM\Column(name=«birth_date», type=«date», nullable=false)
*/
private $birthDate;

/**
* var string
*
* ORM\Column(name=«first_name», type=«string», length=14, nullable=false)
*/
private $firstName;

/**
* var string
*
* ORM\Column(name=«last_name», type=«string», length=16, nullable=false)
*/
private $lastName;

/**
* var string
*
* ORM\Column(name=«gender», type=«string», length=0, nullable=false)
*/
private $gender;

/**
* var \DateTime
*
* ORM\Column(name=«hire_date», type=«date», nullable=false)
*/
private $hireDate;

/**
* var \Doctrine\Common\Collections\Collection
*
* ORM\ManyToMany(targetEntity=«Department», inversedBy=«empNo»)
* ORM\JoinTable(name=«dept_manager»,
* joinColumns={
* ORM\JoinColumn(name=«emp_no», referencedColumnName=«emp_no»)
* },
* inverseJoinColumns={
* ORM\JoinColumn(name=«dept_no», referencedColumnName=«dept_no»)
* }
* )
*/
private $deptNo;

/**
* Constructor
*/
public function __construct()
{
$this->deptNo = new \Doctrine\Common\Collections\ArrayCollection();
}

}


Salary
<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
* Salaries
*
* ORM\Table(name=«salaries», indexes={@ORM\Index(name=«IDX_E6EEB84BA2F57F47», columns={«emp_no»})})
* ORM\Entity
*/
class Salary
{
/**
* var \DateTime
*
* ORM\Column(name=«from_date», type=«date», nullable=false)
* ORM\Id
* ORM\GeneratedValue(strategy=«NONE»)
*/
private $fromDate;

/**
* var int
*
* ORM\Column(name=«salary», type=«integer», nullable=false)
*/
private $salary;

/**
* var \DateTime
*
* ORM\Column(name=«to_date», type=«date», nullable=false)
*/
private $toDate;

/**
* var Employee
*
* ORM\Id
* ORM\OneToOne(targetEntity=«Employee»)
* ORM\JoinColumns({
* ORM\JoinColumn(name=«emp_no», referencedColumnName=«emp_no»)
* })
*/
private $empNo;

/**
* var Employee
*
*/
private $employee;

}



Entity result


Let's start with a simple one, select all departments from the base and project them onto the Department:

        $rsm = new Query\ResultSetMapping();
        $rsm->addEntityResult(Department::class, 'd');
        $rsm->addFieldResult('d', 'dept_no', 'deptNo');
        $rsm->addFieldResult('d', 'dept_name', 'deptName');

        $sql = 'SELECT * FROM departments';

        $query = $this->entityManager->createNativeQuery($sql, $rsm);

        $result = $query->getResult();


The addEntityResult method indicates which class our sample will be projected on, and the addFieldResult methods indicate a mapping between sample columns and object fields. The SQL query passed to the createNativeQuery method will be transferred to the database in this form and Doctrine will not modify it in any way.

It is worth remembering that Entity necessarily has a unique identifier, which must be included in fieldResult.

Joined Entity result


We select the departments in which there are employees hired after the beginning of 2000, together with these employees.

        $rsm = new Query\ResultSetMapping();
        $rsm->addEntityResult(Department::class, 'd');
        $rsm->addFieldResult('d', 'dept_no', 'deptNo');
        $rsm->addFieldResult('d', 'dept_name', 'deptName');

        $rsm->addJoinedEntityResult(Employee::class, 'e', 'd', 'empNo');
        $rsm->addFieldResult('e', 'first_name', 'firstName');
        $rsm->addFieldResult('e', 'last_name', 'lastName');
        $rsm->addFieldResult('e', 'birth_date', 'birthDate');
        $rsm->addFieldResult('e', 'gender', 'gender');
        $rsm->addFieldResult('e', 'hire_date', 'hireDate');

        $sql = "
        SELECT *
            FROM departments d
            JOIN dept_emp ON d.dept_no = dept_emp.dept_no
            JOIN employees e on dept_emp.emp_no = e.emp_no
        WHERE e.hire_date > DATE ('1999-12-31')
       ";

        $query = $this->entityManager->createNativeQuery($sql, $rsm);

        $result = $query->getResult();

ResultSetMappingBuilder


As you can see, working directly with the ResultSetMapping object is somewhat complicated and makes it extremely detailed to describe the comparison of the selection and objects. In part, Doctrine provides a more convenient tool - ResultSetMappingBuilder , which is a wrapper over RSM and adds more convenient methods for working with mapping. For example, the generateSelectClause method , which allows you to create a parameter for the SELECT part of the query with a description of the fields necessary for selecting. The previous request can be rewritten in a simpler form.

        $sql = "
        SELECT {$rsm->generateSelectClause()}
            FROM departments d
            JOIN dept_emp ON d.dept_no = dept_emp.dept_no
            JOIN employees e on dept_emp.emp_no = e.emp_no
        WHERE e.hire_date > DATE ('1999-12-31')
       ";

It is worth noting that if you do not specify all the fields of the created object, Doctrine will return a partial object , the use of which may be justified in some cases, but their behavior is dangerous and not recommended . Instead, ResultSetMappingBuilder allows you not to specify each field of the final class, but to use entities described through annotations (yaml, xml configurations). We rewrite our RSM from the previous request, taking into account these methods:

        $rsm = new Query\ResultSetMappingBuilder($this->entityManager);
        $rsm->addRootEntityFromClassMetadata(Department::class, 'd');
        $rsm->addJoinedEntityFromClassMetadata(Employee::class, 'e', 'd', 'empNo');

Scalar result


Widespread use of the described entity is not justified, it can lead to performance problems, since Doctrine needs to create many objects that will not be used fully in the future. For such cases, a scalar result mapping tool, addScalarResult, is provided .

Choose the average salary for each department:

        $rsm = new ResultSetMappingBuilder($this->entityManager);

        $rsm->addScalarResult('dept_name', 'department', 'string');
        $rsm->addScalarResult('avg_salary', 'salary', 'integer');

        $sql = "
            SELECT d.dept_name, AVG(s.salary) AS avg_salary
            FROM departments d
            JOIN dept_emp de on d.dept_no = de.dept_no
            JOIN employees e on de.emp_no = e.emp_no
            JOIN salaries s on e.emp_no = s.emp_no
            GROUP BY d.dept_name
        ";

        $query = $this->entityManager->createNativeQuery($sql, $rsm);

        $result = $query->getResult();

The first argument to the addScalarResult method is the name of the column in the result set, and the second is the key to the array of results that Doctrine will return. The third parameter is the type of the result value. The return value will always be an array of arrays.

DTO mapping


But working with arrays, especially when they have a complex structure, is not very convenient. You need to keep in mind the names of keys, field types. Doctrine DQL has the ability to use simple DTOs in the selection results, for example:

    SELECT NEW DepartmentSalary(d.dept_no, avg_salary) FROM …

What about Native Query and RSM? Doctrine does not provide documented capabilities for creating new DTOs , but by using the newObjectMappings property , we can specify the objects into which we want to map the selection results. Unlike Entity, these objects will not be controlled by UnitOfWork, and are not required to be in the namespaces specified in the configuration.

Complement RSM from the previous example:

        $rsm->newObjectMappings['dept_name'] = [
            'className' => DepartmentSalary::class,
            'argIndex' => 0,
            'objIndex' => 0,
        ];

        $rsm->newObjectMappings['avg_salary'] = [
            'className' => DepartmentSalary::class,
            'argIndex' => 1,
            'objIndex' => 0,
        ];

The key in the array of the newObjectMappings field points to the resultant column, but its value is another array that describes the object being created. The className key defines the class name of the new object, argIndex - the order of the argument in the constructor of the object, objIndex - the order of the object, if we want to get several objects from each row of the selection.

Meta result


Meta result is used to get meta-data of columns, such as foreign keys or discriminator columns. Unfortunately, I could not come up with an example based on the employees database, so I have to confine myself to a description and examples from the documentation .

        $rsm = new ResultSetMapping;
        $rsm->addEntityResult('User', 'u');
        $rsm->addFieldResult('u', 'id', 'id');
        $rsm->addFieldResult('u', 'name', 'name');
        $rsm->addMetaResult('u', 'address_id', 'address_id');

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

In this case, with the addMetaResult method , we tell Doctine that the users table has a foreign key on addresses, but instead of loading the association into memory (eager load), we create a proxy-object that stores the entity identifier, and when it is accessed, it loads her from the database.

Classic relational databases do not offer table inheritance mechanisms, while inheritance is widespread in the object model. The result of our selection can be projected onto the class hierarchy, according to some attribute, which is the value of the <discriminator_column> column in the resulting selection. In this case, we can tell RSM which column Doctrine should determine the instantiated class by using the setDiscriminatorColumn method .

Conclusion


Doctrine is very rich in various features, including those that not all developers know about. In this post, I tried to introduce an understanding of the operation of one of the key components of ORM - ResultSetMapping in combination with Native Query. Using truly complex and platform-specific queries, while maintaining the availability and comprehensibility of examples, would be a difficult task, because the emphasis is on understanding the work of ResultSetMapping. After that, you can use it in really complex queries for your databases.

More articles:


All Articles