[Answered ]-Select all items that have matching tags

by

1👍

✅

The SQL query could 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:

  1. Count the matching tags per ArticleID in subquery t.
  2. LEFT JOIN the main table to it with data for secondary (blog) and tertiary (rating) sort criteria.
  3. ORDER BY the three criteria, ct first, blog next, rating last. All of them descending (highest value first). That works for the boolean expression (a.blog = 'BBC') as well, because TRUE (1) sorts before FALSE (0) in descending order.

Important: In descending order NULL values would sort first, so NULLS LAST is needed if there can be NULL values (and does not hurt if there cannot).

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

If Django preserves mixed case names with double-quotes, you have to do that in SQL, too. Otherwise all identifiers are cast to lower case.

👤Erwin Brandstetter

1👍

The query could be simpler I believe 🙂
There is no real need for a join here. Here’s the 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
👤Wolph

Leave a comment