php - Doctrine2，ManyToMany 关系 => SQLSTATE[42000] : Syntax error or access violation

Question

我在我的项目中使用 Doctrine2，并且定义了以下实体:

namespace Model;

/**
 * @Entity()
 * @Table(name="author")
 **/
class Author {

    /**
     * @Id
     * @GeneratedValue
     * @Column(type="integer")
     **/
    private $id;

    /** @Column(type="string") **/
    private $firstName;

    /** @Column(type="string") **/
    private $lastName;

    /** @Column(type="string", nullable=true) **/
    private $titleBefore;

    /** @Column(type="string", nullable=true) **/
    private $titleAfter;

    /** @ManyToMany(targetEntity="Article", mappedBy="authors") **/
    private $articles;

    public function __construct() {
        $this->articles = new \Doctrine\Common\Collections\ArrayCollection();
    }

    public function getId() {
        return $this->id;
    }

    public function getTitleBefore() {
        return $this->titleBefore;
    }

    public function setTitleBefore($titleBefore) {
        $this->titleBefore = $titleBefore;
    }

    public function getTitleAfter() {
        return $this->titleAfter;
    }

    public function setTitleAfter($titleAfter) {
        $this->titleAfter = $titleAfter;
    }

    public function getLastName() {
        return $this->lastName;
    }

    public function setLastName($lastName) {
        $this->lastName = $lastName;
    }

    public function getFirstName() {
        return $this->firstName;
    }

    public function setFirstName($firstName) {
        $this->firstName = $firstName;
    }

    public function getArticles() {
        return $this->articles;
    }

    public function addArticle($article) {
        $this->articles->add($article);
    }
}

和

namespace Model;

/**
 * @Entity()
 * @Table(name="article")
 **/
class Article {

    /**
     * @Id
     * @GeneratedValue
     * @Column(type="integer")
     **/
    private $id;

    /** @Column(type="string") **/
    private $name;

    /** @OneToOne(targetEntity="Publication") **/
    private $publication;

    /**
     * @ManyToMany(targetEntity="Author", mappedBy="articles")
     */
    private $authors;

    public function __construct() {
        $this->authors = new \Doctrine\Common\Collections\ArrayCollection();
    }

    public function getId() {
        return $this->id;
    }

    public function getName() {
        return $this->name;
    }

    public function setName($name) {
        $this->name = $name;
    }

    public function getPublication() {
        return $this->publication;
    }

    public function setPublication($publication) {
        $this->publication = $publication;
    }

    public function getAuthors() {
        return $this->authors;
    }

    public function addAuthor($author) {
        $this->authors->add($author);
        $author->addArticle($this);
    }

    public function setAuthors($authors) {
        $this->authors = $authors;
    }
}

看起来，关系作者<->文章效果很好。虽然我遇到了问题。当我尝试像这样访问 Smarty 模板中的作者时:{foreach from=$article->getAuthors() item=author}，抛出以下异常:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON' at line 1 in /code/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:104 Stack trace:
#0 /code/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(104): PDO->query('SELECT t0.id AS...')
#1 /code/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(852): Doctrine\DBAL\Driver\PDOConnection->query('SELECT t0.id AS...')
#2 /code/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(1030): Doctrine\DBAL\Connection->executeQuery('SELECT t0.id AS...', Array, Array)
#3 /code/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(954): Doctrine\ORM\Persisters\Entity\BasicEntityPersister->getManyToManyStatement(Array, Object(Model\Article))
#4 /code/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(2839): Doctrine\ORM\Persiste in /code/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php on line 90

我已经花了一天的时间来解决这个问题，试图找出问题所在。我怀疑我可能使用了 MySQL 保留字，但我在变量中没有找到任何保留字。

我终于设法获取完整的查询日志，直到出现异常。看起来最有趣的查询不存在

mysqld, Version: 5.7.20 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2017-11-25T23:46:52.327597Z        10 Connect   user@articlerepository_php_1.articlerepository_default on article_repository using TCP/IP
2017-11-25T23:46:52.334083Z        10 Query     SELECT t0.id AS id_1, t0.firstName AS firstName_2, t0.lastName AS lastName_3, t0.titleBefore AS titleBefore_4, t0.titleAfter AS titleAfter_5 FROM author t0
2017-11-25T23:46:52.342077Z        10 Query     SELECT t0.id AS id_1, t0.name AS name_2, t0.publication_id AS publication_id_3 FROM article t0
2017-11-25T23:46:52.348058Z        10 Quit

Answer 1

看来我的 ManyToMany 关系不匹配。以下是我解决此问题所采取的步骤:

1. 我通过运行 bin/doctrine orm:schema-tool:drop --force 删除了当前架构
2. 通过多次运行 bin/doctrine orm:validate 验证当前实体并对实体进行更改，直到顺利完成为止
3. 生成新架构:bin/doctrine orm:schema-tool:update --force --dump-sql

正确的关系如下所示:

class Author {
    /**
     * @ManyToMany(targetEntity="Article", inversedBy="authors")
     * @JoinTable(name="authors_articles")
    **/
    private $articles;
}

class Article {
    /**
     * @ManyToMany(targetEntity="Author", mappedBy="articles")
     */
    private $authors;
}

`