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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/groups/opt_out.
