Drupal Query builder

Question

I quite often use Drupal's Views Module to build SQL that I paste into my code. It understands the Drupal database schema quite well.

Is there a module that would give me this functionality or can I factor this out of Views?

Answer 1

Yes, i assume views is the best to know what tables are used for current field, because many modules (and more in views) have hook functions, that provide some information about this field, table, and connection type with other tables.

Also you can read scheme of tables and fields via: http://drupal.org/project/schema

Answer 2

Would be cool of the Views module was extended to better support programmatic usage, but until then you might perhaps want to take a look at one of my colleagues attempt at creating something similar to such a thing: http://github.com/hugowetterberg/query_builder

Related to this might be the Services project attempt at providing Views data as a service, an effort that we right now are separating out into it's own module: http://drupal.org/node/709100 Might be worth follow since it's going to need some level of programmatic access to Views.

Another example of a module that's accessing Views programmatically is Development Seeds Litenode: http://developmentseed.org/blog/2009/feb/4/litenode

Answer 3

Although this isn't the ideal way to do things, you can get the results of a view as follows:

$view = views_get_view('search');
$view->set_display('main');
$view->set_items_per_page(0);
$view->execute();

$items = array();
foreach ($view->result as $row) {
  $items[] = $row;
}

This way, whenever you modify your views query, you don't have to re-copy the code. I do agree that Views needs to be split up into a query building api and a UI.

Answer 4

I'm curious- why would you use Views to build SQL, and then not use Views?

When it comes to more difficult things like many to many relationships, GROUP BY, COUNT, SUM, subquerying etc whatever the function calls for, it's best to write it yourself (especially if contrib modules have no views support and you need more than the node table).

For me, when Views can't get it done, I write a simple module that invokes hook_menu (to register the paths) with a callback that does the querying I need.