Sonata Admin datagrid filter returning last created row using MAX() on DATETIME on a one-to-many relation

current_status_having.sql

SELECT a.angebot_id
(
    SELECT MAX(ash2.angebot_status_datum)
    FROM angebot_status_historie ash2
    WHERE ash2.angebot_id = a.angebot_id
) AS current_state
FROM
angebot a
JOIN angebot_status_historie ash USING (angebot_id)
HAVING ash.angebot_status_datum = current_state

current_status_where_subquery.sql

SELECT DISTINCT a.angebot_id
FROM
angebot a
JOIN angebot_status_historie ash USING (angebot_id)
WHERE ash.angebot_status_datum = (
    SELECT MAX(ash2.angebot_status_datum)
    FROM angebot_status_historie ash2
    WHERE ash2.angebot_id = a.angebot_id
)

list_current_status.html.twig

{% extends 'SonataAdminBundle:CRUD:base_list_field.html.twig' %}

{% block field %}
    <div>
        {{ object.getCurrentStatus }}
    </div>
{% endblock %}

Offer.php

<?php

/**
 * Offer
 *
 * @ORM\Table(name="angebot", indexes={
 *     ...
 * })
 * @ORM\Entity
 */
class Offer
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="angebot_id", type="integer", precision=0, scale=0, nullable=false, unique=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var integer $statusHistories
     *
     * @ORM\OneToMany(targetEntity="OfferStatusHistory", mappedBy="offer")
     * @ORM\OrderBy({"createdAt" = "DESC"})
     */
    private $statusHistories;

    /**
     * @var string $isArchived
     *
     * @ORM\Column(name="archiv", type="smallint", length=1, columnDefinition="TINYINT(1)")
     */
    private $isArchived;

    /**
     * @var datetime $deletedAt
     *
     * @ORM\Column(name="deleted", type="datetime", nullable=true)
     */
    private $deletedAt;

    private $currentStatus;

    public function getCurrentStatus()
    {
        $currentStatusHistory = $this->statusHistories[0];

        return $currentStatusHistory->getStatus()->getName();
    }
}

OfferAdmin.php

<?php

namespace Sps\Bundle\OfferBundle\Admin;

use Sonata\AdminBundle\Admin\Admin;
use Sonata\AdminBundle\Datagrid\ListMapper;
use Sonata\AdminBundle\Datagrid\DatagridMapper;
use Sonata\AdminBundle\Form\FormMapper;
use Symfony\Component\Security\Core\SecurityContextInterface;

class OfferAdmin extends Admin
{
    /**
     * The number of result to display in the list
     *
     * @var integer
     */
    protected $maxPerPage = 5;

    /**
     * The maximum number of page numbers to display in the list
     *
     * @var integer
     */
    protected $maxPageLinks = 10;

    protected function configureListFields(ListMapper $listMapper)
    {
        $listMapper
            ->add('currentStatus', null, array(
                'mapped' => false,
                'template' => 'SpsOfferBundle:OfferAdmin:list_current_status.html.twig'
            ));
    }
    
    protected function configureDatagridFilters(DatagridMapper $datagridMapper)
    {

    }
    
    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);
        
        return $query;
    }    
}

offeradmin_configuredatagridfilters_addselect.php

<?php

class OfferAdmin extends Admin
{
    protected function configureDatagridFilters(DatagridMapper $datagridMapper)
    {
        $builder = $this->getModelManager()->getEntityManager('Sps\Bundle\BaseBundle\Entity\OfferStatus')->createQueryBuilder();
        $offerStatusResults = $builder->select('PARTIAL os.{id, name}')
            ->from("Sps\Bundle\BaseBundle\Entity\OfferStatus", 'os')
            ->getQuery()
            ->getArrayResult();

        $offerStatusChoices = array();
        foreach ($offerStatusResults as $row) {
            $offerStatusChoices[$row['id']] = $row['name'];
        }

        $datagridMapper
            ->add('currentStatus', 'doctrine_orm_callback', array(
                'callback' => function($queryBuilder, $alias, $field, $value) {
                    if (!$value['value']) {
                        return;
                    }

                    $currentStatusDql = 'SELECT ' .
                                        $queryBuilder->expr()->max('osh.createdAt') .
                                        ' FROM Sps\Bundle\BaseBundle\Entity\OfferStatusHistory osh ' .
                                        'WHERE osh.offer = ' . $alias . '.id'
                                        ;
                    $queryBuilder
                        ->addSelect('(' . $currentStatusDql . ') AS HIDDEN current_state')
                    ;
                        
                    return true;
                }),
                'choice',
                array('choices' => $offerStatusChoices)
            )
        ;
    }
}

offeradmin_configuredatagridfilters_having.php

<?php

class OfferAdmin extends Admin
{
    protected function configureDatagridFilters(DatagridMapper $datagridMapper)
    {
        $builder = $this->getModelManager()->getEntityManager('Sps\Bundle\BaseBundle\Entity\OfferStatus')->createQueryBuilder();
        $offerStatusResults = $builder->select('PARTIAL os.{id, name}')
            ->from("Sps\Bundle\BaseBundle\Entity\OfferStatus", 'os')
            ->getQuery()
            ->getArrayResult();

        $offerStatusChoices = array();
        foreach ($offerStatusResults as $row) {
            $offerStatusChoices[$row['id']] = $row['name'];
        }

        $datagridMapper
            ->add('currentStatus', 'doctrine_orm_callback', array(
                'callback' => function($queryBuilder, $alias, $field, $value) {
                    if (!$value['value']) {
                        return;
                    }

                    $currentStatusDql = 'SELECT ' .
                                        $queryBuilder->expr()->max('osh.createdAt') .
                                        ' FROM Sps\Bundle\BaseBundle\Entity\OfferStatusHistory osh ' .
                                        'WHERE osh.offer = ' . $alias . '.id'
                                        ;
                                        
                    $queryBuilder
                        ->addSelect('(' . $currentStatusDql . ') AS HIDDEN current_state')
                        ->having('sh.createdAt = current_state') // 'current_state' is not defined.
                    ;
                    
                    return true;
                }),
                'choice',
                array('choices' => $offerStatusChoices)
            )
        ;
    }
}

offeradmin_configuredatagridfilters_solved.php

<?php

class OfferAdmin extends Admin
{
    protected function configureDatagridFilters(DatagridMapper $datagridMapper)
    {
        $builder = $this->getModelManager()->getEntityManager('Sps\Bundle\BaseBundle\Entity\OfferStatus')->createQueryBuilder();
        $offerStatusResults = $builder->select('PARTIAL os.{id, name}')
            ->from("Sps\Bundle\BaseBundle\Entity\OfferStatus", 'os')
            ->getQuery()
            ->getArrayResult();

        $offerStatusChoices = array();
        foreach ($offerStatusResults as $row) {
            $offerStatusChoices[$row['id']] = $row['name'];
        }

        $datagridMapper
            ->add('currentStatus', 'doctrine_orm_callback', array(
                'callback' => function($queryBuilder, $alias, $field, $value) {
                    if (!$value['value']) {
                        return;
                    }

                    $qb2 = $this->getModelManager()->getEntityManager('Sps\Bundle\BaseBundle\Entity\OfferStatusHistory')->createQueryBuilder();
                    $qb2->select('MAX(sh2.createdAt)')
                        ->from('Sps\Bundle\BaseBundle\Entity\OfferStatusHistory', 'sh2')
                        ->where('sh2.offer = ' . $alias . '.id')
                        ->andWhere('sh2.name = :status')
                        ->addOrderBy('sh2.createdAt', 'desc')
                        ->setMaxResults(1)
                    ;

                    $queryBuilder
                        ->where('sh.createdAt = :maxCreatedAt')
                        ->setParameter('maxCreatedAt', $qb2->getDql())
                        ->setParameter('status', $value['value'])
                    ;

                    return true;
                }),
                'choice',
                array('choices' => $offerStatusChoices)
            )
        ;
    }
}

offeradmin_configuredatagridfilters_wheresubquery.php

<?php

class OfferAdmin extends Admin
{
    protected function configureDatagridFilters(DatagridMapper $datagridMapper)
    {
        $builder = $this->getModelManager()->getEntityManager('Sps\Bundle\BaseBundle\Entity\OfferStatus')->createQueryBuilder();
        $offerStatusResults = $builder->select('PARTIAL os.{id, name}')
            ->from("Sps\Bundle\BaseBundle\Entity\OfferStatus", 'os')
            ->getQuery()
            ->getArrayResult();

        $offerStatusChoices = array();
        foreach ($offerStatusResults as $row) {
            $offerStatusChoices[$row['id']] = $row['name'];
        }

        $datagridMapper
            ->add('currentStatus', 'doctrine_orm_callback', array(
                'callback' => function($queryBuilder, $alias, $field, $value) {
                    if (!$value['value']) {
                        return;
                    }

                    $currentStatusDql = 'SELECT ' .
                                        $queryBuilder->expr()->max('osh.createdAt') .
                                        ' FROM Sps\Bundle\BaseBundle\Entity\OfferStatusHistory osh ' .
                                        'WHERE osh.offer = ' . $alias . '.id'
                                        ;
                                        
                    $queryBuilder
                        ->andWhere('sh.createdAt = (' . $currentStatusDql . ')')
                    ;

                    return true;
                }),
                'choice',
                array('choices' => $offerStatusChoices)
            )
        ;
    }
}

offeradmin_createquery_scalar.php

<?php

class OfferAdmin extends Admin
{
    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        $builder = $this->getModelManager()->getEntityManager('Sps\Bundle\BaseBundle\Entity\OfferStatusHistory')->createQueryBuilder();
        $builder->select($query->expr()->max('osh.createdAt'))
                ->from('Sps\Bundle\BaseBundle\Entity\OfferStatusHistory', 'osh')
                ->where('osh.offer = ' . $query->getRootAlias() . '.id');
        $currentStatusDql = $builder->getDql();

        $query->addSelect(
                'PARTIAL ' . $query->getRootAlias() . '.{id, number, date, name}',
                'PARTIAL sh.{id, name, createdAt}',
                'PARTIAL pqr.{id}',
                'c',
                'a',
                '(' . $currentStatusDql . ') AS current_state'
                // An exception has been thrown during the rendering of a template ("Warning: get_class() expects parameter 1 to be object, array given ...) in SonataAdminBundle:CRUD:base_list_field.html.twig at line 12.
            )
            ->join($query->getRootAlias() . '.statusHistories', 'sh')
            ->leftJoin($query->getRootAlias() . '.priceQuoteRequest', 'pqr')
            ->leftJoin($query->getRootAlias() . '.customer', 'c')
            ->leftJoin('c.address', 'a')
         ;
         
        return $query;         
    }
}    

offeradmin_createquery_solved.php

<?php

class OfferAdmin extends Admin
{
    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        $query->addSelect(
                'PARTIAL ' . $query->getRootAlias() . '.{id, number, date, name}',
                'PARTIAL sh.{id, name, createdAt}',
                'PARTIAL pqr.{id}',
                'c',
                'a',
            )
            ->join($query->getRootAlias() . '.statusHistories', 'sh')
            ->leftJoin($query->getRootAlias() . '.priceQuoteRequest', 'pqr')
            ->leftJoin($query->getRootAlias() . '.customer', 'c')
            ->leftJoin('c.address', 'a')
         ;
         
        return $query;         
    }
}    

OfferStatus.php

<?php

/**
 * OfferStatus
 *
 * @ORM\Table(name="angebot_status")
 * @ORM\Entity
 */
class OfferStatus
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="angebot_status_id", type="integer", precision=0, scale=0, nullable=false, unique=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string $name
     *
     * @ORM\Column(name="angebot_status_name", type="string", length=100, nullable=false, unique=false)
     */
    private $name;

    /**
     * @var integer $histories
     *
     * @ORM\OneToMany(targetEntity="OfferStatusHistory", mappedBy="status")
     */
    private $histories;
}

OfferStatusHistory.php

<?php

/**
 * OfferStatus
 *
 * @ORM\Table(name="angebot_status_historie")
 * @ORM\Entity
 */
class OfferStatusHistory
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="angebot_status_historie_id", type="integer", precision=0, scale=0, nullable=false, unique=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string $date
     *
     * @ORM\Column(name="angebot_status_datum", type="string", length=100, nullable=false, unique=false)
     */
    private $createdAt;

    /**
     * @var offer
     *
     * @ORM\ManyToOne(targetEntity="Offer", inversedBy="statusHistories")
     * @ORM\JoinColumn(name="angebot_id", referencedColumnName="angebot_id")
     */
    private $offer;

    /**
     * @var status
     *
     * @ORM\ManyToOne(targetEntity="OfferStatus", inversedBy="histories")
     * @ORM\JoinColumn(name="angebot_status_id", referencedColumnName="angebot_status_id")
     */
    private $status;
}

With the latest Doctrine release the queries inside files with the _solved suffix are possible.

Though this solution may has performance issues:
https://gist.github.com/webdevilopers/11b695454d816f53b314