PrestaShop (1.4) fastest possible product listing
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
class Category extends CategoryCore | |
{ | |
public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1, $checkAccess = true) | |
{ | |
$id_country = Country::getDefaultCountryId(); | |
$builder = ProductQueryBuilder::create($id_lang, $id_country) | |
->addWhere('category_product.id_category', (int)$this->id); | |
if ($active) | |
{ | |
$builder->addWhere('product.active', 1); | |
} | |
$id_supplier = (int)(Tools::getValue('id_supplier')); | |
if ($id_supplier) | |
{ | |
$builder->addWhere('product.id_supplier', $id_supplier); | |
} | |
if ($getTotal) | |
{ | |
// Return only the number of products | |
$result = Db::getInstance()->getRow($builder->getCountQuery()); | |
return $result ? $result['total'] : 0; | |
} | |
if ($p < 1) $p = 1; | |
$builder->setOrderBy($random === TRUE ? 'RAND()' : $orderBy) | |
->setLimit((((int)($p) - 1) * (int)($n)), (int)$n); | |
$result = Db::getInstance()->ExecuteS($builder->getQuery()); | |
$productIds = array(); | |
foreach ($result as $row) | |
{ | |
$productIds[] = $row['id_product']; | |
} | |
$builder = ProductQueryBuilder::create($id_lang, $id_country) | |
->addWhere('category_product.id_product', $productIds) | |
->addRawSelect( | |
'DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . ( | |
Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) | |
? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20 | |
) . ' DAY)) > 0', 'new' | |
); | |
$result = Db::getInstance()->ExecuteS($builder->getFullQuery()); | |
$products = array_combine($productIds, $productIds); | |
foreach ($result as $row) | |
{ | |
$products[$row['id_product']] = $row; | |
} | |
if ($orderBy == 'orderprice') | |
Tools::orderbyPrice($products, $orderWay); | |
if (!$products) | |
return FALSE; | |
/* Modify SQL result */ | |
return Product::getProductsProperties($id_lang, $products); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
class ProductQueryBuilder | |
{ | |
private $baseTable = 'category_product'; | |
private $defaultOrderBy = 'position'; | |
private $definition = array( | |
'category_product' => array( | |
'id_product' | |
), | |
'product' => array( | |
'id_supplier', | |
'id_manufacturer', | |
'id_tax_rules_group', | |
'id_category_default', | |
'id_color_default', | |
'on_sale', | |
'online_only', | |
'ean13', | |
'upc', | |
'ecotax', | |
'quantity', | |
'minimal_quantity', | |
'price', | |
'wholesale_price', | |
'unit_price_ratio', | |
'additional_shipping_cost', | |
'unity', | |
'reference', | |
'supplier_reference', | |
'location', | |
'width', | |
'height', | |
'depth', | |
'weight', | |
'out_of_stock', | |
'quantity_discount', | |
'customizable', | |
'uploadable_files', | |
'text_fields', | |
'active', | |
'available_for_order', | |
'condition', | |
'show_price', | |
'indexed', | |
'cache_is_pack', | |
'cache_has_attachments', | |
'cache_default_attribute', | |
'date_add', | |
'date_upd' | |
), | |
'product_lang' => array( | |
'description', | |
'description_short', | |
'available_now', | |
'available_later', | |
'link_rewrite', | |
'meta_description', | |
'meta_keywords', | |
'meta_title', | |
'name', | |
), | |
'product_attribute' => array( | |
'id_product_attribute' | |
), | |
'image' => array( | |
'id_image' | |
), | |
'image_lang' => array( | |
'legend' | |
), | |
'manufacturer' => array( | |
'name' | |
), | |
'tax_rule' => array( | |
// Must be here for proper JOIN, is needed by table tax | |
), | |
'tax' => array( | |
'rate' | |
), | |
'tax_lang' => array( | |
'name' | |
), | |
'category_lang' => array( | |
'name' | |
) | |
); | |
private $fieldAliases = array( | |
'manufacturer.name' => 'manufacturer_name', | |
'tax_lang.name' => 'tax_name', | |
'category_lang.name' => 'category_default' | |
); | |
private $tableAliases = array( | |
'category_product' => 'cp', | |
'product' => 'p', | |
'product_lang' => 'pl', | |
'product_attribute' => 'pa', | |
'category_lang' => 'cl', | |
'image' => 'i', | |
'image_lang' => 'il', | |
'tax_rule' => 'tr', | |
'tax' => 't', | |
'tax_lang' => 'tl', | |
'manufacturer' => 'm', | |
'product_filter_value' => 'pfv' | |
); | |
private $orderFields = array( | |
'position', 'id_product', 'date_add', 'quantity', 'name', 'manufacturer', 'price' | |
); | |
private $joinConditions = array( | |
'product' => 'JOIN `ps_product` p ON p.`id_product` = cp.`id_product`', | |
'product_lang' => 'JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = {$ID_LANG})', | |
'product_attribute' => 'LEFT JOIN `ps_product_attribute` pa ON (pa.`id_product` = p.`id_product` AND pa.`default_on` = 1)', | |
'category_lang' => 'LEFT JOIN `ps_category_lang` cl ON (cl.`id_category` = p.`id_category_default` AND cl.`id_lang` = {$ID_LANG})', | |
'image' => 'LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)', | |
'image_lang' => 'LEFT JOIN `ps_image_lang` il ON (il.`id_image` = i.`id_image` AND il.`id_lang` = {$ID_LANG})', | |
'tax_rule' => 'LEFT JOIN `ps_tax_rule` tr ON (tr.`id_tax_rules_group` = p.`id_tax_rules_group` AND tr.`id_country` = {$ID_COUNTRY} AND tr.`id_state` = 0)', | |
'tax' => 'LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)', | |
'tax_lang' => 'LEFT JOIN `ps_tax_lang` tl ON (tl.`id_tax` = t.`id_tax` AND tl.`id_lang` = {$ID_LANG})', | |
'manufacturer' => 'LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`', | |
'product_filter_value' => 'JOIN `ps_product_filter_value` pfv ON pfv.`id_product` = cp.`id_product`' | |
); | |
/** @var int */ | |
private $id_lang = NULL; | |
/** @var int */ | |
private $id_country = NULL; | |
/** @var array */ | |
private $select = array(); | |
/** @var array */ | |
private $join = array(); | |
/** @var array */ | |
private $where = array(); | |
/** @var string */ | |
private $orderBy = NULL; | |
/** @var int */ | |
private $limit = NULL; | |
// ------------------------------------------------------------------------- | |
public static function create($id_lang, $id_country) | |
{ | |
return new static($id_lang, $id_country); | |
} | |
public function __construct($id_lang, $id_country) | |
{ | |
$this->id_lang = (int)$id_lang; | |
$this->id_country = (int)$id_country; | |
foreach ($this->joinConditions as &$condition) | |
{ | |
$condition = str_replace('{$ID_LANG}', $this->id_lang, $condition); | |
$condition = str_replace('{$ID_COUNTRY}', $this->id_country, $condition); | |
} | |
} | |
public function addSelect($field, $alias = NULL) | |
{ | |
list($table, $field) = explode('.', $field); | |
if (isset($this->tableAliases[$table])) | |
{ | |
$this->addRawSelect( | |
"{$this->tableAliases[$table]}.`{$field}`", $alias | |
); | |
} | |
return $this; | |
} | |
public function addRawSelect($sql, $alias = NULL) | |
{ | |
$select = $alias ? "{$sql} AS {$alias}" : $sql; | |
if (!in_array($select, $this->select)) | |
{ | |
$this->select[] = $select; | |
} | |
return $this; | |
} | |
public function setSelect($field, $alias = NULL) | |
{ | |
$this->select = array(); | |
return $this->addSelect($field, $alias); | |
} | |
public function addJoin($table) | |
{ | |
if (isset($this->joinConditions[$table]) && !in_array($this->joinConditions[$table], $this->join)) | |
{ | |
$this->join[] = $this->joinConditions[$table]; | |
} | |
return $this; | |
} | |
public function addWhere($field, $value) | |
{ | |
list($table, $field) = explode('.', $field); | |
if ($table != $this->baseTable) | |
{ | |
$this->addJoin($table); | |
} | |
// TODO: Escape content of IN | |
$where = is_array($value) | |
? "`{$this->tableAliases[$table]}`.`{$field}` IN (" . implode(', ', $value) . ")" | |
: "`{$this->tableAliases[$table]}`.`{$field}` = '" . pSQL($value) . "'"; | |
if (!in_array($where, $this->where)) | |
{ | |
$this->where[] = $where; | |
} | |
return $this; | |
} | |
public function setWhere($field, $value) | |
{ | |
$this->where = array(); | |
return $this->addWhere($field, $value); | |
} | |
public function setOrderBy($orderBy, $orderWay = 'ASC') | |
{ | |
$orderBy = $this->formatOrderBy($orderBy); | |
$orderWay = $this->formatOrderWay($orderWay); | |
switch ($orderBy) | |
{ | |
case 'id_product': | |
$this->orderBy = "cp.`id_product` {$orderWay}"; | |
break; | |
case 'date_add': | |
$this->addJoin('product'); | |
$this->orderBy = "p.`date_add` {$orderWay}"; | |
break; | |
case 'quantity': | |
$this->addJoin('product'); | |
$this->orderBy = "p.`quantity` {$orderWay}"; | |
break; | |
case 'name': | |
$this->addJoin('product_lang'); | |
$this->orderBy = "pl.`name` {$orderWay}"; | |
break; | |
case 'manufacturer': | |
$this->addJoin('manufacturer'); | |
$this->orderBy = "m.`name` {$orderWay}"; | |
break; | |
case 'price': | |
$this->addJoin('product'); | |
$this->addJoin('tax_rule'); | |
$this->addJoin('tax'); | |
$this->addRawSelect('(p.`price` * IF(t.`rate`, ((100 + (t.`rate`))/100), 1))', 'orderprice'); | |
$this->orderBy = "orderprice {$orderWay}"; | |
break; | |
default: | |
$this->orderBy = "cp.`position` {$orderWay}"; | |
break; | |
} | |
return $this; | |
} | |
public function setLimit($offset, $length) | |
{ | |
$this->limit = (int)$offset . ', ' . (int)$length; | |
return $this; | |
} | |
public function getQuery($field = NULL) | |
{ | |
$this->addSelect( | |
"{$this->baseTable}.{$this->definition[$this->baseTable][0]}" | |
); | |
return $this->formatQuery( | |
$field ? $field : $this->select, | |
$this->join, $this->where, | |
$this->orderBy, $this->limit | |
); | |
} | |
public function getCountQuery() | |
{ | |
return $this->getQuery('COUNT(*) AS total'); | |
} | |
public function getFullQuery() | |
{ | |
foreach ($this->definition as $table => $fields) | |
{ | |
if ($table != $this->baseTable) | |
{ | |
$this->addJoin($table); | |
} | |
foreach ($fields as $field) | |
{ | |
$alias = isset($this->fieldAliases["{$table}.{$field}"]) | |
? $this->fieldAliases["{$table}.{$field}"] | |
: NULL; | |
$this->addSelect("{$table}.{$field}", $alias); | |
} | |
} | |
return $this->getQuery(); | |
} | |
// ------------------------------------------------------------------------- | |
private function formatOrderBy($orderBy) | |
{ | |
$orderBy = strtolower($orderBy); | |
if ($orderBy == 'rand()') | |
{ | |
return 'RAND()'; | |
} | |
return in_array($orderBy, $this->orderFields) | |
? $orderBy : $this->defaultOrderBy; | |
} | |
private function formatOrderWay($orderWay) | |
{ | |
$orderWay = strtoupper($orderWay); | |
return in_array($orderWay, array('ASC', 'DESC')) | |
? $orderWay : 'ASC'; | |
} | |
private function formatQuery($select = NULL, $join = NULL, $where = NULL, | |
$orderBy = NULL, $limit = NULL) | |
{ | |
if (!$select) | |
{ | |
$select = "{$this->tableAliases[$this->baseTable]}." | |
. "`{$this->definition[$this->baseTable][0]}`"; | |
} | |
$query = "SELECT\n\t" . (is_array($select) ? implode(', ', $select) : $select); | |
$query .= "\nFROM\n\t`ps_{$this->baseTable}` {$this->tableAliases[$this->baseTable]}"; | |
if ($join) | |
{ | |
$query .= "\n" . implode("\n", $join); | |
} | |
if ($where) | |
{ | |
$query .= "\nWHERE\n\t" . implode(' AND ', $where); | |
} | |
if ($orderBy) | |
{ | |
$query .= "\nORDER BY\n\t" . $orderBy; | |
} | |
if ($limit) | |
{ | |
$query .= "\nLIMIT\n\t" . $limit; | |
} | |
return $query; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment