-->

Listing products with multiple categories in Prest

2019-07-30 19:04发布

问题:

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:

  1. Can I get a list of products that belong to category 1 AND 2?
  2. Can I get a list of products that belong to category 1 OR 2?

回答1:

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 !.