ZF2更改我的表达式并返回错误(Postgre,Distinct)(ZF2 changing my expression and returning error (Postgre, Distinct))
我必须创建一个SELECT,我将通过列idcontrato区分行。 我用SQL做得很好......
SELECT DISTINCT ON (idcontrato) * FROM cad_emprestimo WHERE numerobeneficio = '1135346515';但是当我尝试用ZF2做同样的事情时:
$emprestimos = (new EmprestimoTable())->select(function(Select $select) use($cliente) { $select->columns([new Expression('DISTINCT ON (idcontrato) *')]); $select->where->equalTo('numerobeneficio', $cliente->getBeneficio()->getNumero()); });我收到以下错误:
SQLSTATE[42601]: Syntax error: 7 ERRO: erro de sintaxe em ou próximo a "AS" LINE 1: SELECT DISTINCT ON (idcontrato) * AS Expression1 FROM "cad_e... ^这个'AS Expression1'由ZF2添加......我不知道如何删除它。
I have to make a SELECT where I will distinct the rows by the column idcontrato. I did it well with with SQL...
SELECT DISTINCT ON (idcontrato) * FROM cad_emprestimo WHERE numerobeneficio = '1135346515';But when I try to do the same with ZF2:
$emprestimos = (new EmprestimoTable())->select(function(Select $select) use($cliente) { $select->columns([new Expression('DISTINCT ON (idcontrato) *')]); $select->where->equalTo('numerobeneficio', $cliente->getBeneficio()->getNumero()); });I got the following error:
SQLSTATE[42601]: Syntax error: 7 ERRO: erro de sintaxe em ou próximo a "AS" LINE 1: SELECT DISTINCT ON (idcontrato) * AS Expression1 FROM "cad_e... ^This 'AS Expression1' added by ZF2... I don't know how to remove it.
最满意答案
试试下面的代码
$emprestimos = (new EmprestimoTable())->select(function(Select $select) use($cliente) { $select->columns([new Expression('DISTINCT(idcontrato) AS idcontrato'), '*']); $select->where->equalTo('numerobeneficio', $cliente->getBeneficio()->getNumero()); });The obvious solution I've found is to run the raw SQL:
$dbAdapter = GlobalAdapterFeature::getStaticAdapter(); $numBeneficio = $cliente->getBeneficio()->getNumero(); $sqlEmprestimos = "SELECT DISTINCT ON (idcontrato) * FROM cad_emprestimo WHERE numerobeneficio = '".$numBeneficio."'; "; $dataSource = $dbAdapter->createStatement($sqlEmprestimos)->execute(); $emprestimos = (new \Zend\Db\ResultSet\ResultSet())->initialize($dataSource);Anyway, I'm not happy with this, this code is ugly as hell.
更多推荐
发布评论