Recently I had to do a simple thing. Yet, you know, with Drupal sometimes “simple” takes on a slightly different meaning.
The problem to solve was to retrieve all nodes that were children in a Book and whose root parent was of a specific node type (let’s call it project).
The book table is described like this
A quick SQL thinking gave birth to the following query
The rationale behind the JOIN is that bid = nid when a node is the root of a book.
However, this query cannot be implemented as it is by using db_select in Drupal 7 (you could, if you really wanted, run the query by using db_query). The reason is that db_select does not allow you to have a WHERE condition that compares two fields. It must be a field compared to an actual value. That, despite SQL does not really see the query above as invalid.
Therefore, the only way to implement that query with db_select in Drupal 7 is to actually rewrite it first in a different form
This second query achieves the same goal (with pretty much the same performance) of the first query, just by replacing the WHERE condition that compares two fields with a self-join on the book table using that very same condition.
At this point we are good to go and build our query using db_select
