August 20, 2003 at 1:17 pm
For search capabilities of clients, I need to be able to dynamically build a where clause including the option to select AND or OR.
For example, a user should be able to state they want to see results of customers that have firstname = 'David'
and (lastname = 'Johnson'
or nickname = 'DJ')
Any thoughts on how to dynamically build this? I know generally how to build dynamic queries but am struggling with the dynamic operator. My thought was to use @operator to pass in the operator. However, this becomes difficult to do parenthesis placement.
Any advice appreciated.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
August 20, 2003 at 5:15 pm
Seems to me that the problem will lie in the user interface, not the stored proc.
I mean, if the user can indicate that they want:
firstname = 'David'
and (lastname = 'Johnson'
or nickname = 'DJ')
instead of:
(firstname = 'David'
and lastname = 'Johnson')
or nickname = 'DJ'
then it's as simple as building the where clause at the user interface end.
If that's not helpful, then perhaps you can tell us how the end user indicates what they want now (unless, of course, that's the question)
R David Francis
August 21, 2003 at 12:32 am
what has to be dynamic ?
1) the values to check on
2) the ORs and ANDs
3) something else ?
August 21, 2003 at 12:40 am
Hi bhunter1,
quote:
what has to be dynamic ?1) the values to check on
2) the ORs and ANDs
3) something else ?
in addition to the above can you post some example of what you are trying to achieve?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 21, 2003 at 9:37 am
Thanks for your responses. There is a standard query that will return all rows to the customers view. We will be allowing the customer to create their own filters on the fly. So, they will select a field from a list of fields and then pick an operator such as in, between, less than etc. They will then provide the value. Currently, this information is written to a table as they are allowed to save this filter and come back to it later. Based on what is saved, I am building a dynamic where clause to the standard query. However, currently we are assuming it is always going to use an AND operator. We want to implement the ability to use the OR operator. To this point I have been hard coding "AND" into each additional statement in the where clause. It is easy enough to add whether it should be and or or by using another field in the table. I am getting confused on how to group the OR statements together.
hth to explain.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
August 21, 2003 at 9:50 am
I've thought about this sort of thing aften, but have never actually implemented it.
That said, here are my thoughts.
Your interface will be the mechanism for determining the structure of the where clause. Any assumptions that aren't clear in the interface itself will make this difficult to use. You're already going to have problems, barring a user base that really understands boolean logic.
The user interface will need to allow users to group statements (and groups of statements) together in sets of parentheses. Let's define a "chunk" as either a simple expression:
letter = 'a'
or a series of chunks, within a set of parentheses, connected by a single boolean operator:
(letter = 'a' or letter = 'b')
(letter = 'a' and (number = 1 or number = 0))
and so on.
For a truly flexible system, your interface must allow you to build an expression chunk, and to create a chain of expressions as a complex chunk. Then, you need to be able to move around your chunks.
(I can no longer fight my punnish tendency to express that you will probably find that this will, as they say, blow; please forgive me)
Anyway, that's the path I'd take. The issue shouldn't be "How do I guess what the users want?" but "How do I let the users tell me what they want in detail?"
R David Francis
August 21, 2003 at 12:28 pm
agreed. Although we're probably frustrating you by not answering your question, I must add that we implemented exactly what your seem to be suggesting by adding '(' and ')' to the user interface.
August 21, 2003 at 2:45 pm
Definitely adding the choices of "AND" and "OR" will not cause you to many problems. Most users are familiar enough with boolean logic to get by.
Adding parenthesis to allow nesting and grouping is something altogether different. Most users can't handle it (heck, even programmers get confused by this sometimes).
The answer is to make this modular. Since users can save these selection criterias, can you make it so they can combine them? That would be a lot easier conceptually.
For example
State = 'NJ' and profit > 1000 can be saved as "NJ - over 1000 profit". Then, if the user wants something more complex they could use:
"NJ - over 1000 profit" AND Gender = 'M'
Then, if they wish, they could save that as "NJ - over 1000 profit - Males only" and use it in even more complex expressions.
A recursive, object oriented model is a lot easier for users than ")" and "(".
PS: I think this is what rdfozz was saying, but I'll post this anyway.
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply