PHP JDatabaseQuery::leftJoin方法代码示例

 /**
  * Get a list of logged users.
  *
  * @param	JObject	The module parameters.
  * @return	mixed	An array of articles, or false on error.
  */
 public static function getList($params)
 {
     // Initialise variables
     $db = JFactory::getDbo();
     $user = JFactory::getUser();
     $query = new JDatabaseQuery();
     $query->select('s.time, s.client_id, u.id, u.name, u.username');
     $query->from('#__session AS s');
     $query->leftJoin('#__users AS u ON s.userid = u.id');
     $query->where('s.guest = 0');
     $db->setQuery($query, 0, $params->get('count', 5));
     $results = $db->loadObjectList();
     // Check for database errors
     if ($error = $db->getErrorMsg()) {
         JError::raiseError(500, $error);
         return false;
     }
     foreach ($results as $k => $result) {
         $results[$k]->logoutLink = '';
         if ($user->authorise('core.manage', 'com_users')) {
             $results[$k]->editLink = JRoute::_('index.php?option=com_users&task=user.edit&id=' . $result->id);
             $results[$k]->logoutLink = JRoute::_('index.php?option=com_login&task=logout&uid=' . $result->id . '&' . JUtility::getToken() . '=1');
         }
         if ($params->get('name', 1) == 0) {
             $results[$k]->name = $results[$k]->username;
         }
     }
     return $results;
 }

 /**
  * Method to get a list of options for a list input.
  *
  * @return      array           An array of JHtml options.
  */
 protected function getOptions()
 {
     $db = JFactory::getDBO();
     $query = new JDatabaseQuery();
     $query->select('#__helloworld.id as id,greeting,#__categories.title as category,catid');
     $query->from('#__helloworld');
     $query->leftJoin('#__categories on catid=#__categories.id');
     $db->setQuery((string) $query);
     $messages = $db->loadObjectList();
     $options = array();
     if ($messages) {
         foreach ($messages as $message) {
             $options[] = JHtml::_('select.option', $message->id, $message->greeting . ($message->catid ? ' (' . $message->category . ')' : ''));
         }
     }
     $options = array_merge(parent::getOptions(), $options);
     return $options;
 }

 /**
  * Method to get a list of options for a list input.
  *
  * @return      array           An array of JHtml options.
  */
 protected function getOptions()
 {
     $db = JFactory::getDBO();
     $query = new JDatabaseQuery();
     $query->select('#__jpaudiotracks.id as id,
                                                    pathatweb,
                                                    pathatlocal,
                                                    file,
                                                    title,
                                                    alias,
                                                    tracknumber,
                                                    mediatype,
                                                    bit_rate,
                                                    sample_rate,
                                                    channels,
                                                    channelmode,
                                                    filesize,
                                                    length,
                                                    catid,
                                                    add_datetime,
                                                    artist,
                                                    album,
                                                    year,
                                                    description,
                                                    lyrics,
                                                    frontcover,
                                                    backcover,
                                                    encoder,
                                                    metakey,
                                                    metadesc,
                             #__categories.title as category,catid');
     $query->from('#__jpaudiotracks');
     $query->leftJoin('#__categories on catid=#__categories.id');
     $db->setQuery((string) $query);
     $tracks = $db->loadObjectList();
     $options = array();
     if ($tracks) {
         foreach ($tracks as $track) {
             $options[] = JHtml::_('select.option', $track->id, $track->name . ($track->catid ? ' (' . $track->category . ')' : ''));
         }
     }
     $options = array_merge(parent::getOptions(), $options);
     return $options;
 }

 private function add_product_rows_query_from(JDatabaseQuery $query)
 {
     $db = JFactory::getDbo();
     $j_user = JFactory::getUser();
     $query->from('#__ecommercewd_products AS T_PRODUCTS');
     $query->leftJoin('(SELECT * FROM #__ecommercewd_categories WHERE published = 1) AS T_CATEGORIES ON T_CATEGORIES.id = T_PRODUCTS.category_id');
     $query->leftJoin('(SELECT * FROM #__ecommercewd_manufacturers WHERE published = 1) AS T_MANUFACTURERS ON T_MANUFACTURERS.id = T_PRODUCTS.manufacturer_id');
     $query->leftJoin('(SELECT * FROM #__ecommercewd_labels WHERE published = 1) AS T_LABELS ON T_LABELS.id = T_PRODUCTS.label_id');
     $query->leftJoin('(SELECT * FROM #__ecommercewd_taxes WHERE published = 1) AS T_TAXES ON T_TAXES.id = T_PRODUCTS.tax_id');
     $query->leftJoin('(SELECT * FROM #__ecommercewd_discounts WHERE published = 1) AS T_DISCOUNTS ON T_PRODUCTS.discount_id = T_DISCOUNTS.id');
     $query->leftJoin('(SELECT product_id, COUNT(id) AS reviews_count FROM #__ecommercewd_feedback WHERE published = 1 GROUP BY product_id) AS T_FEEDBACK ON T_FEEDBACK.product_id = T_PRODUCTS.id');
     $query->leftJoin('(SELECT product_id, AVG(rating) AS rating FROM #__ecommercewd_ratings GROUP BY product_id) AS T_RATINGS ON T_RATINGS.product_id = T_PRODUCTS.id');
     // tags
     $query->leftJoin('
         (
             SELECT
                 T_PRODUCT_TAGS.product_id,
                 CONCAT(",", GROUP_CONCAT(T_TAGS.name SEPARATOR ","), ",") as tag_names
             FROM
                 #__ecommercewd_tags AS T_TAGS
                 LEFT JOIN #__ecommercewd_producttags AS T_PRODUCT_TAGS ON T_PRODUCT_TAGS.tag_id = T_TAGS.id
                 GROUP BY product_id)
         AS T_PRODUCT_TAGS ON T_PRODUCT_TAGS.product_id = T_PRODUCTS.id');
     // rating
     if (WDFHelper::is_user_logged_in() == true) {
         $user_identification = 'j_user_id = ' . $j_user->id;
     } else {
         $user_ip_address = WDFUtils::get_client_ip_address();
         $user_identification = 'user_ip_address = ' . $db->quote($user_ip_address);
     }
     $query->leftJoin('(
             SELECT
                 product_id,
                 COUNT(rating) AS ratings_count
             FROM
                 #__ecommercewd_ratings
             WHERE ' . $user_identification . '
             GROUP BY product_id
         ) AS T_USER_RATINGS ON T_USER_RATINGS.product_id = T_PRODUCTS.id');
     if (WDFHelper::is_user_logged_in()) {
         $j_user = JFactory::getUser();
         $user_identification = 'j_user_id = ' . $j_user->id;
     } else {
         $order_product_rand_ids = WDFInput::cookie_get_array('order_product_rand_ids');
         if (empty($order_product_rand_ids) == false) {
             $user_identification = 'j_user_id = 0 AND rand_id IN (' . implode(',', $order_product_rand_ids) . ')';
         } else {
             $user_identification = '0';
         }
     }
     $query->leftJoin('(SELECT product_id, COUNT(id) AS products_in_cart FROM #__ecommercewd_orderproducts WHERE ' . $user_identification . ' AND order_id = 0 GROUP BY product_id) AS T_ORDERPRODUCTS ON T_ORDERPRODUCTS.product_id = T_PRODUCTS.id');
     return $query;
 }

 protected function _load($id)
 {
     $db = JFactory::getDbo();
     $app = JFactory::getApplication();
     $user = JFactory::getUser();
     $extension = $this->_extension;
     // Record that this $id has been checked
     $this->_checkedCategories[$id] = true;
     $query = new JDatabaseQuery();
     // right join with c for category
     $query->select('c.*');
     $query->select('CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as slug');
     $query->from('#__categories as c');
     $query->where('(c.extension=' . $db->Quote($extension) . ' OR c.extension=' . $db->Quote('system') . ')');
     if ($this->_options['access']) {
         $query->where('c.access IN (' . implode(',', $user->getAuthorisedViewLevels()) . ')');
     }
     if ($this->_options['published'] == 1) {
         $query->where('c.published = 1');
     }
     $query->order('c.lft');
     // s for selected id
     if ($id != 'root') {
         // Get the selected category
         $query->leftJoin('#__categories AS s ON (s.lft <= c.lft AND s.rgt >= c.rgt) OR (s.lft > c.lft AND s.rgt < c.rgt)');
         $query->where('s.id=' . (int) $id);
     }
     $subQuery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ' . 'ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = ' . $db->quote($extension) . ' AND parent.published != 1 GROUP BY cat.id) ';
     $query->leftJoin($subQuery . 'AS badcats ON badcats.id = c.id');
     $query->where('badcats.id is null');
     // i for item
     if (isset($this->_options['countItems']) && $this->_options['countItems'] == 1) {
         if ($this->_options['published'] == 1) {
             $query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id AND i.' . $this->_statefield . ' = 1');
         } else {
             $query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id');
         }
         $query->select('COUNT(i.' . $db->nameQuote($this->_key) . ') AS numitems');
     }
     // Group by
     $query->group('c.id');
     // Filter by language
     if ($app->isSite() && $app->getLanguageFilter()) {
         $query->where('(' . ($id != 'root' ? 'c.id=s.id OR ' : '') . 'c.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . '))');
     }
     // Get the results
     $db->setQuery($query);
     $results = $db->loadObjectList('id');
     $childrenLoaded = false;
     if (count($results)) {
         // foreach categories
         foreach ($results as $result) {
             // Deal with root category
             if ($result->id == 1) {
                 $result->id = 'root';
             }
             // Deal with parent_id
             if ($result->parent_id == 1) {
                 $result->parent_id = 'root';
             }
             // Create the node
             if (!isset($this->_nodes[$result->id])) {
                 // Create the JCategoryNode and add to _nodes
                 $this->_nodes[$result->id] = new JCategoryNode($result, $this);
                 // If this is not root, and if the current nodes parent is in the list or the current node parent is 0
                 if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) {
                     // Compute relationship between node and its parent - set the parent in the _nodes field
                     $this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]);
                 }
                 // if the node's parent id is not in the _nodes list and the node is not root (doesn't have parent_id == 0),
                 // then remove this nodes from the list
                 if (!(isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) {
                     unset($this->_nodes[$result->id]);
                     continue;
                 }
                 if ($result->id == $id || $childrenLoaded) {
                     $this->_nodes[$result->id]->setAllLoaded();
                     $childrenLoaded = true;
                 }
             } else {
                 if ($result->id == $id || $childrenLoaded) {
                     // Create the JCategoryNode
                     $this->_nodes[$result->id] = new JCategoryNode($result, $this);
                     if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id)) {
                         // Compute relationship between node and its parent
                         $this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]);
                     }
                     if (!isset($this->_nodes[$result->parent_id])) {
                         unset($this->_nodes[$result->id]);
                         continue;
                     }
                     if ($result->id == $id || $childrenLoaded) {
                         $this->_nodes[$result->id]->setAllLoaded();
                         $childrenLoaded = true;
                     }
                 }
             }
         }
     } else {
         $this->_nodes[$id] = null;
//.........这里部分代码省略.........

 /**
  * Apply currently set filters to the database query
  * @param JDatabaseQuery $query
  */
 protected function applyFilters(JDatabaseQuery &$query)
 {
     if ($this->useEventsTable) {
         $query->from("events");
         $query->leftJoin($this->table . " USING (" . $this->idField . ")");
     } else {
         $query->from($this->table);
     }
     $query->where(array($this->startDateField . " >= '" . Event::timeToDate($this->startDate) . "'", $this->startDateField . " <= '" . Event::timeToDate($this->endDate) . "'"));
     if (!$this->showUnpublished) {
         $query->where($this->readyToPublishField);
     }
     // Filter by attendees
     if (!empty($this->filterAttendedBy)) {
         $query->join("INNER", "eventattendance ON eventattendance.eventtype=" . $this->eventTypeConst . " AND eventattendance.eventid=" . $this->table . "." . $this->idField);
         $query->where("eventattendance.user IN (" . implode(",", $this->filterAttendedBy) . ")");
     }
     // This allows subclasses to modify the query. Normally they'll add extra WHERE clauses.
     $this->modifyQuery($query);
 }