Photo by: 

cobalt123@filckr

Drupal7 introduces a lot of exciting and important innovations. As 1% of the biggest sites are now powered by Drupal, Drupal is at a stage it can take a lead at Internet technologies. Stuff like RDF needs a bootstrap from a major player in order to catch traction, and Drupal is taking a bold step here (and not for the first time). Other features, with less visibility to the non Drupal world, have quite a lot of significance as well.

The adoption of a generic data store allows support of the rather new noSQL movement. By disconnecting the relational part from the business logic, we allow a much broader set of options for storage implementations. This is a good thing. Although I still believe there are merits to the good old relational databases, the ability to use technologies like MongoDB is a huge step in terms of flexibility and capabilities.

Alas, This flexibility comes with a price. This abstraction hides the internal structure of Drupal content types (dubbed bundles in Drupal 7) from the developers. It actually drops the concept of a database as a whole for content types and fields.
The way to deal with this abstraction is to use tools like the brilliant Views module, which provides immensley rich ways to query the underlying objects. However, relational databases go far more than views can ever do. The denial of any relational query ability from Drupal developers greatly limits the complexity of applications that can be developed using Drupal 7. More often than not, I find myself writing a short (or long) query on the Drupal 6 database. The flexibility and familiarity of SQL, and the fact it is available as a simple db_query() call greatly simplify the development of such applications in Drupal 6. Indeed, most of these queries can be implemented by a view, but this is done outside of the flow of the code and I find it hard to debug or proof read the code this way. In addition, some queries are impossible or blatantly complex to achieve using views.

One very troubling example, to which Drupal has no good answer is a recipes application. Creating a node which has a multiple ingredient-value-unit triplets is close to impossible to implement in a clean fashion. The multigroup module is the only option, and Drupal6 support is in alpha state, and a multigroup Drupal7 is an unknown for the moment. There is a patch to views to allow support of queries such a content type,

Here is an example code I have in one of the applications I am working on in Drupal 6 at the moment. I have no idea what to do with that once Drupal 7 upgrade is needed, but I hope a solution will allow this applications to be kept Drupal based, and not forced to be rewritten in a more DB liberal framework.

This query sorts a list of nodes that belong to a taxonomy term. It then divides the nodes to deciles (each decile contains 1/10th of the nodes), and tries to determine in which decile the current node is in. The score CCK field is used for the sorting.

I would love that someone would prove me wrong and show me how to handle this in Drupal 7.

// get the amount of products in the category
$field_score = content_fields('field_score', 'product');
$db_info = content_database_info($field_score);
$products_from = '{node} n INNER JOIN {term_node} tn ON n.vid = tn.vid ' .
  'INNER JOIN {'. $db_info['table'] .'} ct ON n.vid = ct.vid WHERE tn.tid = %d ' .
  'AND n.status=1 AND ct.' . $db_info['columns']['value']['column'] .
  '> %d ORDER BY ct.' . $db_info['columns']['value']['column'];
$cat_size = db_result(db_query("SELECT COUNT(DISTINCT(n.nid)) " . 
  " FROM $products_from", $cat, -1000));

// find the index of the current node within the category
$query = "SELECT * FROM (SELECT round(0.5001+10*(row-1)/$cat_size) as decile," .
  "nid FROM (SELECT @row := @row + 1 as row, nid FROM " . 
  "(SELECT n.nid FROM $products_from) cat, (SELECT @row := 0) r) d) deciles";