Posted by ppc.coder on February 12, 2011 at 7:10pm
Hi,
I am trying to get views_crosstab to work and have made some progress but am stuck on how to properly add new fields to the view. I am trying to fix this (views_crosstab_table.inc line 187):
// Add a "Total" column.
// TODO: Add an option for this.
1. $alias = $crosstab_columns['alias'] . '_total';
2. $this->view->query->add_field(NULL, 'SUM(' . $crosstab_data['table'] . '.' . $crosstab_data['field'] . ')', $alias);
3. $this->view->query->fields[$alias]['aggregate'] = TRUE;
4. $this->view->field[$alias] = drupal_clone($this->view->field[$crosstab_data['field']]);
5. $this->view->field[$alias]->field_alias = $alias;
6. $this->view->field[$alias]->options['label'] = t('Total');
7. $this->view->style_plugin->options['info'][$alias] = $this->view->style_options['info'][$crosstab_data['field']];
8. $this->view->style_plugin->options['columns'][$alias] = $alias;
The problem is that the newly added Total column (line 4) is displaying values from the $this->view->field[$crosstab_data['field']] field instead of the field $alias (line 2) just added to the query (that performs a SUM). I think setting the field_alias to $alias on line 5 was supposed to get this field to show values from the $alias query field but it doesn't.
Any ideas?
Comments
Line 2 is your problem;
Line 2 is your problem; you're not saving the return value of this. In fact, if you eliminate line one and set $alias = line 2, I think that should work better.
And don't actually send an
And don't actually send an $alias in on line 2; let Views calculate the alias for you.
My guess is that what's happening is the $alias you've chosen is alreayd in use, so Views picks a different one; because you don't store what Views actually uses, you get the other field.
Figured out one way
Thanks I tried your suggestion to remove line 1 and do this on line 2:
1. // $alias = $crosstab_columns['alias'] . '_total';
2. $alias = $this->view->query->add_field(NULL, 'SUM(' . $crosstab_data['table'] . '.' . $crosstab_data['field'] . ')');
but the values being picked up on the display were still from the original field and not the calculated query field.
After some experimentation, I found something that worked. I added the following after line 5 and now it displays the correct field:
$this->view->field[$alias]->aliases[$crosstab_data['field']] = $alias;
On a related note. Is what is the preferred way to add fields to a view? On line 4 of the original code the author adds a clone of an existing field and then tries to modify the clone to display another field instead. I came across another post suggesting view->add_item() but can't find any documentation on it.
The API was never really
The API was never really intended to add new fields to a view in realtime, so you're probably doing nearly as well as you can expect.
In Views 3 you shouldn't need
In Views 3 you shouldn't need to hack like this so much; it has group by support natively and can add SUMs from the UI.
Just to clarify
Just thought I would clarify a few things since this was the post I found when trying to add fields to a view.
First you need to create your own views filter handler.
Then you implement the views_handler:filter::query() function with the following code (modified for your field of course):
NOTE: Code used is from the current version (6.x-1.0-alpha1) of views_crosstab
<?php
class nameofyourhandler extends views_handler_filter {
function query() {
parent::query();
//Add your new field here...
// first add to the field to the query
$alias = $crosstab_columns['alias'] . '_total';
$this->view->query->add_field(NULL, $options['crosstab_operation'] . '(' . $crosstab_data['table'] . '.' . $crosstab_data['field'] . ')', $alias);
$this->view->query->fields[$alias]['aggregate'] = TRUE;
//then add the field to the view that displays the results of the field in the query
$this->view->field[$alias] = drupal_clone($this->view->field[$crosstab_data['field']]);
$this->view->field[$alias]->field_alias = $alias;
$this->view->field[$alias]->options['label'] = t('Total');
$this->view->style_plugin->options['info'][$alias] = $this->view->style_options['info'][$crosstab_data['field']];
$this->view->style_plugin->options['columns'][$alias] = $alias;
}
}
?>