Zend\Db:从子查询中选择 [英] Zend\Db: Select from subquery
问题描述
我正在将一个应用程序从 ZF1 移植到 ZF2,作为其中的一部分,我必须重写我们的数据库映射器.
I'm porting an application from ZF1 to ZF2 and as part of that I have to rewrite our database mappers.
我正在为这个 SQL 语句苦苦挣扎:
I'm struggling with this SQL statement:
SELECT full_name, GROUP_CONCAT(value)
FROM (
SELECT full_name, value
FROM my_table
ORDER BY id DESC
) as subtable
GROUP BY full_name
ORDER BY full_name DESC;
我试图解决的根本问题是我需要在运行 GROUP_CONCAT
之前对子查询的结果进行排序,并且我需要它同时适用于 MySQL 和 Sqlite.在 MySQL 中,我可以简单地在 GROUP_CONCAT
函数中指定顺序,但这在 Sqlite 中是不可能的,所以我需要子查询才能与 MySQL 和 Sqlite 兼容.
The underlying problem I'm trying to solve is that I need to order the results of the sub query before running GROUP_CONCAT
and I need it to work for both MySQL and Sqlite. In MySQL I could simply specify the order within the GROUP_CONCAT
function, but this is not possible with Sqlite so I need the sub query for it to be compatible with both MySQL and Sqlite.
在 ZF1 中我可以做到:
In ZF1 I could do:
$fromSql = $db->select()
->from('my_table', array('full_name', 'value'))
->order('id DESC');
$sql = $db->select()
->from(array(
'subtable' => new Zend_Db_Expr('(' . $fromSql . ')')
), array(
'full_name' => 'full_name',
'value' => new Zend_Db_Expr('GROUP_CONCAT(subtable.value)'),
)
)
->group('full_name')
->order('full_name DESC');
但是,ZF2 似乎无法在 from 子句中使用子查询.有没有办法解决这个问题?
However, using a sub query in the from clause doesn't seem possible with ZF2. Is there some work around for this?
推荐答案
EDIT:实际上,我现在发现我的查询存在缺陷.它不会在 MySQL 中按预期工作,这意味着我仍然必须编写专门的查询.请参阅GROUP_CONCAT 更改 GROUP BY 顺序
EDIT: Actually, I now see that my query was flawed. It won't work as expected with MySQL, which means I still have to write specialized queries. See GROUP_CONCAT change GROUP BY order
经过Zend\Db\Sql\Select
的代码后,我发现了以下几行:
After going through the code of Zend\Db\Sql\Select
I found these lines:
if ($table instanceof Select) {
$table = '(' . $this->processSubselect($table, $platform, $driver, $parameterContainer) . ')';
} else {
$table = $platform->quoteIdentifier($table);
}
所以答案其实很简单,我所要做的就是向 from()
提供一个 Zend\Db\Sql\Select
对象,而不用包装它在 Zend\Db\Sql\Expression
中,就像我以前使用 ZF1 一样.
So the answer is actually quite simple, all I had to do was to provide a Zend\Db\Sql\Select
object to from()
, without wrapping it in a Zend\Db\Sql\Expression
like I used to with ZF1.
代码示例:
$adapter = $this->getAdapter(); // Returns Zend\Db\Adapter\Adapter
$sql = new Zend\Db\Sql\Sql($adapter);
$from = $sql->select()
->from(static::$table)
->columns(array(
'full_name',
'value',
))
->order('id DESC');
$select = $sql->select()
->from(array(
'subtable' => $from,
))
->columns(array(
'full_name' => 'full_name',
'value' => new Expression('GROUP_CONCAT(value)'),
))
->group('full_name')
->order('full_name DESC');
$selectString = $sql->getSqlStringForSqlObject($select);
$resultSet = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
return $resultSet->toArray();
这篇关于Zend\Db:从子查询中选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!