Convert or document all usages of db_query()

Below is a documentation of all the uses of db_query in views outside of the TempStore.php. All instances follow the pattern: Filename - Line - Function - query

db_query
views.install - L14 - function views_install - db_query("UPDATE {system} SET weight = 10 WHERE name = 'views'");
views.module - L1750 - function views_load_display_records(&$views) - $result = db_query("SELECT * FROM {{$table_name}} WHERE vid IN (:vids) ORDER BY vid, position", array(':vids' => array_keys($names)));

api-default-views.html - L79 - db_query("UPDATE {system} SET weight = 11 WHERE name = 'mymodule'");

admin.inc - L4737 - function views_ui_get_roles() - $result = db_query("SELECT r.rid, r.name FROM {role} r ORDER BY r.name");

handlers.inc - L253 - function function views_get_timezone() - db_query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE");

handlers.inc - L256 - function function views_get_timezone() - db_query("SET @@session.time_zone = '$offset'");

View.php - L1797 - static function load_view() - $result = db_query("SELECT DISTINCT v.* FROM {views_view} v");

View.php - L1819 - static function load_view() - $result = db_query("SELECT * FROM {{$table_name}} WHERE vid IN (:vids) ORDER BY vid, position", array(':vids' => array_keys($names)));

View.php - L1853 - function save() - $vid = db_query("SELECT vid from {views_view} WHERE name = :name", array(':name' => $this->name))->fetchField();

Block.php - L253 - function save_block_cache - db_query("SELECT bid FROM {block} WHERE module = 'views' AND delta = :delta", array( ':delta' => $delta))->fetchField())
argument/CategoryCid.php - L31 - function title_query() - $result = db_query("SELECT c.title FROM {aggregator_category} c
WHERE c.cid IN (:cid)", array(':cid' => $this->value));

Fid.php - L31 - function title_query() - $result = db_query("SELECT f.title FROM {aggregator_feed} f WHERE f.fid IN (:fids)", array(':fids' => $this->value));

filter/CategoryCid.php - L32 - function get_value_options() - $result = db_query('SELECT * FROM {aggregator_category} ORDER BY title');

Rss.php - L61 - function render($row) - $result = db_query('SELECT * FROM {aggregator_category} ORDER BY title');

UserUid.php - L31 - function title() - $title = db_query('SELECT u.name FROM {users} u WHERE u.uid = :uid', array(':uid' => $this->argument))->fetchField();

NewNodeComments.php - L86 - function pre_render(&$values) - $result = db_query("SELECT n.nid, COUNT(c.cid) as num_comments FROM {node} n INNER JOIN {comment} c ON n.nid = c.nid
LEFT JOIN {history} h ON h.nid = n.nid AND h.uid = :h_uid WHERE n.nid IN (:nids)
AND c.changed > GREATEST(COALESCE(h.timestamp, :timestamp), :timestamp) AND c.status = :status GROUP BY n.nid ", array(
':status' => COMMENT_PUBLISHED,
':h_uid' => $user->uid,
':nids' => $nids,
':timestamp' => NODE_NEW_LIMIT,
));

Version.php - L30 - function get_value_options() - $result = db_query('SELECT DISTINCT(version) FROM {locales_source} ORDER BY version');

Vid.php - L31 - function title_query() - $results = db_query("SELECT nr.vid, nr.nid, nr.title FROM {node_revision} nr WHERE nr.vid IN (:vids)", array(':vids' => $this->value))->fetchAllAssoc('vid', PDO::FETCH_ASSOC);
Type.php - L28 - function get_value_options() - $types = db_query('SELECT DISTINCT(type) FROM {system} ORDER BY type')->fetchAllKeyed(0, 0);

VocabularyMachineName.php - L23 - function title() - $title = db_query("SELECT v.name FROM {taxonomy_vocabulary} v WHERE v.machine_name = :machine_name", array(':machine_name' => $this->argument))->fetchField();

VocabularyVid.php - L29 - function title() - $title = db_query("SELECT v.name FROM {taxonomy_vocabulary} v WHERE v.vid = :vid", array(':vid' => $this->argument))->fetchField();

NodeTnid.php - L31 - function title_query() - $result = db_query("SELECT n.title FROM {node} n WHERE n.tnid IN (:tnids)", array(':tnids' => $this->value));

RolesRid - L28 - function title_query() - $result = db_query("SELECT name FROM {role} WHERE rid IN (:rids)", array(':rids' => $this->value));

User.php - L116 - function validate_argument($argument) - $query = "SELECT uid, name FROM {users} WHERE $where"; $account = db_query($query, array(':argument' => $argument))->fetchObject();

User.php - L128 - function validate_argument($argument) - $result = db_query('SELECT rid FROM {users_roles} WHERE uid = :uid', array(':uid' => $account->uid));

Permission.php - L55 - function pre_render(&$values) - $result = db_query("SELECT u.uid, u.rid, rp.permission FROM {role_permission} rp INNER JOIN {users_roles} u ON u.rid = rp.rid WHERE u.uid IN (:uids) AND rp.module IN (:modules) ORDER BY rp.permission", array(':uids' => $uids, ':modules' => array_keys($modules)));

Roles.php - L44 - function pre_render(&$values) - $result = db_query("SELECT u.uid, u.rid, r.name FROM {role} r INNER JOIN {users_roles} u ON u.rid = r.rid WHERE u.uid IN (:uids) ORDER BY r.name", array(':uids' => $uids));

Name.php - L30 - function value_form(&$form, &$form_state) - $result = db_query("SELECT * FROM {users} u WHERE uid IN (:uids)", array(':uids' => $this->value));

Name.php - L134 - function validate_user_strings(&$form, $values) - $result = db_query("SELECT * FROM {users} WHERE name IN (:names)", array(':names' => $args));

Name.php - L159 - function admin_summary() - $result = db_query("SELECT * FROM {users} u WHERE uid IN (:uids)", array(':uids' => $this->value));

db_query_range
admin.inc - L87 - function views_ui_check_advanced_help() - $filename = db_query_range("SELECT filename FROM {system} WHERE type = 'module' AND name = 'advanced_help'", 0, 1)->fetchField();

RelationshipNodeTermDataTest.php - L36 - function createTerm($vocabulary) - 'format' => db_query_range('SELECT format FROM {filter_format}', 0, 1)->fetchField(),

An initial patch to fix some of the db_query.

Another try.

Thanks Tim, have fixed the errors.

#9: 1748168-views_db_queryfix-9.patch queued for re-testing.

#9: 1748168-views_db_queryfix-9.patch queued for re-testing.

#9: 1748168-views_db_queryfix-9.patch queued for re-testing.

Hopefully this fixes it.

+++ b/includes/admin.incundefined
index 80d7d94..6ec0a1c 100644
--- a/lib/Drupal/views/View.php

--- a/lib/Drupal/views/View.php
+++ b/lib/Drupal/views/View.phpundefined

+++ b/lib/Drupal/views/View.phpundefined
+++ b/lib/Drupal/views/View.phpundefined
@@ -1833,8 +1833,12 @@ class View extends ViewsDbObject {

@@ -1833,8 +1833,12 @@ class View extends ViewsDbObject {
     foreach (View::db_objects() as $key => $object) {
       $table_name = "views_" . $key;
       $object_name = "Views$object";
-      $result = db_query("SELECT * FROM {{$table_name}} WHERE vid IN (:vids) ORDER BY vid, position",
-        array(':vids' => array_keys($names)));
+      $result = db_select($table_name, $key)
+        ->fields($key)
+        ->condition('vid', array_keys($names))
+        ->orderBy('vid')
+        ->orderBy('position')
+        ->execute();
 
       foreach ($result as $data) {
         $object = new $object_name(FALSE);
@@ -1867,7 +1871,10 @@ class View extends ViewsDbObject {

@@ -1867,7 +1871,10 @@ class View extends ViewsDbObject {
     }
     // If there is no vid, check if a view with this machine name already exists.
     elseif (empty($this->vid)) {
-      $vid = db_query("SELECT vid from {views_view} WHERE name = :name", array(':name' => $this->name))->fetchField();
+      $query = db_select('views_view', 'v');
+      $query->addField('v', 'vid');
+      $query->condition('name', $this->name);
+      $vid = $query->execute()->fetchField();
       $this->vid = $vid ? $vid : NULL;

I would personally vote to keep this stuff, as it will create a merge conflict in the future, but yeah this is looking fine.

+++ b/lib/Views/aggregator/Plugin/views/row/Rss.phpundefined
@@ -52,12 +52,12 @@ class Rss extends RowPluginBase {
+    $query = db_select('aggregator_item', 'ai');
+    $query->leftJoin('aggregator_feed', 'af', 'ai.fid = af.fid');
+    $query->fields('ai', array('iid', 'fid', 'title', 'link', 'author', 'description', 'timestamp', 'guid'));
+    $query->addField('af', 'title');

I just rechecked, it is adding all the fields from ai. Could we improve things by just loading the actual stuff?

+++ b/lib/Views/user/Plugin/views/argument_validator/User.phpundefined
@@ -85,7 +85,7 @@ class User extends ArgumentValidatorPluginBase {
+        $where = 'uid';

It would be make the code a bit easier to read if we rename that to $condition, but i'm fine with that as well.

+++ b/lib/Views/user/Plugin/views/filter/Name.phpundefined
@@ -131,7 +131,7 @@ class Name extends InOperator {
+    $result = entity_load_multiple_by_properties('user', array('name' => $args));

Awesome!!

Have made those changes, thanks.

Have made those changes, thanks.

Thanks Alasdair. One minor suggestion on the comment:

+++ b/includes/admin.incundefined
@@ -4693,7 +4693,9 @@ function views_ui_get_roles() {
+    // This db_query is not becoming a db_select as it is static and passes no
+    // variables.

+++ b/lib/Views/aggregator/Plugin/views/filter/CategoryCid.phpundefined
@@ -28,8 +28,9 @@ class CategoryCid extends InOperator {
+    // This db_query is not becoming a db_select as it is static and passes no
+    // variables.

For these, I'd just say something like "Use db_query() rather than db_select() because the query is static and does not include any variables." (Function names should be followed by parens in code comments, and we generally don't want to refer to the past or future of the code in comments unless it's specifically a @todo.)

This patch now deals with all the db_query instances, except the exceptions discussed. Ignore this I need to change the comments.

Fixed the comments issue, thanks for the tips xjm.

#24: 1748168-views_db_queryfix-24.patch queued for re-testing.

re-rolling the patch to work with the latest code base.

+++ b/lib/Views/aggregator/Plugin/views/row/Rss.phpundefined
@@ -52,12 +52,13 @@ class Rss extends RowPluginBase {
+    $query->addexpression('af.title', 'feed_title');
+    $query->addexpression('ai.link', 'feed_LINK');

This should probably be addExpression here, but maybe PHP is forgiving us that.

This should be really the last thing todo before the comment.

Fix for addExpression

Now it is definitively time to get this into views!