ZF2更改我的表达式并返回错误（Postgre，Distinct）(ZF2 changing my expression and returning error (Postgre, Distinct)

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.