How to get the newest entity in a OneToMany relation with Doctrine

Question

Intro

For this question the following two entities are the main characters:

• ObjectElementTask
• Activitites

One ObjectElementTask can have many activities.

Goal

I want to get a collection of ALL Activities and order them by ObjectElementTask and get only the last Activity (based on the 'executiondate' of the activities).

Question

How can i setup the querybuilder from Doctrine to achieve my goal? BTW: I am using Symfony 2.8.

Entities

ObjectElementTask:

class ObjectElementTask
{

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;


    /**
     * @ORM\OneToMany(targetEntity="Activity", mappedBy="objectelementtask", cascade={"persist", "remove"}, orphanRemoval=true)
     * @ORM\OrderBy({"executionDate" = "DESC"})
     * @Expose
     */
    private $activities;

Activity

class Activity {

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="ObjectElementTask", inversedBy="activities", cascade={"persist"})
     * @ORM\JoinColumn(name="objectelementtask_id", referencedColumnName="id", onDelete="CASCADE")
     */
    private $objectelementtask;

    /**
     * @ORM\Column(type="datetime", nullable=true) 
     */
    protected $executionDate;

Thanks in advance!

Edit

Based on the answer in this question: DQL Select every rows having one column's MAX value

i have come up with the following code:

$qb = $this->getDoctrine()
    ->getRepository('AppBundle:Activity')
    ->createQueryBuilder('a')
    ->select('MAX(a.executionDate) maxDate');
$qb2 = $this->getDoctrine()
    ->getRepository('AppBundle:ObjectElementTask')
    ->createQueryBuilder('o');

$qb2->innerJoin('AppBundle:ObjectElementTask', 'oe', 'WITH', $qb2->expr()->eq('oe.activities', '(' . $qb->getDQL() . ')'));

But as you can guess, this doesn't work. It only returns one row (the newest in the Activity). And i want the newest activity of each ObjectElementTask.

Edit 2

I tried this code:

$activities = $this->getDoctrine()
    ->getRepository('AppBundle:Activity')
    ->createQueryBuilder('a')
    ->select('a, MAX(a.executionDate) AS HIDDEN max_date')
    ->groupBy('a.objectelementtask')
    ->getQuery()
    ->getResult();

And the max date returns the value of the last activitie but not the full entity (only the max date...)

Edit 3:

This is the final solution: This is how i finally got what i needed:

$em = $this->getEntityManager();
$rsm = new ResultSetMappingBuilder($em);
$query = $em->createNativeQuery('SELECT a1.* FROM activity a1 INNER JOIN ( SELECT id, max(execution_date) MaxPostDate, objectelementtask_id FROM activity GROUP BY objectelementtask_id ) a2 ON a1.execution_date = a2.MaxPostDate AND a1.objectelementtask_id = a2.objectelementtask_id order by a1.execution_date desc', $rsm);
$rsm->addRootEntityFromClassMetadata('AppBundle:Activity', 'a1');
$activities = $query->getResult();

Answer 1

This is how i finally got what i needed:

    $em = $this->getEntityManager();
    $rsm = new ResultSetMappingBuilder($em);
    $query = $em->createNativeQuery('SELECT a1.* FROM activity a1 INNER JOIN ( SELECT id, max(execution_date) MaxPostDate, objectelementtask_id FROM activity GROUP BY objectelementtask_id ) a2 ON a1.execution_date = a2.MaxPostDate AND a1.objectelementtask_id = a2.objectelementtask_id order by a1.execution_date desc', $rsm);
    $rsm->addRootEntityFromClassMetadata('AppBundle:Activity', 'a1');
    $activities = $query->getResult();