Better support for `TableValueAliased` components

Working with TableValuedAlias is tricky in the oqm API since we break down the statement builder into a series of hooks. Often the TableValuedAlias is used to set parameters for a postgres function call that is only accessible at runtime. We use this pattern in our projects for operations that are complex and easier to maintain on the database server rather then python.

If you know what your alias is going to be ahead of time then the aliases can be set on the DataAccessLayer, but if you are getting those values at runtime (say via query params in an API) then they should be set before the call into query_stmt which is effectively only building the non-dynamic part of the query.

Our current implementation mitigates this by adding a series of subclasses that are designed to work with aliased data and conform the oqm API.


class AliasedFilter(Filter):
    @abc.abstractmethod
    def set_aliased_table(
        self,
        transaction: dal.TransactionManager,
    ) -> None:
        """For queries using table aliases that have runtime arguments (i.e. function calls)
        This method needs to be implemented in order to set the alias at runtime during the transaction.

        Args:
            transaction (dal.TransactionManager): _description_
        """

        ...

AliasedFilterT = TypeVar("AliasedFilterT", bound=AliasedFilter)

class AliasedListQ(ListQ[QueryableT, AliasedFilterT]):
    def __init__(self, where: AliasedFilterT):
        """An AliasedListQ overides internal prepare stmt method to assure that an
        aliased table is set with the correct runtime args before the query is made.

        Args:
            where (AliasedFilterT): _description_
        """
        self.where = where

    def _prepare_stmt(self, transaction: dal.TransactionManager) -> sa.Select[Any]:  # type: ignore
        self.where.set_aliased_table(transaction)
        stmt = self._db_obj.query_stmt(transaction)
        stmt = self.where.filter_stmt(transaction, stmt)
        return stmt

This solution works since with the declarative approach since we allow a method hook for the table aliased definition directly on the transaction instance... In this case it is done on the Filter subclass itself.

Another approach would be to consider modifying the ListQ API to accept an optional FilterT.

stmt = self._db_obj.query_stmt(transaction, where=self.where)

This would give runtime access to the filterable params and they can be used in the query stmt... this would aleviate the need for extra classes... This would also give the client the option of not needing to use the FilterSet if it is not desirable instead writing the where clause as a switch block inside the query itself.

cunybpl / aiodal

Better support for `TableValueAliased` components #15