Columns

Columns

by Michael Eisenbraun

Columns is an easy way of creating JSON data into HTML tables that are sortable, searchable, and paginating. All you need is to provide the data, and Columns will do the rest.

Installation

Include the jQuery Library 1.7 or later and Columns Plugin File:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="js/jquery.columns.min.js"></script>

Include a pre-built Columns theme or create your own

<link rel="stylesheet" href="css/classic.css">

Because Columns create all the necessary HTML dynamically, the only HTML needed is a empty HTML element, such as a <div> tag, with the corresponding id as using in the initialization.

<div id="columns"></div>

Finally, initialize Columns.

<script>
  $(document).ready(function() {
    var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}]; 
    $('#columns').columns({
      data:json
    });
  });
</script>

Examples

Example 1: Setting data inline

$('#example1').columns({
      data: [
          {'Emp. Number': 00001, 'First Name':'John', 'Last Name':'Smith' },
          {'Emp. Number': 00002, 'First Name':'Jane', 'Last Name':'Doe' },
          {'Emp. Number': 00003, 'First Name':'Ted', 'Last Name':'Johnson' },
          {'Emp. Number': 00004, 'First Name':'Betty', 'Last Name':'Smith' },
          {'Emp. Number': 00005, 'First Name':'Susan', 'Last Name':'Wilson' },
          {'Emp. Number': 00006, 'First Name':'John', 'Last Name':'Doe' },
          {'Emp. Number': 00007, 'First Name':'Bill', 'Last Name':'Watson' },
          {'Emp. Number': 00008, 'First Name':'Walter', 'Last Name':'Wright' }
      ]
  });

Example 2: Setting data from external source

$.ajax({
      url:'data.json',
      dataType: 'json', 
      success: function(json) { 
          example2 = $('#example2').columns({
              data:json, 
          }); 
      }
  }); 

Example 3: Using a custom schema

$.ajax({
      url:'data.json',
      dataType: 'json', 
      success: function(json) { 
          example3 = $('#example3').columns({
              data:json,
              schema: [
                  {"header":"ID", "key":"id", "template":"000{{id}}"},
                  {"header":"Name", "key":"name"},
                  {"header":"Email", "key":"email", "template":'<a href="mailto:{{email}}">{{email}}</a>'},
                  {"header":"Gender", "key":"gender"}
              ]

          }); 
      }
  });

Example 4: Using a conditional statement

$.ajax({
      url:'data.json',
      dataType: 'json', 
      success: function(json) { 
          example4 = $('#example4').columns({
              data:json,
              schema: [
                  {"header":"ID", "key":"id", "template":"000{{id}}"},
                  {"header":"Name", "key":"name"},
                  {"header":"Email", "key":"email", "template":'<a href="mailto:{{email}}">{{email}}</a>'},
                  {"header":"Gender", "key":"gender", "condition":function(val) {return (val=="male");}}
              ]

          }); 
      }
  });

Example 5: Destroying an old table and creating a new table

$.ajax({
        url:'data.json',
        dataType: 'json', 
        success: function(json) { 
            example5 = $('#example5').columns({
                data:json,
                schema: [
                    {"header":"ID", "key":"id", "template":"000{{id}}"},
                    {"header":"Name", "key":"name"},
                    {"header":"Email", "key":"email", "template":'<a href="mailto:{{email}}">{{email}}</a>'},
                    {"header":"Gender", "key":"gender"}
                ]

            }); 
        }
    });

    $('#example5Button').click(function() { 
        $('#example5').columns('destroy');

        $.ajax({
            url:'data.json',
            dataType: 'json', 
            success: function(json) { 
                example8 = $('#example8').columns({
                    data:json,
                    schema: [
                        {"header":"Name", "key":"name"},
                        {"header":"Email", "key":"email", "template":'<a href="mailto:{{email}}">{{email}}</a>'},
                        {"header":"Address", "key":"address"},
                        {"header":"City", "key":"city"},
                        {"header":"State", "key":"state"}
                        {"header":"Zip", "key":"zip"}
                    ]
                });
            }
        });
    });

Options

The changing how Columns builds the table is created can be manipulated by passing a object as a parameter during invocation.

There is only one required object attribute, the data attribute, which must be an array of objects (see example above). All other attributes are optional.

conditioning (Boolean)

If false, the default conditioning functionality will be disabled. This may be used to create custom functionality. See Create Plugins for more information.

Default: true

Default: 'ui-table-rows-even'

data (Object)

REQUIRED. This is the data that columns uses to build the table.

 var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];
 
 $('#columns').columns({ 
   data: json
 });

evenRowClass (String)

This class is added to all the even rows within the tbody.

Default: 'ui-table-rows-even'

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json, 
  evenRowClass: 'even-rows'
});

oddRowClass (String)

This class is added to all the odd rows within the tbody.

Default: 'ui-table-rows-even'

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json, 
  oddRowClass: 'odd-rows'
});

liveSearch (Boolean)

If true, results will be filter on keyup. If false, search will not initiate until the "enter" is pressed.

Default: true

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  liveSearch: false
});

page (Number)

The page to be displayed

Default: 1

pages (Number)

The total number of pages in the table. By default, this number is calculated automatically by Columns.

paginating (Number)

If false, the default paginating functionality will be disabled. This may be used to create custom functionality. See Create Plugins for more information.

Default: true

plugins (Array)

Columns will attempt to call the list of plugins. See Plugins for more information.

Default: null

query (String)

If set, will filter data to only to those rows with values that match query.

Default: null

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  query:'row2'
});

reverse (Boolean)

If true, sort data in reverse order; sortBy must be set.

Default: false

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  sortBy: 'col2',
  reverse: true
});

schema (Array of Objects)

If set, formats the table to the schema's design. Each schema object requires two attributes: header (the title of column) and key (the corresponding data attribute key). See Schema, for more information.

Default: null

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  schema:[
      { "header":"Column 1","key":"col1"},
      { "header":"Column 2","key":"col2"}
  ]
});

search (Boolean)

If true, the search box will be displayed. By default, the search box is true only during initialization. See Templating for more information.

Default: true

searchableFields (Array of JSON keys)

If set, the listed keys and there associated values will be searched. If null, all data is searchable.

Default: null

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  searchableFields: ['col1'],
  schema:[
    { "header":"Column 1","key":"col1"},
    { "header":"Column 2","key":"col2"}
  ]
});

searching (Boolean)

If false, the default searching functionality will be disabled. This may be used to create custom functionality. See Create Plugins for more information.

Default: true

showRows (Array of Numbers)

If set, displays a select box with each number as an option.

Default: [5, 10, 25, 50]

size (Number)

The number of rows to display per page.

Default: 10

sortableFields (Array of JSON keys)

If set, the columns associated with the listed keys will be sortable. If null, all columns will be sortable.

Default: null

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  sortableFields: ['col1'],
  schema:[
    { "header":"Column 1","key":"col1"},
    { "header":"Column 2","key":"col2"}
  ]
});

sortBy (String)

If set, sort data by at that attribute key

Default: null

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  sortBy: 'col2'
});

sorting (Boolean)

If false, the default sorting functionality will be disabled. This may be used to create custom functionality. See Create Plugins for more information.

Default: true

templateFile (String)

The path to an external Mustache Template. If null, default template will be used. See Templating for more information.

Default: null

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  template: 'template/custom.mst'
});

Schema

Schema is a blueprint for columns to build the table. With schema you can set column order and column header or even remove columns all together from the original data. Schema allows for templates to be create for row data, and conditional statements can be added to show only desired data.

Schema is an Array of Objects. Each object serves as a table column, and must contain a header and key attribute. The order in which the object are place in the array is the order that they will appear in the table. See below for additional information about the different options that can be applied to each schema object.

condition (Function)

This is a condition that each column data must meet to be displayed. The function must return a Boolean.

var json = [{"col1":"1", "col2":"one"}, {"col1":"2", "col2":"two"}, {"col1":"3", "col2":"three"}];

$('#columns').columns({ 
  data: json,
  schema:[
      {"header":"Numbers","key":"col1", "condition":function(val) { return (val%2 != 0); /*only show odd numbers */ } },
      {"header":"Words","key":"col2"}
  ]
});

header (String)

REQUIRED. The title for the column header.

var json = [{"col1":"1", "col2":"one"}, {"col1":"2", "col2":"two"}, {"col1":"3", "col2":"three"}];

$('#columns').columns({ 
  data: json,
  schema:[
      {"header":"Numbers","key":"col1"},
      {"header":"Words","key":"col2"}
  ]
});

hide (Boolean)

If true, prevents the column from being display. The data within the object is still available to be used by other columns.

var json = [{"col1":"1", "col2":"one"}, {"col1":"2", "col2":"two"}, {"col1":"3", "col2":"three"}];

$('#columns').columns({ 
  data: json,
  schema:[
      {"header":"Numbers","key":"col1"},
      {"header":"Words","key":"col2", "hide": true}
  ]
});

key (String)

REQUIRED. The corresponding data attribute key for this columns data.

var json = [{"col1":"1", "col2":"one"}, {"col1":"2", "col2":"two"}, {"col1":"3", "col2":"three"}];

$('#columns').columns({ 
  data: json,
  schema:[
      {"header":"Numbers","key":"col1"},
      {"header":"Words","key":"col2"}
  ]
});

template (String)

The template allows for a row data to be customized by utilizing a Mustache template. All Mustache compatible syntax is available to the Schema Template.

var json = [{"col1":"1", "col2":"one"}, {"col1":"2", "col2":"two"}, {"col1":"3", "col2":"three"}];

$('#columns').columns({ 
  data: json,
  schema:[
      {"header":"Numbers","key":"col1", "template":"This is row {{#col1}}<strong>{{col1}}</strong>{{/col1}}."},
      {"header":"Words","key":"col2"}
  ]
});

Templating

Columns allows for customize templates to be created using the Mustache template system. This allows developers to add their own classes and structure to Columns.

View Variables

If no custom template is provide, the default template will be used. The variables that are provide to the template are stored in the view object. By default, the follow variables are stored in the this.view:

prevPage: The number of the previous page.

nextPage: The number of the next page.

prevPageExist: If true, there is a valid previous page.

nextPageExists: If true, there is a valid next page.

resultRange: The range of rows currently being displayed.

tableTotal: The total number of rows for the entire table.

showRowsMenu: Outputs the Show Rows select box.

rows: An object containing the rows data for the current page.

headers: An object containing the header data. Headers can be one of four possible states (sortable,notSortable,sortedUp,sortedDown)

query: The current query value, if any.

search: If true, the search box will be rendered. By default Columns will only render the search box once during initialization.

table: If true, the table will be rendered. Unlike the search box, the table is render each time the user calls for different data (i.e. next page, search, sort)

Default Template

<!-- Search Box: Only rendered while search is true --> 

{{#search}}
<div class="ui-columns-search">
  <input class="ui-table-search" placeholder="Search" type="text" name="query" data-columns-search="true" value="{{query}}" />
</div>
{{/search}}

<!-- Search Box: Only rendered while search is true --> 


<!-- Columns Table: Only rendered while table is true --> 

{{#table}}
<div class="ui-columns-table" data-columns-table="true">
  <table class="ui-table">

    <!-- Columns Table Head: Headers have 4 possible states (sortable, notSortable, sortedUp, sortedDown) -->
    <thead>
      {{#headers}} 
        {{#sortable}}
          <th class="" data-columns-sortby="{{key}}">{{header}}</th>
        {{/sortable}}

        {{#notSortable}}
          <th class="" data-columns-sortby="{{key}}">{{header}}</th>
        {{/notSortable}}

        {{#sortedUp}}
          <th class="ui-table-sort-up" data-columns-sortby="{{key}}">{{header}} <span class="ui-arrow">▲<</span>/th>
        {{/sortedUp}}

        {{#sortedDown}}
          <th class="ui-table-sort-down" data-columns-sortby="{{key}}">{{header}} <span class="ui-arrow">▼</span></th>
        {{/sortedDown}} 
      {{/headers}}  
    </thead>
    <!-- Columns Table Head: Headers have 4 possible states (sortable, notSortable, sortedUp, sortedDown) -->

    <!-- Columns Table Body: Table columns are rendered outside of this template  -->
    <tbody>
      {{#rows}}
        {{{.}}}
      {{/rows}}
    </tbody>
    <!-- Columns Table Body: Table columns are rendered outside of this template  -->

  </table>


  <!-- Columns Controls  -->
  <div class="ui-table-footer">
    <span class="ui-table-show-rows">Show rows: {{{showRowsMenu}}}</span> 
    <span class="ui-table-results">Results:
      <strong>{{pageRange.start}} – {{pageRange.end}}</strong> of
      <strong>{{tableTotal}}</strong>
    </span> 
    <span class="ui-table-controls">
      {{#prevPageExists}} 
        <span class="ui-table-control-prev" data-columns-page="{{prevPage}}">
          <img src="images/arrow-left.png">
        </span>
      {{/prevPageExists}}

      {{^prevPageExists}}
        <span class="ui-table-control-disabled">
          <img src="images/arrow-left.png">
        </span>
      {{/prevPageExists}}       

      {{#nextPageExists}}
        <span class="ui-table-control-next" data-columns-page="{{nextPage}}">
          <img src="images/arrow-right.png">
        </span>
      {{/nextPageExists}}

      {{^nextPageExists}}
        <span class="ui-table-control-disabled">
          <img src="images/arrow-right.png">
        </span>
      {{/nextPageExists}}
    </span>
  </div>
  <!-- Columns Controls  -->

</div>
{{/table}}

<!-- Columns Table: Only rendered while table is true -->

API

create

This method creates the Columns table on demand.

External call:

$('#columns').columns('create');

To call internally from a plugin use this.create().

destroy

This method destroys all Columns data associated with the selector and removes it from the DOM. This method is necessary for reinitializing Columns.

External call:

$('#columns').columns('destroy');

To call internally from a plugin use this.destroy().

getObject

This method returns Columns' object

External call:

var columns_object = $('#columns').columns('getObject');

To call internally from a plugin use this.

getPage

This method returns the table's current page.

External call:

var current_page = $('#columns').columns('getPage');

To call internally from a plugin use this.page.

getQuery

This method returns the current search query.

External call:

var search_query = $('#columns').columns('getQuery');

To call internally from a plugin use this.query.

getRange

This method returns the table's current page range. Range is returned as an object.

External call:

var range = $('#columns').columns('getRange');

To call internally from a plugin use this.range.

getRows

This method returns the table's current page rows. Rows are returned as an array.

External call:

var rows = $('#columns').columns('getRows');

To call internally from a plugin use this.rows.

getTemplate

This method returns the Column's Mustache template.

External call:

var template = $('#columns').columns('getTemplate');

To call internally from a plugin use this.template.

getThead

This method returns the table's thead. Thead is returned as an array.

External call:

var thead = $('#columns').columns('getThead');

To call internally from a plugin use this.thead.

getTotal This method returns an interger of the table's current total. Note: This is total after filters and conditions have been applied.

External call:

var total = $('#columns').columns('getTotal');

To call internally from a plugin use this.total.

getView

This method returns the view object that was used to render the Mustache template.

External call:

var columns_view = $('#columns').columns('getView');

To call internally from a plugin use this.view.

gotoPage(int)

This method takes an intiger and if the page exists, changes the table's current page. Note: false is returned if page doesn't exist.

External call:

$('#columns').columns('gotoPage', 3);

To call internally from a plugin use this.gotoPage(3).

pageExists(int)

This method takes an intiger and checks if the page exists in the current table. Returns boolean. Note: Pages start with 1.

External call:

var page_exists = $('#columns').columns('pageExists', 3);

To call internally from a plugin use this.pageExists(3).

resetData

This method resets data to it original state and returns the result.

External call:

$('#columns').columns('resetData');

To call internally from a plugin use this.resetDate().

setMaster(Array)

This method overwrites the master data object and requires an Array. This method would be used to display new data after initialization.

External call:

$('#columns').columns('setMaster', data);

To call internally from a plugin use this.setMaster(data).

setRange()

This method sets the range of rows based on the current page and total.

External call:

$('#columns').columns('setRange');

To call internally from a plugin use this.setRange().

setTotal(int)

This method sets the total number of rows for the table. By default, total is calculated by Columns. To prevent Columns from overwriting total, searching should be disabled.

External call:

$('#columns').columns('setTotal', 100);

To call internally from a plugin use this.setTotal(100).

Creating plugins

Columns is extensible through the use of plugins and Columns API.

The basic plugin structure should look like this. All plugins must include a init and a create method. The init method is only called once during initialization. The create method is called write before a new table display is called.

if (typeof ColumnsPlugins === 'undefined') var ColumnsPlugins = {};
  ColumnsPlugins.your_plugin = {
      init: function() {

      },

      create: function() {

      }
  }

Plugins can be added to Columns by using the plugins option

var json = [{"col1":"row1", "col2":"row1", "col3":"row1"}, {"col1":"row2", "col2":"row2", "col3":"row2"}];

$('#columns').columns({ 
  data: json,
  plugins: ['gotopage']
});

Event Handlers

It is possible for plugins to customize event handlers by overwriting the following variables.

pageHandler: Called when the page navigation (previous or next page) is used. The requested page is set to this.page prior to pageHandler being called.

searchHandler: Called when a search is initiated. The search query is set to this.query prior to searchHandler being called.

sizeHandler: Called when the table size is changed. The table size is set to this.size prior to sizeHandler being called.

sortHandler: Called when the table is sorted. The field to sortBy is set to this.sortBy and the direction is set to this.reverse (if true, ascending) prior to sortHandler being called.