Generating User Search Queries

  • Thread starter Lawrence D'Oliveiro
  • Start date
L

Lawrence D'Oliveiro

I implemented a form in a Web-based database-management application that
lets the user search on a whole lot of different fields. I hate writing
repetitive code. But here's part of the sequence I came up with
for translating entered field values into MySQL query phrases:

condition = \
(
list
( # free-text fields
"%(name)s like %(value)s"
%
{
"name" : field[0],
"value" :
SQLString("%" + EscapeSQLWild(Params.getvalue(field[1])) + "%"
),
}
for field in
(
("make", "search_make"),
("model", "search_model"),
...
)
if Params.getvalue(field[1]) != ""
)
+
list
( # exact-match fields
"%(name)s = %(value)s"
%
{
"name" : field[0],
"value" : SQLString(Params.getvalue(field[1])),
}
for field in
(
("class_name", "search_class"),
...

)
if Params.getvalue(field[1]) != ""
)
+
list
( # date fields
"("
+
" or ".join
(
"%(name)s %(op)s %(value)s"
%
{
"name" : field[0],
"op" : op[0],
"value" : SQLString(Params.getvalue(field[1])),
}
for op in
(
("<", "lt"),
("=", "eq"),
(">", "gt"),
)
if GetCheckbox("%(name)s[%(op)s]" % {"name" : field[1], "op" : op[1]})
)
+
")"
for field in
(
("when_purchased", "search_when_purchased"),
...
)
if reduce
(
operator.__or__,
(
GetCheckbox("%(name)s[%(op)s]" % {"name" : field[1], "op" : op})
for op in ("lt", "eq", "gt")
)
)
)
)

And then you can build the whole thing into a where-clause just with

" and ".join(condition)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top