Dropping out the Verity suggestions...since you did specify that you wanted
just CF and SQL. What you're proposing is not trivial, however it can be
done.
As a hack version of what I'll describe - you might be able to use Replace
(or REReplace) on the input string to get what you want some/most of the
time. For example "a AND b" could translate into "search_col like '%a%' AND
search_col like '%b%'", but that isn't going to be anything efficient...
1) You really need to index your documents. You could do it without
indexing, but the time will be abysmal. I'll leave it to you to decide
exactly how to arrange your index.
Then -
2) You will have to parse the incoming search string.
3) From the parsed search string, you will have to build a SQL where clause.
Warning - from here on out, this gets a bit technical. To properly do
parsing, you need to have a very good understanding of data structures,
regular expressions and parsing.
When I say parse, what I mean is that you need to break apart the search
string into terms and conditions. If I were doing this, I would think about
a tree structure; not an inherient data structure in CF, but it can be
emulated with an array. The tree structure is difficult to display in text
and almost as difficult to describe.
Basically - you create a regular grammar which looks something like:
S->A
A->A and A
A->A or A
A->A and not A
A->[search term]
A->(A)
This grammar should be LL1, which makes it pretty simple to parse. It can
handle any input in the language that you described; I included the ()
because it greatly expands the flexibility. How do you write a parser in
CF? I'm not even going to tackle that; it was a whole semester class in
college and really just can't be summarized in an email.
Parse the input string through the grammar and you come up with an
interpertive data structure (a tree for example) that represents the syntax
and context of the search. From that, you walk the tree to build your where
clause.
If all this is greek...I'm sorry, I don't think it can be done properly
otherwise. What I just described covered about 3 college classes and if you
haven't had them, its going to be extremely difficult. One good suggestion
if your C skills are high would be to write the parser in C and create a CFX
tag to link it. From experience, I don't think the C program would be more
than about 200 lines of code.
This is the reason most people have suggested Verity....
Brian
-----Original Message-----
From: river [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 12, 2001 7:30 AM
To: CF-Talk
Subject: boolean search with CF and SQL statement
How do you go about doing Boolean search with CF and SQL statement?
The search needs to be able to use AND, AND NOT, and OR.
So if a user types in Boston AND Bruins, the application would search for
the matches that have both Boston and Bruins.
If a user types in Boston OR Burins, the application would search for the
matches that have either Boston OR Bruins.
If a user types in Boston AND NOT Bruins, the application would search for
the matches that have Boston but not Bruins.
Is there a custom tag that's already built somewhere for this kind of thing?
Thank you for any help!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
