Query Guide

Rayforce-Py provides a powerful, chainable query API that makes it easy to work with data in RayforceDB. This guide covers all aspects of querying tables, from basic selections to complex aggregations and data modifications.

Rayforce-Py query API is designed to be intuitive and Pythonic. All queries are built using a fluent, chainable interface that reads naturally from left to right.

Query Execution

All query operations are lazy by default. You must call .execute() to run the query and get results:

# This builds the query but doesn't execute it
>>> query = table.select("id", "name").where(Column("age") >= 35)
# This actually runs the query and returns a Table
>>> result = query.execute()

Best Practices

Chain Methods: Take advantage of the fluent API to build readable queries:

>>> result = (
        table.select("id", "name", "salary")
        .where(Column("age") >= 30)
        .where(Column("dept") == "eng")
        .execute()
    )

Complex Conditions: Use boolean-friendly operators for complex conditions:

>>> result = table.select("id", "name", "salary").where(
        (Column("age") >= 30)
        & (Column("salary") > 100000)
    ).execute()

Computed Columns: Use computed columns to derive new data without modifying the original table:

>>> result = table.select(
        "id",
        "price",
        "quantity",
        total=Column("price") * Column("quantity"),
    ).execute()

Filtered Aggregations: Use filtered aggregations in by() to compute conditional statistics:

>>> result = (
        table
        .select(
            total=Column("salary").sum(),
            high_earners=Column("salary").where(Column("salary") > 100000).sum(),
        )
        .by("dept")
        .execute()
    )