SQL and DBTNG Query Builder Versions of the Same Queries

  • 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!