CODEWITHSUNDEEP
pythonsqldjangopostgresqldjango-models
Johnny
Johnny

Reputation: 103

How to implement a Django model that has a many-to-many relationship with 'tags' but also counts of how many times it was tagged with something?

So I have three relevant models here: Word, Definition, and Tag.

Words have a one to many relationship with Definition; many users can posts definitions for the same word. I want to add a tagging feature so that when users submit their definition they can include Tags. These Tags apply to the Word they are defining, not the definition itself. A Word can have more than one Tag, but the same Tag can also apply to more than one word, so Words should have a many-to-many relationship with Tag.

I also want to store how many times a Word has been tagged with something. Every time a user submits definition for word X and includes tag Y, I want to increment that Word's count for how many times its been tagged with Y.

What would be the best way to setup such a schema?

I'm kind of lost here. The only solution I can think of is to store an extra textfield in Word and store num_{tag}=X for each of the tags associated with it. I could also store the tags as part of the Definitions, but to find the number of times a word has been tagged with something I would have to go through all of its Definitions and count the number of times a tag appears.

I'm using Postgres for my database and Django.

Upvotes: 0

Views: 126

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13129

There's two ways to do this. One is to allow to allow the same combination of word_id and tag_id to be inserted into their association table (which facilitates the M2M relationship). The upside is that you don't have to go and store the counts, just count them whenever they're requested. The downside is that you might need to include a reference to Definition to prevent duplicate rows, and the table might grow faster than you'd like. In that case, the Django code would be something like this:

class Word(Model):
  pass

class Tag(Model):
  words = models.ManyToManyField(
      Word,
      related_name='tags',
      db_table='word_tags'
  )

To get the number of counts per tags, a SQL query would look like this:

SELECT word.name,
       tag.name,
       COUNT(*)
FROM word
JOIN word_tags ON word.id = word_tags.word_id
JOIN tag ON tag.id = word_tags.tag_id
WHERE word.id = 5
GROUP BY word.id

The other possibility is to add an extra column to the association table where you keep and update the count. The upside of this is that you have the count much more easily, while the downside is that inserting is more of a hassle. You have to check whether the word has ever been assigned to that tag before, and if not, then insert it, otherwise update it. Postgres has a very useful command for this, but I'm not sure how well Django supports this.

class Word(Model):
  pass

class Tag(Model):
  words = models.ManyToManyField(
      Word,
      related_name='tags',
      through='WordTag',
      through_fields=('words', 'tags')
  )

class WordTag(Model):
   word = models.ForeignKey(Word, on_delete=models.CASCADE)
   tag = models.ForeignKey(Tag, on_delete=models.CASCADE)
   count = models.IntegerField()

To get the number of counts per tags, a SQL query would look like this:

SELECT word.name,
       tag.name,
       word_tags.count
FROM word
JOIN word_tags ON word.id = word_tags.word_id
JOIN tag ON tag.id = word_tags.tag_id
WHERE word.id = 5

To insert a tag, you'd need to so something like this:

def add_tag(word, tag):
  word_tag, _ = WordTag.objects.get_or_create(
      word_id=word.id,
      tag_id=tag.id,
      defaults=dict(count=0)
  )
  word_tag.count += 1
  word_tag.save()

Upvotes: 1

Related Questions