Doctrine: Automatic ResultMapping of Native Query?

Question

After some research, I made this UNION query work in my Repository class:

class PostRepository extends ServiceEntityRepository {

    // ...

    public function getLatestPostsOfUser ($limit = 10) : ?array {

        $sql = <<<SQL
SELECT p.id, p.title, p.content, p.images, p.parent_id, p.parent_type, p.created, p.last_modified FROM cms_posts p
LEFT JOIN cms_user_follow ON (p.parent_type = cms_user_follow.followed_entity_type AND p.parent_id = cms_user_follow.followed_entity_id) 
WHERE cms_user_follow.user_id = {$this->currentUser->getId()}

UNION 

SELECT p.id, p.title, p.content, p.images, p.parent_id, p.parent_type, p.created, p.last_modified FROM cms_posts p
LEFT JOIN project_memberships ON (p.parent_type = 'Project' AND p.parent_id = project_memberships.project_id) 
WHERE project_memberships.user_id = {$this->currentUser->getId()} and project_memberships.status = 1

ORDER BY created DESC
LIMIT $limit
SQL;

        $res = [];

        try {
            $rsm = (new ResultSetMapping())
                ->addEntityResult(Post::class, 'p')
                ->addFieldResult('p', 'id', 'id')
                ->addFieldResult('p', 'title', 'title')
                ->addFieldResult('p', 'content', 'content')
                ->addFieldResult('p', 'images', 'images')
                ->addFieldResult('p', 'parent_id', 'parentId')
                ->addFieldResult('p', 'parent_type', 'parentType')
                ->addFieldResult('p', 'created', 'created')
                ->addFieldResult('p', 'last_modified', 'lastModified')
            ;

            $res = $this->getEntityManager()->createNativeQuery($sql, $rsm)->getArrayResult();
        } catch (DBALException $e) {

        }

        return $res;
    }

}

It involves an awefull lot of manual field mapping, so I was wondering wheather there is an automatic solution to this?

Many thx!

Answer 1

It looks like Doctrine can do something like this under the hood and also apply the mappings automatically:

$qb = $this->em->createQueryBuilder();

$where = $qb->expr()->andX(
    $qb->expr()->eq('f.followedEntityId', ':parentId'),
    $qb->expr()->eq('f.followedEntityType', ':parentType'),
    $qb->expr()->eq('f.following', 1),
    $qb->expr()->eq('u.allowEmailNotifications', 1),
    $qb->expr()->eq('u.enabled', 1),
);

$qb->select('f', 'u')
    ->from(UserFollow::class, 'f')
    ->leftJoin(
        User::class,
        'u',
        Join::WITH,
        'f.userId = u.id'
    )
    ->where($where)
    ->setParameters([
        'parentId' => $post->getParentId(),
        'parentType' => $post->getParentType()
    ])
;

$usersAndFollowings = $qb->getQuery()->getResult();

$usersAndFollowings is then a flat array with both entities alternating: [UserFollow, User, UserFollow, User, ...]

You'll probably want to process it afterwards, so that connected UserFollow and User entities are together in a sub array.