sqlalchemy.orm.util.LoaderCriteriaOption

Add additional WHERE criteria to the load for all occurrences of a particular entity.

The _orm.with_loader_criteria option is intended to add limiting criteria to a particular kind of entity in a query, globally, meaning it will apply to the entity as it appears in the SELECT query as well as within any subqueries, join conditions, and relationship loads, including both eager and lazy loaders, without the need for it to be specified in any particular part of the query. The rendering logic uses the same system used by single table inheritance to ensure a certain discriminator is applied to a table.

E.g., using :term:`2.0-style` queries, we can limit the way the User.addresses collection is loaded, regardless of the kind of loading used:

from sqlalchemy.orm import with_loader_criteria

stmt = select(User).options(
    selectinload(User.addresses),
    with_loader_criteria(Address, Address.email_address != 'foo'))
)

Above, the "selectinload" for User.addresses will apply the given filtering criteria to the WHERE clause.

Another example, where the filtering will be applied to the ON clause of the join, in this example using :term:`1.x style` queries:

q = session.query(User).outerjoin(User.addresses).options(
    with_loader_criteria(Address, Address.email_address != 'foo'))
)

The primary purpose of _orm.with_loader_criteria is to use it in the _orm.SessionEvents.do_orm_execute event handler to ensure that all occurrences of a particular entity are filtered in a certain way, such as filtering for access control roles. It also can be used to apply criteria to relationship loads. In the example below, we can apply a certain set of rules to all queries emitted by a particular _orm.Session:

session = Session(bind=engine)

@event.listens_for("do_orm_execute", session)
def _add_filtering_criteria(execute_state):

    if (
        execute_state.is_select
        and not execute_state.is_column_load
        and not execute_state.is_relationship_load
    ):
        execute_state.statement = execute_state.statement.options(
            with_loader_criteria(
                SecurityRole,
                lambda cls: cls.role.in_(['some_role']),
                include_aliases=True
            )
        )

In the above example, the _orm.SessionEvents.do_orm_execute event will intercept all queries emitted using the _orm.Session. For those queries which are SELECT statements and are not attribute or relationship loads a custom _orm.with_loader_criteria option is added to the query. The _orm.with_loader_criteria option will be used in the given statement and will also be automatically propagated to all relationship loads that descend from this query.

The criteria argument given is a lambda that accepts a cls argument. The given class will expand to include all mapped subclass and need not itself be a mapped class.

stmt = select(A).join(A.bs).options(
    contains_eager(A.bs),
    with_loader_criteria(B, B.flag == 1)
)

SELECT
    b.id, b.a_id, b.data, b.flag,
    a.id AS id_1,
    a.data AS data_1
FROM a JOIN b ON a.id = b.a_id AND b.flag = :flag_1

Undocumented

Undocumented

Undocumented

Apply a modification to a given .CompileState, given entities that were replaced by with_only_columns() or with_entities().