我正在使用 ZF2 DB 适配器,并在查询中收到以下错误:
找不到列:1054 未知列"产品"
"
但"产品"不是列,而是一种价值,那又是何尝呢?
这是我构建查询的方式:
$select = $this->getGateway()->getSql()->select();
$select->join('keywordlink', 'keywordlink_ref_type = "product" AND keywordlink_ref_id = product_id', ['keyword_count' => new Expression('COUNT(keyword_id)')], Select::JOIN_LEFT);
$select->where(['product_deleted IS NULL']);
$select->group(['product_id']);
顺便说一句,字段keywordlink_ref_id是 mysql 数据库中的枚举。
当我自己编写SQL时,它可以工作:
SELECT
product.*, COUNT(DISTINCT keywordlink_keyword_id) AS keyword_count
FROM
adcheck.product
LEFT JOIN
keywordlink
ON
keywordlink_ref_type = "product" AND keywordlink_ref_id = product_id
WHERE
product_deleted IS NULL
GROUP BY
product_id
谢谢
好的,我不知道为什么它不像以前那样工作,但是当我移动
keywordlink_ref_type = "产品"
进入 where 条件:
SELECT
product.*, COUNT(DISTINCT keywordlink_keyword_id) AS keyword_count
FROM
adcheck.product
LEFT JOIN
keywordlink
ON
keywordlink_ref_id = product_id
WHERE
keywordlink_ref_type = "product" AND product_deleted IS NULL
GROUP BY
product_id
$sql = $this->getGateway()->getSql()->select();
$sql->from('product')->join('keywordlink', "keywordlink.ref_id" = "product.id", LEFT);
$sql->where->equalTo( 'keywordlink.column' = 'product.column');
$sql->where->equalTo('product.deleted', NULL);
$sql->group('product.product_id');
找不到该列,因为查询中未指向表。解决方案是这样的,但对于 ZF2 查询生成器的正确语法,您还应该查看此 zend2 链接:http://framework.zend.com/manual/1.12/en/zend.db.select.html