原则 2:如何从专用状态表中获取最新状态 - Doctrine 2: How to get last status from dedicated status table

Doctrine 2: How to get last status from dedicated status table

我有两个表：邮件和状态。

CREATE TABLE mail
(
  mail_id integer NOT NULL,
  mail_from character varying NOT NULL,
  mail_to character varying NOT NULL,
  subject character varying NOT NULL,
  text text,
  CONSTRAINT mail_pkey PRIMARY KEY (mail_id)
)
CREATE TABLE mail_status
(
  status_id integer NOT NULL,
  mail_id integer NOT NULL,
  code integer NOT NULL,
  CONSTRAINT mail_status_pkey PRIMARY KEY (status_id)
)

如何使用原则 2 获取状态代码为 = 1 的最后一个状态代码的所有邮件行。

例如.SQL：

SELECT mail.*
FROM mail
WHERE (SELECT code FROM mail_status WHERE mail_status.mail_id = mail.mail_id ORDER BY mail_status.status_id DESC LIMIT 1) = 1

In MailBundle/Entity/Repository/MailRepository

public function findEmailsByStatusCode($code)
{ 
    $qb = $this->getEntityManager()->createQueryBuilder('mail');
    $qb
        ->select('mail')
        ->from('Path'MailBundle'Entity'Mail', 'mail')
        ->join('mail.statuses', 'mailStatus')
        ->where('mailStatus.code = :code'))
        ->orderBy('mailStatus.creationDate', 'DESC')
        ->groupBy('mail.mail_id')
        ->setParameter('code', $code)
    ;
    $query = $qb->getQuery();
    return $query->getResult();
}

然后从控制器只需调用

$mailRepository = $this->getDoctrine()->getRepository('MailBundle:Mail');
$email = $mailRepository->findEmailsByStatusCode(1);