[web] (SQL) Problems in realizing a search query.
Author
Hi,
I am working on a website for a school project and am having some problems with a particular search query I'd like to do in SQL.
Here is a sketch of what my tables look like:
Item(ID, Name, ...)
ItemToTag(ItemID, TagID)
Tag(ID, Text)
Every Item can have 0 or more tags associated with it.
I would like to do search with several keywords (p.e. "german beer bottle") where only such items should be returned for which every keyword is either contained in the Item.Name or one of it Tag.Text's .
I know how to check whether all keywords are contained in the name and I know how to find items that have a tag for every keyword.
But I am having difficulties to mix it.
A little example maybe.
Say I have those items (Name: German Beer, Tag: Bottle), (Name: Beer, Tag: Bottle, German), (Name: German Beer, Tag: Pint).
In that case a search for "german beer bottle" should only return the first 2 items. A search for beer should return every item.
Any ideas?
IF YOUR USING PHP... to fetch and return the results, use explode() to split the keywords (and/or name), and search query into individual words (searching for 'german beer', and split it into 'german' and 'beer'). Then use a loop to go through each word in the search query and attempt to match it to one or some of the Tags (and/or name words split up earlier). Then you can play with the results, return them in order of different statistics, etc...
PLAIN SQL... I don't know much for SQL searching except for the `LIKE` parameter.
Sorry, I don't know much about searching in general either, if your using PHP my only suggestion would be to play with explode().
PLAIN SQL... I don't know much for SQL searching except for the `LIKE` parameter.
Sorry, I don't know much about searching in general either, if your using PHP my only suggestion would be to play with explode().
Author
Thanks for the reply AaronA,
I am using asp.net and use Split() for the thing you describe.
Are you suggesting, that I load all the items from the db and then search through them in my language of choice? I thought about that, but was afraid that it maybe wouldn't be such a good idea if there were a lot of items. Still an option though.
If someone can show me how to do it all in sql though that would be cool.
I am using asp.net and use Split() for the thing you describe.
Are you suggesting, that I load all the items from the db and then search through them in my language of choice? I thought about that, but was afraid that it maybe wouldn't be such a good idea if there were a lot of items. Still an option though.
If someone can show me how to do it all in sql though that would be cool.
Hmmm. How about something like:
Run this for every word in the query and take the intersection of all the result sets. You could also build your query dynamically and put the entire WHERE clause in a big AND:
Perhaps there's even some variable magic you can pull off here, but that's beyond me.
SELECT DISTINCT i.IDFROM Items i, ItemToTag it, Tag tWHERE i.Name LIKE '%word%' OR ( t.Text = 'word' AND t.ID = it.TagID AND t.ItemID = i.ID)Run this for every word in the query and take the intersection of all the result sets. You could also build your query dynamically and put the entire WHERE clause in a big AND:
SELECT DISTINCT i.IDFROM Items i, ItemToTag it, Tag tWHERE ( i.Name LIKE '%word-1%' OR ( t.Text = 'word-1' AND t.ID = it.TagID AND t.ItemID = i.ID )) AND ( i.Name LIKE '%word-2%' OR ( t.Text = 'word-2' AND t.ID = it.TagID AND t.ItemID = i.ID )) ...Perhaps there's even some variable magic you can pull off here, but that's beyond me.
Author
I am running the MS SQL Server 2005. Not sure what version of SQL that is.
@Sander:
About the second code snippet you posted.
You create several rows where every item is represented as many times as it has tags, right?
One of those rows is only selected if either the name or the tag contains every word, right?
If that is what the code does it is not really what I want. I ran into a similar problem already.
BTW, what would happen if an item did not have any tags at all. It could not possibly get selected I suppose.
This is turning out harder than I thought.
@Sander:
About the second code snippet you posted.
You create several rows where every item is represented as many times as it has tags, right?
One of those rows is only selected if either the name or the tag contains every word, right?
If that is what the code does it is not really what I want. I ran into a similar problem already.
BTW, what would happen if an item did not have any tags at all. It could not possibly get selected I suppose.
This is turning out harder than I thought.
Quote:Original post by B_old
I know how to check whether all keywords are contained in the name and I know how to find items that have a tag for every keyword.
But I am having difficulties to mix it.
I haven't really looked at the problem, but how about you just 'union' to two result sets?
SELECT ...UNION [ALL]SELECT ...Quote:You create several rows where every item is represented as many times as it has tags, right?
Yes. It's basically the same as the first snipped but with the WHERE clause AND-ed together, Once for every word in your query. It's the same thing as running snippet 1 separately for each word, then taking the intersection of all result sets with ASP.
Quote:One of those rows is only selected if either the name or the tag contains every word, right?
No. An ItemID is selected if all the search words are in either the name or the tag. It does *exactly* what you described in your example in the first post. If my code is flawed then your original example is flawed. If you look back at your example, you say that with the search query "german beer bottle" should only return the first two items. My code does that because the word "bottle" does not appear in either the name or the tags of item 3.
Author
@Sander:
You are right! I didn't see those brackets in your code.
And after testing I have to say it really works good.
Thanks a lot for the help!
You are right! I didn't see those brackets in your code.
And after testing I have to say it really works good.
Thanks a lot for the help!
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
