PrestaShop (1.4) fastest possible product listing

Category.php

<?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);
	}

}

ProductQueryBuilder.php

<?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;
	}

}