I try to merge two views, by changing the filter of a views in mymodule_views_pre_exexute function.
The new sql query is taken into account and is shown on the preview table of the view. The problem is that no results are displayed, whereas if I run the query in phpmyadmin (the query done in the views preview table) , I get good results.

Here is the code of the module 

 function mymodule_views_pre_execute(&$view) {
	if($view->name=="my_view") {
		
		$view->build_info['query']="(SELECT DISTINCT(node.nid) AS nid,
   node_data_field_date_publication.field_visuel_a_la_une_imceimage_path AS node_data_field_date_publication_field_visuel_a_la_une_imceimage_path,
   node_data_field_date_publication.field_visuel_a_la_une_imceimage_width AS node_data_field_date_publication_field_visuel_a_la_une_imceimage_width,
   node_data_field_date_publication.field_visuel_a_la_une_imceimage_height AS node_data_field_date_publication_field_visuel_a_la_une_imceimage_height,
   node_data_field_date_publication.field_visuel_a_la_une_imceimage_alt AS node_data_field_date_publication_field_visuel_a_la_une_imceimage_alt,
   node.type AS node_type,
   node.vid AS node_vid,
   node.title AS node_title,
   node_data_field_date_debut.field_date_debut_value AS node_data_field_date_debut_field_date_debut_value,
   node_data_field_date_debut.field_date_fin_value AS node_data_field_date_debut_field_date_fin_value
 FROM drupal_node node 
 LEFT JOIN drupal_content_type_actualite node_data_field_date_publication ON node.vid = node_data_field_date_publication.vid
 INNER JOIN drupal_content_type_actualite node_data_field_affichage ON node.vid = node_data_field_affichage.vid
 LEFT JOIN drupal_content_type_actualite node_data_field_dates_a_la_une ON node.vid = node_data_field_dates_a_la_une.vid
 LEFT JOIN drupal_content_type_actualite node_data_field_date_debut ON node.vid = node_data_field_date_debut.vid
 WHERE ((node.type in ('actualite')) AND (node.status <> 0) AND (node_data_field_affichage.field_affichage_value = 'Afficher à la Une de Rennes 2')
    AND ((DATE_FORMAT(STR_TO_DATE(node_data_field_date_publication.field_date_publication_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '". date('Y-m-d')."')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_date_publication.field_dates_a_la_une_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '". date('Y-m-d')."') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_dates_a_la_une.field_dates_a_la_une_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '". date('Y-m-d')."'))
)
 UNION 
(SELECT DISTINCT(node.nid) AS nid,
node_data_field_date_publication.field_visuel_a_la_une_imceimage_path AS node_data_field_date_publication_field_visuel_a_la_une_imceimage_path,
   node_data_field_date_publication.field_visuel_a_la_une_imceimage_width AS node_data_field_date_publication_field_visuel_a_la_une_imceimage_width,
   node_data_field_date_publication.field_visuel_a_la_une_imceimage_height AS node_data_field_date_publication_field_visuel_a_la_une_imceimage_height,
   node_data_field_date_publication.field_visuel_a_la_une_imceimage_alt AS node_data_field_date_publication_field_visuel_a_la_une_imceimage_alt,
   node.type AS node_type,
   node.vid AS node_vid,
   node.title AS node_title,
   node_data_field_date_debut.field_date_debut_value AS node_data_field_date_debut_field_date_debut_value,
   node_data_field_date_debut.field_date_fin_value AS node_data_field_date_debut_field_date_fin_value
 FROM drupal_node node 
 LEFT JOIN drupal_content_type_actualite node_data_field_date_publication ON node.vid = node_data_field_date_publication.vid
 INNER JOIN drupal_content_type_actualite node_data_field_selection_aire_d_u ON node.vid = node_data_field_selection_aire_d_u.vid
 LEFT JOIN drupal_content_type_actualite node_data_field_date_debut ON node.vid = node_data_field_date_debut.vid
 WHERE ((node.type in ('actualite')) AND (node.status <> 0) AND (node_data_field_selection_aire_d_u.field_selection_aire_d_u_value = 'Afficher dans la sélection de l\'Aire d\'u') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_date_publication.field_date_publication_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '". date('Y-m-d')."'))
 LIMIT 3
)
ORDER BY node_data_field_date_debut_field_date_debut_value ASC, node_data_field_date_debut_field_date_fin_value ASC";
	 $view->build_info['count_query']=$view->build_info['query'];  
	}
}

Is there something wrong in that ?

Comments

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Active » Fixed

Your %d may cause an issue. %d is used as a substitution in Drupal, which may cause the query to fail. Try changing all %d to %%d. (If you happen to have any %f, %b or %s that I didn't see, change those as well).

lucuhb’s picture

lucuhb CreditAttribution: lucuhb commented
Status: Fixed » Closed (fixed)

thanks you very much, changing all my %d in %%d solves the problem : I have results now !

lucuhb’s picture

lucuhb CreditAttribution: lucuhb commented
Title: No result shown when using views_pre_execute function » No result shown for not admin when using views_pre_execute function
Status: Closed (fixed) » Active

The results are only shown for admin user, but nothing for other users (anonymous and authenticated). This does not seem to be due to access permission problem, because when I try with the first part or with the second part of the union query separately, each result is shown for all users.

Does anyone has any idea of the problem ?

lucuhb’s picture

lucuhb CreditAttribution: lucuhb commented

As I did not find the reason of the problem, I changed my module and now use the views_pre_render instead of the views_pre_execute function : I add results from a view to the results of another view like this :

function mymodule_views_pre_render(&$view) {
	
	$another_view = views_get_view('name_of_the_another_view');
	$another_view->execute();
 
 	foreach($another_view->result as $result){
 			$nid=$result->nid ;
 			$exist =false;
 			foreach( $view->result as $aResult) {
 				
 				if ($aResult->nid == $nid){
 						$exist = true;
 						break;
 				}
 			}
 			if (! $exist){
 					$view->result[]=$result;
 			}
 			
 	}
}
merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Active » Fixed

The query does get run through db_rewrite_sql if not UID 1, and it's entirely possible that db_rewrite_sql() fails on a UNION because it just has no idea how to handle that kind of a query. That would be consistent with the results you're getting, and if that's the case, there's not much that can be done about it. So your second solution may be the way to gol

lucuhb’s picture

lucuhb CreditAttribution: lucuhb commented
Status: Fixed » Closed (fixed)

Thanks for your explanation, I understand better now !