The current db_rewrite_sql hook only allows you to add additional 'JOIN' and 'WHERE' statements to a query. This is because the hook was born out of the node access system, where the main thing that needs to be done to a query is to add additional filters to it, to reduce the number of potential results that the query can yield.

I think that this hook also needs to support adding additional 'SELECT', 'GROUP BY', and 'ORDER BY' statements to a query. In particular,adding support for 'SELECT' statements could have numerous applications of great benefit to Drupal.

The application that I have in mind is that of contrib modules loading additional information into nodes. At the moment, contrib modules that maintain their own node-related information in the database need to grab that information using their own separate queries, and then merge that information into the node object using hook_load().

Instead, the majority of those modules (particularly those where the data maps 1-1 to a node) could simply implement hook_db_rewrite_sql() instead, and request additional fields to be selected from the database and then added to the node object. This would be a huge performance benefit, as it would mean that only one query is needed to grab most of the information relating to a specific node.

hook_load() would still be needed for additional operations that need to be performed at load time, and probably for querying more complex data, such as that which involves one-to-many or many-to-many relationships. BUT, I say probably, because it may even be possible to deal with that using db_rewrite_sql. 1-m or m-n fields could be loaded through db_rewrite_sql, and node_load() could be changed to 'look' for such fields, and to build them into an array before adding them to the node object. However, I'm not sure if that would provide better or worse performance than dealing with such cases in a separate query. And, of course, there will be some cases for which nothing will work (including this array-building approach), except for a completely separate query.

Loading node information is just one example - I'm sure there are many more possibilities. I think that extending db_rewrite_sql in this way will open the door for Drupal to provide much better support for custom data structures and relationships. The two hottest modules for 4.7 - views and CCK - would both surely be able to take advantage of this improvement.

Comments

snelson’s picture

I was thinking the same thing. Actually I'm kinda surprised this was the only post I could find on this topic. And no comments??? So from what I understand, for a listing of just say 20 custom nodes on a page, right now we're looking at least 2 or 3 queries per node, correct? Yeah I'm with you on this. I'm pretty sure core taxonomy would benefit from this as well on term pages.

moshe weitzman’s picture

seems reasonable ... if people start adding fields directly to noden table, how will these fields get revision controlled (in the event that this is desired)

pwolanin’s picture

Sounds like a good idea, since I like efficiency, but also sounds like it might muddle the API.

LAsan’s picture

Version: x.y.z » 7.x-dev

Anyone want to assigned this task, it seems unanimous that´s a good idea.
Moving to current dev version

pwolanin’s picture

Status: Active » Postponed

This should certainly wait for the new DB layer, since I think db_rewrite_sql() may be eliminated...

lilou’s picture

Status: Postponed » Closed (fixed)