Archive for February, 2013

Pivot tables

One of the more interesting things I’ve learned about recently, that’s proven itself incredibly useful, is the pivot table. A pivot table turns rows into columns. This may seem odd, but the utility of this transformation becomes apparent when you have data that can’t be modeled precisely by a set of attributes because there are attributes which apply to some pieces of data and not others. The typical solution to this problem is simply to have additional columns, allowing for NULL values or defining an appropriate default value (e.g. empty string). However, with a large number of attributes or with user-defined attributes, this solution becomes unattractive, and constructing a pivot table is preferable.

Here I’ll present an example of constructing a pivot table for a schema in which a number of optional attributes, stored as key-value pairs, are attributed to entities. Here’s an ER diagram of the simple schema used in this example:

Pivot Table example schema

  • entities holds a list of entities (companies, users, etc.), with name being the only attribute required for all entities.
  • keyvals hold a list of key-value pairs to associate with entities.
  • entity_attributes maps keyvals to entities.

An SQL query to grab all entities and all associated keyvals would look something like this:

SELECT *
FROM entities
LEFT JOIN entity_attributes ea ON ea.entity_id=entities.id
LEFT JOIN keyvals kv ON kv.id = ea.keyval_id
WHERE 1;

… this would return every entity LEFT JOINed with any associated keyval:

No pivot

We can turn each entry in the key column (keyvals.key) into its own column, with rows having the corresponding entry from the value column (keyvals.value), using a simple conditional statement and alias as shown below:

SELECT entities.*,
IF(kv.key='location', kv.value, '') AS location,
    
IF(kv.key='fax_num', kv.value, '') AS fax_num
FROM entities
LEFT JOIN entity_attributes ea ON ea.entity_id=entities.id
LEFT JOIN keyvals kv ON kv.id = ea.keyval_id
WHERE 1;

No pivot

The keys, location and fax_num, are now represented as columns. For entities with 2+ associated keyvals, we still have multiple rows for each entity, but the data is such that each row only holds a single value entry (keyvals.value) for a single key (keyvals.key), under the respective column. To get a single row per entity, we GROUP BY the entity and take the MAX of each key column.

SELECT entities.*,
MAX(IF(kv.key='location', kv.value, '')) AS location,
    
MAX(IF(kv.key='fax_num', kv.value, '')) AS fax_num
FROM entities
LEFT JOIN entity_attributes ea ON ea.entity_id=entities.id
LEFT JOIN keyvals kv ON kv.id = ea.keyval_id
WHERE 1
GROUP BY entities.id;

pivoted

The result is a pivot table.

A better toggle button, with jQuery and CSS3

One of my more popular articles on this blog has been my jQuery toggle button article. However, my thinking has changed a lot since writing that post and, approaching the problem now, I’d likely do things differently. There were a few key flaws in my approach:

  • Using an additional image for the border. border-radius was still new at the time, but even so I think it would have been better to develop for the bleeding edge, and then deal with fallback for older tech.
  • Using jQuery animate. While I like Javascript as a control mechanism for animations, Javascript-based animations are another matter entirely. CSS3 transitions and transformations are a much better solution, they’re easier to handle and performance is, almost always, much better. Again, this was because I was avoiding the bleeding edge and focusing too much on backward compatibility, as transition and transform were still very new at the time.
  • Not making it a jQuery plugin. There was so much of a dependency on jQuery for selection, modifying the DOM, etc. that wrapping everything in a jQuery plugin made sense. My thinking was always that by not being bound too closely to jQuery, you can always just rip-out the jQuery bits and have a slim, pure JS component. However, there are 2 problems with this idea:
    • it’s not always that easy to rip-out jQuery, and doing so usually means re-creating much of the abstraction and utility functions offered by jQuery
    • plugin or not, you can still end up coupling heavily with jQuery
  • Widgetizing the HTML/CSS. The flaw here is writing out HTML and inline CSS from a Javascript string to create the button, which is usually not a good idea. It’s usually better to leave markup and styling in the document, where they can be readily manipulated or re-style. This is in line with my rant about Enyo (and widget frameworks in general), where I mentioned that widgets lead to a significant loss of flexibility. There is a case to be made for a toggle button widget, as it enables you to automatically replace checkboxes with toggle buttons without modifying any HTML but, in the general, I don’t think it’s worth it.

Below is an updated toggle button accompanied by the code that creates it. As in my previous post, the button itself is a single image, applied as a background-image to an anchor tag, and state transitions are done by shifting the background-position of the element.

Live Demo

HTML

<!-- toggle button markup -->
<a id="btn-toggle" class="btn-toggle" href="#">
    <
input name="yesno" type="checkbox" checked="checked" />
</
a>

CSS

/* toggle button style, styled for initial state (off) */
a.btn-toggle { margin:0; padding:0; display:block; border-radius:32px; background:url(base.png) -57px 0px no-repeat transparent; width:98px; height:64px; }

/* toggle button active state (on) */
a.btn-toggle.active { background-position:-7px 0px; border-color:#40A1EC; }

/* hide the underlying input checkbox of the toggle button */
a.btn-toggle input { display:none; }

Javascript

// jQuery plugin for toggle button
(function( $ ){

    $.fn.makeToggleButton =
function() {
        
    
this.each(function() {
            
            
var elem = $(this);
                    
            
// get the state of the underlying input checkbox
            
elem.val = function() {
                
return elem.find('input').is(':checked');
            }
                    
            
// function to toggle button state
            
elem.toggle = function() {
                    
                
var chkbx = elem.find('input');                    
                
if (!chkbx.is(':checked')) { // not check, switch on
                        
                    
elem.addClass('active');
                    chkbx.attr(
'checked', true);
                }
                
else {

                    elem.removeClass(
'active');
                    chkbx.attr(
'checked', false);
                }
            }
                    
            
// click handler
            
elem.click(function(e) {                    
                elem.toggle();
                e.preventDefault();                    
            });
                    
            
// adjust state to initial value of input checkbox
            
if(elem.find('input').is(':checked'))
            {
                elem.addClass(
'active');
            }
                    
            
// setTimeout to prevent transition upon setting initial state to active
            
setTimeout(function() {
                elem.css(
'transition', 'background-position 0.4s');
                elem.css(
'-webkit-transition', 'background-position 0.4s');
                elem.css(
'-moz-transition', 'background-position 0.4s');
                elem.css(
'-o-transition', 'background-position 0.4s');
                elem.css(
'-ms-transition', 'background-position 0.4s');
            }, 50 );
                
        });


    
return this;
            
    };
})( jQuery );        


// make toggle button when document is ready
$(document).ready(function() {
    $(
'#btn-toggle').makeToggleButton();
});