Relation Search Fail With Oracle

Archives Bug Discussions
1

I have a File table ,and a Admin table.

and the relation of Admin model is :

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(


		'Admin' => array(self::BELONGS_TO, 'ADMIN', 'ADMIN_ID') 


	);


}

it means every file is created by a admin.

in my admin table I have a name column beside username and password.

and i want to search file by the administrator’s name.

so I follow the step on Searching and sorting by related model in CGridView

it is a great toturial.

but it fail with CDbCommand can not excute SQL statement: SQLSTATE[HY000]: General error: 904 OCIStmtExecute: ORA-00904: "ADMIN"."NAME": Identifier is invalid

(ext\pdo_oci\oci_statement.c:148). The SQL statement executed was: SELECT COUNT(DISTINCT "t"."FILE_ID") FROM "HOUSE_FILE" "t" LEFT OUTER JOIN "HOUSE_ADMIN" "Admin" ON ("t"."ADMIN_ID"="Admin"."ADMIN_ID") WHERE (Admin.NAME LIKE :ycp0)

I think it is caused by the oracle database,

oracle take all table name and table alas name without Double quotes as upper case.

so it take Admin.NAME as ADMIN.NAME , but the Alias is "HOUSE_ADMIN" "Admin" "Admin",

consequently, it said "ADMIN"."NAME": Identifier is invalid.

I know the cause, and I know this issue can be fixed by add double quotes to Admin in Admin.NAME , unfortunately I do not how to add.

If somebody can help me ,I am so gratitude.

2

Yes ,I find out the solution!

just add these code which render the table name and column name with double quotation in the beginning of CDbCriteria compare function .




$column_split_array = explode('.', $column);





		if(count($column_split_array) > 1)

		{


			foreach ($column_split_array as $key => $column_split) {

			 	$column_split_array[$key] = '"'.$column_split.'"';

			 } 

			$column =  implode('.', $column_split_array);

		}
3

I’m so sorry if my bad english… can you show me the complete code in search function please, because I have same problem and I don’t know where that code must be placed. I got error message that I don’t have $column… help me please…

4

In order to avoid oracle problems, try not to use Upper case in your db script (transform them to lower case) by using sql developper.

exemple :

if you have a table called ADMIN rename it "admin". Same thing for the attributs of you table. For exemple if you have USER_NAME in ADMIN table transform it to "user_name". dont forget to use the [color="#FF0000"]" "[/color].