2010年9月22日星期三

[fw-db] Help With Multitable Left Joins

Hello everyone. I'm fairly new to Zend Framework, so I hope I'm not missing
something ridiculous here. But I can't figure out what I'm doing wrong when
trying to left join multiple tables. Here's the class below. (I've omitted
other non-vital parts for now)

class Application_Model_Table_Asset extends Zend_Db_Table_Abstract {

protected $_name = "assets";
protected $_primary = "assetId";
protected $_sequence = true;
protected $_rowClass = "Application_Model_Table_Row_Asset";

public function getAllAssetsByClientId() {
$query = $this->select()
->setIntegrityCheck(false)
->from(array('a' => 'assets'))
->joinLeft(array('t' => 'texts') , 'a.assetId = t.assetId')
->joinLeft(array('p' => 'photos'), 'a.assetId = p.assetId')
->joinLeft(array('v' => 'videos'), 'a.assetId = v.assetId');

Zend_Debug::dump($query);
echo $query->__toString();
Zend_Debug::dump($assets);

return $this->fetchAll($query);
}
}

Here's a snippet of the dump:
Hello everyone. I'm fairly new to Zend Framework, so I hope I'm not missing
something ridiculous here. But I can't figure out what I'm doing wrong when
trying to left join multiple tables. Here's the class below. (I've omitted
other non-vital parts for now)

class Application_Model_Table_Asset extends Zend_Db_Table_Abstract {

protected $_name = "assets";
protected $_primary = "assetId";
protected $_sequence = true;
protected $_rowClass = "Application_Model_Table_Row_Asset";

public function getAllAssetsByClientId() {
$query = $this->select()
->setIntegrityCheck(false)
->from(array('a' => 'assets'))
->joinLeft(array('t' => 'texts') , 'a.assetId = t.assetId')
->joinLeft(array('p' => 'photos'), 'a.assetId = p.assetId')
->joinLeft(array('v' => 'videos'), 'a.assetId = v.assetId');

Zend_Debug::dump($query);
echo $query->__toString();
Zend_Debug::dump($assets);

return $this->fetchAll($query);
}
}

Here's a snippet of the dump:
[0] => array(11) {
["assetId"] => int(58)
["clientId"] => int(8)
["assetType"] => string(5) "video"
["description"] => string(5) "test3"
["timestamp"] => string(19) "2010-09-22 14:05:19"
["textId"] => NULL
["content"] => NULL
["photoId"] => NULL
["path"] => string(60)
"C:\wamp\www\vhosts\public\assets/client/8/video/"
["fileName"] => string(6) "58.flv"
["videoId"] => int(8)
}
[1] => array(11) {
["assetId"] => NULL
["clientId"] => int(8)
["assetType"] => string(5) "photo"
["description"] => string(5) "test2"
["timestamp"] => string(19) "2010-09-22 14:05:06"
["textId"] => NULL
["content"] => NULL
["photoId"] => int(14)
["path"] => NULL
["fileName"] => NULL
["videoId"] => NULL
}
[2] => array(11) {
["assetId"] => NULL
["clientId"] => int(8)
["assetType"] => string(4) "text"
["description"] => string(5) "test1"
["timestamp"] => string(19) "2010-09-22 13:41:34"
["textId"] => int(12)
["content"] => string(5) "test1"
["photoId"] => NULL
["path"] => NULL
["fileName"] => NULL
["videoId"] => NULL
}

"assetId" should not be NULL in case 1 or 2 above.

A quick copy from the "echo $query->__toString();" and paste to phpmyadmin
confirmed that it is indeed a good query. The data in phpmyadmin is exactly
what I would expect. Including the correct assetId's

//////////////////////////////////////////DATA
BELOW////////////////////////////////////////

object(Zend_Db_Table_Select)#88 (7) {
["_info":protected] => array(10) {
["schema"] => NULL
["name"] => string(6) "assets"
["cols"] => array(5) {
[0] => string(7) "assetId"
[1] => string(8) "clientId"
[2] => string(9) "assetType"
[3] => string(11) "description"
[4] => string(9) "timestamp"
}
["primary"] => array(1) {
[1] => string(7) "assetId"
}
["metadata"] => array(5) {
["assetId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(7) "assetId"
["COLUMN_POSITION"] => int(1)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(true)
["PRIMARY_POSITION"] => int(1)
["IDENTITY"] => bool(true)
}
["clientId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(8) "clientId"
["COLUMN_POSITION"] => int(2)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["assetType"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "assetType"
["COLUMN_POSITION"] => int(3)
["DATA_TYPE"] => string(28) "enum('text','video','photo')"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["description"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(11) "description"
["COLUMN_POSITION"] => int(4)
["DATA_TYPE"] => string(7) "varchar"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => string(2) "50"
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["timestamp"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "timestamp"
["COLUMN_POSITION"] => int(5)
["DATA_TYPE"] => string(9) "timestamp"
["DEFAULT"] => string(17) "CURRENT_TIMESTAMP"
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
}
["rowClass"] => string(33) "Application_Model_Table_Row_Asset"
["rowsetClass"] => string(20) "Zend_Db_Table_Rowset"
["referenceMap"] => array(0) {
}
["dependentTables"] => array(0) {
}
["sequence"] => bool(true)
}
["_integrityCheck":protected] => bool(false)
["_table":protected] => object(Application_Model_Table_Asset)#73 (18) {
["_name":protected] => string(6) "assets"
["_primary":protected] => array(1) {
[1] => string(7) "assetId"
}
["_sequence":protected] => bool(true)
["_rowClass":protected] => string(33)
"Application_Model_Table_Row_Asset"
["_definition":protected] => NULL
["_definitionConfigName":protected] => NULL
["_db":protected] => object(Zend_Db_Adapter_Pdo_Mysql)#55 (12) {
["_pdoType":protected] => string(5) "mysql"
["_numericDataTypes":protected] => array(16) {
[0] => int(0)
[1] => int(1)
[2] => int(2)
["INT"] => int(0)
["INTEGER"] => int(0)
["MEDIUMINT"] => int(0)
["SMALLINT"] => int(0)
["TINYINT"] => int(0)
["BIGINT"] => int(1)
["SERIAL"] => int(1)
["DEC"] => int(2)
["DECIMAL"] => int(2)
["DOUBLE"] => int(2)
["DOUBLE PRECISION"] => int(2)
["FIXED"] => int(2)
["FLOAT"] => int(2)
}
["_defaultStmtClass":protected] => string(21) "Zend_Db_Statement_Pdo"
["_config":protected] => array(8) {
["dbname"] => string(8) "<deleted manually -KMS>"
["username"] => string(4) "<deleted manually -KMS>"
["password"] => string(0) "<deleted manually -KMS>"
["host"] => string(9) "<deleted manually -KMS>"
["charset"] => string(4) "UTF8"
["persistent"] => bool(false)
["options"] => array(2) {
["caseFolding"] => int(0)
["autoQuoteIdentifiers"] => bool(true)
}
["driver_options"] => array(1) {
[1002] => int(0)
}
}
["_fetchMode":protected] => int(2)
["_profiler":protected] => object(Zend_Db_Profiler)#56 (4) {
["_queryProfiles":protected] => array(0) {
}
["_enabled":protected] => bool(false)
["_filterElapsedSecs":protected] => NULL
["_filterTypes":protected] => NULL
}
["_defaultProfilerClass":protected] => string(16) "Zend_Db_Profiler"
["_connection":protected] => object(PDO)#87 (0) {
}
["_caseFolding":protected] => int(0)
["_autoQuoteIdentifiers":protected] => bool(true)
["_allowSerialization":protected] => bool(true)
["_autoReconnectOnUnserialize":protected] => bool(false)
}
["_schema":protected] => NULL
["_cols":protected] => array(5) {
[0] => string(7) "assetId"
[1] => string(8) "clientId"
[2] => string(9) "assetType"
[3] => string(11) "description"
[4] => string(9) "timestamp"
}
["_identity":protected] => int(1)
["_metadata":protected] => array(5) {
["assetId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(7) "assetId"
["COLUMN_POSITION"] => int(1)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(true)
["PRIMARY_POSITION"] => int(1)
["IDENTITY"] => bool(true)
}
["clientId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(8) "clientId"
["COLUMN_POSITION"] => int(2)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["assetType"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "assetType"
["COLUMN_POSITION"] => int(3)
["DATA_TYPE"] => string(28) "enum('text','video','photo')"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["description"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(11) "description"
["COLUMN_POSITION"] => int(4)
["DATA_TYPE"] => string(7) "varchar"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => string(2) "50"
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["timestamp"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "timestamp"
["COLUMN_POSITION"] => int(5)
["DATA_TYPE"] => string(9) "timestamp"
["DEFAULT"] => string(17) "CURRENT_TIMESTAMP"
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
}
["_metadataCache":protected] => NULL
["_metadataCacheInClass":protected] => bool(true)
["_rowsetClass":protected] => string(20) "Zend_Db_Table_Rowset"
["_referenceMap":protected] => array(0) {
}
["_dependentTables":protected] => array(0) {
}
["_defaultSource":protected] => string(11) "defaultNone"
["_defaultValues":protected] => array(0) {
}
}
["_bind":protected] => array(0) {
}
["_adapter":protected] => object(Zend_Db_Adapter_Pdo_Mysql)#55 (12) {
["_pdoType":protected] => string(5) "mysql"
["_numericDataTypes":protected] => array(16) {
[0] => int(0)
[1] => int(1)
[2] => int(2)
["INT"] => int(0)
["INTEGER"] => int(0)
["MEDIUMINT"] => int(0)
["SMALLINT"] => int(0)
["TINYINT"] => int(0)
["BIGINT"] => int(1)
["SERIAL"] => int(1)
["DEC"] => int(2)
["DECIMAL"] => int(2)
["DOUBLE"] => int(2)
["DOUBLE PRECISION"] => int(2)
["FIXED"] => int(2)
["FLOAT"] => int(2)
}
["_defaultStmtClass":protected] => string(21) "Zend_Db_Statement_Pdo"
["_config":protected] => array(8) {
["dbname"] => string(8) "<deleted dbname manually -KMS>"
["username"] => string(4) "<deleted dbname manually -KMS>"
["password"] => string(0) "<deleted dbname manually -KMS>"
["host"] => string(9) "<deleted dbname manually -KMS>"
["charset"] => string(4) "UTF8"
["persistent"] => bool(false)
["options"] => array(2) {
["caseFolding"] => int(0)
["autoQuoteIdentifiers"] => bool(true)
}
["driver_options"] => array(1) {
[1002] => int(0)
}
}
["_fetchMode":protected] => int(2)
["_profiler":protected] => object(Zend_Db_Profiler)#56 (4) {
["_queryProfiles":protected] => array(0) {
}
["_enabled":protected] => bool(false)
["_filterElapsedSecs":protected] => NULL
["_filterTypes":protected] => NULL
}
["_defaultProfilerClass":protected] => string(16) "Zend_Db_Profiler"
["_connection":protected] => object(PDO)#87 (0) {
}
["_caseFolding":protected] => int(0)
["_autoQuoteIdentifiers":protected] => bool(true)
["_allowSerialization":protected] => bool(true)
["_autoReconnectOnUnserialize":protected] => bool(false)
}
["_parts":protected] => array(11) {
["distinct"] => bool(false)
["columns"] => array(4) {
[0] => array(3) {
[0] => string(1) "a"
[1] => string(1) "*"
[2] => NULL
}
[1] => array(3) {
[0] => string(1) "t"
[1] => string(1) "*"
[2] => NULL
}
[2] => array(3) {
[0] => string(1) "p"
[1] => string(1) "*"
[2] => NULL
}
[3] => array(3) {
[0] => string(1) "v"
[1] => string(1) "*"
[2] => NULL
}
}
["union"] => array(0) {
}
["from"] => array(4) {
["a"] => array(4) {
["joinType"] => string(10) "inner join"
["schema"] => NULL
["tableName"] => string(6) "assets"
["joinCondition"] => NULL
}
["t"] => array(4) {
["joinType"] => string(9) "left join"
["schema"] => NULL
["tableName"] => string(5) "texts"
["joinCondition"] => string(21) "a.assetId = t.assetId"
}
["p"] => array(4) {
["joinType"] => string(9) "left join"
["schema"] => NULL
["tableName"] => string(6) "photos"
["joinCondition"] => string(21) "a.assetId = p.assetId"
}
["v"] => array(4) {
["joinType"] => string(9) "left join"
["schema"] => NULL
["tableName"] => string(6) "videos"
["joinCondition"] => string(21) "a.assetId = v.assetId"
}
}
["where"] => array(0) {
}
["group"] => array(0) {
}
["having"] => array(0) {
}
["order"] => array(0) {
}
["limitcount"] => NULL
["limitoffset"] => NULL
["forupdate"] => bool(false)
}
["_tableCols":protected] => array(0) {
}
}

SELECT `a`.*, `t`.*, `p`.*, `v`.*
FROM `assets` AS `a`
LEFT JOIN `texts` AS `t` ON a.assetId = t.assetId
LEFT JOIN `photos` AS `p` ON a.assetId = p.assetId
LEFT JOIN `videos` AS `v` ON a.assetId = v.assetId

object(Zend_Db_Table_Rowset)#72 (10) {
["_data":protected] => array(3) {
[0] => array(11) {
["assetId"] => int(58)
["clientId"] => int(8)
["assetType"] => string(5) "video"
["description"] => string(5) "test3"
["timestamp"] => string(19) "2010-09-22 14:05:19"
["textId"] => NULL
["content"] => NULL
["photoId"] => NULL
["path"] => string(60)
"C:\wamp\www\vhosts\public\assets/client/8/video/"
["fileName"] => string(6) "58.flv"
["videoId"] => int(8)
}
[1] => array(11) {
["assetId"] => NULL
["clientId"] => int(8)
["assetType"] => string(5) "photo"
["description"] => string(5) "test2"
["timestamp"] => string(19) "2010-09-22 14:05:06"
["textId"] => NULL
["content"] => NULL
["photoId"] => int(14)
["path"] => NULL
["fileName"] => NULL
["videoId"] => NULL
}
[2] => array(11) {
["assetId"] => NULL
["clientId"] => int(8)
["assetType"] => string(4) "text"
["description"] => string(5) "test1"
["timestamp"] => string(19) "2010-09-22 13:41:34"
["textId"] => int(12)
["content"] => string(5) "test1"
["photoId"] => NULL
["path"] => NULL
["fileName"] => NULL
["videoId"] => NULL
}

"assetId" should not be NULL in either case! (note that the

A quick copy and paste from the "echo $query->__toString();" to phpmyadmin
confirmed that it is indeed a good query. The data in phpmyadmin is exactly
what I would expect.

Every table should have an assetId, clientId, description, and timestamp.
The rest of the data could be null.

The database schema is fairly easy. I have an 'assets' table that has a
one-to-one relationship to either a 'photo', 'video', or 'text' table.

//////////////////////////////////////////DATA
BELOW////////////////////////////////////////

object(Zend_Db_Table_Select)#88 (7) {
["_info":protected] => array(10) {
["schema"] => NULL
["name"] => string(6) "assets"
["cols"] => array(5) {
[0] => string(7) "assetId"
[1] => string(8) "clientId"
[2] => string(9) "assetType"
[3] => string(11) "description"
[4] => string(9) "timestamp"
}
["primary"] => array(1) {
[1] => string(7) "assetId"
}
["metadata"] => array(5) {
["assetId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(7) "assetId"
["COLUMN_POSITION"] => int(1)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(true)
["PRIMARY_POSITION"] => int(1)
["IDENTITY"] => bool(true)
}
["clientId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(8) "clientId"
["COLUMN_POSITION"] => int(2)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["assetType"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "assetType"
["COLUMN_POSITION"] => int(3)
["DATA_TYPE"] => string(28) "enum('text','video','photo')"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["description"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(11) "description"
["COLUMN_POSITION"] => int(4)
["DATA_TYPE"] => string(7) "varchar"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => string(2) "50"
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["timestamp"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "timestamp"
["COLUMN_POSITION"] => int(5)
["DATA_TYPE"] => string(9) "timestamp"
["DEFAULT"] => string(17) "CURRENT_TIMESTAMP"
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
}
["rowClass"] => string(33) "Application_Model_Table_Row_Asset"
["rowsetClass"] => string(20) "Zend_Db_Table_Rowset"
["referenceMap"] => array(0) {
}
["dependentTables"] => array(0) {
}
["sequence"] => bool(true)
}
["_integrityCheck":protected] => bool(false)
["_table":protected] => object(Application_Model_Table_Asset)#73 (18) {
["_name":protected] => string(6) "assets"
["_primary":protected] => array(1) {
[1] => string(7) "assetId"
}
["_sequence":protected] => bool(true)
["_rowClass":protected] => string(33)
"Application_Model_Table_Row_Asset"
["_definition":protected] => NULL
["_definitionConfigName":protected] => NULL
["_db":protected] => object(Zend_Db_Adapter_Pdo_Mysql)#55 (12) {
["_pdoType":protected] => string(5) "mysql"
["_numericDataTypes":protected] => array(16) {
[0] => int(0)
[1] => int(1)
[2] => int(2)
["INT"] => int(0)
["INTEGER"] => int(0)
["MEDIUMINT"] => int(0)
["SMALLINT"] => int(0)
["TINYINT"] => int(0)
["BIGINT"] => int(1)
["SERIAL"] => int(1)
["DEC"] => int(2)
["DECIMAL"] => int(2)
["DOUBLE"] => int(2)
["DOUBLE PRECISION"] => int(2)
["FIXED"] => int(2)
["FLOAT"] => int(2)
}
["_defaultStmtClass":protected] => string(21) "Zend_Db_Statement_Pdo"
["_config":protected] => array(8) {
["dbname"] => string(8) "<deleted manually -KMS>"
["username"] => string(4) "<deleted manually -KMS>"
["password"] => string(0) "<deleted manually -KMS>"
["host"] => string(9) "<deleted manually -KMS>"
["charset"] => string(4) "UTF8"
["persistent"] => bool(false)
["options"] => array(2) {
["caseFolding"] => int(0)
["autoQuoteIdentifiers"] => bool(true)
}
["driver_options"] => array(1) {
[1002] => int(0)
}
}
["_fetchMode":protected] => int(2)
["_profiler":protected] => object(Zend_Db_Profiler)#56 (4) {
["_queryProfiles":protected] => array(0) {
}
["_enabled":protected] => bool(false)
["_filterElapsedSecs":protected] => NULL
["_filterTypes":protected] => NULL
}
["_defaultProfilerClass":protected] => string(16) "Zend_Db_Profiler"
["_connection":protected] => object(PDO)#87 (0) {
}
["_caseFolding":protected] => int(0)
["_autoQuoteIdentifiers":protected] => bool(true)
["_allowSerialization":protected] => bool(true)
["_autoReconnectOnUnserialize":protected] => bool(false)
}
["_schema":protected] => NULL
["_cols":protected] => array(5) {
[0] => string(7) "assetId"
[1] => string(8) "clientId"
[2] => string(9) "assetType"
[3] => string(11) "description"
[4] => string(9) "timestamp"
}
["_identity":protected] => int(1)
["_metadata":protected] => array(5) {
["assetId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(7) "assetId"
["COLUMN_POSITION"] => int(1)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(true)
["PRIMARY_POSITION"] => int(1)
["IDENTITY"] => bool(true)
}
["clientId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(8) "clientId"
["COLUMN_POSITION"] => int(2)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["assetType"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "assetType"
["COLUMN_POSITION"] => int(3)
["DATA_TYPE"] => string(28) "enum('text','video','photo')"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["description"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(11) "description"
["COLUMN_POSITION"] => int(4)
["DATA_TYPE"] => string(7) "varchar"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => string(2) "50"
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["timestamp"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "timestamp"
["COLUMN_POSITION"] => int(5)
["DATA_TYPE"] => string(9) "timestamp"
["DEFAULT"] => string(17) "CURRENT_TIMESTAMP"
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
}
["_metadataCache":protected] => NULL
["_metadataCacheInClass":protected] => bool(true)
["_rowsetClass":protected] => string(20) "Zend_Db_Table_Rowset"
["_referenceMap":protected] => array(0) {
}
["_dependentTables":protected] => array(0) {
}
["_defaultSource":protected] => string(11) "defaultNone"
["_defaultValues":protected] => array(0) {
}
}
["_bind":protected] => array(0) {
}
["_adapter":protected] => object(Zend_Db_Adapter_Pdo_Mysql)#55 (12) {
["_pdoType":protected] => string(5) "mysql"
["_numericDataTypes":protected] => array(16) {
[0] => int(0)
[1] => int(1)
[2] => int(2)
["INT"] => int(0)
["INTEGER"] => int(0)
["MEDIUMINT"] => int(0)
["SMALLINT"] => int(0)
["TINYINT"] => int(0)
["BIGINT"] => int(1)
["SERIAL"] => int(1)
["DEC"] => int(2)
["DECIMAL"] => int(2)
["DOUBLE"] => int(2)
["DOUBLE PRECISION"] => int(2)
["FIXED"] => int(2)
["FLOAT"] => int(2)
}
["_defaultStmtClass":protected] => string(21) "Zend_Db_Statement_Pdo"
["_config":protected] => array(8) {
["dbname"] => string(8) "<deleted dbname manually -KMS>"
["username"] => string(4) "<deleted dbname manually -KMS>"
["password"] => string(0) "<deleted dbname manually -KMS>"
["host"] => string(9) "<deleted dbname manually -KMS>"
["charset"] => string(4) "UTF8"
["persistent"] => bool(false)
["options"] => array(2) {
["caseFolding"] => int(0)
["autoQuoteIdentifiers"] => bool(true)
}
["driver_options"] => array(1) {
[1002] => int(0)
}
}
["_fetchMode":protected] => int(2)
["_profiler":protected] => object(Zend_Db_Profiler)#56 (4) {
["_queryProfiles":protected] => array(0) {
}
["_enabled":protected] => bool(false)
["_filterElapsedSecs":protected] => NULL
["_filterTypes":protected] => NULL
}
["_defaultProfilerClass":protected] => string(16) "Zend_Db_Profiler"
["_connection":protected] => object(PDO)#87 (0) {
}
["_caseFolding":protected] => int(0)
["_autoQuoteIdentifiers":protected] => bool(true)
["_allowSerialization":protected] => bool(true)
["_autoReconnectOnUnserialize":protected] => bool(false)
}
["_parts":protected] => array(11) {
["distinct"] => bool(false)
["columns"] => array(4) {
[0] => array(3) {
[0] => string(1) "a"
[1] => string(1) "*"
[2] => NULL
}
[1] => array(3) {
[0] => string(1) "t"
[1] => string(1) "*"
[2] => NULL
}
[2] => array(3) {
[0] => string(1) "p"
[1] => string(1) "*"
[2] => NULL
}
[3] => array(3) {
[0] => string(1) "v"
[1] => string(1) "*"
[2] => NULL
}
}
["union"] => array(0) {
}
["from"] => array(4) {
["a"] => array(4) {
["joinType"] => string(10) "inner join"
["schema"] => NULL
["tableName"] => string(6) "assets"
["joinCondition"] => NULL
}
["t"] => array(4) {
["joinType"] => string(9) "left join"
["schema"] => NULL
["tableName"] => string(5) "texts"
["joinCondition"] => string(21) "a.assetId = t.assetId"
}
["p"] => array(4) {
["joinType"] => string(9) "left join"
["schema"] => NULL
["tableName"] => string(6) "photos"
["joinCondition"] => string(21) "a.assetId = p.assetId"
}
["v"] => array(4) {
["joinType"] => string(9) "left join"
["schema"] => NULL
["tableName"] => string(6) "videos"
["joinCondition"] => string(21) "a.assetId = v.assetId"
}
}
["where"] => array(0) {
}
["group"] => array(0) {
}
["having"] => array(0) {
}
["order"] => array(0) {
}
["limitcount"] => NULL
["limitoffset"] => NULL
["forupdate"] => bool(false)
}
["_tableCols":protected] => array(0) {
}
}

SELECT `a`.*, `t`.*, `p`.*, `v`.*
FROM `assets` AS `a`
LEFT JOIN `texts` AS `t` ON a.assetId = t.assetId
LEFT JOIN `photos` AS `p` ON a.assetId = p.assetId
LEFT JOIN `videos` AS `v` ON a.assetId = v.assetId

object(Zend_Db_Table_Rowset)#72 (10) {
["_data":protected] => array(3) {
[0] => array(11) {
["assetId"] => int(58)
["clientId"] => int(8)
["assetType"] => string(5) "video"
["description"] => string(5) "test3"
["timestamp"] => string(19) "2010-09-22 14:05:19"
["textId"] => NULL
["content"] => NULL
["photoId"] => NULL
["path"] => string(60)
"C:\wamp\www\vhosts\public\assets/client/8/video/"
["fileName"] => string(6) "58.flv"
["videoId"] => int(8)
}
[1] => array(11) {
["assetId"] => NULL
["clientId"] => int(8)
["assetType"] => string(5) "photo"
["description"] => string(5) "test2"
["timestamp"] => string(19) "2010-09-22 14:05:06"
["textId"] => NULL
["content"] => NULL
["photoId"] => int(14)
["path"] => NULL
["fileName"] => NULL
["videoId"] => NULL
}
[2] => array(11) {
["assetId"] => NULL
["clientId"] => int(8)
["assetType"] => string(4) "text"
["description"] => string(5) "test1"
["timestamp"] => string(19) "2010-09-22 13:41:34"
["textId"] => int(12)
["content"] => string(5) "test1"
["photoId"] => NULL
["path"] => NULL
["fileName"] => NULL
["videoId"] => NULL
}
}
["_table":protected] => object(Application_Model_Table_Asset)#73 (18) {
["_name":protected] => string(6) "assets"
["_primary":protected] => array(1) {
[1] => string(7) "assetId"
}
["_sequence":protected] => bool(true)
["_rowClass":protected] => string(33)
"Application_Model_Table_Row_Asset"
["_definition":protected] => NULL
["_definitionConfigName":protected] => NULL
["_db":protected] => object(Zend_Db_Adapter_Pdo_Mysql)#55 (12) {
["_pdoType":protected] => string(5) "mysql"
["_numericDataTypes":protected] => array(16) {
[0] => int(0)
[1] => int(1)
[2] => int(2)
["INT"] => int(0)
["INTEGER"] => int(0)
["MEDIUMINT"] => int(0)
["SMALLINT"] => int(0)
["TINYINT"] => int(0)
["BIGINT"] => int(1)
["SERIAL"] => int(1)
["DEC"] => int(2)
["DECIMAL"] => int(2)
["DOUBLE"] => int(2)
["DOUBLE PRECISION"] => int(2)
["FIXED"] => int(2)
["FLOAT"] => int(2)
}
["_defaultStmtClass":protected] => string(21) "Zend_Db_Statement_Pdo"
["_config":protected] => array(8) {
["dbname"] => string(8) "<deleted dbname manually -KMS>"
["username"] => string(4) "<deleted dbname manually -KMS>"
["password"] => string(0) "<deleted dbname manually -KMS>"
["host"] => string(9) "<deleted dbname manually -KMS>"
["charset"] => string(4) "UTF8"
["persistent"] => bool(false)
["options"] => array(2) {
["caseFolding"] => int(0)
["autoQuoteIdentifiers"] => bool(true)
}
["driver_options"] => array(1) {
[1002] => int(0)
}
}
["_fetchMode":protected] => int(2)
["_profiler":protected] => object(Zend_Db_Profiler)#56 (4) {
["_queryProfiles":protected] => array(0) {
}
["_enabled":protected] => bool(false)
["_filterElapsedSecs":protected] => NULL
["_filterTypes":protected] => NULL
}
["_defaultProfilerClass":protected] => string(16) "Zend_Db_Profiler"
["_connection":protected] => object(PDO)#87 (0) {
}
["_caseFolding":protected] => int(0)
["_autoQuoteIdentifiers":protected] => bool(true)
["_allowSerialization":protected] => bool(true)
["_autoReconnectOnUnserialize":protected] => bool(false)
}
["_schema":protected] => NULL
["_cols":protected] => array(5) {
[0] => string(7) "assetId"
[1] => string(8) "clientId"
[2] => string(9) "assetType"
[3] => string(11) "description"
[4] => string(9) "timestamp"
}
["_identity":protected] => int(1)
["_metadata":protected] => array(5) {
["assetId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(7) "assetId"
["COLUMN_POSITION"] => int(1)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(true)
["PRIMARY_POSITION"] => int(1)
["IDENTITY"] => bool(true)
}
["clientId"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(8) "clientId"
["COLUMN_POSITION"] => int(2)
["DATA_TYPE"] => string(3) "int"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["assetType"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "assetType"
["COLUMN_POSITION"] => int(3)
["DATA_TYPE"] => string(28) "enum('text','video','photo')"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["description"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(11) "description"
["COLUMN_POSITION"] => int(4)
["DATA_TYPE"] => string(7) "varchar"
["DEFAULT"] => NULL
["NULLABLE"] => bool(false)
["LENGTH"] => string(2) "50"
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
["timestamp"] => array(14) {
["SCHEMA_NAME"] => NULL
["TABLE_NAME"] => string(6) "assets"
["COLUMN_NAME"] => string(9) "timestamp"
["COLUMN_POSITION"] => int(5)
["DATA_TYPE"] => string(9) "timestamp"
["DEFAULT"] => string(17) "CURRENT_TIMESTAMP"
["NULLABLE"] => bool(false)
["LENGTH"] => NULL
["SCALE"] => NULL
["PRECISION"] => NULL
["UNSIGNED"] => NULL
["PRIMARY"] => bool(false)
["PRIMARY_POSITION"] => NULL
["IDENTITY"] => bool(false)
}
}
["_metadataCache":protected] => NULL
["_metadataCacheInClass":protected] => bool(true)
["_rowsetClass":protected] => string(20) "Zend_Db_Table_Rowset"
["_referenceMap":protected] => array(0) {
}
["_dependentTables":protected] => array(0) {
}
["_defaultSource":protected] => string(11) "defaultNone"
["_defaultValues":protected] => array(0) {
}
}
["_connected":protected] => bool(true)
["_tableClass":protected] => string(29) "Application_Model_Table_Asset"
["_rowClass":protected] => string(33) "Application_Model_Table_Row_Asset"
["_pointer":protected] => int(0)
["_count":protected] => int(3)
["_rows":protected] => array(0) {
}
["_stored":protected] => bool(true)
["_readOnly":protected] => bool(false)
}


--
View this message in context: http://zend-framework-community.634137.n4.nabble.com/Help-With-Multitable-Left-Joins-tp2551174p2551174.html
Sent from the Zend DB mailing list archive at Nabble.com.

没有评论: