On 9 Jul 2015, at 9:37pm, Igor Tandetnik <igor at tandetnik.org> wrote:
> On 7/9/2015 11:25 AM, Andy Rahn wrote: > >> I want to build an application that can search for specific documents based >> on a lot of criteria from a user (e.g. matching keywords, text, etc...). >> And then to combine these results using boolean logic ... For example, >> keyword 'animal' AND rating > 3 >> >> Each document has an integer id. >> >> My strategy so far is to gather the id for each subquery into a temporary >> table. Then I can combine the tables using UNION or INTERSECT keywords. > > Why UNION or INTERSECT? Why not simply > > select id from documents where keyword='animal' and rating > 3; Slight change since you have more than one keyword for each document: SELECT id FROM documents WHERE keyword LIKE '%animal%' AND rating > 3 So each criterion entered by your user gets turned into a clause and you combine them by putting "AND" between them. The above example has a small problem that it cannot properly distinguish between the keywords 'man', 'human' and 'mankind' or 'red', 'murdered' and 'reduced'. If you're going to do it properly you have a keywords column which contains strings like , ,animal, ,animal,predator, ,animal,predator,bird, and you search it using SELECT id FROM documents WHERE keyword LIKE '%,animal,%' AND rating > 3 I've used a comma as a separator here for clarity but it can be any character you would never find inside a word. Simon.
