-1

I have a problem converting the following SQL query to doctrine queryBuilder syntax. I opted for the query builder since I read that inner joins are not possible with DQL(which I am confortable with). Your usual help is appreciated in a time I really have headacke after a lot of searching:

SELECT m.* 
FROM  `message` AS m
INNER JOIN (SELECT sender_id, MAX(message_date) as md
                FROM message WHERE  `receiver_id` =1 GROUP BY sender_id) AS t
ON m.id=t.md and m.sender_id=t.sender_id
WHERE  `receiver_id` =1

In words, my final goal is to select all the latest messages received by a receiver_id from a table looking like:

Message(id, sender_id, receiver_id, message_date)

Adib Aroui
  • 4,981
  • 5
  • 42
  • 94

1 Answers1

0

I think you should be able to manage with the documentation.
But here to get you on your way:

In your Message repository:

$queryBuilder = $this->createQueryBuilder('m');
$queryBuilder->addSelect('s')
    ->innerJoin('m.sender', 's')
    ->where('m.receiver = :receiver_id')
    ->andWhere($queryBuilder->expr()->max('m.date'))
    ->setParameters(
        array(
            'receiver_id' => $receiver_id,
        )
    );

$query = $queryBuilder->getQuery();
return $query->getSingleResult();

I hope I understood your SQL correctly. Without entity definitions this is as much as I can say. Otherwise you should add them to your questions...

UPDATE

One last update after seeing your final query. You can do IN with a sub query builder.
In your MembersManagementBundle:Message repository:

$qb  = $this->_em->createQueryBuilder();
$sub = $qb;

$sub->select('t')
    ->where('m.id = t.id')
    ->andWhere($qb->expr()->max('t.date'))
    ->getQuery();

$qb = $qb->select('m')
         ->where($qb->expr()->in('m', $sub->getDQL()))
         ->andWhere('m.receiver = :receiver_id');

$query = $qb->getQuery();
return $query->getResult();
Wilt
  • 41,477
  • 12
  • 152
  • 203
  • Oh thank you fo putting as much effort in understanding my SQL query even if it contains the typo you mentionned in comment. I appreciate your time so much. I will be back to read this carefully (because I just woke up) and combine it with http://stackoverflow.com/questions/27007090/inner-join-results-from-select-statement-using-doctrine-querybuilder/27051815#comment55657922_27051815 since this is also a brilliant idea I discovered last night before sleeping. Good vibes. – Adib Aroui Nov 27 '15 at 10:28
  • Hi sir, I ended up creating a new query with a simpler structure: `SELECT a.* FROM message a LEFT OUTER JOIN message b ON a.sender_id=b.sender_id and a.message_date < b.message_date WHERE b.id is null and a.receiver_id=1` Now I will be trying to implement your functions alongside with the documentation to find out the querybuilder way of things. – Adib Aroui Nov 27 '15 at 12:17
  • And here is the simpler and working final code I will be using: `$query= $this->repository->createQueryBuilder('m') ->leftJoin('MembersManagementBundle:Message', 't', 'WITH', 'm.messageDate < t.messageDate AND m.sender = t.sender') ->where('m.receiver = :ver_id and t.id is null') ->setParameter('ver_id', $receiver_id) ->getQuery();`. If you like the idea of getting rid of aggregate function in raw SQL and using the outer left join instead(which gives shorter SQL), you are free to edit the answer with the new code.Many thanks – Adib Aroui Nov 27 '15 at 12:28
  • @whitelettersinblankpapers Why do you need to `leftJoin` on `Message`? I don't think you need to do this because the fields `messageDate` AND `sender` are also properties of `m`? Or is `m` not a `MembersManagementBundle:Message` entity? – Wilt Nov 27 '15 at 12:56
  • It is. I just based my work on: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column/7745635#7745635. Honnesly, I am not an SQL guy and I am newly gettin familiar to these notions of joins. Tryin now to dig into this:http://stackoverflow.com/questions/565620/difference-between-join-and-inner-join – Adib Aroui Nov 27 '15 at 13:24
  • @whitelettersinblankpapers Try my query; it is simpler (without join) and should give you the same result... – Wilt Nov 27 '15 at 13:26
  • But Sir your query supposes that the date is an input to the query while my goal is to get the max_date (the latest message received by a specific user from any other senders) Please to take a look at this:http://stackoverflow.com/questions/33917534/select-distinct-values-based-on-a-column-with-maximum-date-value-from-second-col – Adib Aroui Nov 27 '15 at 13:28
  • What I mean Sir is that my code contains no variable `$date` to input in `setParameters` What I need is the maximum date from available records on database. I hope my understanding is not looking stupid. Thanks for the edit I am now trying your query. – Adib Aroui Nov 27 '15 at 13:34
  • @whitelettersinblankpapers I thought date was an input param. I updated my answer. This is probably more what you need... – Wilt Nov 27 '15 at 13:41
  • 1
    You know why I am late answering you because I cannot make it work and it is because of my limited experience with such stuff. Currently it is giving me `[Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string.`. I will be back sir when I able to continue my work on this. – Adib Aroui Nov 27 '15 at 14:30
  • This is not working sir. I tried again today because I needed to swap to your solution since mine always excludes one line from results, to no avail. – Adib Aroui Dec 01 '15 at 16:14
  • Man I gave up regarding this. I am now using :`$query = $em->createQuery( 'SELECT m FROM MembersManagementBundle:Message m WHERE m.messageDate IN ( SELECT max(t.messageDate) FROM MembersManagementBundle:Message t WHERE m.sender=t.sender AND t.receiver =:receiverId GROUP BY t.sender) AND m.receiver =:receiverId')->setParameter('receiverId',$receiver_id);`. I am confortable with DQL, and even this one is using IN with subquery, it at least lightened my headacke. Wrote it here for the sake of sharing with others. Thanks – Adib Aroui Dec 02 '15 at 22:13