Skip to content

Instantly share code, notes, and snippets.

@foxycode
Created January 23, 2015 18:17
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save foxycode/55471cd05e103b49074d to your computer and use it in GitHub Desktop.
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