Group by Date Field instead of Date Created.
| Project: | Monthly Archive |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Hi, this module worked a treat for me. I couldn't believe that Views just could not do a 'group by month' as a list based on a date field.
Anyhoo, I used your mod as I wanted just my story pages grouped by month based on a date field as the 'date created' did not allways related to the actual date of the news article. I changed the query in your mod as follows:
// $query = 'SELECT DATE_FORMAT(FROM_UNIXTIME(created),\'%Y-%m\') AS monthno,COUNT(*) AS nodenum FROM {node} n ';
//SACK $query .= 'WHERE n.created>0 AND (n.status=1) AND n.type IN (' . implode(',', $nodes_to_include) . ') ';
$query = 'SELECT DATE_FORMAT(dv5_content_type_story.field_date_value,\'%Y-%m\') AS monthno, COUNT(*) AS nodenum FROM (dv5_content_type_story
INNER JOIN dv5_node ON dv5_node.nid=dv5_content_type_story.nid) WHERE dv5_node.status=1 ';Because the 'date field' is stored in another table, I had to make a join between the 'node' and 'content_type_story' tables. It's obviously 'hard-coded' to the story type, but that's what I want and It seams to work :)
Next trick is to try and change the url of the headers and theme the resulting page. Also, only list months in the current year, but have previous years as collapsed year links..... getting a bit to advanced for me I think ;)
The reason this is in the feature request is that, although this is the first module I've tinkered with, I wonder if there is a way to either make the query editable, or specify what field the group is to be based on through an admin config interface.
Nice module.
#1
Bahh, spoke to soon ;)
Had to do the followoing fix so when you click the month URL, the resulting page will show the correct pages. My Date field is not Unix Time so had to convert the query date and time from Unix time.... my head hurts ;)
// create the SQL for the query
// $query_info[0] = 'SELECT n.nid FROM {node} n WHERE (n.created >= '.$start_date.') AND (n.created < '.$end_date.') AND (n.status = 1) ';
// $query_info[0] .= count($nodes_to_include) > 0 ? 'AND n.type IN (' . implode(',', $nodes_to_include) . ') ': '';
$query_info[0] = 'SELECT dv5_node.nid FROM (dv5_content_type_story INNER JOIN dv5_node ON dv5_node.nid=dv5_content_type_story.nid)
WHERE (dv5_content_type_story.field_date_value >= (FROM_UNIXTIME('.$start_date.'))) AND (dv5_content_type_story.field_date_value < (FROM_UNIXTIME('.$end_date.'))) AND dv5_node.status=1 ';
//$query_info[0] .= 'ORDER BY n.created '. ($_month_config['month_rev_pagesort'] ? 'ASC' : 'DESC');
$query_info[0] .= 'ORDER BY field_date_value '. ($_month_config['month_rev_pagesort'] ? 'ASC' : 'DESC');
$query_info[0] = db_rewrite_sql($query_info[0], 'n', 'nid');
$query_info[1] = $start_date;
$query_info[2] = $end_date;
// $query_info[3] = 'SELECT COUNT(n.nid) FROM {node} n WHERE (n.created >= '.$start_date.') AND (n.created < '.$end_date.') AND (n.status = 1) ';
$query_info[3] = 'SELECT COUNT(dv5_node.nid) FROM (dv5_content_type_story INNER JOIN dv5_node ON dv5_node.nid=dv5_content_type_story.nid) WHERE (dv5_content_type_story.field_date_value >= (FROM_UNIXTIME('.$start_date.'))) AND (dv5_content_type_story.field_date_value < (FROM_UNIXTIME('.$end_date.'))) AND (dv5_node.status = 1) ';
$query_info[3] .= count($nodes_to_include) > 0 ? 'AND dv5_node.type IN (' . implode(',', $nodes_to_include) . ') ': '';
Sorry for the mess but I'd say it can be tidied up and simplified somewhat.