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"