These are for educational purposes only ("what does this query look like in full DBTNG object-oriented query builder?"). The db_select() built versions of the queries will work fine, but the db_query() SQL versions are recommended.
Grouped count of Users
<?php
/**
* Summary data for People section (admin/people).
*/
function xray_people_summary() {
$data = array();
// Fetch the number of enabled and disabled (blocked) users.
$data['users'] = db_query("SELECT status, COUNT(*) as num FROM {users} WHERE uid <> 0 GROUP BY status")->fetchAllKeyed();
// @TODO roles, permissions
return $data;
}
?>A working replacement for db_query() with db_select() would look like:
<?php
$query = db_select('users')
->fields('users', array('status'))
->condition('uid', 0, '<>');
$num = $query->addExpression('COUNT(uid)', 'num');
$result = $query
->groupBy('status')
->execute()
->fetchAllKeyed();
$data['users'] = $result;
?>Count of blocks (necessarily filtered for one theme)
<?php
/**
* Fetch the total number of blocks available on the Drupal site.
*/
function xray_stats_block_total() {
// Get count of total blocks. All blocks are repeated in the block table
// for each theme, so we filter for one theme (it could be any theme).
return db_query("SELECT COUNT(*) FROM {block} WHERE theme = :theme", array(':theme' => variable_get('theme_default', 'bartik')))->fetchField();
}
?>A functionally identical db_select() version:
<?php
return db_select('block')
->fields('block')
->condition('theme', variable_get('theme_default', 'bartik'))
->countQuery()
->execute()
->fetchField();
?>Number of enabled blocks per theme
<?php
/**
* Fetch the number of blocks enabled per theme.
*/
function xray_stats_blocks_enabled_by_theme() {
return db_query("SELECT theme, COUNT(*) as num FROM {block} WHERE status = 1 GROUP BY theme")->fetchAllKeyed();
}
?>And as the much more verbose (and again, not recommended) db_select()...
<?php
$query = db_select('block')
->fields('block', array('theme'))
->condition('status', 1, '=');
$count_alias = $query->addExpression('COUNT(bid)', 'num');
$result = $query
->groupBy('theme')
->execute()
->fetchAllKeyed();
return $result;
?>Historical note: All these are tested, indeed, the db_select() versions were written first. DBTNG was shiny and new, and i struggled through figuring them out because i thought that's what i was supposed to use!