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
--
You received this message because you are subscribed to the Google Groups "Django
users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/ab82befe-0b27-e280-8d3a-438f4135d85a%40net153.net.
