DQL query in Symfony 5 to fetch newest row of subset

Question

I have a database with table called "message" with attributtes received, emitter, text, date. I want to select the last message that a user received, with max date.

With this code I get null value but the user have messages in the table:

 $message = $this->getEntityManager()
        ->createQuery(
            'SELECT m FROM App\Entity\Message m WHERE
                m.receiver = :user
                AND
                m.createdAt = (SELECT MAX(m1.createdAt) FROM App\Entity\Message AS m1)
            '
        )
        ->setParameters([
            'user' => $user
        ])
        ->getResult();

Answer 1

Your subquery doesn't include the user-condition; it fetches max(created) of messages, which is not necessarily one of the given user. But the subquery approach seems to overcomplicate things anyway.

An easier way would be: select messages of user order by created and limit to 1

in SQL

SELECT m.* FROM messages WHERE user_id=:user ORDER BY created DESC LIMIT 1

in DQL

$this
    ->getEntityManager()
    ->createQuery('SELECT m FROM App\Entity\Message m WHERE
        m.receiver = :user
        AND
        m.createdAt = (SELECT MAX(m1.createdAt) FROM App\Entity\Message AS m1)
        ORDER BY m.createdAt DESC LIMIT 1
    ')
    ->setParameters([
        'user' => $user
    ])
    ->getResult();  

or even simpler (using doctrine repository interface)

$entityMangager
  ->getRepository(Message::class)
  ->findOneBy(['user' => $user], ['created' => 'DESC'])

---

Also: Probably you want to make sure you have an index over user_id, created on that table.