Drupal query problems and a possible solution

One of the ugliest part of Drupal is db_rewrite_sql. It somewhat parses the query to find certains stopwords and adds join and where parts. It works mostly but its brittle and so there are always bugs. That was my first big patch -- it's not that great... One solution would be to define our dialect of SQL and do a complete, proper parsing. If this is the route we want to take, then Doctrine or part of it can be a solution. There are other parsers, too, there is even one in PEAR which seems to be abandoned. However, maintaing a lexer in PHP just sounds a terrible prospect.

Instead, we could ask the developers to parse out SQL for us. If we go down this route, we can offer some nice shorthands. We still try to stay close to SQL -- and provide some shorthands where possible. There is code Crell's sandbox which is a PDO driver and a query builder too -- the builder is less than 300 lines of code. Here is one the most complex queries from tracker:

print $query->
  distinct()->
  fields('n.nid', 'n.title', 'n.type', 'n.changed', 'n.uid', 'u.name', 'GREATEST(n.changed, l.last_comment_timestamp) AS last_updated', 'l.comment_count')->
  join('node', 'n')->
  join('node_comment_statistics', 'l', 'n.nid = l.nid')->
  join('users', 'u', 'n.uid = u.uid')->
  leftJoin('comments', 'c', 'n.nid = c.nid AND (c.status = :c_status OR c.status IS NULL)')->
  condition('n.status', 1)->
  condition(db_or()->condition('c.uid', $uid)->condition('n.uid', $uid))->
  orderBy('last_updated', 'DESC');

This produces

SELECT DISTINCT n.nid, n.title, n.type, n.changed, n.uid, u.name, GREATEST(n.changed, l.last_comment_timestamp) AS last_updated, l.comment_count
FROM node AS n
INNER JOIN node_comment_statistics AS l ON n.nid = l.nid
INNER JOIN users AS u ON n.uid = u.uid
LEFT OUTER JOIN comments AS c ON n.nid = c.nid AND (c.status = :c_status OR c.status IS NULL)
WHERE (n.status = :db_n_status_0 AND (c.uid = :db_c_uid_0 OR n.uid = :db_n_uid_0))
ORDER BY last_updated DESC

The interesting / not-really-SQL parts currently are the condition method calls. The most simple form is a field-value pair. You also can do condition('created', '>', $yesterday). The db_or() construct is not my favourite but we had no better idea really. Our hopes is that the builder stayed close enough to SQL that developers can deduce the SQL query from the code without actually running it through the code.

Later on we intend to provide more shorthands: replace join('node_comment_statistics', 'l', 'n.nid = l.nid') with a simple table('node_comment_statistics') method call, but first schema API needs get relationship information for this.

Of course, you will still be able to pass in raw SQL to db_query this kind of building is only for queries that need to run through db_rewrite_sql -- which becomes a simple alter hook after this.

If the community prefers to do SQL parsing instead, then here is a list of SQL parsers in PHP I am aware of:

txtsql, SQL in flat files, GPL, abandoned.
SQL Parser, LGPL, abandoned.
PHP Lexer generator if we want to write our own, BSD license.
PHP Doctrine, LGPL, huge, much more than an SQL parser.
fSQL, another flat file SQL engine, seems abandoned.

Commenting on this Story is closed.

Check out the new module for

Submitted by jwage on Sun, 2008-02-10 06:56.

Check out the new module for Doctrine. It allows you to use Doctrine side by side with the any current drupal project.

http://drupal.org/project/doctrine

Yet another approach

Submitted by fgm@drupal.org on Sun, 2008-02-10 08:20.

There's also the fairly solid approach taken by the Zend Framework.
See the Zend_DB doc.

IANAL, but apparently, their license being BSD with attribution would even allow us to include the code in the main drupal distro.

Here is how Doctrine would

Submitted by jwage on Sun, 2008-02-10 23:51.

Here is how Doctrine would handle the above query as proposed by chx.

http://pastebin.com/m5125f764

That is actually a pretty simple usage of Doctrine query api and doesn't show really the complexity of things it can accomplish.

If you check out the doctrine module, you can immediately perform this query with Doctrine anywhere in Drupal.

All of the other core doctrine models are already there for you to work with.

re:

Submitted by Anonymous on Fri, 2009-09-18 01:11.

Choose, buy and shop for on sale tiffany jewelry including tiffany and coSilver Necklace, Pendants, Bangles, Bracelets, Earrings, Rings and Accessories.

Great.

Submitted by Anonymous on Sun, 2009-10-04 20:05.

This is exactly the soltuion I was in need of. Gotta love drupal!

Steven - Natural Hair Regrowth

Their license being BSD with

Submitted by Anonymous on Thu, 2009-10-08 02:28.

Their license being BSD with attribution would even allow us to include the code in the main drupal distro.
club penguin cheats

Yes.

Submitted by Anonymous on Wed, 2009-10-21 02:19.

I think you guys are on the right page. I have been having problems with many drupal searches, such as running into the same sites etc. These problems will soon be fixed

Thanks - Limassol Marathon Cyprus Website