[ DQL Query, MAX function and getSingleResult ]
Here is my DQL Query in MessageRepository :
public function getLastByIdAndByType($id_employee, $type)
{
$query = $this->_em->createQuery('SELECT MAX(m.sentAt), m.recipient, m.author, m.subject, m.body, m.type FROM MyBundle:Message m JOIN m.employee e WHERE e.id = :id_employee AND m.type = :type')
->setParameter('id_employee', $id_employee)
->setParameter('type', $type)
return $query->getSingleResult();
} </code>
In my controller :
$last_message=$em->getRepository('MyBundle:Message')->getLastByIdAndByType($id, $type);
In my view html.twig {{ last_message.sentAt }}
returns the error : Item "sentAt" for "Array" does not exist
While
{{ last_message.recipient }} works but it's true that for all my messages the recipient is the same.
What should I do to render the most recent date with {{ last_message.sentAt }} ? Thanks a lot!
Answer 1
It looks like you're using the wrong approach, if I'm understanding what you want to achieve.
Going by the function name, I'm assuming that you want to retrieve information about the most recent message that was sent by an employee. For a case like that, what you want to do is order the messages by sentAt and select just the first one.
What you're actually doing is using an aggregate function, which will give you a computed value that isn't actually a part of your Message entity.
Here's an example of how to do what you want (note that because I'm retrieving the whole entity, you can use whatever properties on it that you like in your view - for performance reasons you could still choose to select the individual properties in the query):
public function getLastByIdAndByType($id_employee, $type)
{
$query = $this->_em->createQuery('SELECT m FROM MyBundle:Message m JOIN m.employee e WHERE e.id = :id_employee AND m.type = :type ORDER BY m.sentAt DESC')
->setMaxResults(1)
->setParameter('id_employee', $id_employee)
->setParameter('type', $type);
return $query->getOneOrNullResult();
}
Also worth noting is that you can use getOneOrNullResult as I did here instead of just getSingleResult to avoid throwing an exception if there was no record found. Purely a matter of choice, but it means that you have that possibility in mind while you're coding.
