jQuery Grid Plugin - jqGrid

Many times the nested set model doesn't have a 'level' field. This can be calculated from just the 'lft' and 'rgt' fields although it does add overhead to the mySQL server. Hope this helps someone else. 🙂

This has been adapted from a technique used by Joe Celco and the amazing work he has done with nested sets...

$SQL = sprintf("
SELECT t1.id AS id, t1.title AS title, t1.lft AS lft, t1.rgt AS rgt, COUNT(t2.id) AS level
FROM my_table AS t1, my_table AS t2
WHERE (t1.lft BETWEEN t2.lft AND t2.rgt)
AND (t1.lft > %s AND t1.rgt < %s )
GROUP BY t1.id
HAVING (COUNT(t2.id)= %s )
ORDER BY t1.lft;"
, $n_lft
, $n_rgt
, $n_lvl );