Enhancement: ExistsFilter

[riyavsinha]

Summary

It would be great to be able to specify something like:

variants_not_scored_by_model = await model_score_repo.list(
            CollectionFilter(models.ModelScores.variant_id, variant_ids),
            ~exists().where(
                and_(
                    models.ModelScores.model_id == model_id,
                    models.ModelScores.variant_id == models.Variant.id,
                )
            )
        )

As far as I know, there is no exists filter, and I'm not sure the above doesn't work. Essentially, if an ExistsFilter could take in a sublist of StatementFilters that would be nice.

Basic Example

variants_not_scored_by_model = await model_score_repo.list(
            CollectionFilter(models.ModelScores.variant_id, variant_ids),
            NotExistsFilter(
                    models.ModelScores.model_id == model_id,
                    models.ModelScores.variant_id == models.Variant.id,
            )
        )

Drawbacks and Impact

No response

Unresolved questions

No response

[cofin]

@riyavsinha I think there is some value to have some filters for this. I'll try to get this added in an upcoming release.

Regarding your first question, you should be able to do something close to what your want The args of the repository will accept a StatementFilter or a ColumnElement[bool] (a where clause in SQLAlchemy)

Here's a basic example of mixing Statement Filters and ColumnExpressions into a single set of filters.

filters = [CollectionFilter(models.TeamModel.groups, country_code_groups), Team.is_deleted == False],
if not teams_service.can_view_all(current_user):
  filters.append(
     TeamModel.id.in_(
        select(TeamMemberModel.team_id).where(TeamMemberModel.user_id == current_user.id)
     )
 ) 

results, total = await teams_service.list_and_count(*filters)

You can also mix and match the kwargs and args. For the repository, all kwargs keywords are automatically converted to ColumnElement[bool] and applied to the statement:

results, total = await teams_service.list_and_count(*filters, is_active=True)