After enabling the archive page and selecting which types to display, I get the following error:
PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "t.type" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT t.type AS type, t.name AS name, n.created AS ncreated... ^: SELECT t.type AS type, t.name AS name, n.created AS ncreated, COUNT(n.nid) AS node_count FROM {node} n INNER JOIN {node_type} t ON t.type = n.type WHERE (n.status = :db_condition_placeholder_0) AND (t.type IN (:db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5)) GROUP BY n.type ORDER BY n.created ASC; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => nieuws [:db_condition_placeholder_2] => 0 [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => 0 [:db_condition_placeholder_5] => 0 ) in _archive_node_types() (regel 205 van /Users/stefconsten/repos/drupal7-git/sites/all/modules/archive/archive.pages.inc).
The attached patch solves this issue by adding an aggregate function call to the fields that are not grouped on.
Comment | File | Size | Author |
---|---|---|---|
0001-Fix-SQL-group-by-error-in-Postgres-and-other-standar.patch | 1.53 KB | stufke |
Comments
Comment #1
stufke CreditAttribution: stufke commentedThis also fixes the warning from #1407482: Strict warning: Only variables should be passed by reference in archive_page(). Sorry, I meant to split this up into two patches, but now I can't seem to be able to replace the patch file with my post.
Comment #2
vosindia CreditAttribution: vosindia commented<?php
// $Id: archive.pages.inc,v 1.18.2.4.2.1 2010/06/16 07:10:25 rmuilwijk Exp $
/**
* @file
* Pages for the archive module.
*/
/**
* Fetch nodes for the selected date, or current date if none selected.
*
* @param $year
* Number of year.
* @param $month
* Number of month.
* @param $day
* Number of day.
* @return
* A string with the themed page.
*/
function archive_page($type = 'all', $year = 0, $month = 0, $day = 0) {
// Make sure all values are secure.
$day = (int) $day;
$year = (int) $year;
$month = (int) $month;
if ($month < 0 || $month > 12) {
// Ensure that we have a proper array index later.
$month = 0;
}
if (!_archive_validate_type($type)) {
$type = 'all';
}
drupal_set_title(theme('archive_page_title', array('type' => $type, 'year' => $year, 'month' => $month, 'day' => $day)));
$date = _archive_date($type, $year, $month, $day);
$query = _archive_query($type, $date);
$nodes = variable_get('default_nodes_main', 10);
$result = $query->extend('PagerDefault')->limit($nodes)->execute();
$nodes = $query->countQuery()->execute()->fetchField();
drupal_add_css(drupal_get_path('module', 'archive') .'/archive.css');
$output = theme('archive_navigation', array('type' => $type, 'date' => $date));
if (!$nodes && $type == 'all') {
$output .= t('No content found.');
return $output;
}
$found_rows = FALSE;
$node_date = 0;
foreach ($result as $o) {
$node = node_load($o->nid);
$node_created = $node->created + $date->tz;
// Determine which separators are needed
$separators = array('year' => 0,
'month' => 0,
'day' => 0);
if (!$year) {
$created_year = format_date($node_created, 'custom', 'Y');
$last_year = format_date($node_date, 'custom', 'Y');
if ($created_year != $last_year) {
$separators['year'] = 1;
}
}
// Print month separaters
if (!$month) {
$created_month = format_date($node_created, 'custom', 'n');
$last_month = format_date($node_date, 'custom', 'n');
if ($created_month != $last_month) {
$separators['month'] = 1;
}
}
// Print day separaters
if (!$day) {
$created_day = format_date($node_created, 'custom', 'j');
$last_day = format_date($node_date, 'custom', 'j');
if ($created_day != $last_day) {
$separators['day'] = 1;
}
}
$output .= theme('archive_separator', array('date_created' => $node_created, 'separators' => $separators));
// $output .= drupal_render(node_view($node, 'teaser'));
$view_output = node_view($node, 'teaser');
$output .= drupal_render($view_output);
$found_rows = TRUE;
$node_date = $node->created + $date->tz;
}
if ($found_rows) {
$output .= theme('pager');
}
// Handle URLs that are incorrectly typed and try to parse info out of them
else {
if ($date->days[$date->day]) {
drupal_goto(_archive_url($type, $date->year, $date->month, $date->day));
}
elseif ($date->months[$date->month]) {
drupal_goto(_archive_url($type, $date->year, $date->month));
}
elseif ($date->years[$date->year]) {
drupal_goto(_archive_url($type, $date->year));
}
else {
drupal_goto(_archive_url($type));
}
}
return $output;
}
/**
* Builds an archive SQL query with its parameters for the specified date.
*
* @param $date
* A date object obtained from _archive_date().
* @return
* An array of (query, param_start, param_end).
*/
function _archive_query($type, $date) {
// Confine the display interval to only one day
if ($date->day) {
$start = mktime(0, 0, 0, $date->month, $date->day, $date->year);
$end = mktime(0, 0, 0, $date->month, $date->day + 1, $date->year);
}
// Confine the display interval to one month
elseif ($date->month) {
$start = mktime(0, 0, 0, $date->month, 1, $date->year);
$end = mktime(0, 0, 0, $date->month + 1, 1, $date->year);
}
// Confine the display interval to one year
elseif ($date->year) {
$start = mktime(0, 0, 0, 1, 1, $date->year);
$end = mktime(0, 0, 0, 1, 1, $date->year + 1);
}
else {
$start = 0;
$end = 0;
}
// Grab limits on node types if exist
$final_types = _archive_types_sql_array($type);
// Allow viewing all nodes, not just nodes by year
$query = db_select('node', 'n');
$query->fields('n', array('nid', 'type'));
$query->condition('n.status', 1);
$query->orderBy('n.created', 'DESC');
$query->condition('n.type', $final_types, 'IN');
$query->addTag('node_access');
if ($start && $end) {
$query->condition('n.created', $start - $date->tz, '>=');
$query->condition('n.created', $end - $date->tz, '<=');
}
return $query;
}
/**
* Returns the different node types that have nodes.
*
* @param $date
* A date object obtained from _archive_date().
* @return
* An array of node-types to number of posts of that type.
*/
function _archive_node_types($date) {
$types = variable_get('archive_type_filters', array());
// Confine the display interval to only one day
if ($date->day) {
$start = mktime(0, 0, 0, $date->month, $date->day, $date->year);
$end = mktime(0, 0, 0, $date->month, $date->day + 1, $date->year);
}
// Confine the display interval to one month
elseif ($date->month) {
$start = mktime(0, 0, 0, $date->month, 1, $date->year);
$end = mktime(0, 0, 0, $date->month + 1, 1, $date->year);
}
// Confine the display interval to one year
elseif ($date->year) {
$start = mktime(0, 0, 0, 1, 1, $date->year);
$end = mktime(0, 0, 0, 1, 1, $date->year + 1);
}
else {
$start = 0;
$end = 0;
}
// Setup the query.
$query = db_select('node', 'n');
$query->innerJoin('node_type', 't', 't.type = n.type');
// $query->fields('t', array('type', 'name'));
$query->addExpression('MAX(t.type)', 'type');
$query->addExpression('MAX(t.name)', 'name');
$query->addExpression('COUNT(n.nid)', 'node_count');
$query->condition('n.status', 1);
$query->condition('t.type', $types, 'IN');
$query->groupBy('n.type');
//$query->orderBy('n.created', 'ASC');
$query->orderBy('MAX(n.created)', 'ASC');
$query->addTag('node_acces');
if ($start && $end) {
$query->condition('n.created', array($start - $date->tz, $end - $date->tz), 'BETWEEN');
}
$result = $query->execute();
$n_types = array();
foreach ($result as $row) {
$n_types[$row->type] = array('count' => $row->node_count,
'name' => $row->name);
}
ksort($n_types);
return $n_types;
}
/**
* Theme function for the title of the archive page.
*
* @param $variables
* An associative array containing:
* - type: The node type for the archive page.
* - year: The year of the archivepage.
* - month: The month of the archive page.
* - day: The day of the archive page.
*
* @ingroup themeable
*/
function theme_archive_page_title($variables) {
$title = t('News By Date');
$month_names = array('', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
if ($variables['day']) {
$title .= ' - '. t($month_names[$variables['month']]) .' '. $variables['day'] .', '. $variables['year'];
}
elseif ($variables['month']) {
$title .= ' - '. t($month_names[$variables['month']]) .' '. $variables['year'];
}
elseif ($variables['year']) {
$title .= ' - '. $variables['year'];
}
if ($variables['type'] != 'all') {
$type_name = db_query("SELECT name FROM {node_type} WHERE type = '%s'", $variables['type'])->fetchField();
$title .= ' - '. ($type_name ? t($type_name) : $variables['type']);
}
return $title;
}
/**
* Theme the archive navigation with years, months and dates by default.
*
* @ingroup themeable
*/
function theme_archive_navigation($variables) {
$output = "
$output .= theme('archive_navigation_years', array('type' => $variables['type'], 'date' => $variables['date']));
if (_archive_validate_date($variables['date']->year)) {
$output .= theme('archive_navigation_months', array('type' => $variables['type'], 'date' => $variables['date']));
}
if (_archive_validate_date($variables['date']->year, $variables['date']->month)) {
$output .= theme('archive_navigation_days', array('type' => $variables['type'], 'date' => $variables['date']));
}
$output .= "
";
// Only display node type filter if more than one node type represented
if (sizeof(_archive_node_types($variables['date'])) > 1) {
$output .= "
$output .= theme('archive_navigation_node_types', array('type' => $variables['type'], 'date' => $variables['date']));
$output .= "
";
}
$output .= "
\n";
return $output;
}
/**
* Theme the list of years for the archive navigation.
*
* @ingroup themeable
*/
function theme_archive_navigation_years($variables) {
$output = "
\n";- year?'':' class="selected"') .'>'. l(t('All'), _archive_url($variables['type']), array('attributes' => array('title' => format_plural($all_count, '1 post', '@count posts')))) ."
- '. l($year, _archive_url($variables['type'], $year), array('attributes' => array('title' => format_plural($year_count, '1 post', '@count posts')))) ."
$all_count = 0;
foreach ($variables['date']->years as $year_count) {
$all_count += $year_count;
}
$output .= '
\n";
foreach ($variables['date']->years as $year => $year_count) {
//print "sss:$year
";
$class = '';
if ($year == $variables['date']->year) {
$class = ' class="selected"';
}
$output .= '
\n";
}
$output .= "
\n";
return $output;
}
/**
* Theme the list of months for the archive navigation.
*
* @ingroup themeable
*/
function theme_archive_navigation_months($variables) {
$output = "
\n";- month?'':' class="selected"') .'>'. l(t('All'), _archive_url($variables['type'], $variables['date']->year), array('attributes' => array('title' => format_plural($all_count, '1 post', '@count posts')))) ."
- ". ($posts > 0 ? l(t($month_names[$month]), _archive_url($variables['type'], $variables['date']->year, $month), array('attributes' => array('title' => format_plural($posts, "1 post", "@count posts")))) : t($month_names[$month])) ."
$all_count = 0;
foreach ($variables['date']->months as $month) {
$all_count += $month;
}
$output .= '
\n";
$curr_month = format_date(time(), 'custom', 'n');
$curr_year = format_date(time(), 'custom', 'Y');
foreach (range(1, 12) as $month) {
$posts = !empty($variables['date']->months[$month]) ? $variables['date']->months[$month] : 0;
$class = '';
if ($month == $variables['date']->month) {
$class = ' class="selected"';
}
elseif ($curr_year == $variables['date']->year && $month > $curr_month) {
$class = ' class="future"';
}
$month_names = array('', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
$output .= "
\n";
}
$output .= "
\n";
return $output;
}
/**
* Theme the list of days for the archive navigation.
*
* @ingroup themeable
*/
function theme_archive_navigation_days($variables) {
$output = "
\n";- day?'':' class="selected"') .'>'. l(t('All'), _archive_url($variables['type'], $variables['date']->year, $variables['date']->month), array('attributes' => array('title' => format_plural($all_count, '1 post', '@count posts')))) ."
- ". ($posts ? l($day, _archive_url($variables['type'], $variables['date']->year, $variables['date']->month, $day), array('attributes' => array("title" => format_plural($posts, "1 post", "@count posts")))) : $day) ."
$all_count = 0;
foreach ($variables['date']->days as $day) {
$all_count += $day;
}
$output .= '
\n";
$curr_month = format_date(time(), 'custom', 'n');
$curr_year = format_date(time(), 'custom', 'Y');
$curr_day = format_date(time(), 'custom', 'j');
$day_stop = gmdate('t', gmmktime(0, 0, 0, $variables['date']->month, 1, $variables['date']->year));
for ($day = 1; $day <= $day_stop; $day++) {
$posts = array_key_exists($day, $variables['date']->days) ? $variables['date']->days[$day] : 0;
$class = '';
if ($day == $variables['date']->day) {
$class = ' class="selected"';
}
elseif ($curr_year == $variables['date']->year && $curr_month == $variables['date']->month && $day > $curr_day) {
$class = ' class="future"';
}
$output .= "
\n";
}
$output .= "
\n";
return $output;
}
/**
* Theme the list of node types for the archives.
*
* @ingroup themeable
*/
function theme_archive_navigation_node_types($variables) {
$output = "
\n";- '. l(t('All'), _archive_url('all', $variables['date']->year, $variables['date']->month, $variables['date']->day), array('attributes' => array('title' => format_plural($all_count, '1 post', '@count posts')))) ."
- ". l($name, _archive_url($ft_key, $variables['date']->year, $variables['date']->month, $variables['date']->day), array('attributes' => array("title" => format_plural($types_count[$ft_key]['count'], "1 post", "@count posts")))) ."
- $name
$types_count = _archive_node_types($variables['date']);
$all_count = 0;
foreach ($types_count as $t) {
$all_count += $t['count'];
}
$output .= '
\n";
foreach ($types_count as $ft_key => $ft_value) {
if (!$ft_value['count']) {
continue;
}
$class = ($ft_key == $variables['type'] ? ' class="selected"' : '');
$name = $ft_value['name'];
if ($types_count[$ft_key]['count'] > 0) {
$output .= "
\n";
}
else {
$output .= "
\n";
}
}
$output .= "
\n";
return $output;
}
/**
* Theme the date separators between nodes of different year/month/day.
*
* @param $date_created
* A UNIX timestamp.
* @param $separators
* An array with 'year', 'month', and 'day' keys. A value of 1 for any
* of those keys means a transition for that unit of time.
* Ex. array('year' => 0, 'month' => 1, 'day' => 1)
* ^ Means the month has transitioned
*
* @ingroup themeable
*/
function theme_archive_separator($variables) {
$date_sep = '';
if ($variables['separators']['year'] && $variables['separators']['month'] && $variables['separators']['day']) {
$date_sep = format_date($variables['date_created'], 'custom', 'F jS, Y');
}
elseif ($variables['separators']['month'] && $variables['separators']['day']) {
$date_sep = format_date($variables['date_created'], 'custom', 'F jS');
}
elseif ($variables['separators']['day']) {
$date_sep = format_date($variables['date_created'], 'custom', 'F jS');
}
return '
'. $date_sep .'
';
}
Comment #3
szermek CreditAttribution: szermek commentedThanks, you solved my problem!