Saturday, July 30, 2011

Pad blank rows to Flexigrid

$('#nav').flexigrid({
 url: '/Person/Management/List',
 dataType: 'json',
 colModel: [
  { display: 'Username', name: 'Username', width: 150, sortable: true, align: 'left' },
  { display: 'Firstname', name: 'Firstname', width: 150, sortable: true, align: 'left' },
  { display: 'Lastname', name: 'Lastname', width: 150, sortable: true, align: 'left' },
  { display: 'Favorite#', name: 'FavoriteNumber', width: 150, sortable: true, align: 'left' }
 ],
 buttons: [
  { name: 'Add', bclass: 'add', onpress: add },
  { separator: true },
  { name: 'Edit', bclass: 'edit', onpress: edit },
  { separator: true },
  { name: 'Delete', bclass: 'delete', onpress: del }
 ],
 singleSelect: true,
 sortname: 'Lastname',
 sortorder: 'asc',
 usepager: true,
 title: 'Persons',
 useRp: true,
 rp: 5, 
 rpOptions: [5, 10, 15, 20, 25, 40],
 showTableToggleBtn: true,
 width: 560,
 height: 'auto',
 preProcess: function (data) {                

    var rp = getFgRowsPerPage($('#nav'));
    for (i = data.rows.length; i < rp; ++i) {
        data.rows.push({ 'id': '', 'cell': ['', '', '', ''] });
    }
    return data;
 }
});

function getFgRowsPerPage(tbl) {
    return $('select[name=rp] option:selected', tbl.closest('.flexigrid')).val();
}


Sample output(the last two rows are from the for loop):

Resize Flexigrid's Height

/*
Flexigrid's height is for table's tbody only, it does not include the chrome's (grid's navigation panel, title bar, etc) height. So if you pass 250 to flexigrid's height parameter, and you pass the same 250 to resizeFgHeight, it will actually make the flexigrid smaller.
*/
function resizeFgHeight(tbl, newHeight) {
    grd = tbl.closest('.flexigrid');
    var heightWithChromes = grd.height();
    
    var contentHeight = $('.bDiv', grd).height();
    var chromesHeight = heightWithChromes - contentHeight;    

    $('.bDiv', grd).height(newHeight - chromesHeight);
    grd.css('height', newHeight);
}

// This height is the same as flexigrid's height parameter. 
// Flexigrid's height parameter describes the the tbody's height of the table only,
// it does not include the chromes' heights
function resizeFgContentHeight(tbl, newHeight) {
    grd = tbl.closest('.flexigrid');
    $('.bDiv', grd).height(newHeight);
}

To use
<table id="nav"></table>

$(function() {
   $('#adjust').click(function() { 
      resizeFgHeight($('#nav'), 480);
   });
});

Thursday, July 28, 2011

Postgresql unnest function can do many wonders

I saw an interesting Postgres question on Stackoverflow

How to know if all the elements of the array are NULL?

This code works if you want to test if all the elements of the array are number 8, but doesn't work on NULLs:

SELECT 8 = ALL(ARRAY[8,8,8,8]::int[]); -- returns true


Unfortunately, you cannot use that same construct for comparing nulls, this return null:
SELECT NULL = ALL(ARRAY[NULL,NULL,NULL,NULL]::int[]);

And this returns a syntax error:
SELECT NULL IS ALL(ARRAY[NULL,NULL,NULL,NULL]::integer[]);

Fortunately, there's a function to un-nest an array to row elements; then from there, we can use the ALL construct for rows

So given this:

create table x
(
y serial,
z int[]
);

create table x
(
y serial,
z int[]
);

insert into x(z) values
(array[null,null,null]::int[]),
(array[null,7,null]::int[]),
(array[null,3,4]::int[]),
(array[null,null,null,null]::int[]),
(array[8,8,8,8]::int[]);

q_and_a=# select * from x;
 y |           z
---+-----------------------
 1 | {NULL,NULL,NULL}
 2 | {NULL,7,NULL}
 3 | {NULL,3,4}
 4 | {NULL,NULL,NULL,NULL}
 5 | {8,8,8,8}
(5 rows)


Then let's try to unnest array z from x:
q_and_a=# select *, unnest(z) from x;
 y |           z           | unnest
---+-----------------------+--------
 1 | {NULL,NULL,NULL}      |
 1 | {NULL,NULL,NULL}      |
 1 | {NULL,NULL,NULL}      |
 2 | {NULL,7,NULL}         |
 2 | {NULL,7,NULL}         |      7
 2 | {NULL,7,NULL}         |
 3 | {NULL,3,4}            |
 3 | {NULL,3,4}            |      3
 3 | {NULL,3,4}            |      4
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 4 | {NULL,NULL,NULL,NULL} |
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
 5 | {8,8,8,8}             |      8
(17 rows)

We can compare the element directly from select clause:

q_and_a=# select y, unnest(z), unnest(z) is null from x;
 y | unnest | ?column?
---+--------+----------
 1 |        | t
 1 |        | t
 1 |        | t
 2 |        | t
 2 |      7 | f
 2 |        | t
 3 |        | t
 3 |      3 | f
 3 |      4 | f
 4 |        | t
 4 |        | t
 4 |        | t
 4 |        | t
 5 |      8 | f
 5 |      8 | f
 5 |      8 | f
 5 |      8 | f
(17 rows)


Now, given that information on hand, we could proceed with this query:

with a as
(
    select y, unnest(z) as b
    from x
)
select 
 y, every(b is null)
from a 
group by y
order by y

Output:
 y | every
---+-------
 1 | t
 2 | f
 3 | f
 4 | t
 5 | f
(5 rows)


But the following is a lot better than that:

select
 y, true = ALL (select unnest(z) is null)
from x


Output:
 y | ?column?
---+----------
 1 | t
 2 | f
 3 | f
 4 | t
 5 | f
(5 rows)

If you want to make your query flexible(e.g. by way of concatenated query), you could do the following:

select
y, coalesce( true = ALL (select b is null from unnest(z) as a(b)), false )
from x

Then you can change the expression b is null to something else:

select
y, coalesce( true = ALL (select b = 8 from unnest(z) as a(b)), false )
from x