[André Couto]

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).


Duas imagens da tela seguem em anexo:


Erro:

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
...


Abaixo vou colocar a estrutura da minha base(Postgres)

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
)

Antes:
 Erro Yii - campo pesquisa.png (405.54K)
Number of downloads: 19

Depois:
 Erro Yii - PostgreSql.png (381.19K)
Number of downloads: 13

[Antonio Ramirez]

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?

[André Couto]

It is standard code generated by Gii.

Local: project/protected/models/Categorias.php

<?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,
		));
	}
}

[Ivica]

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).

[André Couto]

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


Thank you!

[André Couto]

Complementar informação:

redguy, on 11 February 2012 - 01:11 PM, said:

you have to use quotes:
"IdUser", "User", "Action"

so criteria would look like this:
$criteria->compare('"IdPessoa"',$this->IdPessoa);

(there are single AND double quotes used in example above)