drupal/views

//
<?php
// $Id: custom_views.module,v 1.272.2.1 2009/04/27 12:25:24 goba Exp $

function custom_views_views_pre_execute(&$view) {

if($view->name=="SiteActivity")
    {
     $view->build_info['query']=
	"
    SELECT node.nid AS nid,
	users.picture AS users_picture,
	users.uid AS users_uid,
	users.name AS users_name,
	node.type AS node_type,
	node.title AS node_title,
	node.created AS node_created
	FROM node node
	INNER JOIN users users ON node.uid = users.uid
	WHERE (node.status <> 0)

	UNION

	SELECT comments.cid AS cid,
	users_comments.picture AS users_comments_picture,
	users_comments.uid AS users_comments_uid,
	users_comments.name AS users_comments_name,
	node_comments.type AS node_type,
	comments.subject AS comments_subject,
	comments.timestamp AS comments_timestamp
	FROM comments comments
	LEFT JOIN node node_comments ON comments.nid = node_comments.nid
	LEFT JOIN users users_comments ON comments.uid = users_comments.uid
	WHERE (node_comments.status <> 0)
	ORDER BY node_created DESC

	";
    //count_query determines the pager.  Do this so the right item count is returned. If you don't do this, you'll only return the query amount
   	$view->build_info['count_query']=$view->build_info['query'];

 }
}

/**
* This module is Views 2.0 enabled.
* Implementation of hook_views_api().
*/
function custom_views_views_api() {
  return array('api' => 2.0);
}

================================================
SELECT total.*
FROM
(SELECT r.realname AS node_title, r.uid AS nid, r.created AS node_created
FROM
realname r
WHERE  (r.uid <> '0')  UNION ALL SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created
FROM
node node
ORDER BY node_created DESC) AS total WHERE total.node_title LIKE '%admin%'
LIMIT 10 OFFSET 0

==================================================
 * See https://drupal.org/node/409808
 * https://drupal.org/node/748844
 * http://sethsandler.com/code/drupal-6-creating-activity-stream-views-custom-sql-query-merging-multiple-views-part-1/