为了计算价格,我在symfony上创建了一个自定义仓库查询。以下是查询:
$sql = " SELECT
SUM(sub.totalPriceWT) AS totalPrice,
c.label
FROM (
SELECT
v.id,
(
SELECT SUM(priceWT)
FROM JSON_TABLE(
JSON_UNQUOTE(JSON_EXTRACT(v.dac, '$.bdc.service.operator.entries[*].priceWT')),
'$[*]' COLUMNS(priceWT DECIMAL(10, 2) PATH '$')
) der
) AS totalPriceWT
FROM valid v
JOIN sheet s ON v.sheet_id = s.id
JOIN step st ON s.step_id = st.id
WHERE JSON_UNQUOTE(JSON_EXTRACT(v.dac, '$.type')) = 'bdc'
AND (
(YEAR(s.sent_date) = :currentYear AND CAST(JSON_EXTRACT(v.dac, '$.anticipated') AS SIGNED) = :anticipated2)
OR
(YEAR(s.sent_date) = :previousYear AND CAST(JSON_EXTRACT(v.dac, '$.anticipated') AS SIGNED) = :anticipated1)
)
AND s.perimeter_id = :perimeter
AND JSON_UNQUOTE(JSON_EXTRACT(v.dac, '$.bdc.service.operator.entries')) IS NOT NULL
AND st.slug != 'rejetee'
) AS sub
JOIN valid v2 ON sub.id = v2.id
JOIN sheet s2 ON v2.sheet_id = s2.id
JOIN cost_center c ON s2.cost_center_id = c.id
GROUP BY c.id
";
$conn = $this->manager->getConnection();
$stmt = $conn->prepare($sql);
$stmt->bindValue('perimeter', $perimeter->getId());
$stmt->bindValue('currentYear', $year);
$stmt->bindValue('previousYear', $year - 1);
$stmt->bindValue('anticipated1', 1);
$stmt->bindValue('anticipated2', 0);
$resultSet = $stmt->executeQuery();
$resultBDC = $resultSet->fetchAllAssociative();
问题是我得到这样的错误:
执行查询时发生异常:SQLSTATE[42000]:语法错误或访问冲突:1064您的SQL语法有错误;检查与您的MariaDB服务器版本对应的手册,以获得正确的语法,以使用near '(JSON_UNQUOTE(JSON_EXTRACT(v. js,'$.bdc.serv.' at line 9
我的语法没什么问题。我想这可能是一个服务器问题,因为奇怪的是,错误说**“检查对应于您的MariaDB服务器的手册”**,但我以为我在MySQL上。
编辑:我在MariaDB上。
我键入以下命令:
php bin/console debug:container --env-vars
php bin/console debug:config doctrine dbal
输出显示我确实在使用mySQL /pdo_mySQL。
我不明白有什么问题。
1条答案
按热度按时间qhhrdooz1#
从https://mariadb.com/kb/en/json_table/
JSON_TABLE在MariaDB 10.6.0中添加。
并不是所有的JSON函数都被植入到MariaDB中,有些只是后来才实现的。