PHP DbQuery::leftOuterJoin方法代码示例

 /**
  * Launch sql query to create collection of objects
  *
  * @param bool $display_query If true, query will be displayed (for debug purpose)
  * @return PrestaShopCollection
  */
 public function getAll($display_query = false)
 {
     if ($this->is_hydrated) {
         return $this;
     }
     $this->is_hydrated = true;
     $alias = $this->generateAlias();
     //$this->query->select($alias.'.*');
     $this->query->from($this->definition['table'], $alias);
     // If multilang, create association to lang table
     if (!empty($this->definition['multilang'])) {
         $this->join(self::LANG_ALIAS);
         if ($this->id_lang) {
             $this->where(self::LANG_ALIAS . '.id_lang', '=', $this->id_lang);
         }
     }
     // Add join clause
     foreach ($this->join_list as $data) {
         $on = '(' . implode(') AND (', $data['on']) . ')';
         switch ($data['type']) {
             case self::LEFT_JOIN:
                 $this->query->leftJoin($data['table'], $data['alias'], $on);
                 break;
             case self::INNER_JOIN:
                 $this->query->innerJoin($data['table'], $data['alias'], $on);
                 break;
             case self::LEFT_OUTER_JOIN:
                 $this->query->leftOuterJoin($data['table'], $data['alias'], $on);
                 break;
         }
     }
     // All limit clause
     if ($this->page_size) {
         $this->query->limit($this->page_size, $this->page_number * $this->page_size);
     }
     // Shall we display query for debug ?
     if ($display_query) {
         echo $this->query . '<br />';
     }
     $this->results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
     if ($this->results && is_array($this->results)) {
         $this->results = ObjectModel::hydrateCollection($this->classname, $this->results, $this->id_lang);
     }
     return $this;
 }

    public function getNewProducts($where, $id_lang, $page_number = 0, $nb_products = 10, $count = false, $order_by = null, $order_way = null, Context $context = null)
    {
        if (!$context) {
            $context = Context::getContext();
        }
        $front = true;
        if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
            $front = false;
        }
        if ($page_number < 0) {
            $page_number = 0;
        }
        if ($nb_products < 1) {
            $nb_products = 10;
        }
        if (empty($order_by) || $order_by == 'position') {
            $order_by = 'date_add';
        }
        if (empty($order_way)) {
            $order_way = 'DESC';
        }
        if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add' || $order_by == 'date_upd') {
            $order_by_prefix = 'p';
        } else {
            if ($order_by == 'name') {
                $order_by_prefix = 'pl';
            }
        }
        if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
            die(Tools::displayError());
        }
        $sql_groups = '';
        if (Group::isFeatureActive()) {
            $groups = FrontController::getCurrentCustomerGroups();
            $sql_groups = 'AND p.`id_product` IN (
					SELECT cp.`id_product`
					FROM `' . _DB_PREFIX_ . 'category_group` cg
					LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
					WHERE cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
				)';
        }
        if (strpos($order_by, '.') > 0) {
            $order_by = explode('.', $order_by);
            $order_by_prefix = $order_by[0];
            $order_by = $order_by[1];
        }
        if ($count) {
            $sql = 'SELECT COUNT(p.`id_product`) AS nb
						FROM `' . _DB_PREFIX_ . 'product` p
						' . Shop::addSqlAssociation('product', 'p') . '
						WHERE product_shop.`active` = 1
						AND product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '"
						' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
						' . $sql_groups;
            return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
        }
        $sql = new DbQuery();
        $sql->select('p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
				pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,
				product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" as new');
        $sql->from('product', 'p');
        $sql->join(Shop::addSqlAssociation('product', 'p'));
        $sql->leftJoin('product_lang', 'pl', '
				p.`id_product` = pl.`id_product`
				AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
        $sql->leftJoin('image', 'i', 'i.`id_product` = p.`id_product`');
        $sql->join(Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1'));
        $sql->leftJoin('image_lang', 'il', 'i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang);
        $sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`');
        $sql->where('product_shop.`active` = 1');
        if ($front) {
            $sql->where('product_shop.`visibility` IN ("both", "catalog")');
        }
        $sql->where('product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '"');
        if (Group::isFeatureActive()) {
            $sql->where('p.`id_product` IN (
					SELECT cp.`id_product`
					FROM `' . _DB_PREFIX_ . 'category_group` cg
					LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
					WHERE ' . $where . ' cg.`id_group` ' . $sql_groups . '
				)');
        }
        $sql->groupBy('product_shop.id_product');
        $sql->orderBy((isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way));
        $sql->limit($nb_products, $page_number * $nb_products);
        if (Combination::isFeatureActive()) {
            $sql->select('MAX(product_attribute_shop.id_product_attribute) id_product_attribute');
            $sql->leftOuterJoin('product_attribute', 'pa', 'p.`id_product` = pa.`id_product`');
            $sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1'));
        }
        $sql->join(Product::sqlStock('p', Combination::isFeatureActive() ? 'product_attribute_shop' : 0));
        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
        if ($order_by == 'price') {
            Tools::orderbyPrice($result, $order_way);
        }
        if (!$result) {
            return false;
        }
        $products_ids = array();
        foreach ($result as $row) {
//.........这里部分代码省略.........