Related tags query, many-to-many double join
Maciej Bliziński
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 <m.bli...@wit.edu.pl>
http://automatthias.wordpress.com
Ivan Sagalaev
> 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)
> -------------------------------------------------------------------
It can look like this:
Tag.objects.filter(document__tags__name='mytag').exclude(name='mytag')
Malcolm Tredinnick
[...]
Basically, no, because there is no way at the moment to do the
equivalent of an SQL "group" command. One day, this sort of thing should
be possible, but not right now.
Regards,
Malcolm
Maciej Bliziński
> It can look like this:
>
> Tag.objects.filter(document__tags__name='mytag').exclude(name='mytag')
Wow, that's short. Thanks.
The only thing I'm missing now is that this expression returns multiple
instances of each tag, while I need counts. I could write:
Tag.objects.filter(document__tags__name='mytag').exclude(name='mytag').distinct()
But then I'd lose the counts. I think I have to write myself a function
that will count the instances of each tag.
Simon Willison
On 19 Jul 2006, at 13:50, Maciej Bliziński wrote:
> is it possible to make the same thing without writing custom SQL code?
No it isn't - but that's fine, that's exactly why Django allows (and
encourages) you to roll your own SQL when you need to:
http://www.djangoproject.com/documentation/model_api/#executing-
custom-sql
My philosophy with respect to the Django ORM (and ORMs in general) is
that it should be used strictly for convenience - it should make the
usual dull collection of queries as simple as possible. If the ORM is
ever less convenient than writing a raw SQL query, write a raw SQL
query!
As long as you keep all of the SQL interactions in your Django model
classes your database logic will all be in the same place and
maintenance should be simple.
Cheers,
Simon
Carlos Yoder
On 7/19/06, Simon Willison <swil...@gmail.com> wrote:
--
Carlos Yoder
http://carlitosyoder.blogspot.com