Problem with showing all nodes when using filter 'User: Current' in a relationship

Thanks for your replies. I took a look at the Views_Or module to get the 'OR is NULL' in the query, but that didn't work out.

It seems I need to have a lot more complicated query in order to get the desired results, a query with an UNION and a subselect.

I figured out that this query will result in the desired results:

SELECT node.nid AS nid,
   node.title AS node_title,
   node.language AS node_language,
   note_node.uid AS note_node_uid,
   note_node.notes AS note_node_notes
FROM node node 
LEFT JOIN note_node note_node ON node.nid = note_node.nid
LEFT JOIN users users_note_node ON note_node.uid = users_note_node.uid
WHERE node.type in ('task') AND note_node.uid = ***CURRENT_USER***
UNION
(
   SELECT node.nid AS nid,
      node.title AS node_title,
      node.language AS node_language,
      NULL AS note_node_uid,
      NULL AS note_node_notes
   FROM node node 
   LEFT JOIN note_node note_node ON node.nid = note_node.nid
   LEFT JOIN users users_note_node ON note_node.uid = users_note_node.uid
   WHERE node.type in ('task')
   AND node.nid not in
   (
      SELECT node.nid AS nid
      FROM node node 
      LEFT JOIN note_node note_node ON node.nid = note_node.nid
      LEFT JOIN users users_note_node ON note_node.uid = users_note_node.uid
      WHERE node.type in ('task') AND note_node.uid = ***CURRENT_USER***
   )
)

In the querypart after the UNION I put in NULL for the columns 'note_node_uid' and 'note_node_notes' so both queryparts results in the same number of columns (or else MySQL will return an error).

And this was the query generated by Views:

SELECT node.nid AS nid,
   node.title AS node_title,
   node.language AS node_language,
   note_node.uid AS note_node_uid,
   note_node.notes AS note_node_notes
 FROM node node 
 LEFT JOIN note_node note_node ON node.nid = note_node.nid
 LEFT JOIN users users_note_node ON note_node.uid = users_note_node.uid
 WHERE (node.type in ('task')) AND (users_note_node.uid = ***CURRENT_USER***)

Now I'm looking for a way to get the first query to be generated by Views, because for the website I'm developping I will need multiple simular views (for example a view of all nodes with the nodetype 'task' and the value of a CCK checkbox-field set to true).
Can this be done with Views?

Thanks for your reply. I've took a look at the hook hook_views_pre_execute().

Because I need multiple views I need to find a good way to let Views generate the subqueries as well, because the number of fields is different per view.
I'm thinking of copying the view-object, manipulate the copy and let that copy generate a new query, which I will add to the main view-object.

I'm now trying to figure out how I can get the following query to be generated:

SELECT node.nid AS nid,
      node.title AS node_title,
      node.language AS node_language,
      NULL AS note_node_uid,
      NULL AS note_node_notes
   FROM node node
   LEFT JOIN note_node note_node ON node.nid = note_node.nid
   LEFT JOIN users users_note_node ON note_node.uid = users_note_node.uid
   WHERE node.type in ('task')

My attempts so far:

<?php
/**
* Implementation of hook_views_pre_execute()
* @param view $p_oView
* @return void
*/
function note_views_pre_execute($p_oView)
{
   // Only manipulate the query under certain conditions,
   // I'm not sure under which conditions yet, this will follow later
   if (1)
   {
      // Clone the view-object
      $sClonedView = serialize($p_oView);
      $oUnionView = unserialize($sClonedView);
      
      // Remove filter uid_current
      unset($oUnionView->filter['uid_current']);
      
      // We want to rebuild the query, so say to views it hasn't been built yet.
      $oUnionView->built = false;
      unset($oUnionView->build_info);
      
      // try 1: manipulate the field names in the query-object
      foreach ($oUnionView->query->fields as $sFieldname => $aField)
      {
         if (strpos($aField['table'], 'note') !== false)
         {
            $oUnionView->query->fields[$sFieldname]['field'] = 'NULL';
         }
      }
      
      // try 2: manipulate the field names in the field array
      foreach($oUnionView->field as $oField)
      {
         if (strpos($oField->table, 'note') !== false)
         {
            $oField->field = 'NULL';
         }   
      }
      
      // Build query
      $oUnionView->build();
      
      $sUnionQuery = $oUnionView->build_info;
   }
}
?>

(I have prefixed the variable names so I know easily of which type the variable is)

The filter 'uid_current' was succesfully removed from the query, but I had no success yet to get 'NULL as note_node_notes' in the query.
What do I need to change on the view-object in order to get the query above? Do I need to implement other hooks as well?

I finally managed to let views generate the desired query. The UNION-part is pasted after the 'main' query views normally generate. To replace the field names with NULL in the UNION-part I now used str_ireplace(). The UNION-part now is generated in a function that implements hook_views_pre_build(). The output is saved in a Registry under the name of the view combined with the name of the current display. The output is retrieved in a function that implements hook_views_pre_execute() and then pasted in the query.

Now there is still one problem. When a user has restricted node access, the query gets altered after the generated query leaves my module. It adds the following directly after the WHERE statement:

(na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'nodeaccess_rid') OR (na.gid = 11 AND na.realm = 'nodeaccess_rid') OR (na.gid = 12 AND na.realm = 'nodeaccess_rid') OR (na.gid = 3 AND na.realm = 'nodeaccess_uid') OR (na.gid = 3 AND na.realm = 'nodeaccess_author'))) AND (

The closing ')' is added at near the end of the query (before the ORDER BY) which results in a MySQL syntax error, because the closing ')' should be added before the UNION-statement.

I'm sure the query gets altered after it leaves my module, because the above SQL-code isn't yet present when the view-object enters my module function that implements hook_views_pre_execute().

I tried to change the weight of my module, but that didn't work out.
I took a look at the nodeaccess-module, which was enabled, but I didn't saw any view-hooks implemented there nor did I see implementations of hook_db_rewrite_sql(), which leaves me to think that the views module itself inserted it at some place.
The class 'views_handler_filter_node_access' I found doesn't seem to be used in this case, because I temporary added a 'die()' there and the page generation didn't get terminated.

So where and how does the above SQL-code gets inserted?

(Btw, I have upgraded to Views 6.x-2.10 now)

Thanks for your reply.
I had just figured this out myself. It is the db_rewrite_sql() function that breaks my query. I've tried to make a fix that works for now, but this will not work when there are other modules then the node-module implementing hook_db_rewrite_sql() that will alter my query.

This is my fix (comes from the class I had created to hold the Union-part of the query):

<?php
/**
* createQuery()
* Build final query for view
* @param view $p_oView
* @access public
* @return string
*/
public function createQuery($p_oView)
{
	$sMainQuery = $p_oView->build_info['query'];
	$sWhere = $p_oView->query->condition_sql();
	
	// Remove everything after WHERE from the query and hold it
	$sAfterWhere = $this->_removeAfterWhere($sMainQuery, $sWhere);
	
	// Because db_rewrite_sql() can't handle with complex queries well
	// here is a little fix. db_rewrite_sql added a bracket after the UNION.
	// This fix adds a bracket before the UNION and removes one after it when
	// a user doesn't have access to all nodes, because that's when the
	// problem occurs.
	$sUnionCloseBracket = ')';
	if (!node_access_view_all_nodes() && _node_access_where_sql())
	{
	  $sMainQuery .= ')';
	  $sUnionCloseBracket = '';
	}
	
	// Build the final query
	$sQuery = $sMainQuery .
	"UNION
	(
	  " . $this->m_sUnionQuery . "
	  AND node.nid not in
	  (
	    " . $p_oView->build_info['count_query'] . "
	  )
	" . $sUnionCloseBracket . "
	"
	. $sAfterWhere
	;
	
	return $sQuery;
}

/**
* _removeAfterWhere()
* Removes from the query everything after WHERE with the WHERE itself
* given as a parameter.
* It will return what was removed from the query.
* @param string $p_sQuery
* @param string $p_sWhere
* @access private
* @return string $sAfterWhere
*/
private function _removeAfterWhere(&$p_sQuery, $p_sWhere)
{
	// Get everything after WHERE
	$iPos = strpos($p_sQuery, $p_sWhere) + strlen($p_sWhere);
	$sAfterWhere = substr($p_sQuery, $iPos);

	// Remove everything after WHERE from the query
	$p_sQuery = substr($p_sQuery, 0, $iPos);
	
	// Return everything what came after the WHERE
	return $sAfterWhere;
}
?>

I used (!node_access_view_all_nodes() && _node_access_where_sql()) to check if the query will be altered by node_db_rewrite_sql().