Generating User Search Queries

[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)