Hello all,
I’m trying to build a query but I get an SQL error :
Here is what’s in my search model:
$query = new yii\db\Query;
$query->select(['post.*'])
->from('post')
->leftJoin('user', 'post.created_by = user.id')
->leftJoin('profile', 'user.id = profile.user_id')
->leftJoin('city postcity', 'profile.location = postcity.id')
->leftJoin('make', 'make_id = make.id')
->leftJoin('modele', 'modele_id = modele.id')
->leftJoin('category', 'category_id = category.id')
->leftJoin('post_type', 'post_type_id = post_type.id')
->leftJoin('currency', 'currency_id = currency.id')
->orderBy([ 'id' => SORT_DESC]);
if (!Yii::$app->user->isGuest && Yii::$app->user->identity->profile->city!=null)
$query->select(['post.*','round(6371 * acos( cos( radians(postcity.latitude) ) * cos( radians(userCity.latitude) ) * cos( radians(userCity.longitude) - radians(postcity.longitude)) + sin(radians(postcity.latitude)) * sin( radians(userCity.latitude)))) AS distance'])
->join('CROSS JOIN', 'city userCity')
->where(['userCity.id' => Yii::$app->user->identity->profile->city->id]);
$query->all();
Here’s the output:
SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'order clause'
The SQL being executed was: SELECT `post`.*, round(6371 * acos( cos( radians(postcity.latitude) ) * cos( radians(userCity.latitude) ) * cos( radians(userCity.longitude) - radians(postcity.longitude)) + sin(radians(postcity.latitude)) * sin( radians(userCity.latitude)))) AS distance FROM `post` LEFT JOIN `user` ON post.created_by = user.id LEFT JOIN `profile` ON user.id = profile.user_id LEFT JOIN `city` `postcity` ON profile.location = postcity.id LEFT JOIN `make` ON make_id = make.id LEFT JOIN `modele` ON modele_id = modele.id LEFT JOIN `category` ON category_id = category.id LEFT JOIN `post_type` ON post_type_id = post_type.id LEFT JOIN `currency` ON currency_id = currency.id CROSS JOIN `city` `userCity` WHERE `userCity`.`id`=1 ORDER BY `id` DESC, `0` LIMIT 20
Error Info: Array
(
[0] => 42S22
[1] => 1054
[2] => Unknown column '0' in 'order clause'
)
↵
Caused by: PDOException
SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'order clause'
in /homepages/28/d707141912/htdocs/test/vendor/yiisoft/yii2/db/Command.php at line 1067
Does anyone know why yii is adding this order by 0
?
I’ve tried adding an order by to the query, same thing except the generated query becomes …ORDER BY id
DESC, 0
LIMIT 20
Edit :
I tried simplifying to the most by entering
$query = new yii\db\Query;
$query->select(['post.*'])
->from('post');
$query->all();
Same issue…
Edit :
If I remove the sort in
$dataProvider = new ActiveDataProvider([
'query' => $query,
'sort'=> ['defaultOrder' => ['post.id'=>SORT_DESC]],
]);
I don’t get the error anymore. I’ll keep looking
Thanks in advance
Benn