Simple association not loading, jointable not even being queried

I have two models, Users and Roles. Users hasMany Roles and Roles belongsToMany Users. It appears that it’s not even trying to query the joinTable (which should be users_roles by default). Forcing the joinTable in the belongsToMany association doesn’t change a thing–the query never changes.

Unknown column ‘Roles.user_id’ in ‘where clause’

Users:

class UsersTable extends Table
{

public function initialize(array $config): void
{
	$this->hasMany('Roles');
}

}

Roles:

class RolesTable extends Table
{

public function initialize(array $config): void
{
	$this->belongsToMany('Users');
}

}

I think you are mixing things up here:

A hasMany association between Users and Roles would look like this:

+-----------+---------+
|   users   |  roles  |
+-----------+---------+
| id        | id      |
| username  | user_id |
| firstname | name    |
+-----------+---------+

But a belongsToMany association between Roles and Users would look like this:


+-----------+-------------+-------+
|   users   | roles_users | roles |
+-----------+-------------+-------+
| id        | role_id     | id    |
| username  | user_id     | name  |
| firstname |             |       |
+-----------+-------------+-------+

So you either have one of these 3 variants:

  1. User hasMany Roles AND Roles belongsTo Users (where the roles table has a user_id colum)
  2. Roles hasMany Users AND Users belongsTo Roles (where the users table has a role_id column)
  3. Roles belongsToMany Users AND Users belongsToMany Roles (junction table roles_users which connects the other 2 tables)

And another important info here: Just because you define the associations inside the model doesn’t mean that CakePHP automatically joins them inside your e.g. Controller.

If you want to join users and roles you would have to do something like

$this->Users->find()->contain('Roles');

or

$this->Users->find()->matching('Roles');

depending on which result you want

1 Like

You absolutely fixed the issue.

It was the user having “havesMany” versus “belongsToMany.” For some reason I didn’t think about the fact hasMany is basically a one-way relationship.

Thanks so much. That was wracking my brain for a few days, sadly.

I can also recommend you take a look at the youtube tutorial series :wink: