D
D
Dmitry2017-01-13 15:12:47
Yii
Dmitry, 2017-01-13 15:12:47

MSSQL in Yii2. How to sort data correctly?

Not strong in OOP. 10 years ago I coded structurally in PHP :)
I started studying OOP and Yii2
#MSSQL2008 via freeTDS7.3 @linux

<?php
namespace frontend\models;
use yii\db\ActiveRecord;
class Plist extends ActiveRecord
{
    public static function getDb()
    {
        return \Yii::$app->db2;  
    }
    public static function tableName() {
         return 'dbo.pList';
    }
}
?>

$pList = Plist::find()
    ->select('ID, Name, FirstName, MidName, WorkPhone, HomePhone, BirthDate')
  ->filterWhere(['like', 'Name', $searchQuery])
  //->orderBy('Name')
  //->all()
  ;
  
Хочу дальше работать с результатом так:

<?php foreach ($pList as $t) { ?>
    <li><?=$t->Name."&nbsp;".$t->FirstName."&nbsp;".$t->MidName?></li>
<?php } ?>

so if I try to add sorting, an error occurs
SQLSTATE[HY000]: General error: 20018 Column "dbo.pList.Name" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. [20018] (severity 16) [(null)]
The SQL being executed was: SELECT COUNT(*) FROM [dbo].[pList] ORDER BY [Name]

->groupBy() does not clarify
Although if you write a query with your hands SELECT * FROM [dbo].[pList] ORDER BY [Name] - it works
Yii norms in the query builder adds COUNT (*) - it does not work with it.
UPD*1: As a result, it turns out that yii creates such a query:
SELECT COUNT(*) FROM (SELECT [ID], [Name] FROM [dbo].[pList] GROUP BY [ID], [Name] ORDER BY [Name ])
but you need something like:
SELECT [ID], [Name], COUNT(*) FROM [dbo].[pList] GROUP BY [ID], [Name] ORDER BY [Name]
Moreover, if I use the method - >all() - the request fulfills, but the result is returned in the form of an array, and I want ActiveQueryInterface Tell me
how to correctly compose a query?
UPD * 2:
Did not indicate, as it turned out, an important point!
/*$pagination = new Pagination([
        'defaultPageSize' => 20,
      'totalCount' => $pList->count()
    ]);*/
    //$pList = $pList->offset($pagination->offset)->limit($pagination->limit)->all();

Used pagination. I confess. Commented out.
And uncommented //->orderBy('Name')->all()
Sorting worked. No pagination...
UPD*3: Done! :)
Added orderBy() to pagination, it worked there..
$pList = $pList->orderBy($sort->orders)->offset($pagination->offset)->limit($pagination->limit)->all();

Thanks for the leading questions!!

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question