phunsanit
(Phunsanit)
1
I create grideview from ActiveDataProvider like
public function search($params)
{
$query = PartnersPeoplesPolicys::find();
$query->select('identity_type, identity_id, name, middle_name, surname');
$query->distinct(true);
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
but i found duplicate row, i found cause in generate query from yii like
SELECT TOP 20 * FROM (SELECT DISTINCT rowNum = ROW_NUMBER() over (ORDER BY (SELECT NULL)), * FROM [partners_peoples_policys]) sub
row number make difference column in my query and make DISTINCT command not work corrected.
how to remove it?
phunsanit
(Phunsanit)
2
now i found problem in vendor/yiisoft/yii2/db/mssql/QueryBuilder.php line 96
protected function oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
{
$orderBy = $this->buildOrderBy($orderBy);
if ($orderBy === '') {
// ROW_NUMBER() requires an ORDER BY clause
$orderBy = 'ORDER BY (SELECT NULL)';
}
$sql = preg_replace('/^([\s(])*SELECT(\s+DISTINCT)?(?!\s*TOP\s*\()/i', "\\1SELECT\\2 rowNum = ROW_NUMBER() over ($orderBy),", $sql);
if ($this->hasLimit($limit)) {
$sql = "SELECT TOP $limit * FROM ($sql) sub";
} else {
$sql = "SELECT * FROM ($sql) sub";
}
if ($this->hasOffset($offset)) {
$sql .= $this->separator . "WHERE rowNum > $offset";
}
return $sql;
}
i change dirty code
protected function oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
{
$orderBy = $this->buildOrderBy($orderBy);
if ($orderBy === '') {
// ROW_NUMBER() requires an ORDER BY clause
$orderBy = 'ORDER BY (SELECT NULL)';
}
/*
pitt phunsanit
phunsanit@hotmail.com
dirty fix code
wrong DISTINCT result
*/
if(strpos($sql, ' DISTINCT ') === false)
{
$sql = preg_replace('/^([\s(])*SELECT(\s+DISTINCT)?(?!\s*TOP\s*\()/i', "\\1SELECT\\2 rowNum = ROW_NUMBER() over ($orderBy),", $sql);
}
else
{
$sql = 'SELECT rowNum = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),* FROM ('.$sql.') source';
}
if ($this->hasLimit($limit)) {
$sql = "SELECT TOP $limit * FROM ($sql) sub";
} else {
$sql = "SELECT * FROM ($sql) sub";
}
if ($this->hasOffset($offset)) {
$sql .= $this->separator . "WHERE rowNum > $offset";
}
return $sql;
}
