Trouble converting CASE statement SQL to Cake Query

Need Help
1

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
    discussions.id as 'discussion_id',
    COUNT(messages.id) as 'messages amount'
FROM discussions
INNER JOIN subscriptions
ON discussions.id = 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 = discussions.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
GROUP BY discussions.id

My current working starting point:
$this->Discussions->find()
->where([‘Subscriptions.user_id’ => $user->id])
->innerJoinWith(‘Subscriptions’)
->innerJoinWith(‘Memberships’)
->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()
->addCase(
    [ 
        $q->newExpr()->isNotNull('Subscriptions.last_seen'),
        $q->newExpr()->('Messages.created > Memberships.created')
    ],
    [
        true, 
        'Messages.created > Subscriptions.last_seen'
    ]
);

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

2

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

3

use gt from the Expressions functions

$q->newExpr()
->addCase(
    [ 
        $q->newExpr()->isNotNull('Subscriptions.last_seen'),
    ],
    [
        $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.

4

Thank you so much!

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