Summary, sorted ascending/descending Views by Date don't work

Have the same problem, subscribing

I have the same behavior (using a cck date field as argument, with a granularity set to either year or month).

Here is the SQL generated by the view, if it can help:

SELECT node_data_field_date.field_date_value AS node_data_field_date_field_date_value,
   COUNT(DISTINCT(node.nid)) AS num_records
 FROM dl_node node 
 LEFT JOIN dl_content_type_nouvelle node_data_field_date ON node.vid = node_data_field_date.vid
 WHERE (node.status <> 0) AND (node.type in ('nouvelle'))
 GROUP BY node_data_field_date_field_date_value
  ORDER BY node_data_field_date_field_date_value DESC

Hi All

I'm having the same problem:

When I try to summarize on the 'node posted date' of an 'event' content type using a views argument of 'Node: Created year + month' (like the default archive view) the view generates the following SQL:

SELECT DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m') AS created_year_month,
COUNT(node.nid) AS num_records
FROM node node
WHERE (node.status <> 0) AND (node.type in ('event'))
GROUP BY created_year_month
ORDER BY created_year_month ASC

producing the following when I run the SQL manually:

created_year_month num_records
200812 1
200901 6

This produces the following correct output:

* December, 2008 (1)
* January, 2009 (6)

but when I use an argument of "Date: Date Node: Post" with a granularity of month the view generates the following SQL:

SELECT node.created AS node_created,
COUNT(node.nid) AS num_records
FROM node node
WHERE (node.status <> 0) AND (node.type in ('event'))
GROUP BY node_created
ORDER BY node_created ASC

producing the following when I run the SQL manually:

node_created num_records
1229811551 1
1231809580 1
1231811465 1
1231881708 1
1232488349 1
1232488498 1
1232488593 1

and the following incorrect output:

* January, 2009 (1)
* January, 2009 (1)
* January, 2009 (1)
* January, 2009 (1)
* January, 2009 (1)
* January, 2009 (1)
* January, 2009 (1)

The SQL is practically the same apart from DATE_FORMAT and FROM_UNIXTIME calls which aren't added by the Date module.

I get the same results when trying to create a summary view using a CCK Date field.

I've attached the exported view - the summary is in the 'Monthly Block' display at the end.

Please let me know if I can supply any more info.

Regards

Terry Child

I just ran into the same issue and managed to figure out why.

In date_api_argument_handler::summary_query(), There's this snippet (around line 229 and following in the RC6 release):

    $this->ensure_my_table();
    
    // Make sure this field is added to the query so we have all necessary tables.
    $this->query->add_field($field['table_name'], $field['field_name']);
      
    // Add the computed field.
    $this->base_alias = $this->name_alias = $this->query->add_field(NULL, $this->formula, $field['query_name']);
    $this->query->set_count_field(NULL, $this->formula, $field['query_name']);    

    return $this->summary_basics(FALSE);

What that does is ensure that the necessary tables are part of the query. Then it adds the original, un-mutated date field to the query to... ensure that the tables are included. Then it adds the mutated form of the field, that is, the formatted "Y" or "Y-m" version. Then it does some other stuff.

But wait! Deep down in views_query::add_field, there's this code:

    if (empty($this->fields[$alias])) {
      $this->fields[$name] = array(
        'field' => $field,
        'table' => $table,
        'alias' => $alias,
      );
    }

That is, if the alias is already in use it silently skips adding the new field. But the code in the summary_query() is using the same alias as the field itself, so the formatted version gets skipped!

I don't actually think that the first call to add_field() is necessary given the ensure_my_table() call above it. When I commented it out, it still worked fine and I got my proper summary view.

Karen, all yours. :-)

I suspect #307256: Argument Summary display not working is a duplicate.

I just posted a change to the argument summary that seems to help get it to group by date and granularity properly and the sorting also works correctly for me. Some of the Views code changed and that changed the way the Date argument needed to react.

If there are problems in the latest -dev code you can reopen.