By phai on

Hi all,

my need is to group by the rendering results of a view.

I find views_pre_execute and write the code:

function my_utility_views_pre_execute(&$view) {
   if($view->name=="lista_rassegna_stampa") {
	$view->query->add_groupby("nid");
   }
}

But it doesn't seem to work (my "group by" is ignored in the final query).

So I read the modify with "group by" doesn't work, and that someone used a string replace solution.
So I try:

function my_utility_views_pre_execute(&$view) {
  if($view->name=="lista_rassegna_stampa") {
    $search = array('ORDER BY');
    $replace = array('GROUP BY nid ORDER BY');
    $view->build_info['query'] = str_replace($search,
                                             $replace,
                                             $view->build_info['query']);
    $view->build_info['count_query'] = str_replace($search,
                                                   $replace,
                                                   $view->build_info['count_query']);
   }
}

But in this way PHP give me an error:

Fatal error: Call to a member function addMetaData() on a non-object in ./sites/all/modules/views/plugins/views_plugin_query_default.inc on line 1306

Can you help me to add this grouping to my view? Any idea'll be really apreciated.

Really thanks

⋅ Categories: Drupal 7.x

Comments

phai’s picture

phai commented

Really noone needed to group by the results by a view?

phai’s picture

phai commented

Wow... finally I found the solution.
This problem seems be related to the new DB Layer introduced in Drupal7, as you can read here:

http://drupal.org/node/1127338

BTW you only need to the following line (and another for 'query_count'):
$view->build_info['query']->groupBy('field_data_field_category_field_category_value');

moniuch’s picture

moniuch commented

This alleviated the error messages but did not produce expected results (removing duplicates).
I'm not a db expert, but AFAIK know the GROUP BY statement work only for fields that are among other aggregated fields. SO how do you do that: you turn Aggregation globally on on the Views UI side, or you alter the query in the code as well?

mroscar’s picture

mroscar commented

I wrote this for my custom module... tried to keep it as general as possible...
Will modify for my new module and simplfy then

/**
  * Implementation of hook_views_pre_execute()
 **/
function views_fusioncharts_views_pre_execute(&$view) {
// We need options to decide execute and build sql
 $options = $view->style_plugin->options;
		// Then We decide if we want to use grouping at plugins setting
  		if (isset($options['basic']['fields']['usegroup']) && intval($options['basic']['fields']['usegroup']) > 0) {
		switch($view->name) {
		// Checking views name
		case 'runprofit':
			switch($view->current_display) {
			// Checking Display name
			case $view->current_display: 
			
			// Define vars for search and replace SQL 
			$xfield = &$options['basic']['fields']['xaxisfield']; // We need that for group by
			$yfield = &$options['basic']['fields']['yaxisfield']; // as well base for totals
			$xgroup = $view->field[$xfield]->field_alias; // GROUP by field also base for table SUM MAX MIN AVG
			$xfieldtable = str_replace('_'. $xfield, '.'. $xfield, $xgroup); // XFIELD GROUP TOTALS			
			$yfieldtable = str_replace('_'. $xfield, '.'. $yfield, $xgroup); // YFIELD GROUP TOTALS					
			
			// Search AND REPLACE for GROUP AND SUM 			
			$search = array($xfieldtable, $yfieldtable, 'ORDER BY');
			$replace = array('DATE('. $xfieldtable .')', 'SUM('. $yfieldtable .')', 'GROUP BY '. $xgroup .' ORDER BY');
			
			// Merge Arguments note dont really know whats going here somebody could be helpful?
			$view->build_info['query_args'] = array_merge($view->build_info['query_args'], $view->build_info['query_args']);
			// Rewrite the query 
			$view->build_info['query'] = str_replace($search, $replace, $view->build_info['query']);			

			break;
			} // end switch display
		break;
 
	   } // end switch viewname
	   
	   } // usegroup
 
} // end pre_execute

hoping helps someone

mroscar’s picture

mroscar commented

I dont think its good enough.. ita tpl code for generating table and gets data from view for grouped and agrrgeted tables.

<div id="smartdiv-wrapp-<?php echo $table_id; ?>" class="smartdiv-wrapper" >
 <table id="table-<?php echo $table_id; ?>" class="<?php print $table_class; ?>">
	<thead>
    <tr><th style="text-align:<?php echo $firstalign; ?>;"><?php echo $firstheader; ?></th>
	<?php $i = 0; foreach ($columnheaders as $header => $label): ?>
	<th style="text-align:<?php print $columnclass[$i]['align']; ?>;"><?php print $label; ?><?php $i++; ?></th>
    <?php endforeach; ?>
    </tr>
	</thead>
   <tbody> 
<tr class="odd">   
<?php // start rows
	$i = 0; foreach($dataset as $key => $value ) { $i++; print '<td align="'.$dataclass[$key]['align'].'" >'.$value.'</td>';
	if($i % $totrow==0) {
	$rowalt = ($i % 2 == 0) ? 'odd' : 'even';	
	print '</tr><tr class="'.$rowalt.'" >'; } // endif 
	} // end foreach
 // end rows
?>
</tr>
</tbody>
</table>  
</div>
mroscar’s picture

mroscar commented

Coding example may help people.. that how I learn basicly

/**
  * Implementation of hook_views_pre_execute()
 **/
<?php  
function views_fusioncharts_views_pre_execute(&$view) {

// We need options to decide execute and build sql
 $options = $view->style_plugin->options;
 $plugintype = $options['plugin']; // plugintype
 
 switch($plugintype) {
// Switch Plugin
	case 'single': 
		// Then We decide if we want to use grouping at plugins setting
  		if (isset($options['basic']['fields']['usegroup']) && intval($options['basic']['fields']['usegroup']) > 0) {
		$thisview = $options['basic']['thisview']; // Check this view name saved by plugins
		switch($view->name) {
		// Checking views name
		case $thisview:
			$thisdisplay = $options['basic']['thisviewdis']; // Check this display name saved by plugins
			switch($view->current_display) {
			// Checking Display name
			case $thisdisplay: 
	
			// Define vars for search and replace SQL 
			$xfield = &$options['basic']['fields']['xaxisfield']; // We need that for group by
			$xformat = &$options['basic']['fields']['xaxisformat']; // Group by format
			$yfield = &$options['basic']['fields']['yaxisfield']; // as well base for totals
			$yformat = &$options['basic']['fields']['yaxisformat']; // Group by format
			
			$xgroup = $view->field[$xfield]->field_alias; // GROUP by field also base for table SUM MAX MIN AVG
			$ygroup = $view->field[$yfield]->field_alias; // GROUP by field also base for table SUM MAX MIN AVG

			if ($xgroup == 'nid') {  // just in case if xfield is nid
			$xgroup = 'node.nid';
			} // end nidcheck			
			
			$xfieldtable = str_replace('_'. $xfield, '.'. $xfield, $xgroup); // XFIELD GROUP TOTALS			
			$yfieldtable = str_replace('_'. $yfield, '.'. $yfield, $ygroup); // YFIELD GROUP TOTALS		
			
			
			// Search AND REPLACE for GROUP AND SUM 			
			$search = array($xfieldtable, $yfieldtable, 'ORDER BY');
			$replace = array($xformat .'('. $xfieldtable .')', $yformat .'('. $yfieldtable .')', 'GROUP BY '. $xgroup .' ORDER BY');
			
			// Merge Arguments note dont really know whats going here somebody could be helpful?
			$view->build_info['query_args'] = array_merge($view->build_info['query_args'], $view->build_info['query_args']);
			// Rewrite the query 
			$view->build_info['query'] = str_replace($search, $replace, $view->build_info['query']);	
			$view->build_info['count_query'] = $view->build_info['query'];
			break;
			} // end switch display
		break;
 
	   } // end switch viewname
	   
	   } // usegroup
	
	break;

	case 'multi': 

		// Then We decide if we want to use grouping at plugins setting
  		if (isset($options['basicml']['fields']['usegroup']) && intval($options['basicml']['fields']['usegroup']) > 0) {
		$thisview = $options['basicml']['thisview']; // Check this view name saved by plugins
	
		switch($view->name) {
		// Checking views name
		case $thisview:
			$thisdisplay = $options['basicml']['thisviewdis']; // Check this display name saved by plugins
			switch($view->current_display) {
			// Checking Display name
			case $thisdisplay: 

			// Define vars for search and replace SQL 
			$xfield = &$options['basicml']['fields']['xaxisfield']; // We need that for group by
			$xformat = &$options['basicml']['fields']['xaxisformat']; // Group by format
			$xgroup = $view->field[$xfield]->field_alias; // GROUP by field also base for table SUM MAX MIN AVG

			if ($xgroup == 'nid') {  // just in case if xfield is nid
			$xgroup = 'node.nid';
			} // end nidcheck	
			
			$xfieldtable = str_replace('_'. $xfield, '.'. $xfield, $xgroup); // XFIELD GROUP TOTALS			
			
			// Search AND REPLACE for XAxis 			
			$search = array($xfieldtable, 'ORDER BY');
			$replace = array($xformat .'('. $xfieldtable .')', 'GROUP BY '. $xgroup .' ORDER BY');
			// Rewrite the query 			
			$newsql = str_replace($search, $replace, $view->build_info['query']);
	
			// Start series 
				for ($i = 0; $i < 8; $i++) {
					$thisOpt = $options['series' . $i];
					if (!isset($thisOpt) || empty($thisOpt['yaxisfield']) || empty($thisOpt['label'])) {
					continue;
					}
					$yfield = $thisOpt['yaxisfield']; // We need that for group by
					$yformat = $thisOpt['yaxisformat'];
					$ygroup = $view->field[$yfield]->field_alias; // GROUP
					$yfieldtable = str_replace('_'. $yfield, '.'. $yfield, $ygroup); // YFIELD GROUP TOTALS

					// Search AND REPLACE for GROUP AND SUM 			
					$ysearch = array('extrasearch', $yfieldtable);
					$yreplace = array('', $yformat .'('. $yfieldtable .')');					
					$newsql = str_replace($ysearch, $yreplace, $newsql);					

				}

				// Merge Arguments note dont really know whats going here somebody could be helpful?
				$view->build_info['query_args'] = array_merge($view->build_info['query_args'], $view->build_info['query_args']);			
				$view->build_info['query'] = $newsql;			
				$view->build_info['count_query'] = $newsql;			


			break;
			} // end switch display
		break;
 
	   } // end switch viewname
	   
	   } // usegroup
			
	break;
			
	case 'table': 
	
		$thisview = $options['tbsetting']['thisview']; // Check this view name saved by plugins
	
		switch($view->name) {
		// Checking views name
		case $thisview:
			$thisdisplay = $options['tbsetting']['thisviewdis']; // Check this display name saved by plugins
			switch($view->current_display) {
			// Checking Display name
			case $thisdisplay: 		

			// Define vars for search and replace SQL 
			$xfield = &$options['tbsetting']['firstcolumn']; // We need that for group by
			$xformat = &$options['tbsetting']['groupformat']; // Group by format
			$xalias = $view->field[$xfield]->field_alias; // GROUP by field  

			if ($xalias == 'nid') {  // just in case if xfield is nid
			$xalias = 'node.nid';			
			} // end nidcheck	
			
			$xfieldname = str_replace('_'. $xfield, '.'. $xfield, $xalias); // Field Name
			$xalias = $xalias; // Field Alias	

			
			// Search AND REPLACE for XAxis 
			$search = array($xfieldname, 'ORDER BY');
			$replace = array($xformat .'('. $xfieldname .')', 'GROUP BY '. $xalias .' ORDER BY');

			
			// Rewrite the query 			
			$newsql = str_replace($search, $replace, $view->build_info['query']);
			
			$ci = (empty($options['tbsetting']['rown']) ?  1 : 2);
			// Start series 
				for ($i = 0; $i < 8; $i++) {
					$thisOpt = $options['series' . $i];
					if (!isset($thisOpt) || empty($thisOpt['columnfield']) || empty($thisOpt['label'])) {
					continue;
					}
					$yfield = $thisOpt['columnfield']; // We need that for group by
					$yformat = $thisOpt['columnfunc'];
					$yalias = $view->field[$yfield]->field_alias; 
					
					$yfieldname = str_replace('_'. $yfield, '.'. $yfield, $yalias); // Field Name
					$yalias = $yalias; // Field Alias
			

					// Search AND REPLACE for GROUP AND SUM 			
					$ysearch = array($yfieldname);
					$yreplace = array($yformat .'('. $yfieldname .')');					
					$newsql = str_replace($ysearch, $yreplace, $newsql);


					// Get Average only one field currently 
					if ($yformat == 'AVG') {  // just in case if there is average			
					$avsearch = array('FROM');
					$avreplace = array(', AVG('. $yfieldname .') AS ' .$yalias. ' FROM');
					$avgquery = str_replace($avsearch, $avreplace, $view->build_info['count_query']);
					$avresult = db_query($avgquery, $view->build_info['query_args']);
						while ($avrow = db_fetch_object($avresult)) {					
						$avgvalue = $avrow->$yalias;
						$view->addavg[$ci] = $avgvalue;
						} // end getavg							
					} // end getavg		
					
				$ci++;
				} // end series
				



				// Merge Arguments note dont really know whats going here somebody could be helpful?
				$view->build_info['query_args'] = array_merge($view->build_info['query_args'], $view->build_info['query_args']);
				// store original query

				$view->build_info['query'] = $newsql;			
				$view->build_info['count_query'] = $newsql;		
				



			break;
			} // end switch display
		break;
 
	   } // end switch viewname			
	break;
	} // end switch plugin 
} // end pre_execute ?>