Search by One-to-Many Association values without limiting the remaining values

Question

Background

My client wishes to have a Product catalogue built with many different kinds of attributes depending on the Category. The client also wishes to have quite a comprehensive search ability that checks over Product names and their attributes. The desired display format is grouped by Category and then a table of Products that are matched to the search term.

Condensed Entity Overview

Category
    - name (string)
    - products (1-to-Many:Product)
    - attributes (1-to-Many:Attribute)
Attribute
    - name (string)
    - isDropdown (bool) - flag meaning can either be custom or from the dropdown options
    - attributeOptions (1-to-Many:AttributeOption)
AttributeOption
    - attribute (Many-to-1:Attribute)
    - value
ProductAttributeValue
    - attribute (Many-to-1:Attribute)
    - selectedOption (Many-to-1:AttributeOption)
    - value (string)
Product
    - name (string)
    - attributeValues (1-to-Many:ProductAttributeValue)
    - category (Many-to-1:Category)

The issue

Searching by Product name is easy enough:

$search = 'gloves and stuff';
$searchTerms = explode(' ', $search);

$categoriesWithProducts = $em->createQueryBuilder()
    ->select('c, p, a, av, ao')
    ->from('AcmeBundle:Category', 'c')
    ->innerJoin('c.products', 'p')
    ->leftJoin('c.attributes', 'a')
    ->leftJoin('p.attributeValues', 'av')
    ->leftJoin('av.selectedOption', 'ao')
;
$i = 0;
foreach ($searchTerm as $st) {
    $categoriesWithProducts
        ->orWhere('p.name LIKE ?'.$i)
        ->setParameter($i++, '%' . $st . '%')
    ;
}
$categoriesWithProducts = $categoriesWithProducts->getQuery()->getResult();

And output is achieved with this Twig code:

{% for category in categoriesWithProducts %}
<h2>{{ category.name }}</h2>
<table>
    <thead>
        {% for attribute in category.attributes %}
            <th>{{ attribute.name }}</th>
        {% endfor %}
    </thead>
    <tbody>
        {% for product in category.products %}
            <tr>
                <td>{{ product.name }}</td>
                {% for product in category.attributes %}
                    <td>
                    {% for attributeValue in product.attributeValues if attributeValue.attribute == attribute %}
                    {{ attribute.isDropdown ? attributeValue.selectedOption.value : attributeValue.value }}
                    {% endfor %}
                    </td>
                {% endfor %}
            </tr>
        {% endfor %}
    </tbody>
</table>
{% endfor %}

Resulting output:

However when I modify the query to also match attributes from the search terms:

$categoriesWithProducts = $em->createQueryBuilder()
    ->select('c, p, a, av, ao')
    ->from('AcmeBundle:Category', 'c')
    ->innerJoin('c.products', 'p')
    ->leftJoin('c.attributes', 'a')
    ->leftJoin('p.attributeValues', 'av')
    ->leftJoin('av.selectedOption', 'ao')
;
$i = 0;
foreach ($searchTerm as $st) {
    $categoriesWithProducts
        ->orWhere('p.name LIKE ?'.$i)
        ->orWhere('av.value LIKE ?'.$i)
        ->orWhere('ao.value LIKE ?'.$i)
        ->setParameter($i++, '%' . $st . '%')
    ;
}

The search will still work for matched Product names, but produce subsets of attributeValues and selectedOption when matches are found from the $search term. For example, with $search = 'red':

And $search = 'red small':

My attempt to fix

I tried using two joins for both of the associated entities, but ended up getting duplicates in my result set which produces double output in my Twig render.

Code:

$categoriesWithProducts = $em->createQueryBuilder()
    ->select('c, p, a, av, ao, avSearch, aoSearch')
    ->from('AcmeBundle:Category', 'c')
    ->innerJoin('c.products', 'p')
    ->leftJoin('c.attributes', 'a')
    ->leftJoin('p.attributeValues', 'av')
    ->leftJoin('av.selectedOption', 'ao')
    ->leftJoin('p.attributeValues', 'avSearch')
    ->leftJoin('av.selectedOption', 'aoSearch')
;
$i = 0;
foreach ($searchTerm as $st) {
    $categoriesWithProducts
        ->orWhere('p.name LIKE ?'.$i)
        ->orWhere('avSearch.value LIKE ?'.$i)
        ->orWhere('aoSearch.value LIKE ?'.$i)
        ->setParameter($i++, '%' . $st . '%')
    ;
}

Result with $search = 'gloves and stuff':

And with $search = 'red small' (note the duplicate Red and Small entries but singular results for the other attributes):

I've tried various ways to hide the aoSearch and avSearch results:

1. Aliasing them as HIDDEN aoSearch and HIDDEN avSearch does nothing
2. Removing aoSearch and avSearch from the SELECT reverts back to the broken subset issue found in the 2nd and 3rd screenshots.

Question

I wish to get all of the attributes for a product shown in the table if a match is found among any of the Product's attributes. Is there a way I can hide the matches from aoSearch and avSearch that I've missed?

Answer 1

Only half an hour after posting this question and I have a workaround:

The only way I've figured out a solution so far is by using the EXISTS function within a single orWhere clause so the match is contained within the subquery:

$categoriesWithProducts = $em->createQueryBuilder()
    ->select('c, p, a, av, ao')
    ->from('AcmeBundle:Category', 'c')
    ->innerJoin('c.products', 'p')
    ->leftJoin('c.attributes', 'a')
    ->leftJoin('p.attributeValues', 'av')
    ->leftJoin('av.selectedOption', 'ao')
;
$i = 0;
$subQuery = '';
foreach ($searchTerm as $st) {
    if ($i > 0) $subQuery .= ' OR ';
    $subQuery = 'sao.text LIKE ?'.$i.' OR pav.text LIKE ?'.$i;
    $categoriesWithProducts
        ->orWhere('p.name LIKE ?'.$i)
        ->setParameter($i++, '%' . $st . '%')
    ;
}
$categoriesWithProducts->orWhere(
    'EXISTS (
        SELECT pav 
        FROM AcmeBundle:ProductAttributeValue pav
        LEFT JOIN pav.selectedOption sao
        WHERE pav.product = p AND ('.$subQuery.'))
    ');
$categoriesWithProducts = $categoriesWithProducts->getQuery()->getResult();

This gives me perfect search results with attributes galore!