While working on the 7.x port for my Document module for Drupal day before yesterday, I had a pretty anxious moment. As you would know, the new DBTNG database abstraction layer in Drupal 7 fundamentally changes the way your module code interacts with the db. Now instead of writing raw Sql queries, you use a set of high level objects to specify the various components of your query using a series of methods which is then translated to Sql by the DBTNG layer and executed against the database. Sample this for example:

 

db_select('node', 'n')
  ->fields('n')
  ->condition('status', 1)
  ->condition('uid', 2)
  ->execute();

The above DBTNG query is roughly equivalent to the following in Drupal 6:

db_query('SELECT n.* FROM node n WHERE status = 1 AND uid = 2');

It should not be too difficult to co-relate the components and the way a DBTNG query is constructed by comparing it to traditional way of writing the raw Sql query.

Coming back to my original point, in the document module’s search page, the Drupal 6 version created a pretty complex query with dynamic conditions (see lines 113-146 here for details on the query generated). The conditions were arbitrarily ANDed/ORed together based on user input in the search form. And when I came to porting this part of the module for Drupal 7, for a moment I was practically numb; how do I represent an arbitrary series of AND and OR Sql conditions using DBTNG in Drupal 7.

If you notice the above DBTNG example carefully, you would see that I added 2 conditions without specifying how to combine them and DBTNG ANDed them by default. You can pass a third parameter to condition() method specifying the operator for the condition (=, <>, <, LIKE etc.) but no way how to tell DBTNG to OR the conditions and not AND them.

At one point, I thought of dumping the DBTNG abstraction layer for this part of the module’s Drupal 7 port, and use the good old db_query method. But that would have meant that I would have had to manually add logic for paginating/sorting results as the output of this query was being formatted and displayed as a table to the user (see this for details on what I mean).

So, I decided to hang-on and do a bit more research. As I kept googling to figure out something useful, I came across an issue page on drupal.org that had some code with methods called db_and and db_or in them. I saw a ray of hope and the next couple of google searches took me to this and this page. The solution was now there for the taking.

The DBTNG layer provides these 2 (rather three, the third one being db_xor) methods which can be used to create arbitrarily ANDed/ORed (even XORed) queries. Each of these methods returns a DatabaseCondition object to which you can add conditions which are combined together with the operation reflected by the name of these methods. For example:

 

$ored = db_or();
$ored
  ->condition('uid', 1)
  ->condition('uid', 2)

We added 2 conditions to the return object of db_or and the conditions would be ORed together. Because the return value of db_or (and other 2 methods) is a DatabaseCondition object, you can pass it to a DatabaseStatement’s condition method. Here’s a compete example to fetch all nodes created by user 1 or 2:

 

$ored = db_or();
$ored
  ->condition('uid', 1)
  ->condition('uid', 2);

db_select('node', 'n')
  ->fields('n')
  ->condition($ored)
  ->execute();

Can you see what we did here. We created a DatabaseCondition object that ORed 2 conditions and then passed that to the condition method of SelectQuery object. This is equivalent to:

 

db_query('SELECT n.* FROM node n WHERE uid = 1 OR uid = 2');

Let us increase the complexity a bit. Let us now fetch all nodes whose status is published and uid is 1 or 2 (a combination of ANDed/ORed conditions). Here’s the DBTNG code:

 

$ored = db_or();
$ored
  ->condition('uid', 1)
  ->condition('uid', 2);

$anded = db_and();
$anded
  ->condition('status', 1)
  ->condition($ored);

db_select('node', 'n')
  ->fields('n')
  ->condition($anded)
  ->execute();

This is equivalent to:

 

db_query('SELECT n.* FROM node n WHERE status = 1 AND (uid = 1 OR uid = 2)');

We first ORed the 2 conditions and then ANDed the result with a third condition. The power and flexibility of the DBTNG approach comes from the fact that you can pass either a discreet condition or a DatabaseCondition object to the condition method.

As one last example, let us fetch all nodes where title contains ‘test’ and user is 1 or 2 and status is published.

 

$ored = db_or();
$ored
  ->condition('uid', 1)
  ->condition('uid', 2);

$anded = db_and();
$anded
  ->condition('status', 1)
  ->condition($ored);

db_select('node', 'n')
  ->fields('n')
  ->condition($anded)
  ->condition('title', '%test%', 'LIKE')
  ->execute();

You would notice I have now added a raw condition directly on SelectQuery object because it ANDs the conditions by default. I could well have added the new condition to the $anded object and it would have produced the same result.

Now as a completely last example, here’s the code from Drupal 7 port of the Document module that creates arbitraty ANDed/ORed conditions based on user input:

 

{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }function document_perform_search($searchFields, $searchText, $searchYear = NULL, $searchDocType = NULL) {
$conditions = db_and();
$conditions->condition(‘n.status’, DOCUMENT_STATUS_PUBLISHED);

switch ($searchFields) {
case 0:
$conditions->condition(‘author’, ‘%’ . $searchText . ‘%’, ‘LIKE’);
break;
case 1:
$conditions->condition(‘keywords’, ‘%’ . $searchText . ‘%’, ‘LIKE’);
break;
case 2:
$or = db_or();
$or->condition(‘keywords’, ‘%’ . $searchText . ‘%’, ‘LIKE’)
->condition(‘author’, ‘%’ . $searchText . ‘%’, ‘LIKE’);
$conditions->condition($or);
break;
case 3:
break;
default:
die(‘Invalid Input’);
}

if (!empty($searchYear)) {
$conditions->condition(‘publish_year’, $searchYear);
}

if (!empty($searchDocType) > 0) {
$conditions->condition(‘d.type’, ‘%’ . $searchDocType . ‘%’, ‘LIKE’);
}

//…More code here

$query = db_select(‘node’, ‘n’);
$query->join(‘document’, ‘d’, ‘n.vid = d.vid’);
$query
->condition($conditions)
->extend(‘PagerDefault’) //Pager Extender
->limit(10) //10 results per page
->extend(‘TableSort’) //Sorting Extender
->orderByHeader($headers)//Field to sort on is picked from $header
->fields (‘n’)
->fields(‘d’);
$results = $query
->execute();

//…More code here{/syntaxhighlighter}

Once you get a hang of DBTNG and how to do such things with the library, you actually start to like it (I have started liking it definitely).

If you think you have a query you want to express using DBTNG but are not able to figure out the representation, post a comment below and I would try having a shot at it.