complex queries

[andykais]

This is just a running list of queries that would make the app better, but may be difficult using a traditional RDBMS.

smarter tag searches

tag searching could incorporate other selected tags to only search for tags that will have more than zero media results.

• given a list of tags, what other tags are referenced from the first list of tags searched
  • this is useful for our search bar. E.g. there are two search terms, ideally tag autocomplete should only show tags that are going to yield any results by adding.

grouped search results

• I want to add a search arg to group results by tag group. For example: "search all media from twitter grouped by username"

-- this just gives us the media_reference count per tag, but we still could grab the top media_reference_id from each too

SELECT tag.name AS tag, COUNT(media_reference.id) AS media_count, tag.unread_media_reference_count
FROM media_reference
INNER JOIN media_reference_tag ON media_reference_id = media_reference.id
INNER JOIN tag ON tag_id = tag.id
INNER JOIN tag_group ON tag_group_id = tag_group.id
WHERE media_reference.id IN (
    SELECT media_reference.id FROM media_reference
    INNER JOIN media_reference_tag ON media_reference_id = media_reference.id
    INNER JOIN tag ON tag_id = tag.id
    INNER JOIN tag_group ON tag_group_id = tag_group.id
    WHERE tag_group.name = 'username'
    AND media_reference_id IN (
      SELECT media_reference_id FROM media_reference_tag
      INNER JOIN tag ON tag_id = tag.id
      WHERE tag.name = 'likee'
    )
    GROUP BY media_reference.id
  )
  AND tag_group.name = 'username'
GROUP BY tag_id
ORDER BY media_count;

• media sequences should also have the option to be grouped. This might involve temporary tables