How to perform translation from RAW SQL to django queryset

I am struggling with conversion to django query having raw sql I am new in django and any help will be appreciated

There are simple models:

Winemaker – target model

Wine

Post

Winemaker has 1+ Wines

Wine has 1+ Posts

I know that it should be done with annotations but have no idea how to implement it.

select w2.*,
       (select count(wp.id)
        from web_winemaker www
                 inner join web_wine ww on www.id = ww.winemaker_id
                 inner join web_post wp on ww.id = wp.wine_id
           where
                ww.status=20
            and
                 wp.status=20
            and
                 www.id = w2.id
       ) as wineposts_count,
       (
           select count(w.id)
           from web_winemaker www1
           inner join web_wine w on www1.id = w.winemaker_id
           where
                w.status=20
           and www1.id = w2.id
        ) as wines_count
from web_winemaker w2;

Answer

You should be able to accomplish this with a Count aggregation expression in an annotate function. I took a guess at your related_name values on your relationship fields, so the following code may not plug in directly, but should give you an idea of how to do what you want.

from django.db.models import Count, Q

wine_makers = Winemaker.objects.annotate(
    posts_count=Count(
        'wine__post__id',
        filter=Q(wines__status=20, wines__posts__status=20),
    ),
    wines_count=Count(
        'wines__id',
        filter=Q(wines__status=20),
    ),
)

You may need to supply distinct=True depending on if you’re crossing relationships.