Attaching a table/matrix/array to a Drupal node

Submitted by tomo on July 5, 2011 - 7:48pm

[This blog post is a rewrite of just the main points due to my baby Macbook Pro dying while I was distracted.]

Drupal content types with CCK make it quite easy to add any number of defined fields to an 'object', and with multiple/unlimited values for a field or with node references it's possible to make a Drupal node 'two-dimensional'.

Sometimes you need more. Sometimes you want tabular data, a table, to be part of a node. If the table always has the same dimensions, and at least the same columns for each node, then the above can work through node references and views.

What if you want to add a different two-dimensional table to nodes of a content type, but without knowing the number or labels for the columns and rows beforehand.  For example, you might want to attach a pricing table to a node, with multiple products and multiple ways to price each product.  An example of that might be 5 t-shirt designs, where shirts are priced based on size and quantity ordered.

There are some contributed modules for this.  First, there's Tablefield:

The input form allows the user to specify the number of rows/columns and allows entry into each table cell using text fields. Tables can be defined globally or on a per-node/per-entity basis, so every node can have multiple tables of arbitrary size. Enter data by hand or by CSV upload. Tables are multi-value and revision capable.

Tablefield lets you define any sized table for any node, and even change after saving a node.  The downside is that the data is stored in a single field as serialized data. This means there's little chance it will be queryable in Views or in sql.  There's also no explicit labeling, although you could use the first row and column for labels.

There's Matrix, which does provide labels. It also makes those available in the field as rows_header and cols_header and the data in a 2D array called data.  This id similar to theming a table with an extra row labels, and is also queryable.  Unfortunately, there's only an uncommitted patch for Views integration.  Furthermore, the dimensions and labels are fixed globally, so won't work if we want unlimited rows and variable columns.



Can we build what we want out of common Drupal building blocks?  Let's think about how we would do it in sql since CCK and Views maps easily to sql.  CCK fields are like columns in a table.  But for our tables, we want columns to be defined per node.  In sql, we could have a columns table where we define the columns and their order per node.  Similarly, rows need to be defined per node, as another table like columns.  Finally, each coordinate in the matrix needs to be recorded with its value (price).  There we would need to record the row, column, and the value, for each node.  In this way, we flatten the 2D matrix into a table.  In fact, more dimensions can be added if we give more generic names to 'rows' and 'columns'.  Furthermore, arbitrary dimensions could be set per node and all labels could go into a single table, with an extra column to order the dimensions.  Example schema: node id | label name | dimension | weight

A table is 2D and easy to see and understand whereas going 3D and beyond gets hard to visualize in a single structure.  So let's stick with 2D for now.

So if we create content types for Rows/Columns with fields for a node reference, title or label, and weight/order and a c-type for Price with either node references or weights for Row/Column and a field for the actual price value and a node reference to the node containing the table (which could also be infered if we reference rows/columns which reference nodes but is a bad idea for other reasons), we just need to add fields in our original c-type to hold unlimited values of Row, Column, and Price.

Unfortunately, such a setup only handles storing the data and entering it one by one with no automatic tabular view unlike Tablefield and Matrix that provide both a table view and a way to edit any value in the table.

So what if Rows were actually nodes with the original node as a parent via node reference, and Column nodes as children.  Actually, Row's children only need the price and column position.  A separate list of nodes would provide the Column labels.  This way, we add rows to a node, perhaps using 'node reference extras' from the little known Noderelationships module to add child nodes in a modal window while editing a node, and then one level deeper to add child prices to a row node.  Using noderelationships, we reverse the node reference to be from parent to child and allow unlimited amounts of the node reference field.

An alternative is to make the Price node allow multiple values for the prices, where each price would correspond with a column.

The downside is that it's not clear when adding 'cells' (the prices, children of a Row, with a Column value) which column we are putting the cell in. When viewing them in a Row node, noderelationships will list them in the order in which they were created without a way to reorder them, nor with the column labels showing.  Viewing a row is also problematic in that we want to display the Column values horizontally with the price values laid out underneath, under each Column.

In the end, it still requires some custom code to theme the node edit forms to show column labels, and also show column labels for the Price nodes values or above all Price nodes like a table.


Conclusion: Tablefield gives us almost what we want except for querying.  We can build the data structures that we want with CCK and Node References, but it doesn't give us a spreadsheet-like form to edit values, nor a table view.

Read the rest of this article...

I kind of skipped over CCK Table, another module, as it states it's a subset of Tablefield.  For completeness: it works by globally defining a maximum number of rows, then in the node edit view you write in a textarea the rows with columns separated by pipes '|'.  The first row is the column labels.  There are no row labels unless you just use the first column for that. I'm not sure how the table displays because it doesn't show for my test.  The field is saved as the textarea text without modification.

© 2010-2014 Saigonist.