Construct Cdbcriteria With Column Names As Mysql Reserved Words

Yii 1.1 General Discussion
1

I trying to use CDbCriteria with column named "key", which is a reserved word in MySQL:


public function search()

{

	$criteria = new CDbCriteria;


	$criteria->compare('key', $this->key);

	$criteria->compare('position', $this->position);

	$criteria->compare('dictionary', $this->dictionary);


	return new CActiveDataProvider(get_class($this), array

	(

    	'criteria'=>$criteria,

    	'pagination'=>array('pageSize'=>10),

    	'sort'=>array('defaultOrder'=>array('dictionary'=>FALSE, 'position'=>FALSE))

	));

}

This, of course fails, when I’m trying to search/filter using “key” column:


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 'key LIKE '%2%')' at line 1. The SQL statement executed was: SELECT COUNT(*) FROM `lookup` `t` WHERE (dictionary LIKE :ycp0) AND (key LIKE :ycp1)

I found some solution at Stack Overflow:


$criteria->condition = 't.key=:key';

$criteria->params = array(':key'=>$this->key);

$criteria->compare('position', $this->position);

$criteria->compare('dictionary', $this->dictionary);

However, this works for me only partially. I don’t get exception anymore, but search works for “key” column only. All other are ignored (if “key” is set, respects only this value in search, if it is not set – always returns empty results set).

What am I missing? How should I construct CDbCriteria queries, when my table contains reserverd words as column names, so search would respect all other (non-reserved) columns as well, not only this one?

2

Have you tried just doing this?




        $criteria->compare('t.key', $this->key);

        $criteria->compare('position', $this->position);

        $criteria->compare('dictionary', $this->dictionary);
3

No, I haven’t tried this, because I haven’t got any idea, that this works that way! :] Thanks! :]

BTW: Keith, how is it feels, to be always right? :] I don’t recall any wrong answer given by you! :}

4

I’m married, so I’m always wrong at home. It’s quite novel to be right for a change. :D

5

Here is part of what all my search functions look like:




    public function search() {

        $criteria = new CDbCriteria;

        $t=$this->getTableAlias(false,false);

        $ds=$this->getDbConnection()->getSchema();


        $criteria->compare($ds->quoteColumnName("$t.key"), $this->key);

        ...

Quoting the column name should do it ;-).

6

I see your point and I think, your answer can be seen as an alternative.

Though, I can’t understand, why you think, that additional call to $this->getTableAlias(false,false) is required? In your solution, it is must, in Keith’ solutions it isn’t necessary and his solution works just fine.

7

LOL! I’m married to a wonderful woman, which in the same time is mother to my two wonderful daughters. Can you imagine that? Three girls! At home I’m always 300% wrong. Before marriage I wasn’t even able to image, that this is possible! :]