php - 1064 你在 Doctrine 中的 SQL 语法有错误。为什么？

Question

我不明白，为什么我的浏览器返回这个错误，为什么我的 sql 不工作。我尝试解决了这个问题，但我不知道该怎么做。

private function getFromDb()
{
    $queryBuilder = $this->connection->createQueryBuilder();
    $query = $queryBuilder
        ->select('art1.articleID', 'art1.valueID')
        ->from('s_filter_articles', 'art1')
        ->innerJoin('art1', 's_filter_articles', 'art2', 'art1.articleID = art2.articleID')
        ->where('art1.valueID = 12499')
        ->andWhere('art2.valueID = 12500');

    return $query->execute()->fetchAll();
}

此代码返回此数组:

array:2 [▼
  0 => array:2 [▼
    "articleID" => "2225"
    "valueID" => "12499"
  ]
  1 => array:2 [▼
    "articleID" => "2250"
    "valueID" => "12499"
  ]
]

...我想删除 MySQL 中的所有结果。所以我写了这段代码:

public function removeFromDb()
{
    $result = $this->getFromDb();


    foreach ($result as $option) {

        $queryBuilder = $this->connection->createQueryBuilder();
        $query = $queryBuilder
            ->delete('s_filter_articles', 's')
            ->where('articleID = :articleID')
            ->andWhere('valueID = :valueID')
            ->setParameter(':articleID', $option['articleID'])
            ->setParameter(':valueID', $option['valueID']);

        return $query->execute();
    }
}

但是我的浏览器返回这个错误:

> 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 's WHERE (articleID = '2225') AND (valueID =
> '12499')' at line 1 in
> /var/www/html/strefatenisa.local/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php
> on line 131

> Doctrine\DBAL\DBALException: An exception occurred while executing
> 'DELETE FROM s_filter_articles s WHERE (articleID = ?) AND (valueID =
> ?)' with params ["2225", "12499"]: 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 's WHERE (articleID = '2225') AND (valueID =
> '12499')' at line 1 in
> /var/www/html/strefatenisa.local/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php
> on line 131

编辑:如果我在我的 mysql 中进行正常查询，一切正常。

SELECT art1.articleID, art1.valueID 
        FROM s_filter_articles as art1
    INNER JOIN s_filter_articles as art2 ON  art1.articleID = art2.articleID
    WHERE art1.valueID = 12499
    AND art2.valueID = 12500

DELETE FROM `s_filter_articles` WHERE `articleID` = 2225 AND `valueID` = 12499

Answer 1

使用以下代码更新您的函数 removeFromDb():

public function removeFromDb()
{
    $result = $this->getFromDb();

    foreach($result as $option){
        $queryBuilder = $this->connection->createQueryBuilder();
        $queryBuilder->delete('s_filter_articles', 's')
                     ->where('articleID = :articleID')
                     ->andWhere('valueID = :valueID')
                     ->setParameter(':articleID', $option['articleID'])
                     ->setParameter(':valueID', $option['valueID']);

        $query = $queryBuilder->getQuery();
        $query->execute();
    }
}

createQueryBuilder 函数返回 QueryBuilder 类的一个新实例，它没有任何 execute() 方法的声明。这就是为什么我添加了 getQuery() 方法来返回 Query 类，该类具有 execute() 方法。我删除的另一件事是从 foreach 内部返回。我希望这对你有用，如果没有让我知道，我可以帮助你。