I'm communicating with Prestashop 1.5 Web Services for listing products and its filters are simple enough for basic cases, but what about multiple categories:
- Can I get a list of products that belong to category 1 AND 2?
- Can I get a list of products that belong to category 1 OR 2?
For your information here is Prestashop 1.5 Filter management process in classes/webservice/WebserviceRequest
:
/**
* get SQL retrieve Filter
*
* @param string $sqlId
* @param string $filterValue
* @param string $tableAlias = 'main.'
* @return string
*/
protected function getSQLRetrieveFilter($sqlId, $filterValue, $tableAlias = 'main.')
{
$ret = '';
preg_match('/^(.*)\[(.*)\](.*)$/', $filterValue, $matches);
if (count($matches) > 1)
{
if ($matches[1] == '%' || $matches[3] == '%')
$ret .= ' AND '.bqSQL($tableAlias).'`'.bqSQL($sqlId).'` LIKE "'.pSQL($matches[1].$matches[2].$matches[3])."\"\n";
elseif ($matches[1] == '' && $matches[3] == '')
{
if (strpos($matches[2], '|') > 0)
{
$values = explode('|', $matches[2]);
$ret .= ' AND (';
$temp = '';
foreach ($values as $value)
$temp .= bqSQL($tableAlias).'`'.bqSQL($sqlId).'` = "'.bqSQL($value).'" OR ';
$ret .= rtrim($temp, 'OR ').')'."\n";
}
elseif (preg_match('/^([\d\.:-\s]+),([\d\.:-\s]+)$/', $matches[2], $matches3))
{
unset($matches3[0]);
if (count($matches3) > 0)
{
sort($matches3);
$ret .= ' AND '.$tableAlias.'`'.bqSQL($sqlId).'` BETWEEN "'.pSQL($matches3[0]).'" AND "'.pSQL($matches3[1])."\"\n";
}
}
else
$ret .= ' AND '.$tableAlias.'`'.bqSQL($sqlId).'`="'.pSQL($matches[2]).'"'."\n";
}
elseif ($matches[1] == '>')
$ret .= ' AND '.$tableAlias.'`'.bqSQL($sqlId).'` > "'.pSQL($matches[2])."\"\n";
elseif ($matches[1] == '<')
$ret .= ' AND '.$tableAlias.'`'.bqSQL($sqlId).'` < "'.pSQL($matches[2])."\"\n";
elseif ($matches[1] == '!')
$ret .= ' AND '.$tableAlias.'`'.bqSQL($sqlId).'` != "'.pSQL($matches[2])."\"\n";
}
else
$ret .= ' AND '.$tableAlias.'`'.bqSQL($sqlId).'` '.(Validate::isFloat(pSQL($filterValue)) ? 'LIKE' : '=').' "'.pSQL($filterValue)."\"\n";
return $ret;
}
From this we can notice that, unlike what the official documention says:
- We can simulate a
OR
condition with a pipe |
between values.
- We can simulate a
BETWEEN
condition with a coma ,
between values.
- We can simulate a
LIKE %%
condition with a leading or/and a following percent %
of our value.
- We can simulate a
>
condition with a >
in front of the value.
- We can simulate a
<
condition with a <
in front of the value.
- We can simulate a
!=
condition with a !
in front of the value.
But I can't find a solution to simulate a AND
condition. If you find one feel free to update this answer.
You can easily override this method to add your own AND
filter.
For Prestashop 1.6 there's a little difference with the !
condition:
} elseif ($matches[1] == '!') {
$multiple_values = explode('|', $matches[2]);
foreach ($multiple_values as $value) {
$ret .= ' AND '.$tableAlias.'`'.bqSQL($sqlId).'` != "'.pSQL($value)."\"\n";
}
}
- We can simulate a
NOT IN()
by adding a pipe |
between each values with a leading !
.