Need Help With Yii - Postgresql

Yii 1.1 General Discussion
1

Hi every one. This is the first time I use yii with postgresql, and I got this error when I try to search in cgridview search column :





Error 500: <h1>CDbException</h1>

<p>CDbCommand failed to execute the SQL statement: SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: timestamp without time zone ~~ unknown

LINE 1: ...FROM "tbl_product_request" "t" WHERE expired_date LIKE '%test%'

                                                             ^

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.. The SQL statement executed was: SELECT COUNT(*) FROM "tbl_product_request" "t" WHERE expired_date LIKE :ycp0. Bound with :ycp0='%d%' (D:\xampp\htdocs\yii-1.1.12\framework\db\CDbCommand.php:528)</p><pre>#0 D:\xampp\htdocs\yii-1.1.12\framework\db\CDbCommand.php(425)

The column’s data type is datetime, and it seems like the problem is the generated query tries to compare datetime data type column with string. Anybody has the solution ? Please help. Thanks

2

show me your code of model search function

3

This is my search function :




public function search()

	{

		$criteria=new CDbCriteria;


		$criteria->compare('product_request_id',$this->product_request_id,true);

		$criteria->compare('product_request_no',$this->product_request_no,true);

		$criteria->compare('user_id',$this->user_id,true);

		$criteria->compare('name',$this->name,true);

		$criteria->compare('pc_id',$this->pc_id,false);

		$criteria->compare('description',$this->description,true);

		$criteria->compare('est_order_qty',$this->est_order_qty,true);

		$criteria->compare('est_order_qty_unit_id',$this->est_order_qty_unit_id,true);

		$criteria->compare('annual_purchase_vol',$this->annual_purchase_vol,true);

		$criteria->compare('annual_purchase_vol_unit_id',$this->annual_purchase_vol_unit_id,true);

		$criteria->compare('expired_date',$this->expired_date,true);

		$criteria->compare('last_updated',$this->last_updated,true);

		

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}
4

Like you say the problem is trying to compare a date time with a string, but if you don’t wanna errors at moment of the search you may put the same format of the database column at the search field or use a calendar to do it.

If you wanna use a calendar you can view

but the guide is in spanish

5

You shouldn’t use compare() on datetime fields (resulting operator would be LIKE instead of ‘=’).

Use addCondition for example.

6

Hi, so sorry for the late reply, been sick and bed rest since two days ago and just getting well today.

I used to use mysql and the compare method works well, even though the datatype is datetime. But I think I cant do that with postgre and the solution would be using addCriteria on my search function. Thanks for the solution, helps me a lot

7

Yeah, MySQL allows users to do a lot of nasty things. That’s how it works.

PG is way more restrictive, but it’s awesome DB.