How to define another data table to join for views (ie from csv file)?

Question

I successfully created two Views query plugins by learning from XML Views. My plugins query a YAML, and CSV file; they are both base view types on their own.

Now I want to learn how to join tables. (The following assumes a minimal views module skeleton called csv_views, I deleted the query plugin class I mentioned before.)

I thought of a fix table (i.e. a CSV file) that is queried without any additional features. I want to join it to a node table, let's say via title field, both in node (node title) and a column in the CSV file.

What do I need to define in mv csv_views.views.inc? For now it contains the following:

function csv_views_views_data() {
  $data['csv']['table']['group'] = t('CSV');

  $data['csv']['table']['join'] = array(
    'node' => array(
      'left_table' => 'node',
      'left_field' => 'title',
      'field' => 'title',
    ),
);

  return $data;
}

Is a field handler necessary?

In which class is the table query execution method defined? Is there a views module I can examine to learn that?
AFAIK all modules which provide tables to be joined use real database tables.

I use Drupal 6 because that is the Drupal version required by XML Views works, but I want to know Drupal 7 too and I could also settle to views 2. I guess it's still the main Views version, right?

Answer 1

Views can do some clever things. But I think that joining a sql query to a csv file may be a bit beyond it. (not that I'll be surprised if someone has a solution).

The issue is that within a data engine (sql, mongodb, sparql), views can work well providing that everything you want to work with is also in the same engine. If you wanted to say join a sql table to a sparql query you would have to try and do the join in code. Bring back results from sql and use in sparql or vice versa. This is not a scalable solution. It may work for a few rows, but beyond that you are going to hit some serious performance buffers.

In this case you may have more luck importing the csv file to mysql and using the data module. that should allow you to define joins and use it in your view.