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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/ab82befe-0b27-e280-8d3a-438f4135d85a%40net153.net.
