Skip to content

Search on bigint using the LIKE operator #1912

Closed
bsagal opened this Issue Jan 2, 2013 · 8 comments

6 participants

@bsagal
bsagal commented Jan 2, 2013

I have a table with a bigint column, when tying to preform a search a get the the following exception:

Error 500: <h1>CDbException</h1>
<p>התרחשה שגיאה בעת הרצת שאילתה בעזרת CDbCommand. השגיאה: SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: bigint ~~ unknown
LINE 1: SELECT COUNT(*) FROM "meter" "t" WHERE (id LIKE '%1860240090...
                                                   ^
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 "meter" "t" WHERE (id LIKE :ycp0) AND (current_reading=:ycp1). Bound with :ycp0='%18602400902%', :ycp1='1102' 

It seems like the like operator is used in the comparison but is not supported by the database for the bigint column.

DB: postgresql 9.1
PHP: 5.4.8
yii: 1.1.13

@samdark
Yii Software LLC member
samdark commented Jan 2, 2013

How are you building search criteria exactly?

@bsagal
bsagal commented Jan 2, 2013

I get this error when entering text into the filler on a gii generated admin form.

I have been able to fix the problem by removing the $partialMatch parameter from $criteria->compare line of the search method in the model.

I think the real problem is that for bigints gii should not set the $partialMatch parameter in the search method of the model

@nineinchnick

Related to #2535.

@cebe
Yii Software LLC member
cebe commented Apr 22, 2014

bigint is considered string to allow store big values in PHP on 32bit systems. This can not be fixed in gii, you have to adjust gii generated code.

@cebe cebe closed this Apr 22, 2014
@cebe cebe removed this from the 1.1.15 milestone Apr 22, 2014
@exru
exru commented Nov 20, 2014

Какой-то недружественный ход в сторону PostgreSQL.
Получил такую же ошибоньку в модели когда пытался вызвать search() для CGridView:
/db postgresql/
CREATE TABLE pay
(
id bigserial NOT NULL,
order_id bigint,
date timestamp without time zone DEFAULT now(),
summ double precision,
status integer,
email character varying,
phone character varying,
CONSTRAINT pay_order_id_fkey FOREIGN KEY (order_id)
REFERENCES orders (id) MATCH Unknown
ON UPDATE CASCADE ON DELETE CASCADE
);

/*grid */
$mdl = new Pay();

'dataProvider'=>$mdl->search(),

/*model сгенерил gii, здесь сокращенно до 1 параметра */
public function search()
{
$criteria=new CDbCriteria;
$criteria->compare('order_id',$this->order_id,true);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
));
}

/собственно сама ошибка/
CDbCommand не удалось исполнить SQL-запрос: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~ unknown
LINE 1: ...ELECT COUNT() FROM "pay" "t" WHERE order_id LIKE '%375111
^
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 "pay" "t" WHERE order_id::int LIKE :ycp0

@exru
exru commented Nov 20, 2014

Приведение типов не помогло:
CDbCommand не удалось исполнить SQL-запрос: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~ unknown
LINE 1: ...ELECT COUNT() FROM "pay" "t" WHERE order_id::int LIKE '%375...
^
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 "pay" "t" WHERE order_id::int LIKE :ycp0

@cebe
Yii Software LLC member
cebe commented Nov 20, 2014

as explained above you can not do partial matching on integer values. change the gii generated code to disable partial matching: $criteria->compare('order_id',$this->order_id,false);

@thehiddennepali

Hi also stucked with postgresql

What I am trying to search the model with date_cated where date is input by select from datepicker

and I got the error as below:

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: timestamp without time zone >= integer LINE 1: ... COUNT(*) FROM "block_mac" "t" WHERE date_created BETWEEN -3... ^ 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 "block_mac" "t" WHERE date_created BETWEEN :ycp0 AND :ycp1 (E:\xampp\htdocs\yii\framework\db\CDbCommand.php:528)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.