Select all items with matching tags.

Question

Suppose I have the following Django models:

class Article(models.Model): title = models.CharField(max_length=200) blog = models.CharField(max_length=255) rating = models.IntegerField(default=0) class ArticleTag(models.Model): article = models.ForeignKey(Article) tag = models.CharField(max_length=200) 

Add some data:

  ArticleID Rating Blog ----------------------------------------- article1 -> 1 3 CNN article2 -> 2 2 BBC article3 -> 3 5 BBC article4 -> 4 9 NTV ArticleID tag ------------------- 1 tag1 1 tag2 1 tag3 2 tag1 2 tag4 3 tag5 4 tag6 4 tag7 

Suppose we have a user who likes tag1 , tag2 , tag6 and BBC . All articles are eligible because article1 has tag1 and tag2 , article 4 has tag1 , article2 and article3 are from the BBC .

If we order them by rating: article4 , article3 , article1 , article2 .

However, I need to order items by the number of matching tags that have a + blog, and then by rating as the second order parameter. Therefore, I expect results in the following order:

• article1 - tag1 and tag2 , rating = 3
• article2 - tag1 and BBC , rating = 2
• article4 - tag6 , rating = 9
• article3 - BBC , rating = 5

Can this be done in Django? If not, what about PostgreSQL?

Answer 1

The SQL query might look like this:

 SELECT * FROM Article a LEFT JOIN ( SELECT ArticleID, count(*) AS ct FROM ArticleTag WHERE tag IN ('tag1', 'tag2', 'tag6') -- your tags here GROUP BY ArticleID ) t ON t.ArticleID = a.ID ORDER BY t.ct DESC NULLS LAST , (a.blog = 'BBC') DESC NULLS LAST -- your blog here , rating DESC NULLS LAST; 

Basically:

• Sign match tags for ArticleID in subquery t .
• LEFT JOIN main table with data for secondary ( blog ) and tertiary ( rating ) sorting criteria.
• ORDER BY three criteria, ct first, blog next, rating last. They all descend (first the highest value). This also works for a logical expression (a.blog = 'BBC') , since TRUE (1) is sorted to FALSE (0) in descending order.

Important: In descending order, NULL values ​​will be sorted first, so NULLS LAST necessary if there can be NULL values ​​(and does not interfere if they cannot).

• Sort PostgreSQL by asc date time, null first?

Even if all of your columns are NOT NULL defined, ct may still be NULL due to LEFT JOIN .

If Django stores mixed double-quoted names, you must do this in SQL too. Otherwise, all identifiers are displayed in lower case.

Answer 2

The request could be simpler, I believe :) There is no real need for combining here. Here sqlfiddle: http://sqlfiddle.com/#!2/1e565/10

 SELECT article.ArticleID, COUNT(DISTINCT tag.tag), COUNT(DISTINCT article.Blog LIKE 'BBC'), COUNT(DISTINCT tag.tag) + COUNT(DISTINCT article.Blog LIKE 'BBC'), article.rating FROM article LEFT JOIN tag ON tag.ArticleID = article.ArticleID WHERE tag.tag IN ('tag1', 'tag2', 'tag6') OR article.Blog LIKE 'BBC' GROUP BY article.ArticleID, article.rating ORDER BY COUNT(DISTINCT tag.tag) + COUNT(DISTINCT article.Blog LIKE 'BBC') DESC, rating DESC