Many to Many Matrix Grids with Knockout

• Download source code - 4.6 MB

In these days of ever evolving web frameworks and JavaScript libraries, it is all too easy to feel that there is no point putting too much effort into learning them more deeply because they will soon be obsolete. A consequence of this is that you can sometimes miss out on a really great tool because the next killer library script bus has just turned up.

One of the greatest frustrations that I have found over time is getting past the glitzy “hello world” introduction tutorials and spending way too much time finding the right technique to accomplish trickier things. To make matters worse, where you are trying to learn a new version of a maturing framework, you all too often find problem solutions for prior versions of the library that are simply no longer relevant.

This is exactly where I found myself a while back when working with Knockout JS. Knockout suited me because it does not prescribe how I have to communicate with the backend model in the MVVM world. For me, this independence was essential because of the legacy framework I happen to be tied to. I wanted to build an intuitive UX for a complicated set of linking data in a many to many relationship in my data model.

Basically, I needed a grid! Now, I hear you say that there are thousands of samples of grids, and you would be right. However, what you find are nearly all about tabular layouts of fields. What I want is a view model to represent a many-to-many relationship as a matrix where the link between two records becomes the cell in the display tabular view. I won’t bore you with all the detail of the particular data model I was working with, but will instead use a slightly contrived many to many structure that everyone will be able to relate to.

The Problem Domain

My example will centre around a set of Students who take Courses. In a given term, Students should be enrolled in one or more Courses (I’m British, so we don’t do semesters, we do terms!)

From the description text above, you can see that I need three tables:

1. Student
2. Course
3. Student_Course

These give us our classic many to many model as shown below:

This is a vast oversimplification of the entities, but will serve for the purpose of this article.

The SQL in Appendix A will create these tables and relationships in Microsoft SQL Server and create a few sample records.

These three tables and the relationships represent the first “m” (the Model) of an MVVM solution.

The View

Anyone who has looked at the excellent documentation and tutorials from http://learn.knockoutjs.com/ will already be familiar with the “VM” portion of how Knockout fits into the world of modern web applications so I want to start by showing you the “V” (or View) for my student and course example.

This view shows records from the Student entity as row headers and records from the Course entity table as column headers. The key point is that we don’t know how many students or how many courses we are going to be dealing with at the time of designing the view. All we know is that it must be flexible!

The thing that we can be certain of is that the top left cell does not relate to either the Student or Course entities so it is a natural place to put controls that relate only to the view itself.

The cells of the matrix are where we wish to display editable fields of the data from the linking table. You will notice that there are 3 fields holding relevant information about the students enrolled in a given course. It can be hard to display 3 lots of information about each linking record in a single small cell, so the top left cell holds a selection that allows the user to pick which particular piece of information they want to work with.

There is a fourth piece of information here that is not immediately obvious and is hidden from the display. In the example above, you can see there are 4 students and 9 courses. Consequently, there are 36 possible linking records, but if you have used the script from Appendix A, you will only have 12 records to start with and still get this display. The illusive fourth piece of information is whether a link record exists or not! Where it does exist, we will have a “student_course_id” value that we will place in the view model to represent the existence of the link. The VIewModel behind this will take care of this for us. I will come back to it later!

The HTML Mark-up

At this point, I am going to assume that you already know about how Knockout uses mark-up in the HTML to declare what portion of the JavaScript ViewModel to display. You can examine the full mark-up in the source files with this article, but since I assume you know how to define a table, I will concentrate on the interesting parts!

Let us break up the view into 4 parts:

Part 1 - The Grid Control Cell

Irrespective of how many records need to be shown, there will always be a need for a top left cell that does not relate to either of the linked entities. This makes it the perfect location to generate some simple view control selections. The cell will contain the following mark-up.

<div id="divControl">

    <h3>Display Mode:</h3
    <p><select

            data-bind="options: $root.displayModes,

                         value: $root.displayMode,

                   optionsText: function (item) { return item}"  

            size="1"></select>

    </p>

</div>

Here, I am using a drop list box to select which of the link record attributes that will be shown in area 4 which I will describe later. What you can see is that the ViewModel is going to have an observable array of strings, displayModes, that will be used to populate the SELECT options and an observed property, displayMode (note the singular name!)

Part 2 – The Column Entity Headers

When building a matrix, you should choose the most appropriate entity for the column headers. If this were a real world example, there would be far more student entries than courses, so it would be sensible to place the courses in part 2.

What we need is an array of header cells for each header entity record plus 1 cell to label the type of the entity. So, in our first table row, I have the following mark-up:

<th class="columnTitle" style="height:12em" >
    <span data-bind="text: columnEntityTypeName" >Columns</span>
</th>

<!-- ko foreach: cols -->
<th class="columnTitle"
   style="height:12em" >
<span data-bind="text: Name">?</span>
</th>
<!-- /ko -->

Again, the knockout mark-up is showing you what the ViewModel will have to contain. There will be an observable property for the name of the column header records, columnEntityTypeName. In this case, we will have the literal name “Classes”.

Following that is where the magic happens to make this matrix work. I am using the comment pseudo element notation to produce a foreach loop over the observable array called cols. The objects contained in the array need a field for the Name of the entity. E.g. Art, Math, etc.

Part 3 – The Row Entries

Unsurprisingly, the rows are built using a standard approach of a foreach loop bound to a container element.

<tbody data-bind="foreach: rows">
    <tr>
        <th class="rowTitle">
            <div data-bind="text: Name">Row ???</div>
        </th>
        <td class="rowSubTitle">&#160;</td>

Notice that it is necessary to generate an empty cell because there is a cell holding the column entity type name in the first row that needs to be balanced in the view. I could have simply put a colspan attribute on the first cell, but I wanted to keep the markup simple and clear! Notice also that I have declared the CSS class of the additional cell to be rowSubTitle, you could put anything you like here, perhaps it would be a good place to add summary information about the set or linking records that relate to the row.

Part 4 – The Link Cells

Just like I did on the main header row to generate the column headers, I have employed the same technique with pseudo comment elements:

<!-- ko foreach: cells -->
    <td class="cell" >
        <input data-bind="value: targetGrade, 
        visible: $root.displayMode() == 'Target'" />
        <input data-bind="value: currentGrade, 
        visible: $root.displayMode() == 'Current'"/>
        <input data-bind="value: finalGrade, 
        visible: $root.displayMode() == 'Final'"/>
    </td>
<!-- /ko -->

You can see that each row object has an array of cells that has been constructed to match the number of columns. I could have used markup like <!-- ko foreach: $parent.cols --> but I preferred to construct my ViewModel so that the rows had a set of cells the same size as the columns collection.

I have declared 3 input controls here, each bound to a different observable property and I choose to display them based on the displayMode property that I discussed in part 1 above.

In practice, it might not be a good idea to create a grid full of input controls because of the resource wasted by the browser in maintaining such a lot of controls. What you would be better off doing is generating a SPAN with a click handler to position a single control over the cell as required and bind it to some currentCell property on your view model. However, I leave this as an exercise to the reader because to describe it fully would detract from the point of this article, which is a matrix grid!

The View Model and Other Code

Having described the View mark-up and the data table structures describing our model, I have covered the “M” and the “V” of the M-V-VM solution. This leaves only the “VM” view model itself.

I am making the assumption that you already know what a view model is and how to use it in a web page, so I will start simply by listing the scripts that will be loaded in the sample.

These script tags are placed in the HEAD section of the page.

<script src="Scripts/jquery-3.1.1.min.js"></script>
<script src="Scripts/knockout-3.4.0.js"></script>
<script src="Scripts/model.js"></script>

Clearly, the first two are just the libraries that I am using. In a production environment, I would tend to use a CDN source for these. The third one, model.js, contains the code describing the essence of my view model. Below is an edited summary of the code.

Matrix Grid View Model Constructor

The model.js script above contains a single constructor function.

function MatrixGrid(options) {
    //overide defaults object with the options using the jQuery facility
    var _defaults = {
... more code
    }

    jQuery.extend(_defaults, options);//merge the options (if any) over the defaults
    var self = this; //standard technique to hold a reference to the constructed VM object

    self.displayMode = ko.observable(_defaults.displayMode);
    self.displayModes = ko.observableArray(_defaults.displayModes);
... more code
    self.cols = ko.observableArray(_defaults.colData);
    self.rows = ko.observableArray(_defaults.rowData);
    self.columnEntityTypeName = ko.observable(_defaults.columnEntityTypeName)
    self.rowEntityTypeName = ko.observable(_defaults.rowEntityTypeName)
   
    self.load = _defaults.load;
    self.save = _defaults.save;

    self.clear = function () {
        self.cols([]);
        self.rows([]);
    };

   ... more code
}

I am using fairly standard conventions here for a constructor function from which to make a view model object. The name starts with an upper case letter because I intend it to be used with the new operator to construct a view model object. Notice that the function declares an argument, options. I am using a technique borrowed from jQuery plugins where options are passed as an object that is used to override some defaults properties and methods.

jQuery.extend(_defaults, options); //merge the options (if any) over the defaults

After overriding the default values, I store a reference to this in a property self. Again, this is a common technique you can read about elsewhere.

Following this, I have declared a bunch of Knockout Observable properties.

self.displayModes = ko.observableArray(_defaults.displayModes);
self.displayMode = ko.observable(_defaults.displayMode);
self.cols = ko.observableArray(_defaults.colData);
self.rows = ko.observableArray(_defaults.rowData);
self.columnEntityTypeName = ko.observable(_defaults.columnEntityTypeName)
self.rowEntityTypeName = ko.observable(_defaults.rowEntityTypeName)

You can recognize these from the view’s HTML markup. There is the displayModes array that is used in the control cell drop down list and the allied display mode. There are two arrays for cols and rows and a couple of observable properties for the names of the column and row entities. The initial values for all of these has been defined in the _defaults object which the passed options should override.

This view model is constructed from script that has been defined in the client HTML page itself in a jQuery DOM ready method callback function. The code used is this:

var vm = null;

jQuery(document).ready(function () {
    vm = new MatrixGrid({
    displayMode: "Current",
    displayModes: ["Target", "Current", "Final"],
    columnEntityTypeName: "Classes",
    rowEntityTypeName : "Students",
    load: loadGrid
});

ko.applyBindings(vm, document.getElementById("mainContent"));

vm.load(vm);

});

You can see here that I have passed the list of display modes and the initial setting in the plain options object used when calling the MatrixGrid constructor function.

At this point, you may be wondering where the more specific data about the students and classes comes in? The listing above glosses over one crucial point where the options used in constructing the view model object had a property load, which is in fact a function. This function has been defined in the main page file and is called immediately after the applyBindings call. It is defined thus:

function loadGrid(model){
    var url = "data/initialgrid.xml";
    jQuery.ajax({
        url: url,
        cache: false,
        dataType: "xml",
        success: function(data,status,jqXHR){
            var $dom = jQuery(data);
            model.clear();
            //load columns
            $dom.find("col").each(function(idx,tagCol){
                var $col = jQuery(tagCol);
                model.cols.push({
                     ID: $col.attr("ID"),
                     Name: jQuery("Name",tagCol).text(),
                     subText:""})
                    }
              );

              //load rows
              $dom.find("row").each(function(idx,tagRow){
                  var $row = jQuery(tagRow);
                  var rowID = $row.attr("ID");
                  var aCells= ko.observableArray();

                  //find cells for this row
                  $dom.find("cell[rowID = " + rowID + "]").each(
                      function(idxCell, tagCell){
                          var $cell = jQuery(tagCell);
                          var colID = $cell.attr("colID");
                          aCells.push({
                              rowID:rowID,
                              colID: colID,
                              currentGrade: $cell.attr("currentGrade"),
                              targetGrade: $cell.attr("targetGrade"),
                              finalGrade: $cell.attr("finalGrade") }
                  );
              });

              model.rows.push({
                   ID:rowID,
                   Name: jQuery("Name",tagRow).text(),
                   cells: aCells}
              )
          });
       },
       error: function(jqXHR,status,errorThrown){
             alert(status + "\n-----------------\n" + errorThrown)
           }
       });
}

Let’s take a brief walk through of this function. It starts by making an AJAX call (using jQuery) to get an XML document describing the data from the link structures. Recently, there has been a preference in application development to use JSON as the preferred transfer of data strategy. However, I have included SQL for MS SQL Server that generates the required data directly as XML. JSON support in SQL server prior to 2016 is only supported through user defined functions or other CLR types, but XML support since 2008 is excellent.

I chose to generate the transfer data as XML that can easily be produced using the MS SQL Server XML extensions. For this particular article, I saved the results of the “For XML” query directly into a file and use that as a data source URL. In a production environment, you would target some ASHX handler or other resource to get your data dynamically from the database.

Assuming that there is no problem in fetching the data from the server, success callback function will be invoked.

function(data, status, jqXHR) {
    var $dom = jQuery(data);
    model.clear();

     //load columns
     $dom.find("col").each(function(idx, tagCol) {
         var $col = jQuery(tagCol);
         model.cols.push({
            ID: $col.attr("ID"),
            Name: jQuery("Name", tagCol).text()
                })
            });

           //load rows
    $dom.find("row").each(function(idx, tagRow) {
        var $row = jQuery(tagRow);
        var rowID = $row.attr("ID");
        var aCells = ko.observableArray();
        //find cells for this row

        $dom.find("cell[rowID = " + rowID + 
        "]").each(function(idxCell, tagCell) {
            var $cell = jQuery(tagCell);
            var colID = $cell.attr("colID");
            aCells.push({
                rowID: rowID,
                colID: colID,
                currentGrade: $cell.attr("currentGrade"),
                targetGrade: $cell.attr("targetGrade"),
                finalGrade: $cell.attr("finalGrade")
            });
        });
        model.rows.push({
            ID: rowID,
            Name: jQuery("Name", tagRow).text(),
            cells: aCells
        })
    });

jQuery is very well placed to navigate an XML data document using some very simple and familiar syntax. So, I have created a jQuery object of the DOM document ($dom) and then I use the find method of a jQuery result to build an “each” function. The structure of the data is very simple:

<grid>
    <row ID="1"><Name>Student Name</Name></row>
    ...

    <col ID="9"><Name>Course</Name></col>

    ...

    <cell rowID="1" colID="9" attributeValue1="?" />
    ...
</grid>

What may not be immediately obvious is that any missing cells in our data model are built in the server code to ensure that we have a full set of cells. In other words, if we have 4 rows and 6 columns, we will always have 24 cells. Where a “blank” cell is found, the ID attribute will be NULL and so does not appear in the XML output.

As you can see, the set of columns is worked out first:

$dom.find("col").each(function(idx, tagCol) {
         var $col = jQuery(tagCol);
         model.cols.push({
            ID: $col.attr("ID"),
            Name: jQuery("Name", tagCol).text()
                })
            });

Each <col> element is used to create a simple object that is added to the observable array of the model.cols property using the push method.

Similarly, the rows are processed. The difference for the rows is that an inner loop is performed using selector predicate syntax to get just the rows that are linked to the current row ID.

$dom.find("cell[rowID = " + rowID + "]")

Each found cell element is used to create a simple object and append to an array of cells for the row.

And that is all there is to it!

To use this in the real world, you would have to decide how you want to collect changes made in the screen and update the server. You could react to every change in the UI and send messages back to your server, or you could leave of the changes data in the client application until the users click some form of submit button where all the changes are sent at once. The choice depends on what needs to happen on the server following changes that are sent.

Whichever strategy you use, you should take care to consider multi user updates. I tend to place a timestamp field on the link table and send this to the client in the cell tags as an additional attribute. The data appears as a Base 64 encoded string which can be checked before updates are committed. However, further discussion on this is well beyond the scope of this article!

I hope you can take this sample forward and make some really useful matrix UI screens. They have certainly simplified life for my users!

Appendix A – Generation SQL

IF not OBJECT_ID('Student_Course') IS NULL DROP TABLE Student_Course
IF not OBJECT_ID('Student') IS NULL DROP TABLE Student
IF not OBJECT_ID('Course') IS NULL DROP TABLE Course

GO

CREATE TABLE dbo.Student(
       Student_ID INT NOT NULL IDENTITY(1,1)
              CONSTRAINT Student_PK PRIMARY KEY
  , FirstName NVARCHAR(100) NOT NULL
  , LastName  NVARCHAR(100) NOT NULL
  , Notes     NVARCHAR(MAX) NULL
  , FullName AS FirstName + ' ' + LastName
)

CREATE TABLE dbo.Course (
       Course_ID INT NOT NULL IDENTITY(1,1)
              CONSTRAINT Course_PK PRIMARY KEY NONCLUSTERED
  , Name NVARCHAR(100) NOT NULL 
              CONSTRAINT Course_Unq_Name UNIQUE CLUSTERED
  , IsCompulsory BIT NOT NULL
              CONSTRAINT Course_Def_IsComp DEFAULT(0)

  , Notes NVARCHAR(MAX) NULL
)

CREATE TABLE dbo.Student_Course (
       Student_Course_ID INT NOT NULL IDENTITY(1,1)
              CONSTRAINT Student_Course_PK PRIMARY KEY
  , Student_ID INT NOT NULL
              CONSTRAINT Student_Course_FK_Student REFERENCES Student(Student_ID) ON DELETE CASCADE
  , Course_ID INT NOT NULL
              CONSTRAINT Student_Course_FK_Course REFERENCES Course (Course_ID) ON DELETE CASCADE
  , Current_Grade CHAR(1) NULL
  , Target_Grade CHAR(1) NULL
  , Final_Grade CHAR(1) NULL
)

GO

INSERT INTO Student(FirstName,LastName)
VALUES
    ('Alan','Adder')
  , ('Belinda','Blair')
  , ('Colin','Carpenter')
  , ('Daniel','Diamond')

INSERT INTO Course(Name,IsCompulsory)
VALUES
    ('Maths',1)
  , ('English Lit.',1)
  , ('English Lang.',1)
  , ('Biology',0)
  , ('Chemistry',0)
  , ('Physics',0)
  , ('Art',0)
  , ('Geography',0)
  , ('History',0)

--Prefill the compulsory links!

INSERT INTO Student_Course( Student_ID,Course_ID)
SELECT
       S.Student_id
  , C.Course_ID
FROM Student S
CROSS JOIN Course C
WHERE C.IsCompulsory = 1