CQWP - Sort items based on popularity

Question

I've enabled Auditing in a list that keeps track of how many times an item has been viewed.

I would now like to use a Content Query Web part to return a list of documents based on their popularity (view/download count)

I tried searching for a solution but it looks like there's nothing out of the box that can do this. Any ideas what I should do to accomplish this?

Thanks a lot for any assistance!

Mr. J

Answer 1

I got this working in the end using JavaScript. The results are not 100% accurate because of the way SharePoint caches item values but it's accurate enough to give a rough indication how many times an item was downloaded.

Here's the steps I went through:

1. I first created a column called Popularity in the document library. Type number.

2. Then I created a new XSL template in ItemStyle.xsl that adds the Popularity column and the ID of the item to the returned value (hidden from user with css). This template is a version of the one called WithDocIcon because I want to display the icon next to the title as well.

   <xsl:template name="WithDocIconAndPopularity" match="Row[@Style='WithDocIconAndPopularity']" mode="itemstyle">
      <xsl:variable name="SafeLinkUrl">
           <xsl:call-template name="OuterTemplate.GetSafeLink">
                <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/>
           </xsl:call-template>
      </xsl:variable>
      <xsl:variable name="DisplayTitle">
           <xsl:call-template name="OuterTemplate.GetTitle">
               <xsl:with-param name="Title" select="''"/>
               <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/>
               <xsl:with-param name="UseFileName" select="1"/>
           </xsl:call-template>
      </xsl:variable>
      <div class="item link-item">
          <xsl:if test="string-length(@DocumentIconImageUrl) != 0">
              <div class="image-area-left">
                  <img class="image" src="{@DocumentIconImageUrl}" title="" />
              </div>
          </xsl:if>
          <div class="link-item cqwp-popularity-item">
              <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
              <a href="{$SafeLinkUrl}" title="{@LinkToolTip}">
                  <xsl:if test="$ItemsHaveStreams = 'True'">
                    <xsl:attribute name="onclick">
                      <xsl:value-of select="@OnClickForWebRendering"/>
                    </xsl:attribute>
                  </xsl:if>
                  <xsl:if test="$ItemsHaveStreams != 'True' and @OpenInNewWindow = 'True'">
                    <xsl:attribute name="onclick">
                      <xsl:value-of disable-output-escaping="yes" select="$OnClickTargetAttribute"/>
                    </xsl:attribute>
                  </xsl:if>
                  <xsl:value-of select="$DisplayTitle"/>
              </a>
              <div class="description">
                  <xsl:value-of select="@Description" />
              </div>
              <div class="cqwp-item-id">
                  <xsl:value-of select="@ID" />
              </div>
              <div class="cqwp-item-popularity">
                  <xsl:value-of select="@Popularity" />
              </div>
   
          </div>
      </div>
   </xsl:template>
   

3. Inside a JavaScript file's $(document).ready(function() {}) I added a listener to the links inside a div with class cqwp-popularity-item:

   $('.cqwp-popularity-item a').click(downloadCounter);
   

4. Then, my downloadCounter function looks pretty much like this:

   var href='';
   function downloadCounter(e)
   {
       // increment counter
       var t = $(this);
       var iId = t.next().next();
       if(iId.hasClass('cqwp-item-id'))
       {
           var id = iId.html(),
               popularity = iId.next().html(),
               listTitle = '';
           href = t.attr('href');
                   // add to this if-else all the lists that you know have a Popularity column
           if(href.indexOf('LIST TITLE') != -1)
           {
               listTitle = 'LIST TITLE';
           }
   
           if(listTitle != '')
           {
               var siteUrl = _spPageContextInfo.siteServerRelativeUrl; // add subsite to this if required
               var clientContext = new SP.ClientContext(siteUrl);
               var oList = clientContext.get_web().get_lists().getByTitle(listTitle);
   
               oListItem = oList.getItemById(id);
   
               oListItem.set_item('Popularity', parseInt(popularity)+1);
   
               oListItem.update();
   
               function onQuerySucceeded() {
                                   // open the document link in a new window
                   window.open(href);
                   href='';
               }
   
               function onQueryFailed(sender, args) {
                   href='';
                   alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
               }
   
               clientContext.executeQueryAsync(onQuerySucceeded, onQueryFailed);
   
               e.stopPropagation();
               return false;
           }
       }
   
       return true;
   
    }
   

I know the code is not the most efficient & streamlined but this fits my requirements quite well.

Hopefully this will help someone else as well!