Hello everyone, this is first time posting, so I hope I am doing this
correctly :)
I have been using Zend Framework since 0.15 and am very familiar with
the code base and API usage. I welcomed the changes of 0.80 in regards
to Zend_DB; however, I have run into a problem when joining tables
across databases. I have provided my error message and code with two
cases below. I am using FAMP (FreeBSD, Apache, MySQL and PHP). The
issue lies in correlation names and how the function _join($type,
$name, $cond, $cols) handles them.
In case 1 of my situation, I provide no required fields for the from()
and join(), hoping to default to '*'. In my code, I will be providing
fields.
In case 2, I do provide the fields.
In either case I do not want a correlation name generated, for example:
FROM `crm.client` crm.client
should be
FROM crm.client
notice there are no backticks or an alias.
I believe my problem would be solved if I could ensure no correlation
name was generated, but I am not sure what the API needs to make this
happen.
As a note, I do not want to rewrite the class or extend it to make this work.
Thanks, in advance. Below you will see the code. I had this working in
0.15 - 0.70. The example queries below have been simplified to
illustrate the error. Also, the sections marked "// Output Desired"
work with a command line query to MySQL.
PHP Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect
table name 'crm.client'' in
/usr/local/www/ZendFramework-0.8.0/library/Zend/Db/Adapter/Pdo/Abstract.php:117
Stack trace:
#0
/usr/local/www/ZendFramework-0.8.0/library/Zend/Db/Adapter/Pdo/Abstract.php(117):
PDO->prepare('SELECT??crm.cli...')
#1 /usr/local/www/ZendFramework-0.8.0/library/Zend/Db/Adapter/Abstract.php(151):
Zend_Db_Adapter_Pdo_Abstract->prepare('SELECT??crm.cli...')
#2
/usr/local/www/ZendFramework-0.8.0/library/Zend/Db/Adapter/Pdo/Abstract.php(151):
Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 /usr/local/www/ZendFramework-0.8.0/library/Zend/Db/Adapter/Abstract.php(302):
Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/local/www/CreateOpportunity/CreateOpportunity.php(919):
Zend_Db_Adapter_Abstract->fetchAll(Object(Zend_Db_Select))
#5 /usr/local/www/CreateOpportunity/CreateOpportunity.php(78):
createOpportunity->fetchOpportunities()
#6 /us in
/usr/local/www/ZendFramework-0.8.0/library/Zend/Db/Adapter/Pdo/Abstract.php
on line 117
Case 1:
$tables['crm.client'] = array(
);
$tables['authentication.realm'] = array(
);
/*
** select
*/
$select = $params['link']->select();
$select->from('crm.client', $tables['crm.client']);
$select->join('authentication.realm', "(authentication.realm.account
= CONCAT('CRM', crm.client.acnt))", $tables['authentication.realm']);
$select->where('crm.client.active = ?', 'T');
$select->order('acnt');
$select->limit($params['limit']);
// Output Generated
SELECT
FROM `crm.client` crm.client
INNER JOIN `authentication.realm` authentication.realm ON
(authentication.realm.account = CONCAT('CRM', crm.client.acnt))
WHERE
crm.client.active = 'T'
ORDER BY
`acnt` ASC
LIMIT 1
// Output Desired
SELECT
crm.client.*,
authentication.realm.*
FROM crm.client
INNER JOIN authentication.realm ON (authentication.realm.account =
CONCAT('CRM', crm.client.acnt))
WHERE
crm.client.active = 'T'
ORDER BY
`acnt` ASC
LIMIT 1
Case 2:
$tables['crm.client'] = array(
new Zend_Db_Expr('crm.client.*'),
);
$tables['authentication.realm'] = array(
new Zend_Db_Expr('authentication.realm.*'),
);
/*
** select
*/
$select = $params['link']->select();
$select->from('crm.client', $tables['crm.client']);
$select->join('authentication.realm', "(authentication.realm.account
= CONCAT('CRM', crm.client.acnt))", $tables['authentication.realm']);
$select->where('crm.client.active = ?', 'T');
$select->order('acnt');
$select->limit($params['limit']);
// Output Generated
SELECT
crm.client.*,
authentication.realm.*
FROM `crm.client` crm.client
INNER JOIN `authentication.realm` authentication.realm ON
(authentication.realm.account = CONCAT('CRM', crm.client.acnt))
WHERE
crm.client.active = 'T'
ORDER BY
`acnt` ASC
LIMIT 1
// Output Desired
SELECT
crm.client.*,
authentication.realm.*
FROM crm.client
INNER JOIN authentication.realm ON (authentication.realm.account =
CONCAT('CRM', crm.client.acnt))
WHERE
crm.client.active = 'T'
ORDER BY
`acnt` ASC
LIMIT 1
