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

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

So what should i do?

merlinofchaos’s picture

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

lets see

Luciuz’s picture

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

$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

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

nothing is changed when i switch it to FALSE

Luciuz’s picture

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

Status: Fixed » Needs review

sup

Luciuz’s picture

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

Status: Needs review » Active

needs review is for patches.

Luciuz’s picture

OKAY.

Luciuz’s picture

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

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

a.milkovsky’s picture

+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

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

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

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

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

nareshbw’s picture

MustangGB’s picture

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.