hook_views_tables_alter() cannot add a table

The use case is bio.module. The bio module selects a node type to use as a "profile". I'm adding views support such that we can ask questions like: "show me all the blog posts by authors who have tagged their bio node with the term 'foo'"

To do this, I'm joining the node table against itself (node node LEFT JOIN node bio ON node.uid = bio.uid AND bio.type = 'bio'). Then I'm copying every other table and prefixing it with 'bio' (e.g. 'bio_term_data', 'bio_node_data_field_some_cck_field'). This way, I can search the cck fields, taxonomy terms, etc of the bio node, but return a list of blog posts, e.g.

So going in the tables array might look like:

array(
  'node' => ...
  'node_data_field_in_a_profile' => ...
  'term_node_1234' => ...
);

Coming out it will look like:

array(
  'node' => ...
  'node_data_field_in_a_profile' => ...
  'term_node_1234' => ...
  'bio' => ...
  'bio_node_data_field_in_a_profile' => ...
  'bio_term_node_1234' => ...
);

I cannot do this in hook_views_tables() because I need to know everyone else's table definitions. I could call module_invoke_all('views_tables') from within hook_views_tables, but that sounds like a path to weird recursion issues. What if some other module did this? Mutual recursion is hard to track down. I think the appropriate behavior is to add my pseudo-tables in the alter hook.

I'm going to self-upgrade this to RTBC. I'll call Moshe's endorsement a review as the patch is all of two lines.

Earl, thoughts?

You bet. I just recreated the same technique that I used for bio, but using CCK's node reference fields as the join instead. I broke MySQL's brain (max_allowed_packet size exceeded 1mb, which was my test boxes (reasonable) default).

Long term, I think we need to pull apart cck table definitions into tables and aliases/joins. Each table can have fields, sorts, filters. The aliases would indicate which table they apply to and perhaps include additional fields, sorts, and filters that combine data from across the join. Then we could link up these tables and aliases to create the join trees.

Eg. let's say we have tables node and comment, and a join of comment->node on nid Then I create an alias "bio" of node that joins against the node table based on uid. Then, the autolinker sees that the bio alias is in fact the node table, and creates the alias for bio_comment on the fly based on the join info object.

Or something of this nature. Cloning the entire tree and grafting it back on it on will not scale.

Plus, this is execution order dependent, which I always hate. Combing my node reference joins with bio joins is going to be unpredictable - as one will go before the other.

With great power comes great responsibility. :-)

That said, in the current views implementation I'm hard pressed to see another solution. If views separated the table definitions (ie. fields, filters, and sorts) from the aliases, this could be simpler. I.e. if I could say that "bio" is just the node table based on a join, I wouldn't have to copy and graft the entire tree to itself. That could be computed at run time, instead of storing it in a data structure.

I'm assuming views will switch to the schema API in D6. I think there are a lot of opportunities to improve this code when that happens.

Thanks for committing,
-M