How to alter result of a CQWP query?

Question

On the site I'm maintaining, alle the news pages has a Note Board webpart for commenting on a newspage. On the news summary page resides a standard CQWP, displaying the most recently posted news.

Drawing from the social data of Sharepoint, I'd like to simply add the number of comments for each specific page, by adding it in parentheses to the title.

How can this be accomplised? I can find lots of documentation on how to override, change or filter the query expression programmatically, but nothing about how to modify the results of an executed query.

Thanks you in advance

Answer 1

About Social Comments

In comparison to page fields (Page Content, Title, Page Image and etc.) that are inserted into the “slots” of the page layout and associated with the content type of the publishing page, social comments are stored separately. Social comments are stored in separate DB and the comment entry is associated by the page Url on which comment has been given

About modifying results in CQWP

At least two approaches could be used for this scenario:

• Modify results on the client side SharePoint Web Services exposes Social Data Service that provides methods for remote clients to Create, Read, Update, and Delete (CRUD) social data. Social comments retrieving and binding is taking place on the client side.
• Modify results using ProcessDataDelegate delegate In this approach the social comments are retrieved and saved in results before it is sent to the XSL transform using ProcessDataDelegate delegate of Content Query web part. For retrieving social comments, SocialCommentManager class is utilized, which represents the entry point in SharePoint object model (OM) that exposes methods to do work with social comments or notes

Modify results on the client side

The following JavaScript code demonstrates how to utilize SharePoint Web Services for retrieving comment count per page and to bind it to comments item template (ItemStyle.xsl):

function CountCommentsOnUrl(url,result)
{
     var soapEnv =
        "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'> \
              <soap:Body>    \
                    <CountCommentsOnUrl xmlns='http://microsoft.com/webservices/SharePointPortalServer/SocialDataService'> \
                                <url>" + url + "</url> \
                     </CountCommentsOnUrl> \
              </soap:Body> \
         </soap:Envelope>";

    $.ajax({
        pageurl: url,
        result: result,
        url: "/_vti_bin/SocialDataService.asmx?op=CountCommentsOnUrl",
        type: "POST",
        dataType: "xml",
        data: soapEnv,
        contentType: "text/xml; charset=\"utf-8\"",
        success: function(data, status, xhr){
           if(this.result !== undefined) {
              var commentCount = $('CountCommentsOnUrlResponse', data).find('CountCommentsOnUrlResult').text();
              this.result(commentCount);
           }    
        }
    });
}

$(function() {
   $('span.comments').each(function() {
        var pageUrl = $(this).attr('pageurl');
        var comment = $(this);
        CountCommentsOnUrl(pageUrl,function(commentCount){
           comment.text(commentCount);
        });

   });
});

Modify results using ProcessDataDelegate delegate

So, in order to attach method for processing of social comments to a delegate ProcessDataDelegate we need to subclass Content Query web part as demonstrated below:

/// <summary>
/// Content By Query web part with social capabilities  
/// </summary>
[ToolboxItem(false)] 
public class SocialCBQ : ContentByQueryWebPart
{
    protected override void OnInit(EventArgs e)
    {
        base.OnInit(e);
        this.ProcessDataDelegate = ProcessSocialData;
    }

    private DataTable ProcessSocialData(DataTable data)
    {
        data.Columns.Add("PageUrl", typeof(string));
        data.Columns.Add("PageComments", typeof(int));
        foreach (DataRow row in data.Rows)
        {
            var fileRefVal = new SPFieldLookupValue((string)row[SPBuiltInFieldId.FileRef.ToString("B")]);
            string pageUrl = SPContext.Current.Site.MakeFullUrl("/" + fileRefVal.LookupValue);
            var context = SPServiceContext.GetContext(SPContext.Current.Site);
            var scm = new SocialCommentManager(context);
            var pageComments = scm.GetCount(new Uri(pageUrl));
            row["PageUrl"] = pageUrl;
            row["PageComments"] = pageComments;
        }
        return data;
    }


    private SocialCommentManager manager;
}

Results

---

For more details please follow my blog post How to retrieve and display social comments in Content Query web part

Answer 2

This is probably a bit late, but some other people might search it up anyway. Have you found a solution yet?

I would probably have manipulated the result with xslt. By default the CQWP used itemstyle.xsl and ContentQueryMain.xsl in Style Library/XSL Style Sheet folder. You can either change one of the templates that you use in itemstyle.xsl or make your own template style.

Well, you dont actually modify the result of the query, but just the view/display of it to the user, but that is probably what you wanted from the start anyway.

Answer 3

You might want to start by looking at Item Styles. That will let you modify formatting and which fields are shown.

I'm not sure this is a good approach, I'm very new.