Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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
Comment #1
dawehnerIf 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.
Comment #2
Luciuz CreditAttribution: Luciuz commentedSo what should i do?
Comment #3
merlinofchaos CreditAttribution: merlinofchaos commentedWell, 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.
Comment #4
Luciuz CreditAttribution: Luciuz commentedlets see
Comment #5
Luciuz CreditAttribution: Luciuz commentedwith inner join code
produces almost right query (without group by)
Comment #6
merlinofchaos CreditAttribution: merlinofchaos commentedThe 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.
Comment #7
merlinofchaos CreditAttribution: merlinofchaos commentedSorry, 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.
Comment #8
Luciuz CreditAttribution: Luciuz commentednothing is changed when i switch it to FALSE
Comment #9
Luciuz CreditAttribution: Luciuz commentedthx merlinofchaos
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
Comment #10
Luciuz CreditAttribution: Luciuz commentedsup
Comment #11
Luciuz CreditAttribution: Luciuz commentedrly
$this->query->add_groupby($field_alias);
doesnt workORDER BY a lot of fields works if i use
'count' => TRUE
parameterHow do query with order by only one field?
Comment #12
tim.plunkettneeds review is for patches.
Comment #13
Luciuz CreditAttribution: Luciuz commentedOKAY.
Comment #14
Luciuz CreditAttribution: Luciuz commentedCode
generates
How to exclude nid from GROUP BY now?
Comment #15
ionmedia CreditAttribution: ionmedia commented+1, how to do group by only one field ?
Comment #16
a.milkovsky+1 from me. how to do group by only one field ?
When I add another fields with SUM() to view
I can see
GROUP BY node.nid, nid, grant_view, grant_update, grant_delete
Comment #17
a.milkovskyI found correct way to add SUM() and avoid unnecessary GROUP BY:
Comment #18
siddhu151plex CreditAttribution: siddhu151plex commentedI'm still having the unnecessary groupby conditions even if I add distinct to true.
My hook is like,
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?
Comment #19
siddhu151plex CreditAttribution: siddhu151plex commentedFinally 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.
Comment #20
nareshbw CreditAttribution: nareshbw as a volunteer commentedThanks siddhu151plex this module is very good. working for me.
Comment #21
nareshbw CreditAttribution: nareshbw commentedhttp://echo.co/blog/2010/06/group-views
https://www.drupal.org/node/1565480#comment-10418017
Comment #22
MustangGB CreditAttribution: MustangGB commentedClosing 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.