CakePHP - creating subquery

Question

My used CakePHP Version is 3.x i need cakePHP to create this sql statement:

    SELECT messages.id, message, receiver_id, sender_id, conversation_id, users.id, users.username FROM 
(
    SELECT messages.id, message, receiver_id, sender_id, conversation_id
    FROM messages as messages
    ORDER BY created DESC
) AS messages
left join users on (users.id = sender_id)
WHERE (sender_id = 13 or receiver_id =13)
GROUP BY conversation_id;

I used the queryBuilder but i do not really understand what im doing ;)

This is my cake code:

$subquery = $messages->find()
        ->order(['Messages.created' => 'DESC']);
    debug($neededMessages = $messages->find('all')
        ->select()
        ->group('Messages.conversation_id')
        ->contain(['Users'])
        ->where(['OR' => ['sender_id' => $userId, ['receiver_id' => $userId]]]));
    $neededMessages->execute();

I think its only a little mistake in there. Cakes debug output is this: SELECT (SELECT Messages.id AS Messages__id, Messages.subject AS Messages__subject, Messages.message AS Messages__message, Messages.created AS Messages__created, Messages.conversation_id AS Messages__conversation_id, Messages.is_new AS Messages__is_new, Messages.receiver_id AS Messages__receiver_id, Messages.sender_id AS Messages__sender_id FROM messages Messages ORDER BY Messages.created DESC) FROM messages Messages INNER JOIN users Users ON Users.id = (Messages.sender_id) WHERE (sender_id = :c0 OR receiver_id = :c1) GROUP BY Messages.conversation_id

im missing the columns in the first select statement. Can anyone help on this issue? grretings maik...

You should add information about CakePHP version and database schema for clearly. — Do Nhu Vy
sorry, cakePHP 3.x I added this information to my post :) thanks — Maik Hagenbruch
This is just an example that doesn't maky any sense right? Cause the subquery doesn't do anything special that make its use necessary. Anyways, I guess the problem will finally be that you cannot easily turn of field aliasing, which would be necessary in the subquery, because otherwise you'd end up with fields like Messages.id AS Messages__id which will cause the outer select of Messages.id to fail. Maybe ask ORM wizard and part time leprechaun tamer jose_zap on IRC if he knows a workaround. — ndm
Hey, jose_zap here. I was writing an answer but realized that your query does not make much sense. Is there something missing in what you want to do? It seems like you just wanted to write select * from messages, join users on (...) where ... GROUP BY conversation_id ORDER BY created DESC — José Lorenzo Rodríguez

Maik Hagenbruch Maik Hagenbruch · Accepted Answer · 2015-01-06T14:19:12

So, i have my results how i need. Only thing that bothers me is that i now have arrays and no objects. Here is my code:

$conn = ConnectionManager::get('default'); $stmt = $conn->query('SELECT max(m.id) as message_id, message, receiver_id, sender_id, m.conversation_id, users.id as user_id, users.username from (select id, message, receiver_id, sender_id, conversation_id from messages order by created desc) as m left join users as users on (m.sender_id = users.id) group by m.conversation_id'); $messages = $stmt->fetchAll('assoc'); $this->set('messages', $messages);

It would be great to read from other cake users how they solve this 'problem'...

CakePHP - creating subquery

1 Answers