CakePHP Syntax error or access violation

Question

I have following relationships Users(id, name, email, ...) movies(id, name, ...) users_watchlists(id, user_id, movie_id)

Its a HABTM relationship.

Error

Error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'UsersWatchlist'

SQL Query: SELECT UsersWatchlist.id, UsersWatchlist.first_name, UsersWatchlist.last_name, UsersWatchlist.display_image, UsersWatchlist.email, UsersWatchlist.password, UsersWatchlist.birthday, UsersWatchlist.gender, UsersWatchlist.group_id, UsersWatchlist.banned, UsersWatchlist.created, UsersWatchlist.modified, UsersWatchlist.id, UsersWatchlist.first_name, UsersWatchlist.last_name, UsersWatchlist.display_image, UsersWatchlist.email, UsersWatchlist.password, UsersWatchlist.birthday, UsersWatchlist.gender, UsersWatchlist.group_id, UsersWatchlist.banned, UsersWatchlist.created, UsersWatchlist.modified FROM reelstubs.users AS UsersWatchlist JOIN reelstubs.users AS UsersWatchlist ON (UsersWatchlist.movie_id = 4 AND UsersWatchlist.user_id = UsersWatchlist.id)

Notice: If you want to customize this error message, create app\View\Errors\pdo_error.ctp

Users Model

    public $hasAndBelongsToMany = array(
       'UsersWatchlist' => array(
            'className' => 'Movie',
            'joinTable' => 'users_watchlists',
            'foreignKey' => 'user_id',
            'associationForeignKey' => 'movie_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
            'deleteQuery' => '',
            'insertQuery' => ''
        )
  );

Movie Model

public $hasAndBelongsToMany = array(
'UsersWatchlist' => array(
        'className' => 'User',
        'joinTable' => 'users_watchlists',
        'foreignKey' => 'movie_id',
        'associationForeignKey' => 'user_id',
        'unique' => 'keepExisting',
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
        'deleteQuery' => '',
        'insertQuery' => ''
    )
);

UsersWatchlist

public $belongsTo = array(
    'User' => array(
        'className' => 'User',
        'foreignKey' => 'user_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),
    'Movie' => array(
        'className' => 'Movie',
        'foreignKey' => 'movie_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )
);

Not sure why its trying to fetch name etc from UsersWatchlist

Answer 1

Not sure why its trying to fetch name etc from UsersWatchlist

Because you have a model for the relation datatable called UsersWatchlist and you named the relations between User and Movie with the same name.

Try to update those names:

User Model

public $hasAndBelongsToMany = array(
   'Movie' => array(
   ...

Movie Model

public $hasAndBelongsToMany = array(
   'User' => array(
   ...

These keys are used as aliases in the SQL query, making it fail in this case.

By the way, if you use the users_watchlists datatable only to maintain a link between Users and Movies (for example if you don't store any properties about the relation), you can probably just get rid of the UsersWatchlist model.