Generating User Search Queries

Discussion in 'Python' started by Lawrence D'Oliveiro, Apr 10, 2009.

  1. 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)
     
    Lawrence D'Oliveiro, Apr 10, 2009
    #1
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Richard K Bethell

    IXXSO queries and asp.net search pages

    Richard K Bethell, Nov 21, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    479
    Richard K Bethell
    Nov 24, 2003
  2. Dedicated 2 Java

    Extract from Search Engine Queries

    Dedicated 2 Java, Jul 29, 2003, in forum: Java
    Replies:
    3
    Views:
    565
    Dedicated 2 Java
    Aug 1, 2003
  3. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,843
    Crutcher
    Feb 26, 2006
  4. Alec Taylor
    Replies:
    0
    Views:
    196
    Alec Taylor
    Oct 11, 2011
  5. Alec Taylor
    Replies:
    5
    Views:
    293
    alex23
    Oct 15, 2011
  6. Abby Lee
    Replies:
    5
    Views:
    673
    Abby Lee
    Aug 2, 2004
  7. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    595
    Aaron [SQL Server MVP]
    Aug 6, 2004
  8. Replies:
    2
    Views:
    153
Loading...