def test_query_where_clause():
query = Query()
query.set_table("Movies")
query.add_where_clause(Condition("PersonId", "=", "chrisevans"))
cmd = query.to_sql_query()
assert cmd == "SELECT * FROM boxofficementat.Movies WHERE PersonId = 'chrisevans' LIMIT 0, 100"
def test_subqueries():
query = Query()
query.set_table("Movies")
subquery1 = Query()
subquery1.set_table("Credits")
subquery1.set_return_columns(["MovieId"])
subquery1.add_where_clause(Condition("PersonId", "=", "chrisevans"))
subquery1.add_where_clause(Condition("Relationship", "=", "Actor"))
subquery2 = Query()
subquery2.set_table("Credits")
subquery2.set_return_columns(["MovieId"])
subquery2.add_where_clause(Condition("PersonId", "=", "chrishemsworth"))
subquery2.add_where_clause(Condition("Relationship", "=", "Actor"))
query.add_subquery("Id", subquery1)
query.add_subquery("Id", subquery2)
cmd = query.to_sql_query()
assert cmd == "SELECT * FROM boxofficementat.Movies " \
"WHERE Id IN (SELECT MovieId FROM boxofficementat.Credits " \
"WHERE PersonId = 'chrisevans' AND Relationship = 'Actor') " \
"AND Id IN (SELECT MovieId FROM boxofficementat.Credits " \
"WHERE PersonId = 'chrishemsworth' AND Relationship = 'Actor') LIMIT 0, 100"