[FIXED] CakePHP: Login over 2 tables

Issue

I have a CakePHP website that has a new database structure and I want to reconfigure the login. First of all this is my error Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Users.email' in 'where clause'.

I have 2 Tables which are connected over the UUID field.

Users	Userdata
ID	ID
UUID	UUID
password	email

In the old website, the email and password were in the same table but not now. I have created the Table / Entitys with the plugin bake. Finally I have set $this->hasOne('Userdata'); on UsersTable.php and $this->hasOne('Users'); in the UserdataTable.php.

UserdataTable.php

<?php
namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class UserdataTable extends Table
{
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('userdata');
        $this->setDisplayField('ID');
        $this->setPrimaryKey('ID');
        $this->hasOne('Users');
    }
}

UserTable.php

<?php
namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class UsersTable extends Table
{
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('users');
        $this->setDisplayField('ID');
        $this->setPrimaryKey('ID');
        $this->hasOne('Userdata');
    }
}

This is in my AppController.php

        $this->loadComponent('Auth', [
            'authorize'=> 'Controller',
            'authenticate' => [
                'Form' => [
                    'fields' => [
                        'username' => 'email',
                        'password' => 'password'
                    ]
                ]
            ],
            'loginAction' => [
                'controller' => 'Users',
                'action' => 'login'
            ],
             //use isAuthorized in Controllers
            'authorize' => ['Controller'],
             // If unauthorized, return them to page they were just on
            'unauthorizedRedirect' => $this->referer()
        ]);

and then in my UsersController.php

    public function login()
    {
        if ($this->request->is('post')) {
            $user = $this->Auth->identify();
            if ($user) {
                $this->Auth->setUser($user);
                return $this->redirect($this->Auth->redirectUrl());
            }
            $this->Flash->error('Your username or password is incorrect.');
        }
    }

Im using CakePHP Version 3.6.15

Solution

What you need here is a custom finder so that you can modify the query that is used to look up the user record.

The most simple, and most fordward compatible way would probably be to simply contain the Userdata association in the query, and configure the form authenticator's username option to include the alias, eg Userdata.email instead of just email.

In your UsersTable class:

public function findWithUserdata(\Cake\ORM\Query $query, array $options): \Cake\ORM\Query
{
    return $query->contain('Userdata');
}

In your Auth component config:

'authenticate' => [
    'Form' => [
        'fields' => [
            'username' => 'Userdata.email', // << this one here
            'password' => 'password'
        ],
        'finder' => 'withUserData', // << and that one
    ]
],

This should then give you a lookup query along the lines of:

SELECT
    ...
FROM
    users Users
LEFT JOIN
    userdata Userdata ON Userdata.UUID = Users.UUID
WHERE
    Userdata.email = 'some@email.address'

See also

• Cookbook > Controllers > Components > AuthComponent > Configuring Authentication Handlers
• Cookbook > Controllers > Components > AuthComponent > Customizing Find Query
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Custom Finder Methods

Answered By - ndm