Utilizing views as a theme layer to a custom query

We had a client requirement that a single view be the combination of:

1. a random list of attorneys with offices in a given state
2. a random list of attorneys that are licensed in a given state.

There should be no duplications and all of list 1 must precede list 2. We could not accomplish this with views alone. After talking to Earl, we were able to access the created SQL statement within views. Here we built each of the two queries. Then, we defined a 'pre' exit and replaced the query in our view with a new one.

The new query is in the form of:

SELECT node.nid FROM ( ( select #1 ) UNION (select #2 ) ORDER BY .... ) AS node.

This is especially important in that we absolutely have to name our selected fields the same as what views would typically name them. If you don't preserve the same naming structure it will simply not work. Now this is only true for the selected fields. Fields on which you join, filter, etc can utilize any naming convention you desire. Of course, both of the select statements were required to provide the exact same column output.

So, as an example the statement looked something like: (with LEFT JOIN detail left out for easier reading)

SELECT node.nid
FROM (
  ( // First select statement
    SELECT node.nid, RAND() as _order
    FROM node node
    WHERE node.attorney_state='%s'
  )
  UNION // concatenate to next
  SELECT
  ( // Second select statement
    SELECT node.nid, (1+RAND()) as _order
    FROM node node
    WHERE node.licenses_state = '%s'
  )
  // This does the sort on all the selects together.
  ORDER BY _order
)
// Now, for Drupal, this outer select MUST be aliased as 'node'.
AS node;

Some of the secret sauce was:

• use of an outer select with an internal union
• The outer select needed to be aliased to node for it to work.
• we only needed the nid as all the data was pulled using node-> syntax.
• There seemed to be bug in cck based on this, obviously, edge case and it took 2 lines of code to fix.
  • cck expects 'vid' to absolutely have a value.
  • wrapping an if ($vid)... around that expectation removed the warning error that we were getting.
  • bottom line... we hacked it, but hopefully it will make sense to the cck authors and it can be submitted back to the module.
• the views hook was hook_views_pre_execute(&$view) and we altered the query directly via $view->build_info['query']. If you'd like to preserve the dynamic nature of certain aspects of the views interface, you must respect your $view->build_info['query_args']. This may require some alteration as well.
• Note that we did a RAND() and 1+RAND(). This is because RAND() returns a random decimal between 0 and 1, thus 1+RAND() would be a random decimal between 1 and 2, allowing us to order by this random number and be sure that our second query's results ALWAYS come after the first query.