Posted by gopagoni on March 19, 2009 at 5:50am
HI,
I am new to drupal.I have an issue with views.
How can i add group by clause to views.
when i used "function add_groupby" ,it is adding group by by grouping all fields in the query.
SELECT state_downtimes.down_id AS down_id, state_downtimes.reason AS state_downtimes_reason, state_downtimes.startdate_planned AS state_downtimes_startdate_planned, group_concat(title separator ' ,') AS title
FROM state_downtimes state_downtimes
LEFT JOIN service_downtimes service_downtimes ON state_downtimes.down_id = service_downtimes.downtime_id
LEFT JOIN node node ON service_downtimes.service_id = node.nid
GROUP BY down_id, state_downtimes_reason, state_downtimes_startdate_planned, title;
I need the query should be group by only with down_Id
Comments
Have you looking into
Have you looking into hook_query_alter? There's another post on here where I describe how to do this.
Dave
problem with hook_query_alter
Hi
In my testmodule iam just creating a test view .
function testmodule_views_query_alter(&$view, &$query) {
// add group by
if($view->name=='test'){
$query->add_groupby('down_id');
}
}
When i used this function it is doing group by with all fields,but my requirement is to use group by only with one field.
And also iam facing other problem with the following function.
function testmodule_views_data_alter(&$data) {
// queue relationship
$data['node']['title'] = array(
'group' => t('Node'),
'title' => t('Title'),
'help' => t('Node title of downtimes.'),
'field' => array(
'handler' => 'views_handler_field_service',
),
);
}
class views_handler_field_service extends views_handler_field {
function query() {
$table = $this->query->ensure_table('node');
$sql = "group_concat(title separator ' ,')";
$this->query->add_field('', "$sql", 'title');
}
function render($values){
if(!empty($values)){
return $values->title;
}
}
}
When i used this function it is appending "group_concat" to all other views .
Is this correct way of doing?
If not please help me how can i use hook_data_alter,hook_query_alter.
with hook_query_alter
Hi
In my testmodule iam just creating a test view .
function testmodule_views_query_alter(&$view, &$query) {
// add group by
if($view->name=='test'){
$query->add_groupby('down_id');
}
}
When i used this function it is doing group by with all fields,but my requirement is to use group by only with one field.
And also iam facing other problem with the following function.
function testmodule_views_data_alter(&$data) {
// queue relationship
$data['node']['title'] = array(
'group' => t('Node'),
'title' => t('Title'),
'help' => t('Node title of downtimes.'),
'field' => array(
'handler' => 'views_handler_field_service',
),
);
}
class views_handler_field_service extends views_handler_field {
function query() {
$table = $this->query->ensure_table('node');
$sql = "group_concat(title separator ' ,')";
$this->query->add_field('', "$sql", 'title');
}
function render($values){
if(!empty($values)){
return $values->title;
}
}
}
When i used this function it is appending "group_concat" to all other views .
Is this correct way of doing?
If not please help me how can i use hook_data_alter,hook_query_alter.
I did it like this
http://echodittolabs.org/blog/2010/06/group-views
Worked for me!
group by in Views 3
what about views 3 on drupal 7?
we can use directly views
we can use directly views extra handlers module .