SQL error Unknown column '0' in 'order clause'

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

me too same problem