def test_select_invalid(): query = Query(User).select(100) message = "`int` is not a valid query expression" with pytest.raises(InvalidExpressionError, match=message): query.to_sql() with pytest.raises(InvalidExpressionError, match=message): query._deserialize({})
def test_join(): query = Query(User).join("pets") sql = to_sql(query.to_sql()) assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql
def test_where_gte(): query = Query(User).where(id__gte=1) assert "WHERE users.id >= 1" in to_sql(query.to_sql())
def test_where_endswith(): query = Query(User).where(name__endswith="e") assert "WHERE (users.name LIKE '%%' || 'e')" in to_sql(query.to_sql())
def test_where_contains(): query = Query(User).where(name__contains="u") assert "WHERE (users.name LIKE '%%' || 'u' || '%%')" in to_sql(query.to_sql())
def test_where_like(): query = Query(User).where(name__like="Sue") assert "WHERE users.name LIKE 'Sue'" in to_sql(query.to_sql())
def test_select_raw(): query = Query(User).select(raw(100)) assert "SELECT 1" in to_sql(query.to_sql())
def test_outerjoin_duplicate(): query = Query(User).join("pets").outerjoin("pets") sql = to_sql(query.to_sql()) assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql assert "LEFT OUTER JOIN pets AS p1 ON p1.owner_id = users.id" in sql
def test_join_alias(): query = Query(User).join("pets", "p") sql = to_sql(query.to_sql()) assert "JOIN pets AS p ON p.owner_id = users.id" in sql
def test_missing_join(): query = Query(User).join("pets.owner") with pytest.raises(MissingJoinError): query.to_sql()
def test_outerjoin_with_join(): query = Query(User).join("pets").outerjoin("pets.owner") sql = to_sql(query.to_sql()) assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql assert "LEFT OUTER JOIN users AS u0 ON u0.id = p0.owner_id" in sql
def test_limit(): query = Query(User).limit(29) assert "LIMIT 29" in to_sql(query.to_sql())
def test_nested_join_back(): query = Query(User).join("pets").join("pets.owner").join("pets.owner.pets") sql = to_sql(query.to_sql()) assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql assert "JOIN users AS u0 ON u0.id = p0.owner_id" in sql assert "JOIN pets AS p1 ON p1.owner_id = u0.id" in sql
def test_duplicate_join(): query = Query(User).join("pets").join("pets") sql = to_sql(query.to_sql()) assert sql.count("JOIN") == 1
def test_select_dict(): query = Query(User).join("pets", "p").select({"user_id": "id", "pet_id": "p__id"}) assert "SELECT users.id, p.id" in to_sql(query.to_sql())
def test_join_duplicate_with_alias(): query = Query(User).join("pets", "p").join("pets") sql = to_sql(query.to_sql()) assert "JOIN pets AS p ON p.owner_id = users.id" in sql assert "JOIN pets AS p0 ON p0.owner_id = users.id" in sql
def test_select_nested(): query = Query(User).join("pets", "p").select(("id", {"pet": [{"id": "p__id"}]})) assert "SELECT users.id, p.id" in to_sql(query.to_sql())
def test_select_literal(): query = Query(User).select(text("1")) assert "SELECT 1" in to_sql(query.to_sql())
def test_select_call(): query = Query(User).select(call(lambda x: x, "id")) assert "SELECT users.id" in to_sql(query.to_sql())
def test_select_name(): query = Query(User).select("id") assert "SELECT users.id" in to_sql(query.to_sql())
def test_where_not_eq(): query = Query(User).where(name__not_eq="Sue") assert "WHERE users.name != 'Sue'" in to_sql(query.to_sql())
def test_offset(): query = Query(User).offset(29) assert "OFFSET 29" in to_sql(query.to_sql())
def test_where_not_ilike(): query = Query(User).where(name__not_ilike="sue") assert "WHERE users.name NOT ILIKE 'sue'" in to_sql(query.to_sql())
def test_select_join_name(): query = Query(User).join("pets", "p").select("p__id") assert "SELECT p.id" in to_sql(query.to_sql())
def test_where_startswith(): query = Query(User).where(name__startswith="S") assert "WHERE (users.name LIKE 'S' || '%%')" in to_sql(query.to_sql())
def test_select_list(): query = Query(User).join("pets", "p").select(["id", "p__id"]) assert "SELECT users.id, p.id" in to_sql(query.to_sql())
def test_where_in(): query = Query(User).where(id__in=[1, 2]) assert "WHERE users.id IN (1, 2)" in to_sql(query.to_sql())
def test_select_tuple(): query = Query(User).join("pets", "p").select(("id", "p__id")) assert "SELECT users.id, p.id" in to_sql(query.to_sql())
def test_where_lt(): query = Query(User).where(id__lt=1) assert "WHERE users.id < 1" in to_sql(query.to_sql())
def test_order_by_invalid(): query = Query(User).order_by(1) message = "`int` is not a valid query expression" with pytest.raises(InvalidExpressionError, match=message): query.to_sql()