PHP Zend_Db_Select::joinLeft方法代码示例

 /**
  * Add attribute to filter
  *
  * @param int $storeId
  * @param string $attributeCode
  * @param mixed $value
  * @param string $type
  * @return Zend_Db_Select
  */
 protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
 {
     if (!isset($this->_attributesCache[$attributeCode])) {
         $attribute = Mage::getSingleton('catalog/product')->getResource()->getAttribute($attributeCode);
         $this->_attributesCache[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal() == Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL, 'backend_type' => $attribute->getBackendType());
     }
     $attribute = $this->_attributesCache[$attributeCode];
     if (!$this->_select instanceof Zend_Db_Select) {
         return false;
     }
     switch ($type) {
         case '=':
             $conditionRule = '=?';
             break;
         case 'in':
             $conditionRule = ' IN(?)';
             break;
         default:
             return false;
             break;
     }
     $this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'r.entity_pk_value=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
     if ($attribute['is_global']) {
         $this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
     } else {
         $ifCase = $this->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
         $this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), array())->where('(' . $ifCase . ')' . $conditionRule, $value);
     }
     return $this->_select;
 }

 /**
  *
  * @param Zend_Db_Select $select
  * @param int $store_id
  * @return Zend_Db_Adapter_Mysqli
  */
 public function addVisibileFilterToCR(&$select, $store_id = 0)
 {
     if ($this->getAttribute()->isScopeGlobal()) {
         $tableName = $this->getAttribute()->getAttributeCode() . '_t';
         $select->joinLeft(array($tableName => $this->getAttribute()->getBackend()->getTable()), "`p`.`product_id`=`{$tableName}`.`entity_id`" . " AND `{$tableName}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$tableName}`.`store_id`='0'", array());
         $valueExpr = $tableName . '.value';
     } else {
         $valueTable1 = $this->getAttribute()->getAttributeCode() . '_t1';
         $valueTable2 = $this->getAttribute()->getAttributeCode() . '_t2';
         $select->joinLeft(array($valueTable1 => $this->getAttribute()->getBackend()->getTable()), "`p`.`product_id`=`{$valueTable1}`.`entity_id`" . " AND `{$valueTable1}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable1}`.`store_id`='0'", array())->joinLeft(array($valueTable2 => $this->getAttribute()->getBackend()->getTable()), "`p`.`product_id`=`{$valueTable2}`.`entity_id`" . " AND `{$valueTable2}`.`attribute_id`='{$this->getAttribute()->getId()}'" . " AND `{$valueTable2}`.`store_id`='{$store_id}'", array());
         $valueExpr = new Zend_Db_Expr("IF(`{$valueTable2}`.`value_id`>0, `{$valueTable2}`.`value`, `{$valueTable1}`.`value`)");
     }
     $select->where($valueExpr . " IN (?)", $this->getVisibleInSiteIds());
     return $this;
 }

 /**
  * Perform the correct join from given object property and return the table alias
  * @todo test and implement in various places in find() method
  * @param Property\ObjectProperty $property
  * @param string $table
  * @return string
  */
 protected function _join(Property\ObjectProperty $property, $table)
 {
     $join = array();
     $class = $property->getParameter('instanceof');
     $stable = $this->_getTableFromClass($class);
     $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($property->getParent()->getDataObject()->getClass(), $property->getId()) : $property->getId();
     $uniqext = $stable . '__joined_for__' . $leftkey;
     if (!isset($this->_alreadyJoined[$stable])) {
         $sbackend = ObjectModel::getObjectBackend($class);
         if ($sbackend->getAlias() != $this->getAlias()) {
             // @todo raise and exception if backends are not of same type
             // We presume that the current backend is allowed to connect to the remote one
             // Should we raise an exception instead ?
             $stable = $sbackend->getUri()->getDatabase() . '.' . $stable;
         }
         $field = $property->getId();
         $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY;
         if (is_array($rightkey)) {
             foreach ($rightkey as $rightkeyObj) {
                 // @todo fix left key that should be provided by mapper
                 $join[] = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName());
             }
         } else {
             $join[] = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey);
         }
         $this->_select->joinLeft("{$stable} AS {$uniqext}", implode(' AND ', $join), array());
         $this->_alreadyJoined[$stable] = $uniqext;
     }
     return $uniqext;
 }

 /**
  * appends sql to given select statement
  *
  * @param  Zend_Db_Select                    $_select
  * @param  Tinebase_Backend_Sql_Abstract     $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     $correlationName = Tinebase_Record_Abstract::generateUID(30);
     $db = $_backend->getAdapter();
     $_select->joinLeft(array($correlationName => $db->table_prefix . 'adb_list_m_role'), $db->quoteIdentifier($correlationName . '.contact_id') . ' = ' . $db->quoteIdentifier('addressbook.id'), array());
     $_select->where($db->quoteIdentifier($correlationName . '.list_role_id') . ' IN (?)', (array) $this->_value);
 }

 /**
  * Add attribute to filter
  *
  * @param int $storeId
  * @param string $attributeCode
  * @param mixed $value
  * @param string $type
  * @return Zend_Db_Select
  */
 protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
 {
     if (!isset($this->_attributesCache[$attributeCode])) {
         $this->_loadAttribute($attributeCode);
     }
     $attribute = $this->_attributesCache[$attributeCode];
     if (!$this->_select instanceof Zend_Db_Select) {
         return false;
     }
     switch ($type) {
         case '=':
             $conditionRule = '=?';
             break;
         case 'in':
             $conditionRule = ' IN(?)';
             break;
         default:
             return false;
             break;
     }
     if ($attribute['backend_type'] == 'static') {
         $this->_select->where('main_table.' . $attributeCode . $conditionRule, $value);
     } else {
         $this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'main_table.entity_id=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
         if ($attribute['is_global']) {
             $this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
         } else {
             $ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
             $this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id = ?', $storeId), array())->where('(' . $ifCase . ')' . $conditionRule, $value);
         }
     }
     return $this->_select;
 }

 /**
  * Add attribute to filter
  *
  * @param int $storeId
  * @param string $attributeCode
  * @param mixed $value
  * @param string $type
  * @return \Zend_Db_Select|bool
  */
 protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
 {
     if (!$this->_select instanceof \Zend_Db_Select) {
         return false;
     }
     if (!isset($this->_attributesCache[$attributeCode])) {
         $attribute = $this->_categoryResource->getAttribute($attributeCode);
         $this->_attributesCache[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType()];
     }
     $attribute = $this->_attributesCache[$attributeCode];
     switch ($type) {
         case '=':
             $conditionRule = '=?';
             break;
         case 'in':
             $conditionRule = ' IN(?)';
             break;
         default:
             return false;
             break;
     }
     if ($attribute['backend_type'] == 'static') {
         $this->_select->where('e.' . $attributeCode . $conditionRule, $value);
     } else {
         $this->_select->join(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id = 0', [])->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
         if ($attribute['is_global']) {
             $this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
         } else {
             $ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
             $this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), [])->where('(' . $ifCase . ')' . $conditionRule, $value);
         }
     }
     return $this->_select;
 }

 /**
  * Add attribute to filter
  *
  * @param int $storeId
  * @param string $attributeCode
  * @param mixed $value
  * @param string $type
  *
  * @return Zend_Db_Select
  */
 protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
 {
     if (!isset($this->_attributesCache[$attributeCode])) {
         $attribute = Mage::getSingleton('catalog/category')->getResource()->getAttribute($attributeCode);
         $this->_attributesCache[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType());
     }
     $attribute = $this->_attributesCache[$attributeCode];
     if (!$this->_select instanceof Zend_Db_Select) {
         return false;
     }
     switch ($type) {
         case '=':
             $conditionRule = '=?';
             break;
         case 'in':
             $conditionRule = ' IN(?)';
             break;
         default:
             return false;
             break;
     }
     if ($attribute['backend_type'] == 'static') {
         $this->_select->where('e.' . $attributeCode . $conditionRule, $value);
     } else {
         $this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'e.entity_id=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
         if ($attribute['is_global']) {
             $this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
         } else {
             $this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), array())->where('IFNULL(t2_' . $attributeCode . '.value, t1_' . $attributeCode . '.value)' . $conditionRule, $value);
         }
     }
     return $this->_select;
 }

 public function getSelectFilesByJobId($jobid)
 {
     // do Bacula ACLs
     Zend_Loader::loadClass('Job');
     $table = new Job();
     if (!$table->isJobIdExists($jobid)) {
         return FALSE;
     }
     // !!! IMPORTANT !!! с Zend Paginator нельзя использовать DISTINCT иначе не работает в PDO_PGSQL
     $select = new Zend_Db_Select($this->db);
     $select->from(array('f' => 'File'), array('FileId', 'FileIndex', 'LStat'));
     $select->joinLeft(array('p' => 'Path'), 'f.PathId = p.PathId', array('Path'));
     $select->joinLeft(array('n' => 'Filename'), 'f.FileNameId = n.FileNameId', array('Name'));
     $select->where("f.JobId = ?", $jobid);
     $select->order(array('f.FileIndex', 'f.FileId'));
     return $select;
 }

 /**
  * Join attribute by code
  *
  * @param int $storeId
  * @param string $attributeCode
  * @return void
  */
 protected function _joinAttribute($storeId, $attributeCode)
 {
     $adapter = $this->getReadConnection();
     $attribute = $this->_getAttribute($attributeCode);
     $this->_select->joinLeft(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND ' . $adapter->quoteInto(' t1_' . $attributeCode . '.store_id = ?', \Magento\Store\Model\Store::DEFAULT_STORE_ID) . $adapter->quoteInto(' AND t1_' . $attributeCode . '.attribute_id = ?', $attribute['attribute_id']), []);
     if (!$attribute['is_global']) {
         $this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id = ?', $storeId), []);
     }
 }

 /**
  * Join attribute by code
  *
  * @param int $storeId
  * @param string $attributeCode
  */
 protected function _joinAttribute($storeId, $attributeCode)
 {
     $adapter = $this->getReadConnection();
     $attribute = $this->_getAttribute($attributeCode);
     $this->_select->joinLeft(array('t1_' . $attributeCode => $attribute['table']), 'e.entity_id = t1_' . $attributeCode . '.entity_id AND ' . $adapter->quoteInto(' t1_' . $attributeCode . '.store_id = ?', Mage_Core_Model_App::ADMIN_STORE_ID) . $adapter->quoteInto(' AND t1_' . $attributeCode . '.attribute_id = ?', $attribute['attribute_id']), array());
     if (!$attribute['is_global']) {
         $this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id = ?', $storeId), array());
     }
 }

 /**
  * Get Client name
  *
  * @return Client name, or "" if not exist
  * @param integer $jobid
  */
 function getClientName($jobid)
 {
     $select = new Zend_Db_Select($this->db);
     $select->from(array('j' => 'Job'), array('JobId', 'ClientId'));
     $select->joinLeft(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('c.Name'));
     $select->where("j.JobId = ?", $jobid);
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $stmt = $select->query();
     $res = $stmt->fetchAll();
     return $res[0]['name'];
 }

 /**
  * Join Left
  *
  * Joins a table, without selecting any columns by default and allowing MySQL table alias syntax
  *
  * @access public
  * @param array|string|Zend_Db_Expr $name
  * @param string $cond
  * @param array|string $cols
  * @param string $schema
  * @return $this - Chainable.
  */
 public function joinLeft($name, $cond, $cols = array(), $schema = null)
 {
     $this->_joinHistory[] = array('function' => 'joinLeft', 'args' => func_get_args());
     if (is_string($name) && strpos($name, ' ')) {
         list($table, $alias) = explode(' ', $name);
         $name = array($alias => $table);
     }
     $this->_isJoined = true;
     $this->_select->joinLeft($name, $cond, $cols, $schema);
     $this->_count = false;
     return $this;
 }

 public function fetchAllUsers($order = 'id')
 {
     $select = new Zend_Db_Select($this->db);
     $select->from(array('user1' => 'webacula_users'), array('id', 'login', 'name', 'email', 'create_login', 'last_login', 'last_ip', 'active', 'role_id'));
     $select->joinLeft(array('role1' => 'webacula_roles'), 'user1.role_id = role1.id', array('role_name' => 'name', 'role_id' => 'id'));
     if ($order) {
         $select->order(array($order . ' ASC'));
     }
     //$sql = $select->__toString(); var_dump($sql); exit; // for !!!debug!!!
     $stmt = $select->query();
     $result = $stmt->fetchAll();
     return $result;
 }

 /**
  * Get info Volumes with Status of media: Disabled, Error
  *
  */
 function getProblemVolumes($order = null)
 {
     $db = Zend_Registry::get('db_bacula');
     // make select from multiple tables
     $select = new Zend_Db_Select($db);
     $select->distinct();
     $select->from(array('m' => 'Media'), array("MediaId", 'PoolId', 'StorageId', 'VolumeName', 'VolStatus', 'VolBytes', 'MaxVolBytes', 'VolJobs', 'VolRetention', 'Recycle', 'Slot', 'InChanger', 'MediaType', 'FirstWritten', 'LastWritten'));
     $select->joinLeft(array('p' => 'Pool'), 'm.PoolId = p.PoolId', array('PoolName' => 'p.Name'));
     $select->where("VolStatus IN ('Error', 'Disabled')");
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $result = $select->query()->fetchAll(null, $order);
     // do Bacula ACLs
     return $this->bacula_acl->doBaculaAcl($result, 'poolname', 'pool');
 }

 public function fetchAllRoles()
 {
     /*
        SELECT roles.id, roles.name, inherits.name AS inherit_name
        FROM webacula_roles AS roles
        LEFT JOIN webacula_roles AS inherits ON inherits.id = roles.inherit_id
        ORDER BY roles.inherit_id, roles.order_role ASC
     */
     $select = new Zend_Db_Select($this->db);
     $select->from(array('roles' => 'webacula_roles'), array('id', 'name', 'description', 'order_role', 'inherit_id'));
     $select->joinLeft(array('inherits' => 'webacula_roles'), 'inherits.id = roles.inherit_id', array('inherit_name' => 'name'));
     $select->order(array('roles.order_role, roles.id ASC'));
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $stmt = $select->query();
     return $stmt->fetchAll();
 }