PrestaShop (1.4) fastest possible product listing
<?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); | |
} | |
} |
<?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