/** * 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; }
/** * @param int * @return Varien_Data_Collerction */ public function getCollection($catId) { $currentCatProducts = $this->_getProductDataByCategory($catId); if (!$currentCatProducts) { return new Varien_Data_Collection(); } $productUrlToCategory = Mage::helper('xsitemap')->getHtmlSitemapProductUrlType(); $onlyCount = false; $store = Mage::app()->getStore(); $storeId = $store->getStoreId(); $read = $this->_getReadAdapter(); $this->_select = $read->select()->distinct()->from(array('e' => $this->getMainTable()), array($onlyCount ? 'COUNT(*)' : $this->getIdFieldName()))->join(array('w' => $this->getTable('catalog/product_website')), "e.entity_id=w.product_id AND product_id IN (" . implode(',', array_keys($currentCatProducts)) . ")", array())->where('w.website_id=?', $store->getWebsiteId()); $name = Mage::getModel('catalog/product')->getResource()->getAttribute('name'); if ($name) { $this->_select->join(array('cpev' => $name->getBackend()->getTable(), array($this->getIdFieldName())), "cpev.entity_id = e.entity_id", array('name' => 'cpev.value')); $this->_select->joinRight(array('ea' => Mage::getSingleton('core/resource')->getTableName('eav_attribute')), "cpev.attribute_id = ea.attribute_id" . new Zend_Db_Expr(" AND cpev.store_id =\n IF(\n\t\t\t\t\t\t(SELECT `cpev_store`.`value` FROM `{$name->getBackend()->getTable()}` AS `cpev_store` WHERE `cpev_store`.`entity_id` = `e`.`entity_id` AND `attribute_id` = {$name->getAttributeId()} AND `store_id` = {$storeId}) IS NOT NULL ,\n\t\t\t\t\t\t(SELECT {$storeId}),\n\t\t\t\t\t\t(SELECT 0)\n\t\t\t\t\t)"), array())->where('ea.attribute_code=?', 'name'); } $excludeAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('exclude_from_html_sitemap'); if ($excludeAttr) { $this->_select->joinLeft(array('exclude_tbl' => $excludeAttr->getBackend()->getTable()), 'exclude_tbl.entity_id = e.entity_id AND exclude_tbl.attribute_id = ' . $excludeAttr->getAttributeId() . new Zend_Db_Expr(" AND exclude_tbl.store_id =\n IF(\n\t\t\t\t\t\t(SELECT `exclude`.`value` FROM `{$excludeAttr->getBackend()->getTable()}` AS `exclude` WHERE `exclude`.`entity_id` = `e`.`entity_id` AND `attribute_id` = {$excludeAttr->getAttributeId()} AND `store_id` = {$storeId}) ,\n\t\t\t\t\t\t(SELECT {$storeId}),\n\t\t\t\t\t\t(SELECT 0)\n\t\t\t\t\t)"), array())->where('exclude_tbl.value=0 OR exclude_tbl.value IS NULL'); } if (Mage::helper('xsitemap')->isExcludeFromXMLOutOfStockProduct($storeId)) { $cond = 'e.entity_id = csi.product_id'; if (Mage::getStoreConfig('cataloginventory/item_options/manage_stock', $storeId)) { $cond .= ' AND IF (csi.use_config_manage_stock = 1, csi.is_in_stock = 1, (csi.manage_stock = 0 OR (csi.manage_stock = 1 AND csi.is_in_stock = 1)))'; } else { $cond .= ' AND IF (csi.use_config_manage_stock = 1, TRUE, (csi.manage_stock = 0 OR (csi.manage_stock = 1 AND csi.is_in_stock = 1)))'; } $this->_select->join(array('csi' => $this->getTable('cataloginventory/stock_item')), $cond, array('is_in_stock', 'manage_stock', 'use_config_manage_stock')); } $this->_addFilter($storeId, 'visibility', Mage::getSingleton('catalog/product_visibility')->getVisibleInSiteIds(), 'in'); $this->_addFilter($storeId, 'status', Mage::getSingleton('catalog/product_status')->getVisibleStatusIds(), 'in'); $suffix = ''; $suffix2 = ''; $sort = ''; if ($productUrlToCategory == 'canonical') { $suffix = "AND canonical_url_rewrite.category_id IS NOT NULL"; $suffix2 = "AND category_id IS NOT NULL"; $productCanonicalType = Mage::helper('xsitemap')->getSeosuiteProductCanonicalType(); if ($productCanonicalType) { if ($productCanonicalType == 1 || $productCanonicalType == 4) { $sort = 'DESC'; } elseif ($productCanonicalType == 2 || $productCanonicalType == 5) { $sort = 'ASC'; } elseif ($productCanonicalType == 3) { } else { $productUrlToCategory = 'yes'; } } } if ($productUrlToCategory == 'no') { if (!Mage::helper('xsitemap')->isProductUrlUseCategory()) { $sort = ''; $cropCategory = true; } } elseif ($productUrlToCategory == 'yes') { } $canonicalAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_url'); $urlPathAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('url_path'); if (Mage::helper('xsitemap')->isEnterpriseSince113()) { $urlSuffix = Mage::helper('catalog/product')->getProductUrlSuffix($storeId); if ($urlSuffix) { $urlSuffix = '.' . $urlSuffix; } else { $urlSuffix = ''; } $this->_select->joinLeft(array('ecp' => $this->getTable('enterprise_catalog/product')), 'ecp.product_id = e.entity_id ' . 'AND ecp.store_id = ' . $storeId, array())->joinLeft(array('euur' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'ecp.url_rewrite_id = euur.url_rewrite_id AND euur.is_system = 1', array())->joinLeft(array('ecp2' => $this->getTable('enterprise_catalog/product')), 'ecp2.product_id = e.entity_id AND ecp2.store_id = 0', array())->joinLeft(array('euur2' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'ecp2.url_rewrite_id = euur2.url_rewrite_id', array('url' => 'concat( ' . $this->_getWriteAdapter()->getIfNullSql('euur.request_path', 'euur2.request_path') . ',"' . $urlSuffix . '")')); } elseif ($canonicalAttr && $productUrlToCategory == 'canonical') { $this->_select->columns(array('url' => new Zend_Db_Expr("\n IFNULL(\n (IFNULL((SELECT canonical_url_rewrite.`request_path`\n FROM `" . $canonicalAttr->getBackend()->getTable() . "` AS canonical_path\n LEFT JOIN `" . $this->getTable('core/url_rewrite') . "` AS canonical_url_rewrite ON canonical_url_rewrite.`id_path` = canonical_path.`value`\n WHERE canonical_path.`entity_id` = e.`entity_id` AND canonical_path.`attribute_id` = " . $canonicalAttr->getAttributeId() . " AND canonical_url_rewrite.`store_id` IN (0," . $storeId . ") {$suffix}" . ($sort ? " ORDER BY LENGTH(canonical_url_rewrite.`request_path`) " . $sort : "") . " LIMIT 1),\n (SELECT `request_path`\n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id` IN (0," . $storeId . ") AND `is_system`=1 AND `request_path` IS NOT NULL {$suffix2}" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)\n )),\n (SELECT p.`value` FROM `" . $urlPathAttr->getBackend()->getTable() . "` AS p\n WHERE p.`entity_id` = e.`entity_id` AND p.`attribute_id` = " . $urlPathAttr->getAttributeId() . " AND p.`store_id` IN (0," . $storeId . ") LIMIT 1\n )\n )"))); } else { $this->_select->columns(array('url' => new Zend_Db_Expr("(\n SELECT `request_path`\n FROM `" . $this->getTable('core/url_rewrite') . "`\n WHERE `product_id`=e.`entity_id` AND `store_id`=" . intval($storeId) . " AND `is_system`=1 AND `category_id`= " . intval($catId) . " AND `request_path` IS NOT NULL" . ($sort ? " ORDER BY LENGTH(`request_path`) " . $sort : "") . " LIMIT 1)"))); } $crossDomainAttr = Mage::getModel('catalog/product')->getResource()->getAttribute('canonical_cross_domain'); if ($crossDomainAttr && !empty($productCanonicalType)) { $this->_select->joinLeft(array('cross_domain_tbl' => $crossDomainAttr->getBackend()->getTable()), 'cross_domain_tbl.entity_id = e.entity_id AND cross_domain_tbl.attribute_id = ' . $crossDomainAttr->getAttributeId(), array('canonical_cross_domain' => 'cross_domain_tbl.value')); } $sortOrder = Mage::helper('xsitemap')->getHtmlSitemapSort(); if ($sortOrder == 'position') { $sortOrder = 'e.entity_id'; } if ($sortOrder) { $this->_select->order($sortOrder); } //echo $this->_select->assemble(); $query = $read->query($this->_select); $collection = new Varien_Data_Collection(); while ($row = $query->fetch()) { $product = $this->_prepareProduct($row); //If use root canonical or Product url without category. if (isset($productCanonicalType) && $productCanonicalType == 3 || isset($cropCategory) && $cropCategory) { $urlArr = explode('/', $product->getUrl()); $product->setUrl(end($urlArr)); } if (!in_array($product->getId(), $collection->getAllIds())) { $collection->addItem($product); } } return $collection; }