I need to prevent inserting dupes into the object fields table. Users can enter inputs for this field in UI with/without spaces b/w the words and also upper/lower case of the field name, for example:
1. test field
2. TestField
3. TEST FIELD
Any combination of above. I should prevent inserting dupe values by scrubbing the white spaces b/w words. The check is only for checking the existing value before insert/update. But we store the actual input given by the user.
I have to write a propel query in Symfony which is equivalent of below Oracle select query that I run to check for the same "field name" existence.
`SELECT lower(REPLACE(field_name, ' ', '')),
field_id
from object_fields
where field_name=lower('testfield');`
SYmfony/Propel Query I have tried:
$fieldName = preg_replace('/\s+/', '', strtolower($fieldName));
$selectColumn = "lower(REPLACE( ObjFieldsPeer::FIELD_NAME,' ', ''))";
ObjFieldsQuery::create( )
->withColumn($selectColumn)
->filterByFieldName( $fieldName )
->findOne( );
It gives me the following Oracle SQL Query:
select OBJECT_FIELDS.FIELD_ID,
lower(REPLACE(OBJECT_FIELDS.FIELD_NAME, ' ', ''))
AS lowerREPLACEOBJECT_FIELDSFIELD_NAME, ' ', ''
FROM OBJECT_FIELDS
WHERE OBJECT_FIELDS.FIELD_NAME='testfield';
and I get Oracle error as " ORA-00972: identifier is too long"
Anyone has any idea how to write the same query in Propel/Symfony with alias name for the column ?
Thanks Raj
Untested, but I think this should work:
$fieldName = preg_replace('/\s+/', '', strtolower($fieldName));
$selectColumn = "lower(REPLACE( ObjFieldsPeer::FIELD_NAME,' ', ''))";
ObjFieldsQuery::create( )
->withColumn($selectColumn, 'your_alias')
->filterByFieldName( $fieldName )
->findOne( );
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments