NovelEssay.com Programming Blog

Exploration of Big Data, Machine Learning, Natural Language Processing, and other fun problems.

AngularJs Pagination with custom Total Count reuse.

I have an AngularJS application that paginates through a table. You can filter the table by column. I'm using angular-paginate-anything as my AngularJS plugin for my paginated table. My table has over a million rows, and the filtered queries are not fast. Selecting "top 10" records is much faster than "get total count", so I'd like to reuse the total count value if the table filters don't change between pages. Unfortunately, angular-paginate-anything doesn't do that out of the box. Fortunately, this article shows how to do that.


Background, I have another article that shows how to use angular-paginate-anything for an Mvc.Net project with pagination. Consider that the starting point. Go ahead and read it quick. We'll wait right here...

http://blog.novelessay.com/post/mvc-net-with-angularjs-and-pagination


Next, we'll open the paginate-anything-tpls.js file and work with that (since it's not minimized). Again, our goal is to only request a new total count when the parameters change.


When the pagination app initially loads, we need to get a total count so we'll initialize that state to true:

              controller: ['$scope', '$http', function ($scope, $http) {
                  $scope.urlParamsChanged = true;

After we get the paginated results from the server, we want to set the urlParamsChange state to false.

function requestRange(request) {
$scope.$emit('pagination:loadStart', request);
$http({
...
}).success(function (data, status, headers, config) {
$scope.urlParamsChanged = false;

Then, we need to set urlParamsChanged to true again when the filter parameters do change:

                  $scope.$watch('urlParams', function (newParams, oldParams) {
                      if ($scope.passive === 'true') { return; }
                      if (!angular.equals(newParams, oldParams)) {
                          $scope.urlParamsChanged = true;

When we do make a request to the server we need to check the urlParamsChanged and indicate if we need total count or not:

                      $http({
                          method: 'GET',
                          url: $scope.url,
                          params: $scope.urlParams,
                          headers: angular.extend(
                            {}, $scope.headers,
                            { 'Range-Unit': 'items', Range: $scope.urlParamsChanged == true ? [request.from, request.to].join('-') : [request.from, request.to, $scope.numItems].join('-') }
                          ),
That's all the java script changes we need. Next, we need to modify our server to check if the total count is passed back to the server. Originally, total count on the input paging model was always 0, so we'll check if it's non-zero in the new code.

[Route("GetSearchResults"), HttpGet()]
[WithPaging]
public IHttpActionResult Search([FromUri] PagingModel pagingModel
, [FromUri] string Name = null
, [FromUri] string Region = null
)
{
IQueryable <company> query = ...
PagingModeSearch result = new PagingModeSearch();
result.From = pagingModel.From;
result.To = pagingModel.To;
if (pagingModel.TotalRecordCount > 0)
{
result.TotalRecordCount = pagingModel.TotalRecordCount;
}

else
{
result.TotalRecordCount = query.Count();
}


Finally, we need to update our WithPagingAttribute class in our NgPaginateModel.cs file to parse the range header for the total count value like this:

    public class WithPagingAttribute : ActionFilterAttribute
    {
        private readonly string _paramName;
        private static readonly Regex RangeHeaderRegex = new Regex(@"(?<from>\d+)-(?<to>\d*)-(?<total>\d*)", RegexOptions.Compiled);
And, very lastly this:
        private static PagingModel IncomingPagingModel(HttpRequestHeaders headers, PagingModel pagingModel)
        {
            if (pagingModel == null)
                pagingModel = new PagingModel();
            IEnumerable<string> rangeUnitValues;
            IEnumerable<string> rangeValues;
            if (headers.TryGetValues("Range-Unit", out rangeUnitValues)
                && rangeUnitValues.First().Equals("items", StringComparison.OrdinalIgnoreCase)
                && headers.TryGetValues("Range", out rangeValues))
            {
                var rangeHeaderMatch = RangeHeaderRegex.Match(rangeValues.First());
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["from"].Value))
                    pagingModel.From = int.Parse(rangeHeaderMatch.Groups["from"].Value);
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["to"].Value))
                    pagingModel.To = int.Parse(rangeHeaderMatch.Groups["to"].Value);
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["total"].Value))
                    pagingModel.TotalRecordCount = int.Parse(rangeHeaderMatch.Groups["total"].Value);

            }
            return pagingModel;
        }


This makes navigating through each page of a search result much faster when the total count can be reused.















Make local MySQL or ElasticSearch instance publicly available for a MVC.Net website with NgRok

Background:

I have a GoDaddy hosting account for NovelEssay.com, and I want to use and provide a large data set (like a personal copy of Wikipedia). GoDaddy's MySQL database limits are 1GB, which is good - but not awesome. I could host MySQL at home on my personal computer, but how could my GoDaddy MVC.Net project access the data without a public address on my local instance?

(Note: I use ngrok, but they are certainly not paying me to write about it. Please read the security disclaimer at the bottom of this article. Exposing local resources to the internet is awesome and will have extra security concerns.)


Introducting: NgRok

If you are thinking something like "I want to expose my local service to a public address" then ngrok is a great (free) tool that I like using. My current usage is exposing my local MySQL database service to the internet, so my public services can use its data.


I kick off ngrok on my local machine like this:

ngrok.exe tcp 3306

That starts up a local process that gives me a public address and port that I can connect to using my MVC.Net projects:

After running ngrok.exe, you should see something like the above. This shows TCP traffic at the public address 0.tcp.ngrok.io on port 123456 (actual value obfuscated) is routed to my localhost:3306, which is exactly where my local MySQL is listening.

Setup:

Head over to ngrok.com and create a free account, and download their ngrok.exe. Your free account has a secret key associated with it. The first time you run ngrok.exe you'll need to set that key like this:

ngrok.exe authtoken <YOUR_AUTHTOKEN>

After that, you're all set to spin up ngrok for exposing local services to the internet.


MVC.Net Connection String:

In order for your mvc.net connection string to hit the ngrok pubilc address, you'll need to change the data source part of the connection string.

An example server and port for connecting to an exposed MySQL instance:

server=0.tcp.ngrok.io;Port=123456
An example data source for connecting to an exposed SQL Server:
data source=0.tcp.ngrok.io,55555

Notice the comma before the port number in the SQL Server connection string format.


In my experience, I only had to change data source or server/port. All of the other fields (user, password, timeouts, etc...) did not need any changes when switching to a ngrok publicly exposed database.


Other Uses:

I also use ngrok.com to expose my local ElasticSearch and some local IIS hosted websites for various (short term) demos and other purposes.


When you publish a website to local IIS, that will be serving over some port that you can configure. We'll use 5555 for this example. You can expose that local IIS hosted website to the internet like this:

ngrok.exe http 5555

Or, a local ElasticSearch like this (with usually runs on localhost:9200):

ngrok.exe http 9200

Warning: ElasticSearch doesn't have built in authentication, so wrapping that with an nginx locally is recommended. I do that with NovelEssay.com in that I'm hosting my website via GoDaddy and use an ElasticSearch in Azure with ngrok and nginx authentication.


Security Disclaimer:

Be careful with ngrok. Exposing your local ElasticSearch directly is not recommended, because ElasticSearch doesn't have authentication built in. You can use things like nginx to create a basic authentication wrapper around ElasticSearch, and then expose the nginx service to the internet via ngrok. The same is with any other local service. Ngrok is awesome, but be careful.

Mvc.Net with AngularJS and Pagination

My personal current "goto" for web based pagination uses angular-paginate-anything plugin with a Mvc.Net backend. As with most web development tools, there's many ways to use something like this. This blog will show one full example of how to do Mvc.Net with AngularJS and Pagination using angular-paginate-anything.


I'm going to try and keep this very simple, so excuse some of the nuance being skipped.

0) Create a MVC.Net project and a controller/view. I'll leave that outside the scope of this article.


1) Create an AngularJS App & Controller. Nothing fancy. Just simple stuff:

Link the AngularJS:

    <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.3.15/angular.min.js"></script>
Create a JS file with the AngularJS code:
var app = angular.module('MyPersonalApp', ['bgf.paginateAnything']);
app.controller('MyPersonalCtrl', ['$scope', '$http', '$filter', '$interval', '$location', function ($scope, $http, $filter, $interval, $location) {
...
}]);
In your cshtml view, link your new javascript file, and make some divs that hold your app:
<script src="~/Scripts/Search/MyPersonalApp.js"></script>
<div id="MyPersonalAppId" ng-app="MyPersonalApp">
    <div ng-controller="MyPersonalCtrl" ng-cloak>
...


2) Download and link the javascript in your view (cshtml).

        <script src="~/Scripts/paginate-anything-tpls.js"></script>


3) In side your app divs, add a table to hold the paginated results and bgf-pagination markup like this:

                            <table>
                                <tr>
                                    <th>Name</th>
                                </tr>
                                <tr ng-repeat="result in searchResults">
                                    <td>{{result.Name}}</td>
                                </tr>
                            </table>
                            <bgf-pagination collection="searchResults"
                                            url="'/api/GetMySearchResults'"
                                            url-params="searchParams"
                                            num-items="searchResultsTotalItems">
                            </bgf-pagination>


4) You'll notice that the bgf-pagination references several things. First, lets look at the searchParams. Ideally, you'll want to hook that up to a searchbox of some sort. For now, we'll hard code a query string to the searchParams in our angular controller like this:

app.controller('MyPersonalCtrl', ['$scope', '$http', '$filter', '$interval', '$location', function ($scope, $http, $filter, $interval, $location) {
    $scope.searchParams = {};
    $scope.searchParams.queryName= "Jo";


5) Next, notice that /api/GetFreeSearchResults URL points at a web API endpoint. We'll set that up by installing the WebAPI Nuget project in our MVC.Net project.

    


6) Create new WebAPI Controller in your project, and make it look roughly like this:

    [RoutePrefix("api")]
    public class SearchApiController : ApiController
    {
        MyDbEntities _db;

        public SearchApiController()
        {
            _db = new MyDbEntities();
_db.Configuration.ProxyCreationEnabled = false; } [Route("GetMySearchResults"), HttpGet()]
[WithPaging] public IHttpActionResult Search([FromUri] PagingModeFreeSearch pagingModel , [FromUri] string queryName = null
) { string localQueryName = string.Empty; if(!string.IsNullOrEmpty(queryName ))
{ localQueryName = queryName;
} var peopleResultsTotal= _db.People.Where(t => t.Name.ToLower().Contains(localQueryName));
var peopleResultsSubset = peopleResultsTotal.OrderBy(o => o.Id).Skip(pagingModel.From).Take(pagingModel.Size);
PagingModeMySearch result = new PagingModeMySearch();
result.From = pagingModel.From; result.To = pagingModel.To; result.TotalRecordCount = peopleResultsTotal.Count();
result.People = peopleResultsSubset.ToArray();
return this.PagedPartialContent(result.People , result.From, result.To, (int)result.TotalRecordCount);
} }

Notice the routing annotations are setup. This also assumes you have a DB with a table named People as your MyDbEntities data source.


7) Where does the [WithPaging] annotation come from? Make a file named NgPaginateModels.cs, and use this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Web;
using System.Web.Http;
using System.Web.Http.Controllers;
using System.Web.Http.Filters;
using System.Web.Http.Results;
namespace MyProject.MVC.Models
{
    // Found this awesome sauce here:
    // https://github.com/begriffs/angular-paginate-anything/wiki/How-To-Configure-ASP.NET-Web-API
    public class WithPagingAttribute : ActionFilterAttribute
    {
        private readonly string _paramName;
        private static readonly Regex RangeHeaderRegex = new Regex(@"(?<from>\d+)-(?<to>\d*)", RegexOptions.Compiled);
        public WithPagingAttribute(string paramName = "pagingModel")
        {
            _paramName = paramName;
        }
        public override void OnActionExecuting(HttpActionContext actionContext)
        {
            object data;
            if (actionContext.ActionArguments.TryGetValue(_paramName, out data))
            {
                //manipulate/inject paging info from headers
                var pagingModel = data as PagingModel;
                pagingModel = IncomingPagingModel(actionContext.Request.Headers, pagingModel);
                actionContext.ActionArguments[_paramName] = pagingModel;
            }
        }
        private static PagingModel IncomingPagingModel(HttpRequestHeaders headers, PagingModel pagingModel)
        {
            if (pagingModel == null)
                pagingModel = new PagingModel();
            IEnumerable<string> rangeUnitValues;
            IEnumerable<string> rangeValues;
            if (headers.TryGetValues("Range-Unit", out rangeUnitValues)
                && rangeUnitValues.First().Equals("items", StringComparison.OrdinalIgnoreCase)
                && headers.TryGetValues("Range", out rangeValues))
            {
                var rangeHeaderMatch = RangeHeaderRegex.Match(rangeValues.First());
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["from"].Value))
                    pagingModel.From = int.Parse(rangeHeaderMatch.Groups["from"].Value);
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["to"].Value))
                    pagingModel.To = int.Parse(rangeHeaderMatch.Groups["to"].Value);
            }
            return pagingModel;
        }
    }
    public class PartialNegotiatedContentResult<T> : OkNegotiatedContentResult<T>
    {
        readonly int _requestedFrom;
        readonly int _requestedTo;
        readonly int _totalCount;
        public PartialNegotiatedContentResult(T content, ApiController controller, int requestedFrom, int requestedTo, int totalCount)
            : base(content, controller)
        {
            _requestedFrom = requestedFrom;
            _requestedTo = requestedTo;
            _totalCount = totalCount;
            if (_requestedTo > _totalCount && _totalCount > 0)
                _requestedTo = _totalCount;
        }
        public override async Task<HttpResponseMessage> ExecuteAsync(CancellationToken cancellationToken)
        {
            var response = await base.ExecuteAsync(cancellationToken);
            const string unit = "items";
            response.Headers.Add("Accept-Ranges", unit);
            response.Headers.Add("Range-Unit", unit);
            if (_totalCount > 0)
            {
                response.StatusCode = HttpStatusCode.PartialContent;
                response.Content.Headers.ContentRange = new ContentRangeHeaderValue(_requestedFrom, _requestedTo, _totalCount)
                {
                    Unit = unit
                };
            }
            else
            {
                response.StatusCode = HttpStatusCode.NoContent;
                response.Content.Headers.ContentRange = new ContentRangeHeaderValue(0);
            }
            return response;
        }
    }
    public static class ApiControllerExtensions
    {
        public static IHttpActionResult PagedPartialContent<T>(
            this ApiController controller,
            IEnumerable<T> content, int from, int to, int count) where T : class
        {
            return new PartialNegotiatedContentResult<IEnumerable<T>>(content, controller, from, to, count);
        }
    }
}


8) What about the PagingModel class? Create a PagingModel base class, and then inherit from it like this:

    public class PagingModeMySearch: PagingModel
    {
        public PagingModeMySearch()
        {
        }
        public Person[] People { get; set; }
    }
    public class PagingModel
    {
        public PagingModel()
        {
            From = 0;
            To = 4;
        }
        public int From { get; set; }
        public int To { get; set; }
        public long TotalRecordCount { get; set; }
        public int Page { get { return (To / Size) + 1; } }
        public int Size { get { return To - From + 1; } }
    }


If you spin this up, your web page should load and the table should show Names like "Joe", "Joseph", "John", etc... with the hard coded "Jo" search string we put in our AngularJS controller.


That's really about all there is to making this work. The rest is search controls and styling up the results.


You can get angular-paginate-anything on Github here:

https://github.com/begriffs/angular-paginate-anything