PHP Zend_Db_Select::joinInner方法代码示例

 /**
  * Initialize payment data.
  * replaces method getPayment($id)
  */
 protected function _initData()
 {
     $tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
     $select = new Zend_Db_Select($this->_db);
     $select->from($tbl_prefix . 'payment');
     $select->joinInner($tbl_prefix . 'invoices', $tbl_prefix . "payment.ac_inv_id = " . $tbl_prefix . "invoices.id", NULL);
     $select->joinInner($tbl_prefix . 'customers', $tbl_prefix . "invoices.customer_id = " . $tbl_prefix . "customers.id", array('customer_id' => $tbl_prefix . "customers.id", 'customer' => $tbl_prefix . "customers.name"));
     $select->joinInner($tbl_prefix . 'biller', $tbl_prefix . "invoices.biller_id = " . $tbl_prefix . "biller.id", array('biller_id' => $tbl_prefix . "biller.id", 'biller' => $tbl_prefix . "biller.name"));
     $select->where($tbl_prefix . 'payment.id=?', $this->_id);
     $this->_data = $this->_db->fetchRow($select);
     $this->_data['date'] = siLocal::date($payment['ac_date']);
 }

 /**
  * Get the total paid amount by this customer.
  * Replaces: calc_customer_paid()
  * Enter description here ...
  */
 public function getPaidAmount()
 {
     $tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
     $select = new Zend_Db_Select($this->_db);
     $select->from($tbl_prefix . "payment", array('amount' => new Zend_Db_Expr("COALESCE(SUM(" . $tbl_prefix . "payment.ac_amount), 0)")));
     $select->joinInner($tbl_prefix . "invoices", $tbl_prefix . "payment.ac_inv_id=" . $tbl_prefix . "invoices.id", NULL);
     $select->where($tbl_prefix . "invoices.customer_id=?", $this->_id);
     return $this->_db->fetchOne($select);
 }

 /**
  * Join Inner
  *
  * 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 joinInner($name, $cond, $cols = array(), $schema = null)
 {
     $this->_joinHistory[] = array('function' => 'joinInner', 'args' => func_get_args());
     if (is_string($name) && strpos($name, ' ')) {
         list($table, $alias) = explode(' ', $name);
         $name = array($alias => $table);
     }
     $this->_isJoined = true;
     $this->_select->joinInner($name, $cond, $cols, $schema);
     $this->_count = false;
     return $this;
 }

 protected function _addExtTablesToSelect(Zend_Db_Select &$select)
 {
     foreach ($this->_extTables as $tableName => $info) {
         $select->joinInner($tableName, $info['joinCondition'], $info['fields']);
     }
 }

 /**
  * Get payments for this invoice.
  */
 public function getPayments()
 {
     $tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
     $select = new Zend_Db_Select($this->_db);
     $select->from($tbl_prefix . 'payment');
     $select->joinInner($tbl_prefix . 'invoices', $tbl_prefix . 'payment.ac_inv_id=' . $tbl_prefix . 'invoices.id', NULL);
     $select->joinInner($tbl_prefix . 'customers', $tbl_prefix . 'customers.id=' . $tbl_prefix . 'invoices.customer_id', array('cname' => $tbl_prefix . 'customers.name'));
     $select->joinInner($tbl_prefix . 'biller', $tbl_prefix . 'biller.id=' . $tbl_prefix . 'invoices.biller_id', array('bname' => $tbl_prefix . 'biller.name'));
     $select->where($tbl_prefix . 'payment.ac_inv_id=?', $this->_id);
     $select->order($tbl_prefix . 'payment.id DESC');
     return $this->_db->fetchAll($select);
 }

 /**
  * Join the specified groupscatalog index table to the passed select instance
  *
  * @param Zend_Db_Select $select
  * @param string $table The groupscatalog index table
  * @param int $groupId
  * @param int $storeId
  * @param string $entityField The entity table column where the product or category id is stored
  * @return void
  */
 protected function _addGroupsCatalogFilterToSelect(Zend_Db_Select $select, $table, $groupId, $storeId, $entityField = 'e.entity_id')
 {
     // NOTE to self:
     // Using joinTable() seems to trigger an exception for some users that I can't reproduce (so far).
     // It is related to the flat catalog (Mage_Catalog_Model_Resource_Category_Flat_Collection missing
     // joinTable()). Using getSelect()->joinInner() to work around this issue.
     /*
     $collection->joinTable(
         $helper->getIndexTableByEntityType($entityType), // table
         "catalog_entity_id=entity_id", // primary bind
         array('group_id' => 'group_id', 'store_id' => 'store_id'), // alias to field mappings for the bind cond.
         array( // additional bind conditions (see mappings above)
             'group_id' => $groupId,
             'store_id' => $collection->getStoreId(),
         ),
         'inner' // join type
     );
     */
     // Avoid double joins for the wishlist collection.
     // They clone and clear() the collection each time, but the joins on the
     // collections select objects persist. This is more reliable then setting
     // a flag on the collection object.
     foreach ($select->getPart(Zend_Db_Select::FROM) as $joinedTable) {
         if ($joinedTable['tableName'] == $table) {
             // filter join already applied
             return;
         }
     }
     $select->joinInner($table, "{$table}.catalog_entity_id={$entityField} AND " . $this->_getReadAdapter()->quoteInto("{$table}.group_id=? AND ", $groupId) . $this->_getReadAdapter()->quoteInto("{$table}.store_id=?", $storeId), array());
 }

 /**
  * Build the advanced search.
  * 
  * @param Zend_Db_Select $select
  * @param array $simpleTerms
  */
 protected function _advancedSearch($select, $terms)
 {
     $db = $this->getDb();
     $advancedIndex = 0;
     foreach ($terms as $v) {
         // Do not search on blank rows.
         if (empty($v['element_id']) || empty($v['type'])) {
             continue;
         }
         $value = isset($v['terms']) ? $v['terms'] : null;
         $type = $v['type'];
         $elementId = (int) $v['element_id'];
         $alias = "_advanced_{$advancedIndex}";
         $inner = true;
         $extraJoinCondition = '';
         // Determine what the WHERE clause should look like.
         switch ($type) {
             case 'contains':
                 $predicate = "LIKE " . $db->quote('%' . $value . '%');
                 break;
             case 'is exactly':
                 $predicate = ' = ' . $db->quote($value);
                 break;
             case 'does not contain':
                 $extraJoinCondition = "AND {$alias}.text LIKE " . $db->quote('%' . $value . '%');
             case 'is empty':
                 $inner = false;
                 $predicate = "IS NULL";
                 break;
             case 'is not empty':
                 $predicate = "IS NOT NULL";
                 break;
             default:
                 throw new Omeka_Record_Exception(__('Invalid search type given!'));
         }
         // Note that $elementId was earlier forced to int, so manual quoting
         // is unnecessary here
         $joinCondition = "{$alias}.record_id = items.id AND {$alias}.record_type = 'Item' AND {$alias}.element_id = {$elementId}";
         if ($extraJoinCondition) {
             $joinCondition .= ' ' . $extraJoinCondition;
         }
         if ($inner) {
             $select->joinInner(array($alias => $db->ElementText), $joinCondition, array());
         } else {
             $select->joinLeft(array($alias => $db->ElementText), $joinCondition, array());
         }
         $select->where("{$alias}.text {$predicate}");
         $advancedIndex++;
     }
 }

 /**
  * WARNING: Only columns copying supported.
  * @param Lib_Model_Db_Mysql $model
  * @param string|null $thisKeyCol may contain table prefix or not
  * @param string|null $thatKeyCol may contain table prefix or not
  * @param string $conditions
  * @return $this
  */
 public function joinFrom($model, $thisKeyCol, $thatKeyCol = null, $conditions = '')
 {
     if ($thatKeyCol === null) {
         debug_assert($thisKeyCol !== null);
         $thatKeyCol = $model->getPrimaryKey();
     } elseif ($thisKeyCol === null) {
         debug_assert($thatKeyCol !== null);
         $thisKeyCol = $this->getPrimaryKey();
     }
     $this->prefixColumn($model, $thatKeyCol);
     $this->prefixColumn($this, $thisKeyCol);
     $conditions = str_replace('{that}', $model->getAlias(), $conditions);
     $conditions = str_replace('{this}', $this->getAlias(), $conditions);
     $this->mapPartWhere($this->addAliasToConditionDg());
     $model->mapPartWhere($model->addAliasToConditionDg());
     $thisFrom = $this->_select->getPart(Zend_Db_Select::FROM);
     $modelColumns = array_chain($model->_select->getPart(Zend_Db_Select::COLUMNS), array_group_dg(array_get_dg(return_dg(0))), array_map_val_dg(array_chain_dg(array_map_val_dg(function ($descriptor) {
         return null === $descriptor[2] ? $descriptor[1] : [$descriptor[2] => $descriptor[1]];
     }), function ($columns) {
         $outArray = [];
         array_map_val($columns, function ($column) use(&$outArray) {
             if (is_array($column)) {
                 array_map_val($column, function ($column, $alias) use(&$outArray) {
                     $outArray[$alias] = $column;
                 });
             } else {
                 $outArray[] = $column;
             }
         });
         return $outArray;
     })));
     array_each($model->_select->getPart(Zend_Db_Select::FROM), function ($descriptor, $alias) use($modelColumns, $thisFrom, $model, $thisKeyCol, $thatKeyCol, $conditions) {
         debug_enforce(!array_key_exists($alias, $thisFrom), "Alias `{$alias}` already used for table `{$descriptor['tableName']}`");
         switch ($descriptor['joinType']) {
             case Zend_Db_Select::FROM:
                 $this->_select->joinLeft([$model->getAlias() => $model->getTable()], "{$thisKeyCol}={$thatKeyCol} " . $conditions, array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
                 break;
             case Zend_Db_Select::INNER_JOIN:
                 $this->_select->joinInner([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
                 break;
             case Zend_Db_Select::LEFT_JOIN:
                 $this->_select->joinLeft([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
                 break;
             case Zend_Db_Select::RIGHT_JOIN:
                 $this->_select->joinRight([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
                 break;
             case Zend_Db_Select::FULL_JOIN:
                 $this->_select->joinFull([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
                 break;
             case Zend_Db_Select::CROSS_JOIN:
                 $this->_select->joinCross([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
                 break;
             case Zend_Db_Select::NATURAL_JOIN:
                 $this->_select->joinNatural([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
                 break;
             default:
                 debug_assert(false, "Unknown join type " . var_dump_human_compact($descriptor['joinType']));
                 break;
         }
     });
     $this->settingsJoin($model);
     return $this;
 }

 /**
  * Get Select object (Zend_Db_Select) for sorting table records
  *
  * @param Zend_Db_Select $select
  * @param string $order         
  *
  * @return Zend_Db_Select
  */
 public static function GetSelectForSort($select, $options)
 {
     $aliasTable = '';
     $order = $options['order'];
     //--------------------------
     $arrOrder = explode('.', $order);
     // Если в параметре сортировки не задан псевдоним таблицы
     // то определим его, и если нужно присоединим,
     // соответствующую таблицу
     if (count($arrOrder) == 1) {
         $joinTableForSort = $options['joinTableForSort'];
         if ($joinTableForSort) {
             // Определим какие таблицы уже присоединены
             $fromTables = $select->getPart(Zend_Db_Select::FROM);
             foreach ($fromTables as $alias => $joinParams) {
                 // Если таблица -> $joinTableForSort уже присоединена
                 // то получим ее псевдоним
                 if ($joinParams['tableName'] == $joinTableForSort) {
                     $aliasTable = $alias;
                 }
             }
             if ($aliasTable) {
                 $order = $aliasTable . '.' . $order;
             } else {
                 // Получим поле сортировки
                 $arrOrder = explode(' ', trim($order));
                 $field = $arrOrder[0];
                 // Присоединим таблицу
                 $joinTable = $joinTableForSort;
                 switch ($joinTable) {
                     case 'blog_posts_profile':
                         $select->joinInner(array('p_profile' => $joinTable), 'p_profile.post_id = p.id', array())->where('p_profile.profile_key = ?', $field);
                         $order = 'p_profile.profile_value ' . $arrOrder[1];
                         break;
                     case 'blog_posts_images':
                         $select->joinInner(array('img' => $joinTable), 'img.post_id = p.id', array());
                         $order = 'img.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_audio':
                         $select->joinInner(array('au' => $joinTable), 'au.post_id = p.id', array());
                         $order = 'au.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_video':
                         $select->joinInner(array('v' => $joinTable), 'v.post_id = p.id', array());
                         $order = 'v.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_lacations':
                         $select->joinInner(array('l' => $joinTable), 'l.post_id = p.id', array());
                         $order = 'l.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_tags':
                         $select->joinInner(array('t' => $joinTable), 't.post_id = p.id', array());
                         $order = 't.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_comments':
                         $select->joinInner(array('c' => $joinTable), 'c.post_id = p.id', array());
                         $order = 'c.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'users':
                         $select->joinInner(array('u' => $joinTable), 'u.id = p.user_id', array());
                         $order = 'u.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'users_profile':
                         $select->joinInner(array('u_profile' => $joinTable), 'u_profile.user_id = p.user_id', array())->where('u_profile.profile_key = ?', $field);
                         $order = 'u_profile.profile_value ' . $arrOrder[1];
                         break;
                     default:
                         $order = 'p.' . $field . ' ' . $arrOrder[1];
                         break;
                 }
             }
         } else {
             $order = 'p.' . $order;
         }
     }
     $select->order($order);
     return $select;
 }

 function getLastJobRun($numjob)
 {
     $select = new Zend_Db_Select($this->db);
     $select->distinct();
     $select->limit($numjob);
     switch ($this->db_adapter) {
         case 'PDO_MYSQL':
             $select->from(array('j' => 'Job'), array('JobId', 'Type', 'JobName' => 'Name', 'Level', 'ClientId', 'sortStartTime' => 'StartTime', 'StartTime', 'EndTime', 'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId', 'FileSetId', 'PurgedFiles', 'JobStatus', 'DurationTime' => 'TIMEDIFF(EndTime, StartTime)'));
             $select->joinLeft(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('ClientName' => 'Name'));
             $select->joinLeft(array('p' => 'Pool'), 'j.PoolId = p.PoolId', array('PoolName' => 'Name'));
             $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
             $select->joinInner(array('s' => 'Status'), "j.JobStatus = s.JobStatus", array('JobStatusLong'));
             break;
         case 'PDO_PGSQL':
             // PostgreSQL
             // http://www.postgresql.org/docs/8.0/static/functions-datetime.html
             $select->from(array('j' => 'Job'), array('JobId', 'Type', 'JobName' => 'Name', 'Level', 'ClientId', 'sortStartTime' => 'StartTime', 'StartTime', 'EndTime', 'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId', 'FileSetId', 'PurgedFiles', 'JobStatus', 'DurationTime' => '(EndTime - StartTime)'));
             $select->joinLeft(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('ClientName' => 'Name'));
             $select->joinLeft(array('p' => 'Pool'), 'j.PoolId = p.PoolId', array('PoolName' => 'Name'));
             $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
             $select->joinInner(array('s' => 'Status'), "j.JobStatus = s.JobStatus", array('JobStatusLong'));
             break;
         case 'PDO_SQLITE':
             // SQLite3 Documentation
             // http://sqlite.org/lang_datefunc.html
             // bug http://framework.zend.com/issues/browse/ZF-884
             $select->from(array('j' => 'Job'), array('jobid' => 'JobId', 'type' => 'Type', 'JobName' => 'Name', 'level' => 'Level', 'clientid' => 'ClientId', 'sortStartTime' => 'StartTime', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'volsessionid' => 'VolSessionId', 'volsessiontime' => 'VolSessionTime', 'jobfiles' => 'JobFiles', 'jobbytes' => 'JobBytes', 'joberrors' => 'JobErrors', 'reviewed' => 'Reviewed', 'poolid' => 'PoolId', 'filesetid' => 'FileSetId', 'purgedfiles' => 'PurgedFiles', 'jobstatus' => 'JobStatus', 'DurationTime' => "(strftime('%H:%M:%S',strftime('%s',EndTime) - strftime('%s',StartTime),'unixepoch'))"));
             $select->joinLeft(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('ClientName' => 'Name'));
             $select->joinLeft(array('p' => 'Pool'), 'j.PoolId = p.PoolId', array('PoolName' => 'Name'));
             $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
             $select->joinInner(array('s' => 'Status'), "j.JobStatus = s.JobStatus", array('jobstatuslong' => 'JobStatusLong'));
             break;
     }
     $select->where("j.JobStatus IN ('T', 'E', 'e', 'f', 'A', 'W', 'D')");
     $select->where("j.Type = 'B'");
     $select->order(array("sortStartTime DESC"));
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // !!!debug
     $stmt = $select->query();
     // do Bacula ACLs
     return $this->bacula_acl->doBaculaAcl($stmt->fetchAll(), 'jobname', 'job');
 }

 /**
  * inner Join
  * 
  * @param array|String $name  
  * @param unknown_type $cond
  * @return WeFlex_Db_Model
  */
 public function joinInner($name, $cond)
 {
     $this->_selector->joinInner($name, $cond);
     return $this;
 }

 /**
  * Helper to specify new rules in queries for Omeka releases < 2.5.
  *
  * @see Table_Item::_advancedSearch()
  *
  * @param Zend_Db_Select $select
  * @param array $simpleTerms
  * @return void
  */
 private function _addRulesOldOmeka($select, $rulesOmekaNew)
 {
     if (empty($rulesOmekaNew)) {
         return;
     }
     $db = $this->_db;
     $advancedIndex = 0;
     foreach ($rulesOmekaNew as $v) {
         // Do not search on blank rows.
         if (empty($v['element_id']) || empty($v['type'])) {
             continue;
         }
         $value = isset($v['terms']) ? $v['terms'] : null;
         $type = $v['type'];
         $elementId = (int) $v['element_id'];
         $alias = "_advanced_old_{$advancedIndex}";
         $inner = true;
         $extraJoinCondition = '';
         // Determine what the WHERE clause should look like.
         switch ($type) {
             case 'starts with':
                 $predicate = "LIKE " . $db->quote($value . '%');
                 break;
             case 'ends with':
                 $predicate = "LIKE " . $db->quote('%' . $value);
                 break;
             case 'is not exactly':
                 $predicate = ' != ' . $db->quote($value);
                 break;
             case 'matches':
                 if (!strlen($value)) {
                     continue 2;
                 }
                 $predicate = 'REGEXP ' . $db->quote($value);
                 break;
             case 'does not match':
                 if (!strlen($value)) {
                     continue 2;
                 }
                 $predicate = 'NOT REGEXP ' . $db->quote($value);
                 break;
             default:
                 throw new Omeka_Record_Exception(__('Invalid search type given!'));
         }
         // Note that $elementId was earlier forced to int, so manual quoting
         // is unnecessary here
         $joinCondition = "{$alias}.record_id = items.id AND {$alias}.record_type = 'Item' AND {$alias}.element_id = {$elementId}";
         if ($extraJoinCondition) {
             $joinCondition .= ' ' . $extraJoinCondition;
         }
         if ($inner) {
             $select->joinInner(array($alias => $db->ElementText), $joinCondition, array());
         } else {
             $select->joinLeft(array($alias => $db->ElementText), $joinCondition, array());
         }
         $select->where("{$alias}.text {$predicate}");
         $advancedIndex++;
     }
 }

 /**
  * Get Select object (Zend_Db_Select) for sorting table records
  *
  * @param Zend_Db_Select $select                 
  * @param array $options                          
  *
  * @return Zend_Db_Select
  */
 public static function GetSelectForSort($select, $options)
 {
     $aliasTable = '';
     $order = $options['order'];
     //--------------------------
     $arrOrder = explode('.', $order);
     // Если в параметре сортировки не задан псевдоним таблицы
     // то определим его, и если нужно присоединим,
     // соответствующую таблицу
     if (count($arrOrder) == 1) {
         $joinTableForSort = $options['joinTableForSort'];
         if ($joinTableForSort) {
             // Определим какие таблицы уже присоединены
             $fromTables = $select->getPart(Zend_Db_Select::FROM);
             foreach ($fromTables as $alias => $joinParams) {
                 // Если таблица -> $joinTableForSort уже присоединена
                 // то получим ее псевдоним
                 if ($joinParams['tableName'] == $joinTableForSort) {
                     $aliasTable = $alias;
                 }
             }
             if ($aliasTable) {
                 $order = $aliasTable . '.' . $order;
             } else {
                 // Получим поле сортировки
                 $arrOrder = explode(' ', trim($order));
                 $field = $arrOrder[0];
                 // Присоединим таблицу
                 $joinTable = $joinTableForSort;
                 switch ($joinTable) {
                     case 'blog_info_profile':
                         $select->joinInner(array('i_profile' => $joinTable), 'i_profile.info_id = i.id', array())->where('i_profile.profile_key = ?', $field);
                         $order = 'i_profile.profile_value ' . $arrOrder[1];
                         break;
                     default:
                         $order = 'i.' . $field . ' ' . $arrOrder[1];
                         break;
                 }
             }
         } else {
             $order = 'i.' . $order;
         }
     }
     $select->order($order);
     return $select;
 }