I am trying to write a query for an advanced search tool but having some trouble wrapping my head around how to make the query work.
Here is the scenario
User wants to search shoes website. She selects from the advanced search:
Designer Checkboxes Selected: Steve Madden
Color Checkboxes Selected: Black, Brown
keyword Text Field: wedge heel
She wants to do a broad search so she wants to return all records that meet any of her criteria but wants to get the most relevant results first.
Here is my database:
SHOES:
id | price | name | sku | decription
1 | 30.00 | Chloe | 004351 | stylish modern shoes with wedge heel
2 | 60.00 | Verona | 004441 | casual but elegant flats
3 | 40.00 | Streetslider | 004111 | urban trendy sneakers
XREF_SHOE_DESIGNER
shoe_id | designer_id
1 | 21
2 | 21
3 | 24
REF_SHOE_DESIGNER
ID | Designer
21 | Steve Madde
22 | Nike
23 | Keds
24 | Puma
XREF_SHOE_COLOR
Shoe_ID | Color_ID
1 | 1
2 | 1
3 | 3
REF_SHOE_COLOR
1 | Black
2 | White
3 | Brown
So she should first see Chloe since it is black, Steve Madden and wedge heel
next should be Verona since it is black and Steve Madden. Streetslider would appear last since it is brown and therefore least relevant.
