Query builder - case statements

Hi!

I’m wondering if the following can be achieved with Query Builder or I have to use plain SQL for this one.

Version: CakePHP 3.5.1

What I want to achieve:
I have a DB table Games which are related to table Ratings and Ratings table is related to Users table. Users can have roles: admin, user. The admins and the users can rate a game and what I want is to calculate the average of the ratings separately for users with admin & user roles.

Plain SQL which works:

SELECT Games.id,
(AVG((CASE WHEN Users.role = 'admin' THEN Ratings.rating ELSE null END))) AS `average_by_admins`,
(AVG((CASE WHEN Users.role = 'user' THEN Ratings.rating ELSE null END))) AS `average_by_users` 
FROM games Games 
INNER JOIN ratings Ratings ON (Ratings.approved = 1 AND Games.id = (Ratings.game_id)) 
INNER JOIN users Users ON (Users.id = (Ratings.user_id)) 
WHERE Games.adventuregame = 1
GROUP BY Games.id
ORDER BY Games.id asc;

plainSQL-works

Query builder in Cake:

$query = $this->Games
                ->find()
                ->contain([
                    'Platforms'
                ])
                ->where(['Games.adventuregame' => $isAdventuregame])
                ->order(['Games.created' => 'desc'])
                ->innerJoinWith('Ratings', function ($q) {
                    return $q
                        ->where(['Ratings.approved' => true])
                        ->innerJoinWith('Users');
                });

            $adminRatings = $query->newExpr()
                ->addCase(
                    [
                        $query->newExpr()->add(['Users.role' => 'admin'])
                    ],
                    [
                        'Ratings.rating',
                        null
                    ]
                );
            $userRatings = $query->newExpr()
                ->addCase(
                    [
                        $query->newExpr()->add(['Users.role' => 'user'])
                    ],
                    [
                        'Ratings.rating',
                        null
                    ]
                );

            $query
                ->select([
                    'Games.id', 'Games.title',
                    'average_by_admins' => $query->func()->avg($adminRatings),
                    'average_by_users' => $query->func()->avg($userRatings),
                ])
                ->group('Games.id');

And this is what I get with dd($query):

'sql' => 'SELECT Games.id AS `Games__id`, Games.title AS `Games__title`, (AVG((CASE WHEN Users.role = :c0 THEN :param1 ELSE :param2 END))) AS `average_by_admins`, (AVG((CASE WHEN Users.role = :c3 THEN :param4 ELSE :param5 END))) AS `average_by_users` FROM games Games INNER JOIN ratings Ratings ON (Ratings.approved = :c6 AND Games.id = (Ratings.game_id)) INNER JOIN users Users ON Users.id = (Ratings.user_id) WHERE Games.adventuregame = :c7 GROUP BY Games.id  ORDER BY Games.created desc',
	'params' => [
		':c0' => [
			'value' => 'admin',
			'type' => null,
			'placeholder' => 'c0'
		],
		':param1' => [
			'value' => 'Ratings.rating',
			'type' => null,
			'placeholder' => 'param1'
		],
		':param2' => [
			'value' => null,
			'type' => null,
			'placeholder' => 'param2'
		],
		':c3' => [
			'value' => 'user',
			'type' => null,
			'placeholder' => 'c3'
		],
		':param4' => [
			'value' => 'Ratings.rating',
			'type' => null,
			'placeholder' => 'param4'
		],
		':param5' => [
			'value' => null,
			'type' => null,
			'placeholder' => 'param5'
		],
		':c6' => [
			'value' => true,
			'type' => 'boolean',
			'placeholder' => 'c6'
		],
		':c7' => [
			'value' => true,
			'type' => 'boolean',
			'placeholder' => 'c7'
		]
	],

With the above query builder code I always get 0 values for the average_by_admins & average_by_users fields. My opinion is, that the problem will be with these lines:

[
    'Ratings.rating',
    null
]

‘Ratings.rating’ - Cake assumes this is a text field and not a placeholder for the Ratings table’s rating field which contains a float number.

Is there any way to force Cake to use real float numbers there from the Ratings table?

Thanks for your help.

Can you group by GROUP BY Games.id, Users.role
Then you could just use AVG(IFNULL(Ratings.rating, 0))?

Thanks for the answer, but I already managed to solve this. I realized that I can put into select more complex sql too, like this:

$this->Games->find()->select([
  'Games.id',
  'average_by_admins' => 'AVG(CASE WHEN Users.role = \'admin\' THEN Ratings.rating ELSE null END)',
  'average_by_users' => 'AVG(CASE WHEN Users.role = \'user\' THEN Ratings.rating ELSE null END)'
])...

Now it works perfectly.