Trouble converting CASE statement SQL to Cake Query

Hi y’all,

I have been trying to convert my SQL query into a cake ORM query but I seem to struggle with adding the case statements. When running the query below in mysql directly it works great but unfortunately I cannot get it right in Cake. Any change somebody can show me how to handle this case statement?

SELECT as 'discussion_id',
    COUNT( as 'messages amount'
FROM discussions
INNER JOIN subscriptions
ON = subscriptions.discussion_id 
INNER JOIN memberships
ON memberships.user_id = 'some_user_id' AND memberships.group_id = discussions.group_id
LEFT JOIN messages
ON messages.discussion_id = AND
    CASE WHEN subscriptions.last_seen IS NULL THEN messages.created > memberships.created
    ELSE messages.created > subscriptions.last_seen END
WHERE subscriptions.user_id = 'some_user_id

My current working starting point:
->where([‘Subscriptions.user_id’ => $user->id])
->leftJoinWith(‘Messages’, function(Query $q) {
return $q;

I really feel like im missing something very basic but I got stuck on a case statement like this:

        $q->newExpr()->('Messages.created > Memberships.created')
        'Messages.created > Subscriptions.last_seen'

You would be my hero if you could explain to me how I should handle this within cake :grinning:

You can use as is with the db connection. PDO instance example: Count and group by

use gt from the Expressions functions

        $q->newExpr()->gt($q->identifier('Messages.created'), $q->identifier('Memberships.created'))
        $q->newExpr()->gt($q->identifier('Messages.created'), $q->identifier('Subscriptions.last_seen'))

The first params of the addCase is the conditions, and the second param are the results + 1 item for the else.

Thank you so much!

I never knew $q->identifier() was an option, works like a charm!