Re: Spaces in Field Names

Further clarifying, I'd rather work this through model joins. So I have two models, Customer and Quote. Quote belongsTo Customer. These two models refer to tables in different databases. Quotes is from "my" database and Customers is in one that I can't change. My Customers model looks like this:

class Customer extends AppModel

{

var $name = 'Customer';

public $useDbConfig = 'otherDB';

public $useTable = 'customers';

public $primaryKey = 'name has spaces';

}

Over in Quote, the model looks like this:

class Quote extends AppModel

{

var $name = 'Quote';

public $useTable = 'quotes';

public $primaryKey = 'key_';

public $belongsTo = array(

'Customer' => array(

'className' => 'Customer',

'foreignKey' => 'CustomerKey',

'fields' => array('name'),

),

);

}

The first thing I notice is, Cake won't do a join in my query to the external database to bring back Customer fields, as it will with other $belongsTo or $hasOne references within the same database. It retrieves the records in the primary query to Quote and then pops a query for each record back to the external model (Customer). Since the key field in the external database table has spaces, the secondary query to Customer fails with this error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'spaces = 1234' at line 1

SQL Query: SELECT `Customer`.`name` FROM `externalDatabase`.`customers` AS `Customer` WHERE `Customer.name has` spaces = 1234

I would expect Cake to render the WHERE clause as WHERE `Customer`.`name has spaces` = 1234, but it's messed up in two places - no escaping on either side of the dot and escaping on the field name happening after word 2. (Why not after word 1? Who knows?)

As a workaround, I tried removing the model belongsTo and placed a join in the query. It failed similarly. It actually adds the join to the query (instead of making separate queries after the fact0, and produces this broken join syntax: 

LEFT JOIN `externalDatabase `.`customers` AS `Customer` ON (`Quote`.`CustomerKey` = `Customer`.`name` has spaces)

Note the syntax is escaped properly around the dot, and the field name has improper escaping after word 1. Different than above.

I've spent a whole day on this. Does anyone have a workaround? This is killing me.

Thanks,

Dale

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/groups/opt_out.