Annotate related_object_count

[JakNowy]

Is your feature request related to a problem? Please describe.
I can't find a way to annotate number of related objects using the fastcrud.get_multi(). This is a raw sqlalchemy query I have instead: [note m2m relationship in this specific case]

    searches = await db.execute(
        select(Search, func.count(Video.id))
        .outerjoin(VideoSearchAssociation)
        .outerjoin(Video, VideoSearchAssociation.video_id == Video.id)
        .group_by(Search.id)
    )

Describe the solution you'd like
I'd like to be able to retrieve videos_count from search_crud.get_multi() method and potentialy others. Extending to_select with func.count(Video.id) seems relatively easy, but whole query must be wrapped in a GROUP_BY clause, which seems to be a bit harder to implement in an elegant way. Maybe some CountConfig resembling JoinConfig would be a way to go?

[igorbenav]

Do you mean something like

video_count_config = CountConfig(
    related_model=Video,
    join_condition=(Video.id == VideoSearchAssociation.video_id) 
                   & (VideoSearchAssociation.search_id == Search.id),
    count_alias='videos_count'
)

Then passing it to a get_multi_joined, and getting something like:

{
    "data": [
        {
            "id": 1,
            "term": "cats",
            "videos_count": 5  // This Search has 5 associated Videos
        },
        {
            "id": 2,
            "term": "dogs",
            "videos_count": 3  // This Search has 3 associated Videos
        },
        {
            "id": 3,
            "term": "birds",
            "videos_count": 0  // This Search has no associated Videos
        }
    ],
    "total_count": 3  // Total number of Search records retrieved
}

?

[JakNowy]

Yep, I think something like that would be perfect. Looks pretty flexible and extendable. In particular supports m2m relationship. It should be safe to always groupby the primary_model.id (as it's required by sqlalchemy syntax).

[igorbenav]

I'm fixing this one next

[igorbenav]

Sorry, this is taking a while. I'm fixing all the other issues related to joined methods before adding a new feature

[JakNowy]

No worries, alot of new issues recently. Let's not force anything!