Part 5: Graphing

Necessary Materials:

  • Node.js
  • Express
  • Chart.js

Node Modules Used:


To maintain a social network, and improve the experience for an end-user, it’s important to keep track of information about these users and their usage habits. Much of this information may not seem immediately useful to the end-user, but one intersection of this tracking and useful end-user information is login times. This information is used in many different ways to optimize usage of these networks for the consumers (scale up nodes for peak traffic), as well as to track potential security concerns.

Since this application concerns itself primarily with the basic bones of a social network, we use some graphing to analyze data we are collecting about the user.

From the start we discussed the ‘timeTracker’ object within each user document. This had a ‘loginTimes’ attribute which we have been continually updating during every login with the ‘/api/loginAuth’ endpoint. If this is new to you, refer back to part 2 where this endpoint was discussed at length. In essence, this endpoint continually prepends the latest time the user logs in to the array ‘loginTimes’. This is the data we will analyze using a N1QL query, as well as a Chart.js adaptation for Angular.js (tc-angular-chartjs), to display the information retrieved from the N1QL date query.

Below, we have the statistics endpoint which is used to retrieve the data from each user’s ‘loginTimes’ attribute, and return an object that the front-end graphing library can use to plot the site’s usage over time.

‘/api/graphData’ API

This endpoint takes in one argument, which is the scope of time which should be displayed. In this case, the function either accepts ‘day’ or ‘week’. This will return an array of either 24 integer values (‘day’), or 7 integer values (‘week’) depending on which type of date range is specified. The function that gets all of this data is ‘Statistics.newGraph’, which will create a N1QL query to get the values we desire. The function is shown below.

‘Statistics.newGraph’ function

This incredibly long function is actually doing something very simple. There are three parts to this:

  1. Check the date range of ‘week’ or ‘day’
  2. Execute N1QL query to group data by the last 7 days or the last 24 hours
  3. Use moment.js and arrays to create the proper order of the x-axis

The first part of this is accomplished simply using ‘if/else’ statements. The next part gets slightly more complicated, so we will take a second to break down the N1QL query that is being executed, and how we understand what data it will retrieve for us.

N1QL date query example for past day

Let’s take the example of a ‘day’ to understand the process. The way to look at it is that we first unnest all of the array elements into their own separate objects. In this way we can observe each one individually. After we UNNEST them, we find out how many hours ago each login time occured, from the time of the API request, which is done using DATE_DIFF_STR(STR_TO_UTC(NOW_STR()). We then take each of these times and GROUP BY how long ago they occured. Based on these groups, we get a COUNT of the number of times that are in that group (how many of these logins occured per hour). This gives us an array of objects that contain the number of hours ago these times occured, and a count that describes how many logins occured this many hours ago. Once we have this, we create a HAVING clause that checks to make sure the number of hours ago these happened does not exceed 24, so that we simply get one day; no more, no less. This now gives us exactly what we needed, which is an array of objects stratified by the number of hours ago each of these logins happened, as well as how many logins happened.

After this, we take the array of objects, and place the login counts into an array, which is indexed based on the hour at which they happened. This array is initialized with 0s, such that there is no error with graphing undefined values. Once this is done, the process of handling data from the N1QL query is complete.

That takes care of a bulk of the work, and now all we have to do is simply match these up with the proper hours and dates accordingly. We use a predefined array of either ‘hoursX’ or ‘daysX’ and loop through it to create the proper x-axis. Using the current day, which is found by moment.js, we then create a successful iterative pattern to find the according days and weeks. When we use the moment.js function for ‘moment().day();’, etc. we retrieve a day or hour indexed starting at 1. This is accounted for in the iterative pattern.

Front-end usage

Once both of these arrays are created/obtained, there is no more work to be done, other than on the front-end. We simply return these two arrays to the API endpoint in the form of ‘graphObj’ and then send them back to the client in our ‘/api/graphData’ endpoint. This completes the back-end work. A set of example code of using this with tc-angular-chartjs can be seen in the github repo in public/js/touchbase.js under ‘statisticsController’, and the HTML for it can be seen in public/html/statistics-partial.html.

That concludes the graphing portion of the Touchbase tutorial. Please comment below with any feedback or criticism. Thanks for reading, and hope you have an easier time making beautiful charts using N1QL date queries and a great front-end graphing library!


Posted by Pranav Mayuram

Pranav Mayuram is a N1QL Query language intern, Couchbase. Built a social network platform, Touchbase, using Couchbase Server, Node.js, Express & Angular.js.

Leave a reply