Monthly Archives: September 2011

Drupal views subqueries

I was using a views_handler_filter in order to add a subquery to a where clause, like so:

/* IN solution */
SELECT DISTINCT(node_revisions.vid) AS vid,
node.nid AS node_nid,
node.type AS node_type,
node.status AS node_status,
FROM node_revisions node_revisions
LEFT JOIN node node ON node_revisions.nid = node.nid
WHERE
node_revisions.vid in
(SELECT MAX(node_revisions.vid) FROM node_revisions GROUP BY node_revisions.nid)

But this is terribly slow. It’s got something to do with using ‘in’. I couldn’t work out how to successfully rewrite the query using ‘exists’, so I rewrote it as a left join like this:


/* LEFT JOIN solution */
SELECT DISTINCT(node_revisions.vid) AS vid,
node.nid AS node_nid,
node.type AS node_type,
node.status AS node_status,
FROM node_revisions node_revisions
LEFT JOIN ( SELECT max(nr.vid) as vid FROM node_revisions nr GROUP BY nr.nid ) nr ON nr.vid = node_revisions.vid
LEFT JOIN node node ON node_revisions.nid = node.nid
WHERE
nr.vid is not null

With the SQL fixed and running smoothly (I am still not sure why using ‘IN’ was such a big issue for mysql), I needed to edit the query somehow, and I wasn’t going to bother doing it with filter handlers. I could have used (http://drupal.org/node/1065554) hook_views_pre_execute(&$view), but I decided to use hook_views_query_alter(), like this:


/**
* Implementation of hook_views_query_alter().
*/
function workflow_views_query_alter(&$view, &$query) {
if($view->name != 'my_content')
return;

$subquery = '(SELECT max(nr.vid) as vid FROM node_revisions nr GROUP BY nr.nid )';
$join = new views_join('node_revisions', 'node_revisions', 'vid', 'vid');
$join->definition = array('table' => $subquery, 'left_field' => 'vid', 'field' => 'vid', 'left_table' => 'node_revisions');
$join->extra_type = 'AND';
$join->table = $subquery;
$join->left_field = 'node_revisions.vid';
$join->field = 'vid';
$join->type = 'LEFT';
$join->adjusted = true;
$query->table_queue['nr'] = array(
'table' => $subquery,
'alias' => 'nr',
'num' => 1,
'join' => $join,
'relationship' => 'node_revisions');
$query->where[2] = array( // Note: do print_r($query) here to find out where to place your where
'clauses' => array('nr.vid is not null'),
'args' => array(),
'type' => 'AND');
}

Drupal menu admin ‘admin/build/menu-customize’ with menu items collapsed

If you’re managing a lot of menu items it can get confusing (in the Drupal 6 interface at least) to see move things around. What would be good is if they were indented or collapsible. There’s a request for it floating about but it doesn’t seem to have generated that much interest. I’ve written a quick javascript solution for it which isn’t all that fast if you have lots of items, but it works for me. Something nicer is probably possible to do with the theme_menu_overview_form (modules/menu/menu.admin.inc), but I haven’t got the time at this point to investigate it further. See this thread http://drupal.org/node/521546#comment-4947478.

You need to add the javascript to your administration theme using the .info file, and substitute your theme name in the code where necessary.


Drupal.behaviors.sanbi_administration = function(context) {
$('#menu-overview tr').click( function() {
$indentationcount = $(this).find('.indentation').length;
if($(this).next().find('.indentation').length > $indentationcount) {
$show = false;
if($(this).hasClass('highlight'))
$show = true;

$(this).toggleClass('highlight');
recursiveHiding($(this).next(), $indentationcount, $show)
}
});

// Walk through table and hide rows which are more greatly indented than the row which was clicked
function recursiveHiding(element, $count, $show) {
if($(element).find('.indentation').length > $count) {
// Remove the highlight class for anything within the hierarchy
$(element).removeClass('highlight');
if($show)
$(element).show();
else
$(element).hide();
if($(element).next().length != 0)
recursiveHiding($(element).next(), $count, $show);
}
}
}

Because I am also adding a lot of custom javascript to certain pages for my administration theme, I actually included it only for the primary links menu like this:


/**
* Implementation of HOOK_preprocess_page().
*/
function mythemename_preprocess_page(&$vars) {
if (module_exists('path')) {
$path = drupal_get_path_alias($_GET['q']);
if($path == 'admin/build/menu-customize/primary-links') {
$vars['scripts'] .= '';
}
}

... Other stuff here...