Hello djangoers,
Having documents and tags with many-to-many relationship, I'd like to
take a tag and find its related tags. In other words, what other tags
are associated with documents that are associated to my tag?
A small(est I could make it) example:
myapp/models.py:
-------------------------------------------------------------------
from django.db import models
class Tag(models.Model):
name = models.CharField(maxlength = 200)
class Document(models.Model):
content = models.TextField()
tags = models.ManyToManyField(Tag)
-------------------------------------------------------------------
Then a script to fill the data with some values, execute it with
manage.py shell:
-------------------------------------------------------------------
from myproject.myapp.models import Tag, Document
for tagno in range(8):
t, c = Tag.objects.get_or_create(name = "tag%d" % tagno)
t.save()
alltags = Tag.objects.all()
for docno in range(8):
d, c = Document.objects.get_or_create(content = "document %d" \
% docno)
d.save()
for i in range(4): # add 4 random tags to the document
d.tags.add(random.choice(alltags))
-------------------------------------------------------------------
Finally, the SQL query:
-------------------------------------------------------------------
SELECT
dt.name,
count(*)
FROM
myapp_tag AS st -- source tag
INNER JOIN myapp_document_tags AS sti ON (st.id = sti.tag_id)
INNER JOIN myapp_document AS d ON (sti.document_id = d.id)
INNER JOIN myapp_document_tags AS dti ON (d.id = dti.document_id)
INNER JOIN myapp_tag AS dt ON (dti.tag_id = dt.id)
WHERE
st.name = 'tag1' -- define the source tag
AND
st.id <> dt.id -- list only other tags
GROUP BY
dt.name
ORDER BY
count(*) DESC
;
-------------------------------------------------------------------
Example result:
name | count
------+-------
tag5 | 6
tag2 | 6
tag8 | 6
tag3 | 4
tag7 | 4
tag6 | 4
tag0 | 3
tag4 | 2
(8 rows)
Shows, that tag5, tag2 and tag8 are associated with the same documents
as tag1 in 6 cases. And so on.
The question is:
is it possible to make the same thing without writing custom SQL code?
--
Maciej Bliziński <[EMAIL PROTECTED]>
http://automatthias.wordpress.com
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users
-~----------~----~----~----~------~----~------~--~---
