Activerecord Cdbcriteria Error #1054 - Unknown Column In 'where Clause' When Using Alias

Yii 1.1 General Discussion
1

I’m using yii Version 1.1.14

SQL version: 5.5.24-log - MySQL Community Server (GPL)

PHP v5.3.13

Here’s my code:


   public $calculated_height;  // this is for the calculated field 

        

    	public function search()

	{

	       $criteria=new CDbCriteria;

               $criteria->select='height as calculated_height';

               $criteria->condition='calculated_height > 50';

               $mtnresult = Mtn::model()->find($criteria);

	}

this runs

SELECT height as calculated_height FROM stagingIR2013 t WHERE calculated_height > 50 LIMIT 1

and gives this error

#1054 - Unknown column ‘calculated_height’ in ‘where clause’

I get the same error if I run this SQL in phpmyadmin

it’s because the SQL is calculated from right to left and calculated_height is evaluated in the where clause.

Because of this the aliasing of height to calculated_height has not yet occurred.

in other posts on this forum I read that i need to have public $calculated_height; in my model.

I have done that.

Can someone give some more examples of how I could change this so that the calculated_height is already there when I do the select

2

Yes, I did that. From previous posts, I thought it was enough to do that. So that’s why I’m confused.

Here’s my code:




class Mtn extends CActiveRecord

{	

       public $calculated_height;  // this is for the calculated field
3

i think you may change the


$criteria->condition='calculated_height > 50';

to


 $criteria->condition='height > 50';

because you may defind the alias property on height and it’s not on table so that’s why give the error…

4

Yes, I agree this will work. However, I need to use the alias. The example here is a simplified version of what I need to do. In my actual production code, I will be using a stored function on MySQL so that is why I have to use calculated_height.

5

so you can write a two query

first you can find the calculated_height (so commnet the condition) and based on calculated_height write a second query.


 public function search()

        {

               $criteria=new CDbCriteria;

               $criteria->select='height as calculated_height';

               //$criteria->condition='calculated_height > 50';

               $mtnresult = Mtn::model()->find($criteria);

        }





    $height = Mtn::model()->find('$mtnresult->calculated_height > 50 ')
6

Thanks Ankit. This looks like a very good idea. I will try this and report back with (hopefully) working code.

7

Hi Ankit

I used this code as you suggested


  $criteria=new CDbCriteria;

               $criteria->select='height as calculated_height';

               $mtnresult = Mtn::model()->find($criteria);

               $height = Mtn::model()->find('$mtnresult->calculated_height > 50 ');

But I get this error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘>calculated_height > 50 LIMIT 1’ at line 1. The SQL statement executed was: SELECT * FROM stagingIR2013 t WHERE $mtnresult->calculated_height > 50 LIMIT 1

If I put $mtnresult in quotes in the find statement it will be evaluated as a literal so that’s not going to work in an SQL statement.

Thanks

Neil

8

Use CDbExpression.

9

Hi,

have you got the calculated_height on $mtnresult array?

10

Hi Ankit, ORey

Thank you for your help but I’m going to take a slightly different approach.

I was doing a $criteria->compare on a calculated field and it was giving an error so instead of compare with an alias which doesn’t exist, I am doing the compare against the actual SQL function call. I have it almost working now. I think this is a cleaner approach for me.

Thanks.

Neil

11

Hi Neil Kenealy.

cool… :rolleyes:

can you post your code so someone may be help this.

12

I am doing this


$criteria->compare(sprintf ('gcdist(%s, lonwgs84best,%s, latwgs84best, 100)',$templon,$templat),"< ".Yii::app()->session['maxdistancefromrefpoint']);

before I was doing this




I am doing this[code]$criteria->compare('greatcircledistance',"< ".Yii::app()->session['maxdistancefromrefpoint']);

and greatcircledistance was an alias field which hadn’t been created and so was giving an unknown column error.

13

ok thanks… :rolleyes: