Complex DB query fun

[Samuel Smith]

I have a blog that is using django-taggit and on my list and detail
views, I'd like to have a section to display other posts with related
tags. At first I thought I could just use a correlated sub-select with
annotate() along with the postgresql string_agg() to squeeze the related
blog post's title and tag list into one column, but then realized that
I'd still have the issue of returning multiple rows. I could put another
select with string_agg around that but figured there must be a better way..

So I played with just using joins and came up with:


select b.title, r.*
from blog_post b
left join lateral (
select
bp.id as id, bp.title as related_title, bp.slug as related_slug,
string_agg(tt.name,', ') as related_tags, count(*) as related_count
from blog_post bp
inner join taggit_taggeditem tti on tti.object_id = bp.id
inner join taggit_tag tt on tt.id = tti.tag_id
where tt.id in ( select tag_id from taggit_taggeditem tt3 where
tt3.object_id = b.id ) and bp.id != b.id
group by bp.title,bp.slug, bp.id
having count(*) > 1
limit 4
) as r on 1 = 1
order by b.title, r.related_count desc

So that gives me a list of all blog posts joined with a list of their
top 4 other related posts including a related tag list and count.

So how to shove that into django? Should I try something with annotate
and rawsql() or would it be better to just create this query as a
database view and make an unmanaged model to join to?

Regards,
Samuel Smith