Jun 17, 2014

db_*() functions largely remain the same, at least in basic usage

Functions for accessing and manipulating the database are as in D7. So far, I have successfully used the following functions in Drupal 8 while changing very little.

db_select() 
db_query() 
db_update()
db_insert()

I did run into one problem with using db_query() to carry out a SELECT statement. Applying the rowCount() method to the object returned by db_query() resulted in this runtime error.
Drupal\Core\Database\RowCountException: rowCount() is supported for DELETE, INSERT, or UPDATE statements performed with structured query builders only,  . . .
I'm not sure what that message means. I ended up replacing this offending code (I omitted most of the SQL statement)
$query = db_query('SELECT project_title' . 
                   ' FROM {optimizely} WHERE .... ');
$query_count = $query->rowCount();
with
$query = db_query('SELECT project_title' . 
                   ' FROM {optimizely} WHERE .... ');
$results = $query->fetchCol(0);
$query_count = count($results);
If you're doing anything a bit advanced, you may run into other problems. One such use case is described in the source article below.

Sources:

Paging db_selects in Drupal 8
http://running-on-drupal8.co.uk/blog/paging-dbselects-drupal8

public function Statement::rowCount
https://api.drupal.org/api/drupal/core!lib!Drupal!Core!Database!Statement.php/function/Statement%3A%3ArowCount/8

3 comments:

  1. FYI, count() might be off by 1 as it counts the number of index in the array. A zero based index $some_array[0] = '??' would count as one.

    Another option is to add count to the query column with:
    $query = db_query('SELECT project_title, COUNT(oid) ' .

    or

    https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Statement.php/function/Statement%3A%3ArowCount/8

    ReplyDelete
    Replies
    1. The PHP count() function counts the number of array elements if its argument is an array, which in this case is equal to the number of rows in the result, which is what we want.

      It looks like the SQL COUNT() function could have been used as part of the query itself in addition to retrieving the project_title column. Thanks for mentioning this alternate approach, as I wasn't aware that COUNT() can be used this way.

      Delete
    2. Thanks for the reference to the doc on the rowCount() method, which confirms the runtime problem I had. I've added that doc to the list of sources.

      Delete