0

First of all thank you for your help.

My problem is that I have to query the server with a lot of inputs. I am updating a web page that on the past only accept one input but now, i created a checkbox so the user can have more flexibility on the query:

query = session.query(table_name).filter(and_(*conditions) , or_(*multiple_choice_filter))

Where multiple choice is a list with letters like ["A", "B"] And conditions are a list of conditions:

table_name.c.column_1.op('~*')(filter_1))

Where on the past, there weren't anything else apart from the initial conditions but now I want to add the checkbox.

Somebody now how to combine in the query the conditions from the past with the multiple_choice_filter?

Thank you!

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
BorjaP
  • 45
  • 4
  • [Using OR in SQLAlchemy](https://stackoverflow.com/questions/7942547/using-or-in-sqlalchemy) - the link in the answer will also help you with the `and_` syntax you need. – benvc Nov 21 '18 at 14:36

1 Answers1

0

I found how to do it.The point of my question is to chain AND with OR.

First, create the filters:

conditions = []

for the and statement is very straight forward:

conditions.append(table_name.c.column_name_1.op('~*')(value_1))
conditions.append(table_name.c.column_name_2.op('~*')(value_2))
conditions.append(table_name.c.column_name_3.op('~*')(value_3))

query = session.query(table_name).filter(and_(*conditions)).order_by(table_name.c.column_name_1)

Which is translated to:

SELECT   *   FROM     table_name WHERE (column_name_1 == value_1 AND column_name_2 == value_2  AND column_name_3 == value_3)

Now, to chain conditions with OR and AND:

We use two different variables: one for the AND statement (exactly like above) and another other one like the next one:

filter_or_1.append(table_name.c.column_name_4.op('~*')(value_4))# First possible value for the OR statement
filter_or_1.append(table_name.c.column_name_4.op('~*')(value_5))# Second possible value for the OR statement
filter_or_1.append(table_name.c.column_name_4.op('~*')(value_6))# Third possible value for the OR statement

and the query will be:

query = session.query(table_name).filter(and_(*conditions) , and_(or_(*filter_or_1))).order_by(table_name.c.column_name_1)

Which is translated to:

SELECT   *   FROM     table_name WHERE (column_name_1 == value_1 AND column_name_2 == value_2  AND column_name_3 == value_3 AND(column_name_4==value_4 OR column_name_4==value_5 column_name_4==value_6 ))
BorjaP
  • 45
  • 4