CakePHP 语法错误或访问冲突

Question

我有以下关系用户(ID，姓名，电子邮件，...)电影(id，名字，...)users_watchlists(id, user_id, movie_id)

它是 HABTM 关系。

错误

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

用户模型

    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' => ''
        )
  );

电影模特

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' => ''
    )
);

不确定为什么要从 UsersWatchlist 中获取名称等

Answer 1

Not sure why its trying to fetch name etc from UsersWatchlist

因为您有一个名为 UsersWatchlist 的关系数据表模型，并且您将 User 和 Movie 之间的关系命名为相同的名称。

尝试更新这些名称:

用户模型

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

电影模特

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

这些键在 SQL 查询中用作别名，因此在这种情况下会失败。

顺便说一下，如果你使用 users_watchlists 数据表来维护用户和电影之间的链接(例如，如果你不存储任何关于关系的属性)，你可能只是得到摆脱 UsersWatchlist 模型。