Scenario:

Let’s discuss about one special field in SharePoint List - ‘LikedBy‘ and how to effectively retrieve it. These are columns which get added to list once we configure their “Rating Settings”. We can configure these setting by going to List settings.   


Once we configure Rating setting and choose 'Which voting/rating experience you would like to enable for this list?' as "Likes", there are couple of new columns added to the list. 
      

Following 2 new fields get added to the list.

Also, there will be a hidden field "LikedBy" of type "People and Group" 

Now user has capability to ‘Like’/’unlike’ a list item. But there is a catch, ID of user who likes an item, gets stored as string in a field ‘LikedByStringID’. This particular column can soon carry large string once too many users like one item. Let's consider a normal mid-size company with 5000+ employees, and if 300 user likes one item (Some favorite item). Now if we need to show user a Favorite screen with their liked/un-liked items, there are couple of ways to approach it.


Approach 1: CAML Query 


We can use CAML query to fetch "LikedBy" column 

<FieldRef Name='ID' />
<FieldRef Name='LikedBy' />

This is a simple approach which will work without any issue. But if we look little deeper, it’s a very heavy call on server just because of one column. To populate this column, in back-end, there is multiple JOIN queries in Content Database. SQL query will look for each User ID and get information from multiple tables to populated user details in ‘LikedBy’ column in our CAML Query.

                   

So, for our scenario of 300 users on ‘LikedBy’ column - response size will be really large and operation by very heavy on server. If we are using this operation in SharePoint Online, this can even bring down Office 365 . It’s been tested, if multiple user simultaneously accesses the same Favorite page – page will soon start throwing Time out exception.

Approach 2: CAML + REST

In this approach we will fetch all list items WITHOUT ‘LikedBy’ column. So, it will be a simple CAML query to get all fields other then 'LikedBy'. Now, we need to check which all items are liked by a logged-in user.

This part we will do by a simple REST call,

$.ajax({
      url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/GetByTitle('<list name>')/items?$select=id&$filter=(LikedByStringId%20eq%20%27" + _spPageContextInfo.userId +"%27)",
      async:false,
      headers: { "accept": "application/json;odata=verbose" },
      success: function (data) {
                if(data != null){
                       var entries = data && data.d && data.d.results ? data.d.results : [];
                       //'entries' will have id of items which are liked by current user     
                     };
              },
      error: function () {}
    });

In above REST call we are only selecting “ID” of list items where “LikedByStringID” matches with current logged in user ID. After this call, we have list of all item IDs (using first CAML call) and list of item IDs which current user has liked (using REST in second call). Now, with normal JQuery we can show items which are Favorite of current user on Favorite page and rest items from first call will be an>available for user to like.

Conclusion:

We need to carefully treat special columns while accessing it via code. When there is a multiple User Field, rough estimate how many users accounts their might be. As long as we don’t have many users in one field, we can use simple CAML query to access it. Best approach will be using simple REST call to reduce the size of response.