Member Avatar for Szabi Zsoldos

Hi guys,

Trying to develop a custom facet like search in Mysql.
I have 3 tables for this:

1.Attribute class
2.Attribute class Name
3.Attribute class Value
    - attributeClassName Id
    - productId
4.Products

Each product has one attribute class
Each attribute class has many values

Each value is contained in a sepparate row in the AttributeClassValue table, how can i Filter by values in that table ?
Group Concat is not the best solution....

  • 1 Contributor
  • 1 Reply
  • 217 Views
  • 28 Minutes Discussion Span
  • Latest Post Latest Post by Szabi Zsoldos

Dani AI

Generated

’s conditional-aggregation pivot is a useful way to show attributes as columns, but it’s brittle for filtering because it requires a fixed set of attribute IDs and a lot of GROUP work. For faceted filters it’s generally simpler and faster to treat the attribute table as normalized rows and use set logic over those rows.

One common pattern for "match all selected values" (AND semantics) is to filter by the wanted value IDs and then GROUP BY product, requiring the number of distinct matched values to equal the number of filters:

SELECT p.id, p.SKU, p.productName
FROM products p
JOIN product_attribute_values pav ON p.id = pav.productId
WHERE pav.attributeValueId IN (10,20,30)
GROUP BY p.id
HAVING COUNT(DISTINCT pav.attributeValueId) = 3;

For a small, fixed number of filters a chain of self-joins can be slightly faster because each join applies an index filter early:

SELECT p.id
FROM products p
JOIN product_attribute_values a ON a.productId = p.id AND a.attributeValueId = 10
JOIN product_attribute_values b ON b.productId = p.id AND b.attributeValueId = 20;

Indexing and query shape matter most. Add composite indexes to support the chosen access pattern (for example, (attributeValueId, productId) or (productId, attributeValueId)), run EXPLAIN to confirm index use, and avoid scanning large result sets unnecessarily. For large catalogs, precompute facet counts or use a dedicated search engine (Elasticsearch/Solr) or a denormalized signature table for very fast lookups. When a pivoted display is still desired, generate the pivot columns dynamically (prepared statement) or pivot in the application layer rather than hard-coding many MAX(IF(...)) columns.

Member Avatar for Szabi Zsoldos

Got my solution.

SELECT p.SKU,
  p.productName,
  MAX(IF(t.AttributeClassValueId = '1', t.AttributeClassValue, NULL)) AS Atribute1,
  MAX(IF(t.AttributeClassValueId = '2', t.AttributeClassValue, NULL)) AS Atribute2,
  MAX(IF(t.AttributeClassValueId = '3', t.AttributeClassValue, NULL)) AS Atribute3,
  MAX(IF(t.AttributeClassValueId = '4', t.AttributeClassValue, NULL)) AS Atribute4,
  MAX(IF(t.AttributeClassValueId = '5', t.AttributeClassValue, NULL)) AS Atribute5
FROM CatSys_products p
    LEFT JOIN CatSys_productAttributeClassValuesFromProducts t ON p.id = t.ProductId
    LEFT JOIN CatSys_productAttributeClassValues a ON t.AttributeClassValueId = a.id
GROUP BY p.SKU, p.productName
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.