Query builder - case statements

Need Help
1

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.

2

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

3

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.