Yii Framework Forum: SQL error Unknown column '0' in 'order clause' - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

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

#1 User is offline   Benn 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 16-January 11
  • Location:Paris

Posted 14 April 2018 - 06:03 PM

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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users