Database query expression notExists() not working properly CakepPHP 3.6


#1

Hi everyone!
I’m having troubles using the Database Expression notExists() in my UsersTable. Users hasMany UserRoles.
This is my code:

  • UsersTable.php
    public function findUsersPaginate (\Cake\ORM\Query $query, array $options)
    {
        $query
            ->select(['Users.id', 'Users.name'])
            ->where(function (QueryExpression $exp, Query $q) {
                $query = $this->UserRoles->find()->where(['UserRoles.user_id =' => 'Users.id', 'UserRoles.role_id IN' => [1,7]]);
                return $exp->notExists($query);
            });
        return $query;
    }

I’ve get it done succesfully using a SQL query directly on my DB (MySQL), using the following query:

SELECT
	id, name
FROM
	users
WHERE
	NOT EXISTS (
		SELECT
			*
		FROM
			user_roles
		WHERE
			 user_roles.user_id = users.id AND user_roles.role_id IN (1,7)
	);

And this is the result:

|id|    |name|
2       MARIA
22     JOSE
23     SOFIA

The response that I get from the debug kit is this:

SELECT 
  Users.id AS `Users__id`, 
  Users.name AS `Users__name` 
FROM 
  users Users 
WHERE 
  NOT EXISTS (
SELECT 
  UserRoles.id AS `UserRoles__id`, 
  UserRoles.user_id AS `UserRoles__user_id`, 
  UserRoles.role_id AS `UserRoles__role_id`, 
  UserRoles.active AS `UserRoles__active`, 
  UserRoles.created_by AS `UserRoles__created_by`, 
  UserRoles.modified_by AS `UserRoles__modified_by`, 
  UserRoles.created AS `UserRoles__created`, 
  UserRoles.modified AS `UserRoles__modified` 
FROM 
  user_roles UserRoles 
WHERE 
  (
    UserRoles.user_id = 0 
    AND UserRoles.role_id in (1, 7)
  )
  )

Note that in the second WHERE clause, the first condition is ‘UserRoles.user_id = 0’ when it should be ‘users.id = user_roles.user_id’;

Thanks in advance.