0

I'm trying to implementing faceted search in a Jewellery store, but failed. Problem is when try to filter the attributes table. structure is as follows:
Products Table:

Id Product_Code Product_Name
1  ABCGOLD1GM   1 gm Gold
2  ABCGOLD2GM   2 gm Gold
3  ABCGOLD394   3.94 gm Gold

Attributes Table:

Id   Attr_Name   Alias
1    Metal       metal
2    Fineness    fineness
3    Weight      weight

Product_Attributes Table:

id    product_id    attr_id   value
1         1            2      9999
2         1            3      1 gm
3         2            2      9999
4         2            3      2 gm
5         3            3      3.94 gm
6         3            2      9167 

Now i want to filter if someone selects 1 gm with 999 and 3.94 gm. If you know faceted search then you can easily know what i mean. I want to make query to do the same with the table structure above.

I used the below query but it gives only for 2 gm with 9999 but when i'm selecting 1 gm also it gives no results

select * from product_attributes where (attr_id = 4 and text = 2 gms) or (attr_id = 2 and text = 9999) group by product_id having count(*) = 2
3
Contributors
5
Replies
15
Views
2 Years
Discussion Span
Last Post by diafol
0

OK, I can see why you'd have it like this. If you're not expecting any other attributes, you could place them in the main products table as fields in their own right:

Id | Product_Code | Product_Name | metal_id | fineness | weight

It may appear to be less flexible than your current setup as in order to add an attribute, you'd have to change the schema. Also you can't easily get attributes from the table to create a dropdown or list for a form.

However, filtering on this table would be extremely easy:

SELECT ... FROM Products AS p 
    INNER JOIN Product_Attributes AS pa ON p.id = pa.product_id 
    INNER JOIN Attributes AS a ON pa.attr_id = a.Id
    WHERE (pa.attr_id = ? AND pa.`value` = ?) AND (pa.attr_id = ? AND pa.`value` = ?) 
    ORDER BY ...
    LIMIT ...

Building up the WHERE clause and associated 'bind array' for execution of a prepared statement would be a trivial thing to do.

0

First of all Thank you for your reply but in future we will add more attributes to the products..

0

First of all Thank you for your reply but in future we will add more attributes to the products..

OK, no problem

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.