Symfony2 DQL How to join the last row in a OneToMany relation

Question

I have two entities related by a OneToMany relation:

<?php

namespace CRMBundle\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * User
 *
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="CRMBundle\Entity\ContactRepository")
 */
class User
{

/*...*/

    /**
     * @ORM\OneToMany(targetEntity="CRMBundle\Entity\Message", mappedBy="user", cascade={"persist"})
     * @ORM\OrderBy({"datetime" = "DESC"})
     */
    protected $messages;

/*...*/

}

And

<?php

namespace CRMBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Message
 *
 * @ORM\Table()
 * @ORM\Entity
 */
class Message
{

/*...*/

    /**
     * @ORM\ManyToOne(targetEntity="CRMBundle\Entity\User", inversedBy="messages")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="SET NULL")
     */
    private $user;

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



/*...*/
}

My question is how to create a query in the UserController to get every user with the last message (i.e. the most recent according to the datetime attribute) of each user?

how is 'last message' defined? by any time attribute? by highest id (assuming you're using sequenced integers as primary key)? we won't be able to answer based on your question. — LBA, Nov 05 '15 at 11:03
As messages are ordered by ascending datetime, I want to get the most recent message per user — Kevlar, Nov 05 '15 at 11:11
then please share your message entity definition with us, at least the attribute where the create datetime of a message is persisted — LBA, Nov 05 '15 at 11:13
So you can e.g. select the max(datetime) grouped by user in one query and use this as a subselect in another select: select user, message leftjoin message where message.id in (subselect) — LBA, Nov 05 '15 at 13:37

score 7 · Answer 1 · edited May 23 '17 at 11:47

I think what you are looking for is in one of my previous answers to one of my own questions ^^

You have to use a subquery to select dynamically the most recent datetime value of one user's messages, and to join the message having this value.

To do this, you must define the (sub) query selecting the MAX value of message.datetime:

$qb2= $this->createQueryBuilder('ms')
        ->select('MAX(ms.datetime) maxDate')
        ->where('ms.user = u')
        ;

And then use it in your join clause, the whole function being in your UserRepository:

$qb = $this->createQueryBuilder('u');
$qb ->leftJoin('u.messages', 'm', 'WITH', $qb->expr()->eq( 'm.datetime', '('.$qb2->getDQL().')' ))
    ->addSelect('m');

Your User (each of them) will then have a messages Collection, containing one (or null if no message from the user) message, which you will get this way:

$user->getMessages()->first();

But if you use the lazy loading function of Symfony, as you have already defined an orderby annotation on your user.messages attribute, calling

$user->getMessages()->first()

should return to you the most recent message (but will also load all the others silently). To avoid this silent second DB query, you can join it directly to the query requesting your users:

$qb = $this->createQueryBuilder('u');
$qb ->leftJoin('u.messages', 'm')
    ->addSelect('m');

Symfony2 DQL How to join the last row in a OneToMany relation

1 Answers1