Build a filters system

Discussion in 'Databases' started by myself2009, Nov 25, 2009.

0
  1. #1
    Hello everyone. This is my first post and is already urging for help...

    I'm trying to build a filters system for an online shopping cart. For example, let's say i'm in "Shirts" category. The available filters are: "Country", "Color" and "Team". Each filter has multiple values, for example the "Country" filter has "England", "Belgium", "Austria" values, "Color" filter has "Red","Green","Blue" values and "Team" filter has "Local" and "International" values

    More graphical:

    [Country]
    |____ England
    |____ Belgium
    |____ Austria

    [Color]
    |____ Red
    |____ Green
    |____ Blue

    [Team]
    |____ Local
    |____ International

    When i check the "Local" value i'm getting all products that are local.
    When i check and the "Green" value, i'm getting all products that are local and green.
    When i check and the "England" i'm getting all products that are local, green and ONLY england (the selecting order is random... the country can be selected first and after that the colour and local).

    I tried in two ways, both failures.

    Option 1.
    I have two tables:

    t.products and t.filters

    [t_products]
    idProduct
    some_other_field
    some_other_field
    some_other_field

    [t_products] data:
    1 | some_data | some_data | some_data

    [t_filters_values]
    idValue
    idProduct
    value -- which is type varchar

    [t_filters_values] data:

    1 | 1 | Red
    2 | 1 | Blue
    3 | 1 | Local

    The query i'm trying is:

    
select * from t_products
inner join t_filters_values on (t_products.idProduct = t_filters_values.idProduct)
where (t_filters_values.value like '%Local%' and t_filters_values.value like '%Red%' and t_filters_values.value like '%Blue%')
    Code (markup):
    and i expect to get one row. But it does not happening. Why ?

    If i have only one value to match, it works. For example

    
select * from t_products
inner join t_filters_values on (t_products.idProduct = t_filters_values.idProduct)
where (t_filters_values.value like '%Local%')
    Code (markup):
    Option 2.

    I have just one table:

    [t_products]
    idProduct
    filters
    some_other_field
    some_other_field

    [t_products] data:
    1 | Red,Blue,Local,Austria | some_data | some_data

    If i try

    
select * from t_products
where t_products.filters like '%Red%' and t_products.filters like '%Local%' and t_products.filters like '%Austria%'
    Code (markup):
    it works. But if i try

    
select * from t_products
where t_products.filters like '%Red%' and t_products.filters like '%Blue%' and t_products.filters like '%Local%' and t_products.filters like '%Austria%'
    Code (markup):
    it fails. Why ?

    I thinking that maybe my logic is all wrong. In this case, how should i think the system ? What is the best way to make a filters system ? I'm opened for any tables structure.

    The number of filters (and values) is variable and unknown at any given time. A product can have one filter or five or seven or 2 and so on... That's why i did not chose to add extra columns in the product table...

    Thank you in advance and thank you for your time spent to read this.
     
    myself2009, Nov 25, 2009 IP