Exposing External and Custom Data to Views with hook_views_data()

You’re absolutely right 👍 — before rolling your own @ViewsQuery plugin, it’s worth checking whether an existing contributed module already solves most of the problem.

• views_database_connector
  Lets you connect Views directly to an external database (MySQL, PostgreSQL, etc.). If your data lives in another DB, this is often the simplest solution.
• views_json_source
  Lets you pull data from a JSON feed or REST API into Views. For your example with https://jsonplaceholder.typicode.com/comments?postId=1, this module could expose that endpoint to Views without writing custom query plugins.
• views_csv_source
  Similar idea, but for CSV files. Useful if your external system exports CSV instead of offering an API.

✅ These modules give you a starting point with less custom code.
⚡ But if you need fine-grained control, custom hook_views_data() + @ViewsQuery is still the way to go.

This is a common pitfall when first experimenting with custom data sources in Views. Let me break down what’s happening:

Why you’re seeing

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'testable.external_data' doesn't exist

• Your ExternalDataQuery plugin extends Sql, which means Views assumes there’s a real SQL table called external_data.
• That’s why Views tries to generate a SELECT … FROM external_data query.
• Since that table doesn’t exist in your DB, you get the error.

Two important clarifications

1. You cannot use Sql if you don’t have a database table.
   Instead, you need to extend ViewsQueryBase (non-SQL query) and implement your own logic in execute().
2. Custom query plugins don’t automatically show up in the UI.
   They must be connected via hook_views_data() where you define a base table with query_id = "external_data_query".
   Without this, Views doesn’t know that your plugin should handle the “table”.

Minimal working pattern

1. Define query plugin

<?php 

namespace Drupal\external_data\Plugin\views\query; 

use Drupal\views\Plugin\views\query\Sql; 
use Drupal\views\ViewExecutable; 
use GuzzleHttp\Exception\RequestException; 

/ 
 * @ViewsQuery( 
 *   id = "external_data_query", 
 *   title = @Translation("External Data Query") 
 * ) 
 */ 
class ExternalDataQuery extends Sql { 

  / 
   * {@inheritdoc} 
   */ 
  public function execute(ViewExecutable $view) { 
    $client = \Drupal::httpClient(); 
    $rows = []; 

    try { 
      // Perform request to external API. 
      $response = $client->get('https://jsonplaceholder.typicode.com/comments', [ 
        'query' => ['postId' => 1], 
      ]); 

      if ($response->getStatusCode() === 200) { 
        $data = json_decode($response->getBody()->getContents()); 

        // Convert JSON response into a result array for Views. 
        foreach ($data as $item) { 
          $rows[] = (object) [ 
            'id' => $item->id, 
            'name' => $item->name, 
            'email' => $item->email, 
            'body' => $item->body, 
          ]; 
        } 
      } 
    } 
    catch (RequestException $e) { 
      // Log an error if API request fails. 
      \Drupal::logger('external_data')->error('API request failed: @msg', ['@msg' => $e->getMessage()]); 
    } 

    // Pass results into the View. 
    foreach ($rows as $row) { 
      $view->result[] = $row; 
    } 
  } 
} 

👉 Notice: extends ViewsQueryBase (not Sql).

2. Define hook_views_data()

<?php 

use Drupal\views\ResultRow; 
use Drupal\views\ViewExecutable; 

/ 
 * Implements hook_views_data(). 
 */ 
function external_data_views_data() { 
  $data['external_data']['table'] = [ 
    'group' => t('External Data'), 
    'base' => [ 
      'field' => 'id', 
      'title' => t('External Data'), 
      'help' => t('Data from external source.'), 
      'query_id' => 'external_data_query', // 👈 custom query instead of a real DB table 
    ], 
  ]; 

  $data['external_data_table']['id'] = [ 
    'title' => t('ID'), 
    'help' => t('External ID'), 
    'field' => [ 
      'id' => 'numeric', 
    ], 
  ]; 
  $data['external_data_table']['name'] = [ 
    'title' => t('Name'), 
    'help' => t('External Name'), 
    'field' => [ 
      'id' => 'string', 
    ], 
  ]; 

  // Field: id 
  $data['external_data']['id'] = [ 
    'title' => t('External ID'), 
    'help' => t('ID from external source'), 
    'field' => [ 
      'id' => 'standard', 
    ], 
  ]; 

  // Field: name 
  $data['external_data']['name'] = [ 
    'title' => t('External name'), 
    'help' => t('Name from external source'), 
    'field' => [ 
      'id' => 'standard', 
    ], 
  ]; 

  return $data; 
} 

/ 
 * Implements hook_views_data_alter(). 
 */ 
function external_data_views_data_alter(array &$data) { 
  // Attach a row plugin for the external data table. 
  $data['external_table']['table']['base']['row_plugin'] = 'views_plugin_row'; 
} 

/** 
 * Implements hook_views_pre_render(). 
 * 
 * This runs before the View is rendered. 
 */ 
function external_data_views_pre_render(ViewExecutable $view) { 
  // Iterate over all result rows. 
  foreach ($view->result as $i => $row) { 

    // Skip if the row is already a ResultRow object. 
    if ($row instanceof ResultRow) { 
      continue; 
    } 

    $values = []; 

    // Map View fields to the row data. 
    foreach ($view->field as $alias => $field) { 
      // Use the alias if present on the source object. 
      if (isset($row->{$alias})) { 
        $values[$alias] = $row->{$alias}; 
      } 
      // Otherwise try the handler field name. 
      elseif (!empty($field->field) && isset($row->{$field->field})) { 
        $values[$alias] = $row->{$field->field}; 
      } 
      else { 
        $values[$alias] = NULL; 
      } 
    } 

    // Replace the raw object with a ResultRow. 
    $view->result[$i] = new ResultRow($values); 
  } 

  // Debug log (can be removed in production). 
  \Drupal::logger('external_data')->notice('<pre>@result</pre>', [ 
    '@result' => print_r($view->result, TRUE), 
  ]); 
} 

👉 Critical difference: query_id binds your fake “table” to the ExternalDataQuery plugin.

3. Add it in Views

• In the “Show” dropdown (Advanced → Query settings), your External Data Query should now appear as a selectable data source.
• You’ll be able to add fields like id and name.

About “fields not available as filters”

That’s expected unless you define filter handlers in hook_views_data():

$data['external_data']['id']['filter'] = [
  'id' => 'numeric',
];
$data['external_data']['name']['filter'] = [
  'id' => 'string',
];

Without those, Views only knows how to display the fields, not how to filter/sort them.

✅ So the fix:

• Use ViewsQueryBase instead of Sql.
• Add query_id to your base table definition.
• Explicitly define filter/sort handlers in hook_views_data().