SQL - 过滤搜索结果

Question

OK – need some help here – might have bitten off more than I can chew here – but I’m looking to write a SQL query that always returns a minimum of 10 results. If the first condition is applied and the result set exceeds 10 results then move on to the next condition etc..

Example Find all the small red plastic toy cars that cost less than £5.00.

I always want to show a minimum of 10 items on the screen. Want to search on “small” then on “red” and then on “< £5.00”. If the query returns more than 10 items then continue to filter on as many tags as possible (i.e plastic, toy and car) – the more matches it can make the higher it should be ranked (i.e if a products has all 3 tags associated with it – it will be at the top of the list, if a product only matches 1 tag – then this will be lower down the list.

Price Table
ID      Price
1       £1.50
2       £2.50
3       £6.00
.
Colour Table
ID      Colour
1       Red
2       Blue
3       Yellow
.
Size  Table
ID      Shape
1       Small
2       Medium
3       Large
.
Products Table
ID  Description    price_id     colour_id   size_id
1   Item 1         1            2           2
2   Item 2         2            2           1
3   Item 3         1            1           1
4   Item 4         3            2           3
5   Item 5         3            1           2
6   Item 6         1            1           2
7   Item 7         1            1           3

 .
Tags Table
ID      Description
1   Shiny
2   Plastic
3   Wood
4   Toy
5   Disney
6   Animal
7   Car
.
Items_Tags Table
ID      tag_id      product_id
1       1           1
2       4           1
3       7           1
4       2           2
5       3           3
6       4           3
7       7           6
8       7           7
9       7           2

Quite a long example - but I hope you get the point. I have wondered whether there would be any benefit to putting all the filters within the tags table - i.e the price, colour and size and then would only have to search against the tags table.

Any ideas anyone?

Thanks

Answer 1

It depends on your usage: filtering integers is faster than string (by the way, are they indexed?) but JOIN is also time consuming. So if the code outside the sql works with the integers, keep them (and don't join if not required to show the text)