static public function getProducts($id_lang, $start, $limit, $orderBy, $orderWay, $id_category = false, $only_active = false)
{
  if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))
  die (Tools::displayError());
  if ($orderBy == 'id_product' OR $orderBy == 'price' OR $orderBy == 'date_add')
  $orderByPrefix = 'p';
  elseif ($orderBy == 'name')
  $orderByPrefix = 'pl';
  elseif ($orderBy == 'position')
  $orderByPrefix = 'c';

  $rq = Db::getInstance()->ExecuteS('
  SELECT p.*, pl.* , t.`rate` AS tax_rate, m.`name` AS manufacturer_name, s.`name` AS supplier_name
  FROM `'._DB_PREFIX_.'product` p
  LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)
  LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = p.`id_tax`)
  LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
  LEFT JOIN `'._DB_PREFIX_.'supplier` s ON (s.`id_supplier` = p.`id_supplier`)'.
  ($id_category ? 'LEFT JOIN `'._DB_PREFIX_.'category_product` c ON (c.`id_product` = p.`id_product`)' : '').'
  WHERE pl.`id_lang` = '.intval($id_lang).
  ($id_category ? ' AND c.`id_category` = '.intval($id_category) : '').
  ($only_active ? ' AND p.`active` = 1' : '').'
  ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'`'.pSQL($orderWay).
  ($limit > 0 ? ' LIMIT '.intval($start).','.intval($limit) : '')
  );
  if($orderBy == 'price')
  Tools::orderbyPrice($rq,$orderWay);

  return ($rq);
}

 
public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1)
{
  global $cookie;

  if ($p < 1) $p = 1;
  if (empty($orderBy))
  $orderBy = 'position';
  if (empty($orderWay))
  $orderWay = 'ASC';
  if ($orderBy == 'id_product' OR $orderBy == 'date_add')
  $orderByPrefix = 'p';
  elseif ($orderBy == 'name')
  $orderByPrefix = 'pl';
  elseif ($orderBy == 'manufacturer')
  {
  $orderByPrefix = 'm';
  $orderBy = 'name';
  }
  elseif ($orderBy == 'position')
  $orderByPrefix = 'cp';

  if ($orderBy == 'price')
  $orderBy = 'price';

  if (!Validate::isBool($active) OR !Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))
  die (Tools::displayError());

  $id_supplier = intval(Tools::getValue('id_supplier'));

  /* Return only the number of products */
  if ($getTotal)
  {
  $result = Db::getInstance()->getRow('
  SELECT COUNT(cp.`id_product`) AS total
  FROM `'._DB_PREFIX_.'product` p
  LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
  WHERE cp.`id_category` = '.intval($this->id).($active ? ' AND p.`active` = 1' : '').'
  '.($id_supplier ? 'AND p.id_supplier = '.intval($id_supplier) : ''));
  return isset($result) ? $result['total'] : 0;
  }

  $sql = '
  SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, 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 AS new,
  (p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice
  FROM `'._DB_PREFIX_.'category_product` cp
  LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
  LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
  LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.intval($id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.intval($id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
  LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.intval($id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'tax` t ON t.`id_tax` = p.`id_tax`
  LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.intval($id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
  WHERE cp.`id_category` = '.intval($this->id).($active ? ' AND p.`active` = 1' : '').'
  '.($id_supplier ? 'AND p.id_supplier = '.$id_supplier : '');

  if ($random === true)
  {
  $sql .= ' ORDER BY RAND()';
  $sql .= ' LIMIT 0, '.intval($randomNumberProducts);
  }
  else
  {
  $sql .= ' ORDER BY p.`quantity` DESC,'.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
  LIMIT '.((intval($p) - 1) * intval($n)).','.intval($n);
  }

  $result = Db::getInstance()->ExecuteS($sql);

  if ($orderBy == 'orderprice')
  {
  Tools::orderbyPrice($result, $orderWay);
  }
  if (!$result)
  return false;

  /* Modify SQL result */
  return Product::getProductsProperties($id_lang, $result);
}

Чтобы при любой сортировке товары с нулевыми остатками выводились в конце на последни