Hello

I'm trying to add GROUP BY suggestion into views sql query, but i'have no idea why it doesn't work

Here is my code

/**
 * My custom filter handler
 */
class united_product_handler_filter_mycustomfilter2 extends views_handler_filter {

  function query() {

    $this->ensure_my_table();
    
    $this->field_alias = $this->table . '_' . $this->field;
    
    $join = new views_join();
    $join->construct('field_data_field_united_parameter', $this->table_alias, 'nid', 'entity_id');
    
    $this->belts_table = $this->query->ensure_table('field_data_field_united_parameter', $this->relationship, $join);
    $this->query->add_field('', "$this->belts_table.field_united_parameter_value", $this->field_alias, array('aggregate' => TRUE));

    $this->query->add_groupby($this->field_alias);
    
    $this->query->has_aggregate = TRUE; 
  }

}

We have JOIN but do not a GROUP BY

Comments

dawehner’s picture

dawehner
Primary language German
commented
Category: task » support

If you have sql you can either have a field in a groupby clause, or as part of an aggregation function like count.

Here you seem to have the second one missing.

Luciuz’s picture

Luciuz commented

So what should i do?

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

Well, you're constructing field_alias but not actually adding it to the query, so what you're joining on looks like it doesn't actually exist. You need to make sure your field is added and is NOT marked as an aggregate so it doesn't get grouped on.

Luciuz’s picture

Luciuz commented

lets see

Luciuz’s picture

Luciuz commented

with inner join code

$this->field_alias = 'field_data_field_united_parameter';
$join = new views_join();
$join->construct('field_data_field_united_parameter', $this->table_alias, 'nid', 'entity_id', array(), 'INNER');
$this->query->ensure_table('field_data_field_united_parameter', $this->relationship, $join);
$this->query->add_field(NULL, "field_data_field_united_parameter.field_united_parameter_value", $this->field_alias, array('aggregate' => TRUE));
$this->query->add_groupby($this->field_alias);
$this->query->has_aggregate = TRUE;

produces almost right query (without group by)

SELECT node.created AS node_created, node.nid AS nid, field_data_field_united_parameter.field_united_parameter_value AS field_data_field_united_parameter
FROM
{node} node
INNER JOIN {field_data_field_united_parameter} field_data_field_united_parameter ON node.nid = field_data_field_united_parameter.entity_id
WHERE (( (node.status = '1') ))
ORDER BY node_created DESC

merlinofchaos’s picture

merlinofchaos commented 
$this->field_alias = 'field_data_field_united_parameter';

The above is a giant clue that you're doing it wrong; you should never be setting field aliases manually.

Plus, as I said, that field does not actually appear in the query you pasted, so naturally it can't be grouped on.

merlinofchaos’s picture

merlinofchaos commented

Sorry, it does appear -- but you set 'aggregate' => TRUE which means it won't be grouped on. The whole purpose of 'aggregate' => TRUE is to *prevent* the field from being grouped on.

Luciuz’s picture

Luciuz commented

nothing is changed when i switch it to FALSE

Luciuz’s picture

Luciuz commented

thx merlinofchaos

/**
 * My custom filter handler
 */
class united_product_handler_filter_mycustomfilter2 extends views_handler_filter {

  function query() {
    $this->ensure_my_table();
	
    $field_alias = 'united_parameter';
    
    $join = new views_join();
    $join->construct('field_data_field_united_parameter', $this->table_alias, 'nid', 'entity_id', array(), 'INNER');
    $this->query->ensure_table('field_data_field_united_parameter', $this->relationship, $join);
	
    $this->query->add_field('field_data_field_united_parameter', 'field_united_parameter_value',
	$field_alias);
    $this->query->add_field('field_data_field_united_parameter', 'field_united_parameter_value',
	'cnt', array('count' => TRUE));
	
    $this->query->add_groupby($field_alias);
}

}

generate sql with GROUP BY united_parameter, node_created, nid

Is there a way to group only by united_parameter?
I couldn't apply this

Luciuz’s picture

Luciuz commented
Status: Fixed » Needs review

sup

Luciuz’s picture

Luciuz commented

rly $this->query->add_groupby($field_alias); doesnt work
ORDER BY a lot of fields works if i use 'count' => TRUE parameter

How do query with order by only one field?

tim.plunkett’s picture

tim.plunkett
he/him
Primary language English
Location Philadelphia
commented
Status: Needs review » Active

needs review is for patches.

Luciuz’s picture

Luciuz commented

OKAY.

Luciuz’s picture

Luciuz commented

Code

function query() {
       $this->ensure_my_table();
	
    $field_alias = 'united_parameter';
    
    $join = new views_join();
    $join->construct('field_data_field_united_parameter', $this->table_alias, 'nid', 'entity_id', array(), 'INNER');
    $this->query->ensure_table('field_data_field_united_parameter', $this->relationship, $join);
	
    $this->query->add_field('field_data_field_united_parameter', "field_united_parameter_value",
	$field_alias, array('function' => 'groupby'));

    $this->query->add_groupby('field_data_field_united_parameter.field_united_parameter_value');
 
    $this->query->options['distinct'] = TRUE;
}

generates

SELECT DISTINCT node.created AS node_created, node.nid AS nid
FROM 
{node} node
INNER JOIN {field_data_field_united_parameter} field_data_field_united_parameter ON node.nid = field_data_field_united_parameter.entity_id
WHERE (( (node.status = '1') ))
GROUP BY field_data_field_united_parameter.field_united_parameter_value, nid
ORDER BY node_created DESC

How to exclude nid from GROUP BY now?

ionmedia’s picture

ionmedia commented

+1, how to do group by only one field ?

a.milkovsky’s picture

a.milkovsky
he/him
Location UTC +2
commented

+1 from me. how to do group by only one field ?
When I add another fields with SUM() to view

$this->query->add_field($alias, 'nid', 'node_nid', array('function' => 'groupby'));
$this->query->add_groupby($alias . '.nid');

$this->query->add_field(NULL, 'SUM(' . $aul_user_table . '.grant_view)', 'grant_view', array());
$this->query->add_field(NULL, 'SUM(' . $aul_user_table . '.grant_update)', 'grant_update', array());
$this->query->add_field(NULL, 'SUM(' . $aul_user_table . '.grant_delete)', 'grant_delete', array());

I can see
GROUP BY node.nid, nid, grant_view, grant_update, grant_delete

a.milkovsky’s picture

a.milkovsky
he/him
Location UTC +2
commented

I found correct way to add SUM() and avoid unnecessary GROUP BY:

    $this->query->add_field($aul_user_table, 'grant_view', 'grant_view', array('function' => 'sum'));
    $this->query->add_field($aul_user_table, 'grant_update', 'grant_update', array('function' => 'sum'));
    $this->query->add_field($aul_user_table, 'grant_delete', 'grant_delete', array('function' => 'sum'));
    

    // GROUP BY node nid.
    $this->query->add_field($node_table, 'nid', 'node_nid', array('function' => 'groupby'));
    $this->query->add_groupby($node_table . '.nid');
    

    // Fix to avoide unnecessary GROUP BY conditions.
    $this->query->distinct = TRUE;
siddhu151plex’s picture

siddhu151plex commented
Issue summary: View changes

I'm still having the unnecessary groupby conditions even if I add distinct to true.
My hook is like,

function custom_views_query_alter(&$view, &$query) {

  if ($view->name == 'tests') {		
		
	$query->add_field('field_data_field_start_end_date','field_start_end_date_value', 'latest', array('function' => 'max'));
	$query->distinct=TRUE;
  }
}

I'm still having group by conditions like,
GROUP BY nid, field_data_field_start_end_date_node_entity_type
Thats an unnecessary 'nid'
How to avoid this?

siddhu151plex’s picture

siddhu151plex commented

Finally I found a way to resolve my problem
I have used Extra views Handlers module to remove extra group by conditions.
It worked for me.
Thanks.

nareshbw’s picture

nareshbw commented

Thanks siddhu151plex this module is very good. working for me.

nareshbw’s picture

nareshbw commented

http://echo.co/blog/2010/06/group-views
https://www.drupal.org/node/1565480#comment-10418017

mustanggb’s picture

mustanggb commented
Status: Active » Closed (outdated)

Closing this as outdated to tidy up a bit around here. If you're still having problems with the latest release please create a new issue.