548 lines
30 KiB
PHP
548 lines
30 KiB
PHP
<?php
|
|
/**
|
|
* Copyright since 2007 PrestaShop SA and Contributors
|
|
* PrestaShop is an International Registered Trademark & Property of PrestaShop SA
|
|
*
|
|
* NOTICE OF LICENSE
|
|
*
|
|
* This source file is subject to the Academic Free License 3.0 (AFL-3.0)
|
|
* that is bundled with this package in the file LICENSE.md.
|
|
* It is also available through the world-wide-web at this URL:
|
|
* https://opensource.org/licenses/AFL-3.0
|
|
* If you did not receive a copy of the license and are unable to
|
|
* obtain it through the world-wide-web, please send an email
|
|
* to license@prestashop.com so we can send you a copy immediately.
|
|
*
|
|
* @author PrestaShop SA <contact@prestashop.com>
|
|
* @copyright Since 2007 PrestaShop SA and Contributors
|
|
* @license https://opensource.org/licenses/AFL-3.0 Academic Free License 3.0 (AFL-3.0)
|
|
*/
|
|
|
|
namespace PrestaShop\Module\FacetedSearch\Tests\Adapter;
|
|
|
|
use Configuration;
|
|
use Context;
|
|
use Db;
|
|
use Mockery;
|
|
use Mockery\Adapter\Phpunit\MockeryTestCase;
|
|
use PrestaShop\Module\FacetedSearch\Adapter\MySQL;
|
|
use PrestaShop\Module\FacetedSearch\Product\Search;
|
|
use Product;
|
|
use stdClass;
|
|
use StockAvailable;
|
|
|
|
class MySQLTest extends MockeryTestCase
|
|
{
|
|
private $adapter;
|
|
|
|
protected function setUp()
|
|
{
|
|
$this->adapter = new MySQL();
|
|
|
|
$mock = Mockery::mock(StockAvailable::class);
|
|
$mock->shouldReceive('addSqlShopRestriction')
|
|
->with(null, null, 'sa')
|
|
->andReturn('');
|
|
|
|
StockAvailable::setStaticExpectations($mock);
|
|
|
|
$stdClass = new stdClass();
|
|
$stdClass->shop = new stdClass();
|
|
$stdClass->shop->id = 1;
|
|
$stdClass->language = new stdClass();
|
|
$stdClass->language->id = 2;
|
|
$stdClass->country = new stdClass();
|
|
$stdClass->country->id = 3;
|
|
$stdClass->currency = new stdClass();
|
|
$stdClass->currency->id = 4;
|
|
|
|
$contextMock = Mockery::mock(Context::class);
|
|
$contextMock->shouldReceive('getContext')
|
|
->andReturn($stdClass);
|
|
Context::setStaticExpectations($contextMock);
|
|
|
|
$configurationMock = Mockery::mock(Configuration::class);
|
|
$configurationMock->shouldReceive('get')
|
|
->with('PS_LAYERED_FILTER_SHOW_OUT_OF_STOCK_LAST')
|
|
->andReturn(0);
|
|
Configuration::setStaticExpectations($configurationMock);
|
|
}
|
|
|
|
public function testGetEmptyQuery()
|
|
{
|
|
$this->assertEquals(
|
|
'SELECT FROM ps_product p ORDER BY p.id_product DESC LIMIT 0, 20',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @dataProvider oneSelectFieldDataProvider
|
|
*/
|
|
public function testGetQueryWithOneSelectField($type, $expected)
|
|
{
|
|
$this->adapter->addSelectField($type);
|
|
|
|
$this->assertEquals(
|
|
$expected,
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetMinMaxPriceValue()
|
|
{
|
|
$dbInstanceMock = Mockery::mock(Db::class)->makePartial();
|
|
$dbInstanceMock->shouldReceive('executeS')
|
|
->once()
|
|
->with('SELECT psi.price_min, MIN(price_min) as min, MAX(price_max) as max FROM ps_product p INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 4 AND psi.id_country = 3)')
|
|
->andReturn(
|
|
[
|
|
[
|
|
'price_min' => '11',
|
|
'min' => '11',
|
|
'max' => '35',
|
|
],
|
|
]
|
|
);
|
|
|
|
$dbMock = Mockery::mock(Db::class)->makePartial();
|
|
$dbMock->shouldReceive('getInstance')
|
|
->andReturn($dbInstanceMock);
|
|
|
|
Db::setStaticExpectations($dbMock);
|
|
$this->assertEquals(
|
|
[11.0, 35.0],
|
|
$this->adapter->getMinMaxPriceValue()
|
|
);
|
|
}
|
|
|
|
public function testGetMinMaxValueForWeight()
|
|
{
|
|
$dbInstanceMock = Mockery::mock(Db::class);
|
|
$dbInstanceMock->shouldReceive('executeS')
|
|
->once()
|
|
->with('SELECT MIN(weight) as min, MAX(weight) as max FROM ps_product p')
|
|
->andReturn(
|
|
[
|
|
[
|
|
'min' => '10',
|
|
'max' => '42',
|
|
],
|
|
]
|
|
);
|
|
|
|
$dbMock = Mockery::mock(Db::class);
|
|
$dbMock->shouldReceive('getInstance')
|
|
->andReturn($dbInstanceMock);
|
|
|
|
Db::setStaticExpectations($dbMock);
|
|
$this->assertEquals(
|
|
[10.0, 42.0],
|
|
$this->adapter->getMinMaxValue('weight')
|
|
);
|
|
}
|
|
|
|
public function testCount()
|
|
{
|
|
$dbInstanceMock = Mockery::mock(Db::class);
|
|
$dbInstanceMock->shouldReceive('executeS')
|
|
->once()
|
|
->with('SELECT COUNT(DISTINCT p.id_product) c FROM ps_product p')
|
|
->andReturn(
|
|
[
|
|
[
|
|
'c' => '100',
|
|
],
|
|
]
|
|
);
|
|
|
|
$dbMock = Mockery::mock(Db::class);
|
|
|
|
$dbMock->shouldReceive('getInstance')
|
|
->andReturn($dbInstanceMock);
|
|
|
|
Db::setStaticExpectations($dbMock);
|
|
$this->assertEquals(
|
|
100,
|
|
$this->adapter->count()
|
|
);
|
|
}
|
|
|
|
public function testValueCount()
|
|
{
|
|
$dbInstanceMock = Mockery::mock(Db::class);
|
|
$dbInstanceMock->shouldReceive('executeS')
|
|
->once()
|
|
->with('SELECT p.weight, COUNT(DISTINCT p.id_product) c FROM ps_product p GROUP BY p.weight')
|
|
->andReturn(
|
|
[
|
|
[
|
|
'weight' => '10',
|
|
'c' => '100',
|
|
],
|
|
]
|
|
);
|
|
|
|
$dbMock = Mockery::mock(Db::class);
|
|
$dbMock->shouldReceive('getInstance')
|
|
->andReturn($dbInstanceMock);
|
|
|
|
Db::setStaticExpectations($dbMock);
|
|
$this->assertEquals(
|
|
[
|
|
0 => [
|
|
'weight' => '10',
|
|
'c' => '100',
|
|
],
|
|
],
|
|
$this->adapter->valueCount('weight')
|
|
);
|
|
}
|
|
|
|
public function testValueCountWithInitialPopulation()
|
|
{
|
|
$this->adapter->useFiltersAsInitialPopulation();
|
|
$dbInstanceMock = Mockery::mock(Db::class);
|
|
$dbInstanceMock->shouldReceive('executeS')
|
|
->once()
|
|
->with('SELECT p.id_product, p.weight, COUNT(DISTINCT p.id_product) c FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, psales.quantity as sales FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product)) p GROUP BY p.weight')
|
|
->andReturn(
|
|
[
|
|
[
|
|
'weight' => '10',
|
|
'c' => '1000',
|
|
],
|
|
]
|
|
);
|
|
|
|
$dbMock = Mockery::mock(Db::class);
|
|
$dbMock->shouldReceive('getInstance')
|
|
->andReturn($dbInstanceMock);
|
|
|
|
Db::setStaticExpectations($dbMock);
|
|
$this->assertEquals(
|
|
[
|
|
0 => [
|
|
'weight' => '10',
|
|
'c' => '1000',
|
|
],
|
|
],
|
|
$this->adapter->valueCount('weight')
|
|
);
|
|
}
|
|
|
|
public function testValueCountWithInitialPopulationAndStockManagement()
|
|
{
|
|
$this->adapter->useFiltersAsInitialPopulation();
|
|
$this->adapter->getInitialPopulation()->addOperationsFilter(
|
|
Search::STOCK_MANAGEMENT_FILTER,
|
|
[[['quantity', [0], '>=']]]
|
|
);
|
|
|
|
$dbInstanceMock = Mockery::mock(Db::class);
|
|
$dbInstanceMock->shouldReceive('executeS')
|
|
->once()
|
|
->with('SELECT p.id_product, p.weight, COUNT(DISTINCT p.id_product) c FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, psales.quantity as sales FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product) WHERE ((sa.quantity>=0))) p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) WHERE ((sa.quantity>=0)) GROUP BY p.weight')
|
|
->andReturn(
|
|
[
|
|
[
|
|
'weight' => '10',
|
|
'c' => '1000',
|
|
],
|
|
]
|
|
);
|
|
|
|
$dbMock = Mockery::mock(Db::class);
|
|
$dbMock->shouldReceive('getInstance')
|
|
->andReturn($dbInstanceMock);
|
|
|
|
Db::setStaticExpectations($dbMock);
|
|
$this->assertEquals(
|
|
[
|
|
0 => [
|
|
'weight' => '10',
|
|
'c' => '1000',
|
|
],
|
|
],
|
|
$this->adapter->valueCount('weight')
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithAllSelectField()
|
|
{
|
|
$this->adapter->setSelectFields(
|
|
[
|
|
'id_product',
|
|
'id_product_attribute',
|
|
'id_attribute',
|
|
'id_attribute_group',
|
|
'id_feature',
|
|
'id_shop',
|
|
'id_feature_çvalue',
|
|
'id_category',
|
|
'name',
|
|
'nleft',
|
|
'nright',
|
|
'level_depth',
|
|
'out_of_stock',
|
|
'quantity',
|
|
'price_min',
|
|
'price_max',
|
|
'range_start',
|
|
'range_end',
|
|
'id_group',
|
|
'manufacturer_name',
|
|
]
|
|
);
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product, pa.id_product_attribute, pac.id_attribute, a.id_attribute_group, fp.id_feature, ps.id_shop, p.id_feature_çvalue, cp.id_category, pl.name, c.nleft, c.nright, c.level_depth, sa.out_of_stock, SUM(sa.quantity) as quantity, psi.price_min, psi.price_max, psi.range_start, psi.range_end, cg.id_group, m.name FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) INNER JOIN ps_attribute a ON (a.id_attribute = pac.id_attribute) INNER JOIN ps_feature_product fp ON (p.id_product = fp.id_product) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_product_lang pl ON (p.id_product = pl.id_product AND pl.id_shop = 1 AND pl.id_lang = 2) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 4 AND psi.id_country = 3) LEFT JOIN ps_category_group cg ON (cg.id_category = c.id_category) INNER JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer) ORDER BY p.id_product DESC LIMIT 0, 20',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithManyFilters()
|
|
{
|
|
$this->adapter->setSelectFields(
|
|
[
|
|
'id_product',
|
|
'out_of_stock',
|
|
'quantity',
|
|
'price_min',
|
|
'price_max',
|
|
'range_start',
|
|
'range_end',
|
|
]
|
|
);
|
|
|
|
$this->adapter->addFilter('condition', ['new', 'used'], '=');
|
|
$this->adapter->addFilter('weight', [10], '=');
|
|
$this->adapter->addFilter('price_min', [10], '>=');
|
|
$this->adapter->addFilter('price_min', [100], '<=');
|
|
$this->adapter->addFilter('id_product', [2, 20, 200], '=');
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product, sa.out_of_stock, SUM(sa.quantity) as quantity, psi.price_min, psi.price_max, psi.range_start, psi.range_end FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 4 AND psi.id_country = 3) WHERE p.condition IN (\'new\', \'used\') AND p.weight=\'10\' AND psi.price_min>=10 AND psi.price_min<=100 AND p.id_product IN (2, 20, 200) ORDER BY p.id_product DESC LIMIT 0, 20',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @dataProvider getManyOperationsFilters
|
|
*/
|
|
public function testGetQueryWithManyOperationsFilters($fields, $operationsFilter, $expected)
|
|
{
|
|
$this->adapter->setSelectFields($fields);
|
|
|
|
$this->adapter->addOperationsFilter(
|
|
'out_of_stock_filter',
|
|
$operationsFilter
|
|
);
|
|
|
|
$this->assertEquals(
|
|
$expected,
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithGroup()
|
|
{
|
|
$this->adapter->addSelectField('id_product');
|
|
$this->adapter->addGroupBy('id_product');
|
|
$this->adapter->addGroupBy('id_feature_value');
|
|
$this->adapter->addGroupBy('p.something_defined_by_me');
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product FROM ps_product p GROUP BY p.id_product, fp.id_feature_value, p.something_defined_by_me ORDER BY p.id_product DESC LIMIT 0, 20',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithPriceOrderFieldInDesc()
|
|
{
|
|
$this->adapter->addSelectField('id_product');
|
|
$this->adapter->setOrderField('price');
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product FROM ps_product p ORDER BY psi.price_max DESC LIMIT 0, 20',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithPriceOrderFieldInAsc()
|
|
{
|
|
$this->adapter->addSelectField('id_product');
|
|
$this->adapter->setOrderField('price');
|
|
$this->adapter->setOrderDirection('asc');
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product FROM ps_product p ORDER BY psi.price_min ASC LIMIT 0, 20',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithPriceOrderFieldInAscWithInitialPopulation()
|
|
{
|
|
$this->adapter->addSelectField('manufacturer_name');
|
|
$this->adapter->useFiltersAsInitialPopulation();
|
|
$this->adapter->setOrderField('manufacturer_name');
|
|
$this->adapter->setOrderDirection('asc');
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, psales.quantity as sales, m.name FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product) INNER JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)) p INNER JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer) ORDER BY m.name ASC',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithPositionOrderFieldInAscWithInitialPopulation()
|
|
{
|
|
$this->adapter->addSelectField('id_product');
|
|
$this->adapter->useFiltersAsInitialPopulation();
|
|
$this->adapter->setOrderField('position');
|
|
$this->adapter->setOrderDirection('desc');
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, psales.quantity as sales, cp.position FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)) p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) ORDER BY p.position DESC',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithComputeShowLastEnabled()
|
|
{
|
|
$configurationMock = Mockery::mock(Configuration::class);
|
|
$configurationMock->shouldReceive('get')
|
|
->with('PS_LAYERED_FILTER_SHOW_OUT_OF_STOCK_LAST')
|
|
->andReturn(true);
|
|
Configuration::setStaticExpectations($configurationMock);
|
|
|
|
$productMock = Mockery::namedMock(Product::class);
|
|
$productMock->shouldReceive('isAvailableWhenOutOfStock')
|
|
->with(2)
|
|
->andReturn(true);
|
|
|
|
$this->adapter->addSelectField('id_product');
|
|
$this->adapter->useFiltersAsInitialPopulation();
|
|
$this->adapter->setOrderField('position');
|
|
$this->adapter->setOrderDirection('desc');
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product, sa.out_of_stock FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, psales.quantity as sales, cp.position FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)) p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) ORDER BY IFNULL(p.quantity, 0) <= 0, IFNULL(p.quantity, 0) <= 0 AND FIELD(sa.out_of_stock, 0) ASC, p.position DESC',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function testGetQueryWithComputeShowLastEnabledAndDenyOrderOutOfStockProducts()
|
|
{
|
|
$configurationMock = Mockery::mock(Configuration::class);
|
|
$configurationMock->shouldReceive('get')
|
|
->with('PS_LAYERED_FILTER_SHOW_OUT_OF_STOCK_LAST')
|
|
->andReturn(true);
|
|
Configuration::setStaticExpectations($configurationMock);
|
|
|
|
$productMock = Mockery::namedMock(Product::class);
|
|
$productMock->shouldReceive('isAvailableWhenOutOfStock')
|
|
->with(2)
|
|
->andReturn(false);
|
|
|
|
$this->adapter->addSelectField('id_product');
|
|
$this->adapter->useFiltersAsInitialPopulation();
|
|
$this->adapter->setOrderField('position');
|
|
$this->adapter->setOrderDirection('desc');
|
|
|
|
$this->assertEquals(
|
|
'SELECT p.id_product, sa.out_of_stock FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, psales.quantity as sales, cp.position FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)) p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) ORDER BY IFNULL(p.quantity, 0) <= 0, IFNULL(p.quantity, 0) <= 0 AND FIELD(sa.out_of_stock, 1) DESC, p.position DESC',
|
|
$this->adapter->getQuery()
|
|
);
|
|
}
|
|
|
|
public function getManyOperationsFilters()
|
|
{
|
|
return [
|
|
[
|
|
'fields' => [
|
|
'id_product',
|
|
'out_of_stock',
|
|
'quantity',
|
|
'price_min',
|
|
'price_max',
|
|
'range_start',
|
|
'range_end',
|
|
],
|
|
'operationsFilter' => [
|
|
[
|
|
['quantity', [0], '>='],
|
|
['out_of_stock', [1, 3, 4], '='],
|
|
],
|
|
[
|
|
['quantity', [0], '>'],
|
|
['out_of_stock', [1], '='],
|
|
],
|
|
],
|
|
'expected' => 'SELECT p.id_product, sa.out_of_stock, SUM(sa.quantity) as quantity, psi.price_min, psi.price_max, psi.range_start, psi.range_end FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 4 AND psi.id_country = 3) LEFT JOIN ps_stock_available sa_1 ON (p.id_product = sa_1.id_product AND IFNULL(pac.id_product_attribute, 0) = sa_1.id_product_attribute) WHERE ((sa.quantity>=0 AND sa_1.out_of_stock IN (1, 3, 4)) OR (sa.quantity>0 AND sa_1.out_of_stock=1)) ORDER BY p.id_product DESC LIMIT 0, 20',
|
|
],
|
|
[
|
|
'fields' => [
|
|
'id_product',
|
|
'quantity',
|
|
],
|
|
'operationsFilter' => [
|
|
[
|
|
['id_attribute', [2]],
|
|
['id_attribute', [4]],
|
|
],
|
|
[
|
|
['quantity', [0], '>'],
|
|
['out_of_stock', [1], '='],
|
|
],
|
|
],
|
|
'expected' => 'SELECT p.id_product, SUM(sa.quantity) as quantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) LEFT JOIN ps_product_attribute_combination pac_1 ON (pa.id_product_attribute = pac_1.id_product_attribute) LEFT JOIN ps_stock_available sa_1 ON (p.id_product = sa_1.id_product AND IFNULL(pac.id_product_attribute, 0) = sa_1.id_product_attribute) WHERE ((pac.id_attribute=2 AND pac_1.id_attribute=4) OR (sa.quantity>0 AND sa_1.out_of_stock=1)) ORDER BY p.id_product DESC LIMIT 0, 20',
|
|
],
|
|
[
|
|
'fields' => [
|
|
'id_product',
|
|
'quantity',
|
|
],
|
|
'operationsFilter' => [
|
|
[
|
|
['id_attribute', [2]],
|
|
['id_attribute', [4, 5, 6]],
|
|
['id_attribute', [7, 8, 9]],
|
|
],
|
|
[
|
|
['quantity', [0], '>'],
|
|
['out_of_stock', [0], '='],
|
|
],
|
|
],
|
|
'expected' => 'SELECT p.id_product, SUM(sa.quantity) as quantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) LEFT JOIN ps_product_attribute_combination pac_1 ON (pa.id_product_attribute = pac_1.id_product_attribute) LEFT JOIN ps_product_attribute_combination pac_2 ON (pa.id_product_attribute = pac_2.id_product_attribute) LEFT JOIN ps_stock_available sa_1 ON (p.id_product = sa_1.id_product AND IFNULL(pac.id_product_attribute, 0) = sa_1.id_product_attribute) WHERE ((pac.id_attribute=2 AND pac_1.id_attribute IN (4, 5, 6) AND pac_2.id_attribute IN (7, 8, 9)) OR (sa.quantity>0 AND sa_1.out_of_stock=0)) ORDER BY p.id_product DESC LIMIT 0, 20',
|
|
],
|
|
];
|
|
}
|
|
|
|
public function oneSelectFieldDataProvider()
|
|
{
|
|
return [
|
|
['id_product', 'SELECT p.id_product FROM ps_product p ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['id_product_attribute', 'SELECT pa.id_product_attribute FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['id_attribute', 'SELECT pac.id_attribute FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['id_attribute_group', 'SELECT a.id_attribute_group FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) INNER JOIN ps_attribute a ON (a.id_attribute = pac.id_attribute) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['id_feature', 'SELECT fp.id_feature FROM ps_product p INNER JOIN ps_feature_product fp ON (p.id_product = fp.id_product) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['id_shop', 'SELECT ps.id_shop FROM ps_product p INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['id_feature_value', 'SELECT fp.id_feature_value FROM ps_product p LEFT JOIN ps_feature_product fp ON (p.id_product = fp.id_product) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['id_category', 'SELECT cp.id_category FROM ps_product p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['position', 'SELECT cp.position FROM ps_product p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['name', 'SELECT pl.name FROM ps_product p INNER JOIN ps_product_lang pl ON (p.id_product = pl.id_product AND pl.id_shop = 1 AND pl.id_lang = 2) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['nleft', 'SELECT c.nleft FROM ps_product p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['nright', 'SELECT c.nright FROM ps_product p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['level_depth', 'SELECT c.level_depth FROM ps_product p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['out_of_stock', 'SELECT sa.out_of_stock FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['quantity', 'SELECT SUM(sa.quantity) as quantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['price_min', 'SELECT psi.price_min FROM ps_product p INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 4 AND psi.id_country = 3) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['price_max', 'SELECT psi.price_max FROM ps_product p INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 4 AND psi.id_country = 3) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['range_start', 'SELECT psi.range_start FROM ps_product p INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 4 AND psi.id_country = 3) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['range_end', 'SELECT psi.range_end FROM ps_product p INNER JOIN ps_layered_price_index psi ON (psi.id_product = p.id_product AND psi.id_shop = 1 AND psi.id_currency = 4 AND psi.id_country = 3) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['id_group', 'SELECT cg.id_group FROM ps_product p INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) LEFT JOIN ps_category_group cg ON (cg.id_category = c.id_category) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
['manufacturer_name', 'SELECT m.name FROM ps_product p INNER JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer) ORDER BY p.id_product DESC LIMIT 0, 20'],
|
|
];
|
|
}
|
|
}
|