Erro na Pesquisa com Postgres

[color="#0000FF"]

Blz Pessoal! Alguém pode me ajudar?

Sou novo em tudo(Php…), Estou tentando aprender com a ajuda do Yii desenvolvendo um pequeno prog de cadastro de itens.

Mas é o seguinte, na pagina onde apresenta a grade com opções para editar, excluir,viualizar… Onde tem um campo acima para filtrar, quando eu coloco o criterio para pesquisa, logo é informado um alerta com a mensagem de erro SQL…

Acho que deve ser relacionado ao campo tipo serial no Postgres(Na verdade não sei).[/color]

[color="#FF0000"]Duas imagens da tela seguem em anexo:[/color]

[color="#FF0000"]Erro:[/color]

Error 500: <h1>CDbException</h1>

<p>CDbCommand falhou ao executar o comando SQL: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: bigint ~~

unknown

LINE 1: …COUNT(*) FROM “categorias” “t” WHERE id_categoria LIKE ‘%2%’

                                                         ^

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 "categorias" "t" WHERE id_categoria LIKE :ycp0 (/home/andre/lamp/apache2/htdocs/site/framework/

db/CDbCommand.php:516)</p><pre>#0 /home/andre/lamp/apache2/htdocs/site/framework/db/CDbCommand.php(411): CDbCommand->queryInternal

(‘fetchColumn’, 0, Array)

#1 /home/andre/lamp/apache2/htdocs/site/framework/db/ar/CActiveRecord.php(1540): CDbCommand->queryScalar()

#2 /home/andre/lamp/apache2/htdocs/site/framework/web/CActiveDataProvider.php(176): CActiveRecord->count(Object(CDbCriteria))

#3 /home/andre/lamp/apache2/htdocs/site/framework/web/CDataProvider.php(184): CActiveDataProvider->calculateTotalItemCount()

#4 /home/andre/lamp/apache2/htdocs/site/framework/web/CActiveDataProvider.php(126): CDataProvider->getTotalItemCount()

#5 /home/andre/lamp/apache2/htdocs/site/framework/web/CDataProvider.php(128): CActiveDataProvider->fetchData()

#6 /home/andre/lamp/apache2/htdocs/site/framework/zii/widgets/CBaseListView.php(105): CDataProvider->getData()

#7 /home/andre/lamp/apache2/htdocs/site/framework/zii/widgets/grid/CGridView.php(264): CBaseListView->init()

#8 /home/andre/lamp/apache2/htdocs/site/framework/web/CBaseController.php(140): CGridView->init()

#9 /home/andre/lamp/apache2/htdocs/site/framework/web/CBaseController.php(165): CBaseController->createWidget

(‘zii.widgets.gri…’, Array)

#10 /home/andre/lamp/apache2/htdocs/site/patrimonio/protected/views/categorias/admin.php(51): CBaseController->widget

(‘zii.widgets.gri…’, Array)

#11 /home/andre/lamp/apache2/htdocs/site/framework/web/CBaseController.php(119): require(’/home/andre/lam…’)

#12 /home/andre/lamp/apache2/htdocs/site/framework/web/CBaseController.php(88): CBaseController->renderInternal(’/home/andre/

lam…’, Array, true)

#13 /home/andre/lamp/apache2/htdocs/site/framework/web/CController.php(866): CBaseController->renderFile(’/home/andre/lam…’,

Array, true)

#14 /home/andre/lamp/apache2/htdocs/site/framework/web/CController.php(779): CController->renderPartial(‘admin’, Array, true)

#15 /home/andre/lamp/apache2/htdocs/site/patrimonio/protected/controllers/CategoriasController.php(148): CController->render

(‘admin’, Array)

#16 /home/andre/lamp/apache2/htdocs/site/framework/web/actions/CInlineAction.php(50): CategoriasController->actionAdmin()

#17 /home/andre/lamp/apache2/htdocs/site/framework/web/CController.php(300): CInlineAction->runWithParams(Array)

#18 /home/andre/lamp/apache2/htdocs/site/framework/web/filters/CFilterChain.php(134): CController->runAction(Object

…

[color="#FF0000"]Abaixo vou colocar a estrutura da minha base(Postgres)[/color]

CREATE DATABASE dbsite

  WITH OWNER = postgres

       ENCODING = 'UTF8'

       TABLESPACE = pg_default

       LC_COLLATE = 'pt_BR.UTF-8'

       LC_CTYPE = 'pt_BR.UTF-8'

       CONNECTION LIMIT = -1;


use dbsite;


create table empresas(

id_empresa bigserial NOT NULL primary key,

cnpj character varying(14),

empresa character varying(50)

);


create table locais(

id_local bigserial not null primary key,

local character varying(50)

);


create table categorias(

id_categoria bigserial not null primary key,

categoria character varying(50)

);


create table objetos(

id_objeto bigserial not null primary key,

id_categoria bigint references categorias,

objeto character varying(50),

comentario text

);


create table itens(

id_item bigserial not null primary key,

id_empresa bigint references empresas,

id_objeto bigint references objetos,

patriminio int,

comentario text

);


create table itens_log(

id_inten_log bigserial not null primary key,

patrimonio int,

id_item bigint references itens,

id_empresa_anterior bigint references empresas,

id_empresa_atual bigint references empresas,

comentario text

)

[color="#FF0000"]Antes:[/color]

1800

[color="#FF0000"]Depois:[/color]

1801

mmm… it seems is a common issue with Postgress if you do not pass the correct parameter type.

Can you show the code where do you the search of the filter criteria?

[color="#000080"]It is standard code generated by Gii.[/color]

Local: [color="#FF0000"]project/protected/models/Categorias.php[/color]

<?php


/**

 * This is the model class for table "categorias".

 *

 * The followings are the available columns in table 'categorias':

 * @property string $id_categoria

 * @property string $categoria

 */

class Categorias extends CActiveRecord

{

	/**

	 * Returns the static model of the specified AR class.

	 * @return Categorias the static model class

	 */

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return 'categorias';

	}


	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('categoria', 'length', 'max'=>50),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('id_categoria, categoria', 'safe', 'on'=>'search'),

			array('categoria', 'required'),

		);

	}


	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'objetoses' => array(self::HAS_MANY, 'Objetos', 'id_categoria'),

		);

	}


	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'id_categoria' => 'Id Categoria',

			'categoria' => 'Categoria',

		);

	}


	/**

	 * Retrieves a list of models based on the current search/filter conditions.

	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

	 */

	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


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


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


		return new CActiveDataProvider('Categorias', array(

			'criteria'=>$criteria,

		));

	}

}

You can’t use like operator on integers in postgresql.

Try just to replace

$criteria->compare(‘categoria’,$this->categoria,true);

with

$criteria->compare(‘categoria’,$this->categoria);

And tell me if you will get any error(Note that this will use ‘=’ oerator instead ‘LIKE’ so you will need to enter complete category_id).

[font="Comic Sans MS"][b][color="#000080"]

Perfect! I do the other models with the serial type field.

Thank you![/color][/b][/font]

Complementar informação: