Skip to content

Instantly share code, notes, and snippets.

@webdevilopers
Last active January 13, 2016 15:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save webdevilopers/9f182b113c9130b2dc68 to your computer and use it in GitHub Desktop.
Save webdevilopers/9f182b113c9130b2dc68 to your computer and use it in GitHub Desktop.
Sonata Admin datagrid filter returning last created row using MAX() on DATETIME on a one-to-many relation
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
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
)
{% extends 'SonataAdminBundle:CRUD:base_list_field.html.twig' %}
{% block field %}
<div>
{{ object.getCurrentStatus }}
</div>
{% endblock %}
<?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();
}
}
<?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;
}
}
<?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)
)
;
}
}
<?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)
)
;
}
}
<?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)
)
;
}
}
<?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)
)
;
}
}
<?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;
}
}
<?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;
}
}
<?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;
}
<?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;
}
@webdevilopers
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment