I'm looking for help writing views hooks for a custom module of mine. In particular, I'm adding views fields which are the result of SQL COUNTs. When I do this, there are some nasty side effects in views. I doubt I'm the first to attempt this, but I'm unable to find any examples out there.
My situation is that I have a node type, call it sample, and a custom table of sample_data. Each sample node has an arbitrary number of sample_data rows associated with it. I'm trying to make a field which is a count of how many sample_data rows are associated with a node. A similar situation would be a field which shows how many taxonomy terms are associated with a node.
My fields declaration looks approximately like this (I've simplified code for this post):
<?php
'fields' => array(
'data_count' =>
array('name' => t('Sunflower Sample: Number of Datum'),
'notafield' => TRUE,
'query_handler' => 'sample_data_count_query_handler',
'handler' => 'sample_data_count_field_handler',
'help' => t('Shows the number of data rows.'),
),
),
);
?>My query handler does the dirty work. Notice the add_groupby:
<?php
function sample_data_count_query_handler($field, $info, &$query) {
$query->ensure_table("sample_data");
$query->add_field("COUNT(sample_data.data_id)", NULL, "data_count");
$query->add_groupby("sample_data.nid");
}
function sample_bee_count_field_handler($info, $field, $value, $data) {
return $data->data_count;
}
?>The add_groupby is necessary for the count query to work. Much of this code works as intended, but there are two unwanted side effects.
- When the view is rendered the views engine performs 2 queries. FIrst, it does a count query to determine if a pager is needed. The count query does not include the group by. The count query returns a number that is too high, causing the pager to appear in the view and show too many pages. (The second query includes the group by and returns the results as desired.)
- When the view includes an argument, and the option is set to 'summary, sorted as view', then the group by is included in the query, but it is not desired. The result in this case is that the summary shows identical links more than once.
I'm tired of pounding my head against a wall on this one. Has anyone out there written fields that return a count? And if so, what's the secret? (And no, the secret is not adding the Node:DISTINCT filter.)
Many thanks. -Dave
?>
Comments
Seen some days ago ...
http://drupal.org/project/views_calc
best regards
Ray
If you need a drupal developer contact me!
--
best regards
Ray
If you need a drupal developer contact me!
Thanks for the pointer, but
Thanks for the pointer, but that is something different. That computes a sum of the fields presented by the view. What I want is the view to present a SQL count as a field.
Interesting concept, similar to what I need...
I have a similar need, with a twist...
I would like to execute an arbitrary SQL query, perhaps returning one or more columns and one or more rows, and have the query return its values as a views-formatted result.
Moreover, it would be great if the queries could be executed at another DB location (mysql://server/DB).
Your use of a query handler is intriguing - perhaps there is a way to make your approach more generic, to include perhaps a query connection parameter?
Why? I have a third-party database from which I would like to present data as a "report" within a node or nodefamily. The node ID in drupal would drive a SQL WHERE clause at the remote database. We'd want the result of the query execution well integrated into the Drupal framework.
I'm really surprised nobody has tried this yet. I've thought about making a CCK custom field that does this. Thoughts?
-brian
The computed field module
The computed field module would allow you to do something like this, I think. You could put any code you like into it, then create a Views view of your computed fields.
views_groupby is a module
views_groupby is a module that has worked wonders for counting things for me in views. For now, it still only implements a count field.
Frank Carey
Twelve Grove Inc. Drupal Development