Filtering product query with two sets of filters. (J2Store)

Question

I have two disjointed sets of product filters, $special_filters and $other_filters. I'm trying to retrieve the products from the database which match any of the filters in $special_filters AND any of the filters in $other_filters. For example, if $special_filters = {'New'} and $other_filters = {'Brakes', 'Tires'} then I want to get all new brakes and all new tires, as opposed to all new parts, all brakes, and all tires. Here's what I've gotten to work in phpMyAdmin (here the product_filters table is a 2-column table relating product_id and filter_id:

SELECT q.product_id FROM (
    SELECT product_id FROM `<prefix>_j2store_product_filters`
    WHERE filter_id = 'tag1'
) AS q
INNER JOIN `<prefix>_j2store_product_filters` AS t ON q.product_id = t.product_id
WHERE filter_id IN ('tag2', 'tag3')

This successfully finds all products that have tag1, and either tag2 or tag3. However my translation into Joomla doesn't seem to work.

I'm modifying J2Store's code in administrator/components/com_j2store/models/products.php, near the end of the _sfBuildWhereQuery function, which is setting up a JDatabase query object to fetch a list of products. At this point in the code there is a $filter_ids array which contains all of the active filters and is the union of my two filter arrays, and I need to add an appropriate "WHERE" clause to the current query object. I've basically just tried translating my sql code into JDatabase. Here's my attempt:

$other_ids = array_diff($filter_ids, $special_filters);
if (empty($special_filters) || empty($other_ids)) {
   // This is the usual case for the 'OR' operator.
   $query->where ( '#__j2store_product_filters.filter_id IN (' 
                   . implode ( ',', $filter_ids ) . ')' );
} else {
    // Make sub-sub-query to filter with special filters
    $subSubQuery = $db->getQuery(True);
    $subSubQuery
        ->select($db->quoteName('product_id'))
        ->from($db->quoteName('#__j2store_product_filters'))
        ->where($db->quoteName('filter_id') 
                . ' IN (' . implode(',', $special_filters) . ')');

    // Filter the result with the other filters in another subquery
    $subQuery = $db->getQuery(True);
    $subQuery
        ->select($db->quoteName('s.product_id'))
        ->from($subSubQuery, 's')
        ->join('INNER', 
                $db->quoteName('#__j2store_product_filters', 'f') 
                . ' ON (' . $db->quoteName('s.product_id') 
                          . ' = ' . $db->quoteName('f.product_id') . ')')
        ->where($db->quoteName('filter_id') 
                . ' IN (' . implode(',', $other_ids) . ')');

     $query->where('EXISTS (SELECT * FROM (' . $subQuery->__toString() . ') AS t 
                   WHERE ' . $db->quoteName('#__j2store_product_filters.product_id') . 
                    ' = ' . $db->quoteName('t.product_id') . ')');
}

However, it's returning too many results: it's still returning all new parts, all brakes, and all tires, instead of just new brakes and new tires.

Here is a demonstration of the conditional logic that I require: db-fiddle.com/f/o1UWTKXVTxxwyvrpcupmqu/3

Answer 1

You are better served to GROUP BY item then execute your qualifying id_filters, special_filters, and other_filters conditions on the aggregate data via the HAVING clause.

For your db-fiddle demo...

SELECT
    item
FROM item_tags
GROUP BY item
HAVING
    MAX(CASE WHEN tag IN ('Used') THEN 1 ELSE NULL END)
    AND MAX(CASE WHEN tag IN ('Brake') THEN 1 ELSE NULL END)

The 1 is used simply because the satisfying outcomes must be "truthy" versus NULL outcomes being "falsey". If both conditions evaluate as non-null, you have a qualifying item. Easy and clean.

Now, if micro-optimization is necessary, there may be a benefit to merging the two filtering lists into a single string and adding:

WHERE tag IN ($all_csv_filters_as_one)

I didn't investigate this additional clause because I didn't have realistic data to benchmark (and I'm not all that interested in doing so either). Note, this doesn't replace the HAVING clause, but rather pre-filters the "aggregate" data so that HAVING doesn't have to sift through as much.

The Joomla query builder syntax for your j2store query could look something like this:

$query = $db->getQuery(true)
    ->select("product_id")
    ->from("#__j2store_product_filters")
    ->group("product_id")
    ->having([
        "MAX(CASE WHEN filter_id IN ({$filter_ids}) THEN 1 ELSE NULL END)",
        "MAX(CASE WHEN filter_id IN ({$special_filter_ids}) THEN 1 ELSE NULL END")
    ]);

p.s.

If you want to conditionally add more HAVING clauses depending on if the respective filter is not an empty string, then you can conditionally call a new HAVING method every time you have one or more filter_id values to include. Like this...

// build $query with all of the other clauses then after ->group()...
if ($filter_ids)
{
    $query->having("MAX(CASE WHEN filter_id IN ({$filter_ids}) THEN 1 ELSE NULL END)");
}
if ($special_ids)
{
    $query->having("MAX(CASE WHEN filter_id IN ({$special_ids}) THEN 1 ELSE NULL END)");
}
if ($other_ids)
{
    $query->having("MAX(CASE WHEN filter_id IN ({$other_ids}) THEN 1 ELSE NULL END)");
}

Answer 2

My problem was actually not in the SQL. I did some more debugging and found out I was only entering the "if empty" clause, so that's why it was doing the default behaviour. My problem was that I was not understanding the variable

$filter_ids = $state->productfilter_id;

I thought it was an array, but actually it's a comma separated string! Once I took that into account my Joomla code worked.

I guess this question could be deleted, but on the other hand the above code does provide a solution to this particular problem.