191: Postgresql and sources/blocks/main_news.php

ID	Project	Category	View Status	Date Submitted	Last Update

191	Composr	General / Uncategorised	public	2010-07-14 17:46	2010-07-15 20:52

Reporter	Guest	Assigned To	Chris Graham
Priority	normal	Severity	major
Status	resolved	Resolution	fixed

Summary	191: Postgresql and sources/blocks/main_news.php
Description	Once logged in as admin I get the error: Unfortunately a query has failed [SELECT *,p.id AS p_id FROM cms4_news p LEFT JOIN cms4_news_category_entries d ON d.news_entry=p.id WHERE (1=1 OR 1=1) AND validated=1 AND date_and_time>=1278510143 GROUP BY p.id ORDER BY p.date_and_time DESC]
Additional Information	All fields to be selected in postgresql, when there is a "group by", must be 1) be in the "group by" OR 2) be only in an aggregate function. So the fix is either, fill up the group by's (lines 114-123 on sources/blocks/main_news.php) but, since you are selecting "*" that seems pointless unless you have complete duplicate records, which seems unlikely, and is not useful. So the fix is to remove the group by clauses.
Tags	No tags attached.
Attach Tags	(Separate by ",")

Time estimation (hours)
Sponsorship open

Date Added	Member	Amount Sponsored

Chris Graham 2010-07-14 17:52 administrator ~57	I was aware of this one actually, in the sense of it not meeting the SQL spec rather than the sense of knowing it failing on postgresql. The duplicate records are due to the join. The problem is someone can add a news entry such that it is in the same primary and secondary category and hence it comes out twice. Alternatively, a filter might filter on multiple categories and hence it comes out multiple times. GROUP BY is the only way I really know to remove duplicates without having to do processing in PHP, which breaks the ability for COUNT(*) to work for pagination or is a performance hit due to reading in lots of rows. Any suggestions welcome!

Chris Graham 2010-07-15 20:52 administrator ~61	Ok, this one was tough. The only way to solve without making the code into spaghetti was to do a small compromise. Non-MySQL users will have pagination that gets a bit uneven if items are returned that are in multiple categories that are filtered on, due to the duplicates being taken out (in PHP) and leaving holes in the perceived ordering. I don't think this'll cause any issues for people.

Date Modified	Username	Field	Change
2023-02-26 18:29	Chris Graham	Category	General => General / Uncategorised