I’ve been working on getting tagging working in Orbital Feed Reader. Tagging feeds was really brilliant in Google Reader and I want that in the feed reader I use. So I’m building it.

The Setup

You can store tagging in your database a few different ways, but here’s the way I chose:

                         +----------+
      +----------+       | feed_tag |
      | feeds    |       |----------|
      |----------|       |  tag_id  +---+
      |  id      +-------+  feed_id |   |   +--------+
      |  name    |       +----------+   |   | tags   |
      |  url     |                      |   |--------|
      |  unread  |                      +---+  id    |
      +----------+                          |  name  |
                                            |        |
                                            +--------+

You’ve got feeds and tags, those have an ID number. You’ve got a table linking those by ID number. Easy.

The Goal

I wanted to be able to toggle between either a simple list of feeds or a list of tags with feeds underneath.

<script type="text/ng-template"  id='feedline.html'>
  <div class="feed" id="feed-{{feed.feed_id}}" 
    ng-class="{'is-editable': editable, 'is-selected': feed.isSelected}" 
    ng-click="select(feed)"  >
      {{feed.feed_name}} 
      <span class="feedcounter">{{feed.unread_count}}</span>
      <a ng-show="editable" ng-click="editFeed(feed)">âš™</a>
  </div>
</script>
<ul id='feeds' ng-hide="showByTags" >
  <li ng-repeat="feed in feeds" ng-include="'feedline.html'" ></li>
</ul>
<ul id='tags' ng-show="showByTags">
  <li class="tag" ng-repeat="(tag, feeds) in tags" >
    <div id="{{tag}}" ng-click="select(tag)" 
      ng-class="{'is-selected':tag.isSelected}" >
      #{{tag}} 
      <span class="feedcounter">{{tagUnreadCount(tag)}}</span> 
    </div>
    <ul>
      <li ng-repeat="feed in feeds" ng-include="'feedline.html'"></li>
    </ul>
  </li>
</ul>

My first stab at doing this was very wrong and I wanted to share that so you don’t go down that dumb path on your project.

The Wrong way to do tagging

I decided I would pull the feeds in two different ways. I would pull feeds from the database as a normal list, but then I would also pull a list of tags with feeds underneath.
This was my first mistake. Hitting the DB twice for the same data means I’m doing lots of duplication and sending lots of information over the internet (slooooow) twice. It also means keeping lists in sync which is also a code smell that means you are probably not keeping things DRY.

So I pull feeds from the db once like this:

SELECT
  feeds.id AS feed_id,
  COALESCE(u_feeds.feed_name,feeds.feed_name ) AS feed_name,
  feeds.feed_url,
  COALESCE(u_feeds.icon_url, feeds.icon_url ) AS icon_url,
  COALESCE(u_feeds.site_url, feeds.site_url ) AS site_url,
  feeds.lASt_updated,
  feeds.lASt_error,
  u_feeds.private,
  SUM(IF(COALESCE(ue.isRead,1)=0,1,0)) AS unread_count
FROM user_feeds AS u_feeds
INNER join feeds AS feeds
  ON u_feeds.feed_id = feeds.id
LEFT OUTER JOIN user_entries AS ue
  ON ue.feed_id=feeds.id
  AND u_feeds.owner = current_user_ID
GROUP BY feeds.id,
  feeds.feed_url,
  feeds.feed_name,
  feeds.icon_url,
  feeds.site_url,
  feeds.lASt_updated,
  feeds.lASt_error,
  u_feeds.private

And then pull feeds by tag like this:

SELECT
COALESCE(tags.name,'Untagged') AS tag,
COALESCE(tags.id, null) AS tag_id,
feeds.id AS feed_id,
COALESCE(u_feeds.feed_name,feeds.feed_name ) AS feed_name,
feeds.feed_url,
COALESCE(u_feeds.icon_url, feeds.icon_url ) AS icon_url,
COALESCE(u_feeds.site_url, feeds.site_url ) AS site_url,
feeds.last_updated,
feeds.last_error,
u_feeds.private,
SUM(IF(COALESCE(ue.isRead,1)=0,1,0)) AS unread_count
FROM user_feed_tags AS uft
INNER JOIN tags AS tags
  ON tags.id = uft.tag_id
INNER JOIN user_feeds AS u_feeds
  ON uft.user_feed_id = u_feeds.id
INNER JOIN feeds AS feeds
  ON u_feeds.feed_id = feeds.id
LEFT OUTER JOIN user_entries AS ue
  ON ue.feed_id=feeds.id
WHERE u_feeds.owner = current_user_ID
GROUP BY
feeds.id,
feeds.feed_url,
feeds.feed_name,
feeds.icon_url,
feeds.site_url,
feeds.last_updated,
feeds.last_error,
u_feeds.private,
tags.name

UNION
SELECT
'Untagged' AS tag,
null AS tag_id,
feeds.id AS feed_id,
COALESCE(u_feeds.feed_name,feeds.feed_name ) AS feed_name,
feeds.feed_url,
COALESCE(u_feeds.icon_url, feeds.icon_url ) AS icon_url,
COALESCE(u_feeds.site_url, feeds.site_url ) AS site_url,
feeds.last_updated,
feeds.last_error,
u_feeds.private,
SUM(if(COALESCE(ue.isRead,1)=0,1,0)) AS unread_count

FROM user_feeds AS u_feeds
LEFT OUTER JOIN user_feed_tags AS uft
  ON uft.user_feed_id = u_feeds.id
INNER JOIN feeds AS feeds
  ON u_feeds.feed_id = feeds.id
LEFT OUTER JOIN user_entries AS ue
  ON ue.feed_id=feeds.id
WHERE u_feeds.owner = current_user_ID
  AND ISNULL(uft.user_feed_id)
GROUP BY
feeds.id,
feeds.feed_url,
feeds.feed_name,
feeds.icon_url,
feeds.site_url,
feeds.last_updated,
feeds.last_error,
u_feeds.private

For feeds by tag I then would use underscore.js’s excellent _.groupBy() method. It’s easy to see tags in angular using groupBy and I thought it was good enough.

It worked! Sure, it was inefficient, but I’m pragmatic – I just wanted to get this done and out the door so I could use it. No point in prematurely optmizing, right?

Next step was making sure I could show the unread count per tag. And keep it all in sync between the tags and the feeds themselves. This is when I smelled my stinky code smells. Every time I marked an entry in a feed as read I would have to search it out multiple times on multiple lists. This means this information was stored in multiple places – and it’s always good to follow the DRY principle. Don’t Repeat Yourself.

The Right(er) way to do tagging

Let’s regroup and try this again. I want to have ONE place where I pull info from my DB about a feed, and I want to only update information in one place but display it in multiple ways.

I changed it so I pull a straight list of the feeds, but use the group_concat SQL function to also pull in all the tags associated with a feed.

SELECT 
  u_feeds.id AS feed_id,
  COALESCE(u_feeds.feed_name,feeds.feed_name ) AS feed_name,
  feeds.feed_url, 
  COALESCE(u_feeds.icon_url, feeds.icon_url ) AS icon_url,
  COALESCE(u_feeds.site_url, feeds.site_url ) AS site_url,
  feeds.last_updated,
  feeds.last_error,
  u_feeds.private,
  SUM(IF(COALESCE(ue.isRead,1)=0,1,0)) AS unread_count,
  GROUP_CONCAT(DISTINCT COALESCE(tags.name,'Untagged')) as tags
FROM $user_feeds AS u_feeds
INNER JOIN $feeds AS feeds
  ON u_feeds.feed_id = feeds.id
  AND u_feeds.owner =  $current_user->ID.
LEFT OUTER JOIN $user_entries AS ue
  ON ue.feed_id=feeds.id
LEFT OUTER JOIN $user_feed_tags uft
  ON uft.user_feed_id = u_feeds.id
LEFT OUTER JOIN $tags tags
  ON uft.tag_id = tags.id
GROUP BY 
  feed_id,
  feed_url,
  feed_name,
  icon_url,
  site_url,
  last_updated,
  last_error,
  private

When I want to show a list of feeds I can just go with the same solution I had before.
But when I want to group by tags I can apply a couple of interesting tricks to solve this for me.

    refresh : function refresh(callback){
  _isLoading = true;
  $http.get(opts.ajaxurl + '?action=orbital_get_feeds')
  .success( function( data ){
    //Here is our simple feed list
    _feeds= data;

    //Now lets get a list of all the unique tags in those feeds
    var taga = _.unique(_.pluck(_feeds, 'tags').join().split(","));

    //For each tag, lets build up a list of the feeds that have that tag
    _.each(taga, function(tag){
      _tags[tag] = _.filter(_feeds,function(feed){
                      return _.contains(feed.tags.split(","),tag);
                    });
    })
    _isLoading = false;
    //Should we do some extra work?
    if(callback){
      callback(_feeds);
    }
  });
},

What’s going on there?
I want to get an array of all my unique tags, so first thing I do, I use _.pluck() to give me an array of the tags element of each feed.
This gives me:

[
  ["art,gifs"],
  ["art"],
  ["Untagged"],
  ["gifs"],
  ["Untagged"],
  ["Untagged"],
]

Then I join() those together (the default join is with a comma):

"art,gifs,art,Untagged,gifs,Untagged,Untagged"

I split(“,”) that on commas:

["art","gifs","art","Untagged","gifs","Untagged","Untagged"]

And finally we just grab the _.unique() values:

["art","gifs","Untagged"]

So now we have a list of the tags for our feeds – how exciting! Let’s build a list of the feeds for each of these tags.
So, for _.each() tag in our list of tags we go back to our list of feeds and _.filter() it – we want only a feed where the feed.tags _.contains() the tag.

Why’s this so much better? Well – if we update the `unread_count` for a feed or change the name of a feed – it updates the underlying object. That means your change percolates up automatically through angular databinding to every part of your UI. No more keeping lots of lists in sync.

And now I’ve bored even myself to death with this so I guess that’s it.