SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Build a filters system

    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:

    Code:
    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%')
    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

    Code:
    select * from t_products 
    inner join t_filters_values on (t_products.idProduct = t_filters_values.idProduct)
    where (t_filters_values.value like '%Local%')
    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

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

    Code:
    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%'
    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.

    Thank you in advance and thank you for your time spent to read this.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you forgot the obvious simple solution...
    Code:
    CREATE TABLE t_products
    ( idProduct INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , some_other_field VARCHAR(99)
    , some_other_field VARCHAR(99)
    , country VARCHAR(99)
    , colour  VARCHAR(99)
    , team    VARCHAR(99)
    );
    
    
    SELECT * 
      FROM t_products
     WHERE country = 'Austria'
       AND colour = 'Red' 
       AND team = 'Local'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry, i forgot to mentions the fact that 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...

    (also forgive me that i'm "playing" in two forums... )

  4. #4
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nothing... anybody ? Please ?

  5. #5
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Option 3
    I have three tables:

    t_products, t_filters and t_filters_products

    [t_products]
    idProduct
    some_other_field
    some_other_field
    some_other_field

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

    [t_filters]
    idFilter
    filter_value -- which is type varchar

    [t_filters] data:

    1 | Red
    2 | Blue
    3 | Men
    4 | Local

    [t_filters_products]
    idFilter
    idProduct

    [t_filters_products] data:
    1 | 1
    2 | 1
    3 | 1
    4 | 1

    How will a query look like that will do the following:

    select all products that are "Red" and "Blue", are "Local" and are for "Men" ?

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by myself2009 View Post
    How will a query look like that will do the following:

    select all products that are "Red" and "Blue", are "Local" and are for "Men" ?
    There are two ways that I can think of to do it (though Rudy could probably give you a dozen quite a few more)

    There's the first way which would be the easiest to build in your code because you'd just be adding another and statement. This one (I believe) would get very expensive in terms of processing time/power the more options/filters you apply.
    Code SQL:
    SELECT idProduct
         , some_other_field
         , some_other_field
         , some_other_field
      FROM t_products
     WHERE idProduct IN (SELECT idProduct 
                           FROM t_filter_products p 
                           JOIN t_filters F ON p.idFilter = f.idFilter 
                          WHERE filter_value = 'Red')
       AND idProduct IN (SELECT idProduct 
                           FROM t_filter_products p 
                           JOIN t_filters F ON p.idFilter = f.idFilter 
                          WHERE filter_value = 'Blue')
       AND idProduct IN (SELECT idProduct 
                           FROM t_filter_products p 
                           JOIN t_filters F ON p.idFilter = f.idFilter 
                          WHERE filter_value = 'Local')
       AND idProduct IN (SELECT idProduct 
                           FROM t_filter_products p 
                           JOIN t_filters F ON p.idFilter = f.idFilter 
                          WHERE filter_value = 'Men')

    Then there's the second way which will require a little more care, but should be less expensive to process with a lot of options/filters applied. You'd just need to change the IN clause with the filters to apply, as well as the COUNT(*) = with the total number of filters you added to the IN clause.
    Code SQL:
    SELECT idProduct
         , some_other_field
         , some_other_field
         , some_other_field
      FROM t_products
     WHERE idProduct IN (SELECT ProductID
                           FROM t_filters 
                          INNER JOIN t_filters_products ON t_filters.FilterID = t_filters_products.FilterID
                          WHERE FilterValue IN ('Red','Blue','Local','Men')
                          GROUP BY productID
                         HAVING COUNT(*) = 4)

    I personally would go for door number two since the cost is about the same no matter how many filters you apply, but the final call will be yours since you have to maintain it.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you DaveMaxwell,

    I'll try your proposals and get back with the status...

  8. #8
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, i tried every suggestion and the result is the same:

    * if i chose ONLY ONE value foreach filter, the products filtering is ok

    For example: select all products that are "Red" and are in "Europe" and are for "Men"

    * if i chose MORE THAN ONE value for a filter, the products filtering fails

    For example: select all products that are "Red" and "Green" and are in "Europe" and are for "Men"

    Is the database structure wrong ? How it should look like then ?

    I know it's not impossible because others are doing it ok (see every serious online shop)... it's just i don't see how...

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Which database structure are you using? I used the latest structure you provided, and my examples both worked.

    Are you sure the data is what you're expecting?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  10. #10
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've attached the sql dump of my tables along with their data
    Attached Files Attached Files

  11. #11
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    OK, I'll need to look at that in a little bit. That structure is just different enough to cause problems with the sample I provided you.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  12. #12
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Well, the structure is not quite what I expected, and I think that's part of what's causing you issues. I will mention first off - check your ios_filters table. There are quite a few duplicates in there(especially in groups 3, 4 and 5), and in the long run, that's going to cause you trouble. Next, let's look at your structure. Here's the structure you have at the moment:

    Table: ios_filters. Fields: idFilter (key), idGroup, filter_value

    Table: ios_filters_groups. Fields: idGroup (key), group_name

    These table structures seems OK.

    Table: ios_filters_products. Fields: idEntry (key), idFilter, idProduct, filter, value,

    This table is where I believe your problem lies. The structure (if the last structure is what you want - and I think it should be) needs to be like this:

    Table: ios_filters_products. Fields: filterProducID (key), idFilter, idProduct

    That's it. The filters need to be finite and set, not loose like they were.

    As for table ios_products, I would remove size, colour and filters from that list - those should be filters which could/would be applied.

    Note: I'm assuming you're going to be doing inventory for these products? If so, I would create a child table for products, and apply the filters to the child table instead of the product table. This child table is where you would keep the inventory attached. This would also allow you to provide different prices for the options (if one team is much more popular and/or harder to get a hold of, you can charge more for it).

    So I personally would set up the tables like this (note: I'm leaving in fields I don't understand, but some of these may be more appropriate in the child table):
    Code:
    Table: ios_filters         Fields: idFilter (key), idGroup, filter_value
     
    Table: ios_filters_groups  Fields: idGroup (key), group_name
     
    Table: ios_products        Fields: idProduct (key), sku, idCat, price, price_curency, price_vat, price_with_vat, special_price, special_price_curency, special_price_vat, special_price_with_vat, aquisition_price, aquisition_price_curency, aquisition_price_vat, aquisition_price_with_vat, meta_robots, images, idManufacturer, manufacturer_specs, type, attributes, related, status_active, land_on, idGroup, idPromo, physical, attachment, movies, views, recomended, sort_order, avail, created, modified
     
    Table: ios_product_options Fields: idProductOption(key), idProduct, sku, optionDesc, pricedelta, active, inventoryQty
     
    Table: ios_filters_product_options.  Fields: filterOptionID (key), idFilter, idProductOption
    Now, the downside to this is the query that would be needed to build what can be ordered (i.e. what's in inventory) will be more difficult, but it will be more accurate.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  13. #13
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, i got it to work

    I have used one of your solution, DaveMaxwell, the one that you think it would be slow... Did some benchmarks, it runs ok... so far...

    I modified a bit in order to select multiple values for the same filter... Now it looks like this:

    Code MySQL:
    SELECT *
    FROM ios_products 
    WHERE (
        ios_products.idProduct IN (
            SELECT idProduct
            FROM ios_filters_products p
            JOIN ios_filters f ON p.idFilter = f.idFilter
            WHERE filter_value = 'Men'
        )
    AND (
            ios_products.idProduct IN (
                SELECT idProduct
                FROM ios_filters_products p
                JOIN ios_filters f ON p.idFilter = f.idFilter
                WHERE filter_value = 'Red'
            )
            OR ios_products.idProduct IN (
                SELECT idProduct
                FROM ios_filters_products p
                JOIN ios_filters f ON p.idFilter = f.idFilter
                WHERE filter_value = 'Green'
            )
        )
    )

    The modification is that OR between different values of the same filter.

    Thank you very much for your time and patience

    This topic can be marked as SOLVED.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •