doctrine result set mapping for sql, this will map sql data to entity

DQL_example.php

<?php
// DQL example, however the result has to be mapped

$entityManager = $this->getEntityManager();
$query = $entityManager->createQuery(
    'SELECT nm.id AS nm_id, n FROM \App\Entity\Negotiation n
        INNER JOIN n.messages nm WITH nm.id = (SELECT MAX(id) FROM \App\Entity\NegotiationMessage WHERE negotiation_id = n.id)
        ORDER BY nm.id DESC'
)->setParameter('direction', EnumMessageDirectionType::TYPE_INBOUND);

join_on_latest_record.php

<?php
// join on latest record for a many to one relation
$order = 'DESC';
$sql = 'SELECT nm.id AS nm_id, n.* FROM `negotiation` n
        INNER JOIN `negotiation_message` nm ON nm.negotiation_id = n.id
        AND nm.id = (SELECT MAX(id) FROM negotiation_message WHERE negotiation_id = n.id AND nm.direction = \'INBOUND\')
        ORDER BY nm.id ' . $order;

$rsm = new ResultSetMappingBuilder($this->getEntityManager());
$rsm->addRootEntityFromClassMetadata('App\Entity\Negotiation', 'n');

return $this->getEntityManager()->createNativeQuery($sql, $rsm)->getResult();

manual_mapping.php

<?php
// manual result set mapping, the ResultSetMappingBuilder is better.

$rsm = new ResultSetMapping();
$rsm->addFieldResult('n','id', 'id');
$rsm->addFieldResult('n','email', 'email');
$rsm->addFieldResult('n','currency', 'currency');
$rsm->addFieldResult('n','cost_proposed', 'costProposed');
$rsm->addFieldResult('n','cost_agreed', 'costAgreed');

return $this->getEntityManager()->createNativeQuery($sql, $rsm)->getResult();