Paginator not working with custom finder

Need Help
1

I have 3 models, Jobs, Users, and UserTypes with the following relationships:

Users hasMany UserTypes
UserTypes hasMany Users

I want Jobs to belong to a User, but only when that user has a specific UserType (“Account Manager”).

In the JobsTable.php I have:

$this->belongsTo('AccountManagers', [
    'className' => 'Users',
    'foreignKey' => 'account_manager_id',
    'joinType' => 'LEFT',
    'finder' => 'accountManagers'
]);

In UsersTable.php:

public function findAccountManagers(Query $query, array $options)
{
    return $query->innerJoinWith('UserTypes', function ($q) {
        return $q->where([
            'UserTypes.name' => 'Account Manager'
        ]);
    })
}

This works fine when adding Jobs, etc. Only users with that UserType are presented in the drop down. However when using a Paginator for the jobs index page, I get a generic “Database Error”:

“Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘UserTypes.name’ in ‘on clause’”

The code for paginating is from cake bake:

public function index()
{
    $this->paginate = [
        'contain' => ['AccountManagers']
    ];

    $jobs = $this->paginate($this->Jobs);

    $this->set(compact('jobs'));
    $this->set('_serialize', ['jobs']);
}

Not sure where I’m going wrong since the relationship seems to be set up correctly. but I’m not sure why the join with the UserTypes table is failing here.

2

Hi,

I believe it’s necessary to inform the UserTypes model in paginate. Thus:

$this->paginate = [
‘contain’ => [‘AccountManagers’ => [‘UserTypes’]]
];

3

I get the exact same error message with that code.

4

Could you display the schema of the three tables involved?

5

Here are the schemas for Users (users), UserTypes (user_types), and Jobs (jobs).

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `username` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `user_types` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(30) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `jobs` (
  `id` int(10) UNSIGNED NOT NULL,
  `jobcode` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `client_id` int(10) UNSIGNED NOT NULL,
  `account_manager_id` int(10) UNSIGNED NOT NULL,
  `job_status_id` int(10) UNSIGNED NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `days` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The schema for the Users / UserTypes join table is:

CREATE TABLE `users_user_types` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_type_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
6

Hi,

Have you tried using matching() instead of innerJoinWith() ?

7

I have, yes. That was one of the first things I tried. I also tried adding a contain() to the query as well but to no avail.

8

Could you put here the debug($jobs) results?

9

Not sure how. The error seems to be triggered on the $this->paginate() function call, so the error is being thrown before the application reaches the debug() call.

Here’s the stack trace if that helps: http://pastebin.com/SFTZb9R8

Oh, and the error itself is “Database Error: PDOException”

10

Hello, I’m sorry not to have given continuity in helping you.

You’ve solved the problem?

11

I think you should try with like that
$this->paginate = [ 'contain' => ['AccountManagers'], 'finder' => 'accountManagers' ];

12

Thanks for all replies.

@vqdat169: That does not work unfortunately.

After speaking to several people in the Slack chat, the prevailing consensus was this was actually a bug, and I have reported it to the devs: https://github.com/cakephp/cakephp/issues/8892