3
\$\begingroup\$

I have an app where the user can search for items in my database by entering a set of information, then the server receives it and for each option it queries the database, and stores the result in a list. I have around 10 different options and each option have from two to three possible values, so it's taking around 200 lines alone.

I use SQLAlchemy to handle the database from my Python backend. The way this works is that all options are sent as a dict via HTTP to the server, on the backend I use around a dozen of conditional statement to run the right query for each option.

I will only show how it works for 3 options, because all of them share almost the same logic:

if "type" in query and query["type"] is not None:
    if query["type"] == "blog":
        s = table.select(table.c.wordpress >= "2" )
        temp_list = self.run(s)
        full_list.append(temp_list)
    if query["type"] == "ecommerce":
        s = table.select(and_(table.c.ecommerce >= "3", table.c.ssl == "true"))
        temp_list = self.run(s)
        full_list.append(temp_list)
    if query["type"] == "other":
        s = table.select().all()
        temp_list = self.run(s)
        full_list.append(temp_list)
if "storage" in query and query["storage"] is not None:
    if query["storage"] == "10":
        s = table.select(or_(table.c.storage >= "10",table.c.storage == "0") )
        temp_list = self.run(s)
        full_list.append(temp_list)
    if query["storage"] == "100":
        s = table.select(or_(table.c.storage >= "100", table.c.storage == "0"))
        temp_list = self.run(s)
        full_list.append(temp_list)
    if query["storage"] == "0":
        s = table.select(table.c.storage == "0")
        temp_list = self.run(s)
        full_list.append(temp_list)
if "bandwidth" in query and query["bandwidth"] is not None:
    if query["bandwidth"] == "10":
        s = table.select(or_(table.c.bandwidth >= "10",table.c.bandwidth == "0") )
        temp_list = self.run(s)
        full_list.append(temp_list)
    if query["bandwidth"] == "100":
        s = table.select(or_(table.c.bandwidth >= "100", table.c.bandwidth == "0"))
        temp_list = self.run(s)
        full_list.append(temp_list)
    if query["bandwidth"] == "0":
        s = table.select(table.c.bandwidth == "0")
        temp_list = self.run(s)
        full_list.append(temp_list)

Type, storage and bandwidth are options, for example type has 3 possible values: blog, ecommerce or other, and for each possible value I'm running an SQL statement to get the DB results that meet that query. run is a function that executes the SQL query(s) and returns a list of rows.

This is working very well and very fast (MY databases don't have more than 50 rows). However, there are multiple option with multiple values, and I'm using an if statement for each option and each value, making this part of the app around 200 lines of code. I would like to find a shorter way to achieve this.

Notes:

  • Users can only select one value for each option
  • I can't use the AND and OR operators in one query, because there are values that need a whole logic. For example, the value "0" on storage means unlimited so it would be actually bigger than 100 and 10 so I can't use just one SQL query for that logic.
\$\endgroup\$
1
  • \$\begingroup\$ This is a nice question, but the code is somewhat lacking, if you were to include more of the code, such as run and the rest of this class function, then we'd be able to give a better review. As it stands, it mostly comes down to using a function. \$\endgroup\$
    – Peilonrayz
    Commented Jan 13, 2017 at 12:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.