Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Postgresql GROUP BY - missing clauses


Reply


2 Posts   1821 Views

Avatar
Tony C

Community Member, 9 Posts

20 September 2010 at 2:44pm

Hi,

Still trying to find my way around the framework, but learning slowly!

I'm trying to Postgresql enable the ecommerce module, but this will be a common question I think for those attempting to migrate existing modules to Postgresql.

Postgresql (and other databases) can be really strict about missing fields from the GROUP BY clause. MySQL seems pretty slack about it, which is both a good and a bad thing. Good in that developers can be slack, but bad in that their queries will never work with another database.

My question is, given the monster error message below, what should I be adding, and where should I be adding it to, in order to force the addition of the GROUP BY??

ERROR [Warning]: pg_query(): Query failed: ERROR: column "Product_ProductGroups.ProductID" must appear in the GROUP BY clause or be used in an aggregate function
IN POST /admin/getitem?ID=9&ajax=1
Line 174 in /var/www/tonyculshaw.com/postgresql/code/PostgreSQLDatabase.php

Source
======
165:       if(isset($_REQUEST['previewwrite']) && in_array(strtolower(substr($sql,0,strpos($sql,' '))),
array('insert','update','delete','replace'))) {
166:          Debug::message("Will execute: $sql");
167:          return;
168:       }
169:
170:       if(isset($_REQUEST['showqueries'])) {
171:          $starttime = microtime(true);
172:       }
173:
* 174:       $handle = pg_query($this->dbConn, str_replace('`', '"', $sql)); // MY OWN DODGY CHANGE FOR POSTGRESQL - WILL EVENTUALLY BE REMOVED WHEN ALL QUERIES ARE UPDATED
175:       
176:       if(isset($_REQUEST['showqueries'])) {
177:          $endtime = round(microtime(true) - $starttime,4);
178:          Debug::message("\n$sql\n{$endtime}ms\n", false);
179:       }
180:       

Trace
=====
<ul>pg_query(Resource id #87,SELECT "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", "ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END AS "RecordClassName", CASE WHEN "ProductID" IS NULL THEN '0' ELSE '1' END AS Checked FROM "SiteTree" LEFT JOIN "ProductGroup" ON "ProductGroup"."ID" = "SiteTree"."ID" LEFT JOIN "Product_ProductGroups" ON ("SiteTree"."ID" = "Product_ProductGroups"."ProductGroupID" AND "ProductID" = '9') WHERE ("SiteTree"."ClassName" IN ('ProductGroup')) GROUP BY "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", "ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END ORDER BY "Sort" LIMIT 30)
line 174 of PostgreSQLDatabase.php

PostgreSQLDatabase->query(SELECT "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", "ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END AS "RecordClassName", CASE WHEN "ProductID" IS NULL THEN '0' ELSE '1' END AS Checked FROM "SiteTree" LEFT JOIN "ProductGroup" ON "ProductGroup"."ID" = "SiteTree"."ID" LEFT JOIN "Product_ProductGroups" ON ("SiteTree"."ID" = "Product_ProductGroups"."ProductGroupID" AND "ProductID" = '9') WHERE ("SiteTree"."ClassName" IN ('ProductGroup')) GROUP BY "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", "ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END ORDER BY "Sort" LIMIT 30,256)
line 126 of DB.php

DB::query(SELECT "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", "ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END AS "RecordClassName", CASE WHEN "ProductID" IS NULL THEN '0' ELSE '1' END AS Checked FROM "SiteTree" LEFT JOIN "ProductGroup" ON "ProductGroup"."ID" = "SiteTree"."ID" LEFT JOIN "Product_ProductGroups" ON ("SiteTree"."ID" = "Product_ProductGroups"."ProductGroupID" AND "ProductID" = '9') WHERE ("SiteTree"."ClassName" IN ('ProductGroup')) GROUP BY "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", "ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END ORDER BY "Sort" LIMIT 30,256)
line 386 of SQLQuery.php

SQLQuery->execute()
line 461 of TableListField.php

TableListField->sourceItems()
line 273 of ComplexTableField.php

ComplexTableField->FieldHolder()
line 38 of HasManyComplexTableField.php

HasManyComplexTableField->FieldHolder()
line 369 of ViewableData.php

ViewableData->obj(FieldHolder,,,1)
line 445 of ViewableData.php

ViewableData->XML_val(FieldHolder,,1)
line 73 of .cache.var.www.tonyculshaw.com.sapphire.templates.TabSetFieldHolder.ss

include(/var/www/tonyculshaw.com/silverstripe-cache/.cache.var.www.tonyculshaw.com.sapphire.templates.TabSetFieldHolder.ss)
line 392 of SSViewer.php

SSViewer->process(TabSet)
line 342 of ViewableData.php

ViewableData->renderWith(TabSetFieldHolder)
line 61 of TabSet.php

TabSet->FieldHolder()
line 369 of ViewableData.php

ViewableData->obj(FieldHolder,,,1)
line 445 of ViewableData.php

ViewableData->XML_val(FieldHolder,,1)
line 58 of .cache.var.www.tonyculshaw.com.sapphire.templates.TabSetFieldHolder.ss

include(/var/www/tonyculshaw.com/silverstripe-cache/.cache.var.www.tonyculshaw.com.sapphire.templates.TabSetFieldHolder.ss)
line 392 of SSViewer.php

SSViewer->process(TabSet)
line 342 of ViewableData.php

ViewableData->renderWith(TabSetFieldHolder)
line 61 of TabSet.php

TabSet->FieldHolder()
line 369 of ViewableData.php

ViewableData->obj(FieldHolder,,,1)
line 445 of ViewableData.php

ViewableData->XML_val(FieldHolder,,1)
line 77 of .cache.var.www.tonyculshaw.com.sapphire.templates.Includes.Form.ss

include(/var/www/tonyculshaw.com/silverstripe-cache/.cache.var.www.tonyculshaw.com.sapphire.templates.Includes.Form.ss)
line 392 of SSViewer.php

SSViewer->process(Form)
line 342 of ViewableData.php

ViewableData->renderWith(Array)
line 1081 of Form.php

Form->forTemplate()
line 1108 of Form.php

Form->formHtmlContent()
line 386 of LeftAndMain.php

LeftAndMain->getitem(SS_HTTPRequest)
line 193 of Controller.php

Controller->handleAction(SS_HTTPRequest)
line 134 of RequestHandler.php

RequestHandler->handleRequest(SS_HTTPRequest)
line 147 of Controller.php

Controller->handleRequest(SS_HTTPRequest)
line 283 of Director.php

Director::handleRequest(SS_HTTPRequest,Session)
line 127 of Director.php

Director::direct(/admin/getitem)
line 127 of main.php

</ul>

Avatar
Tony C

Community Member, 9 Posts

20 September 2010 at 6:57pm

AHHHHHH! Could it a core framework bug....?

DataObject.php line 2600 (or there abouts)....
// In order to group by unique columns we have to group by everything listed in the select

The regex is missing a CASE statement in the select.

Once again here is what gets generated....

SELECT "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription",
"SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile",
"SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID",
"ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END AS "RecordClassName",
CASE WHEN "ProductID" IS NULL THEN '0' ELSE '1' END AS Checked
FROM "SiteTree"
LEFT JOIN "ProductGroup" ON "ProductGroup"."ID" = "SiteTree"."ID"
LEFT JOIN "Product_ProductGroups" ON ("SiteTree"."ID" = "Product_ProductGroups"."ProductGroupID" AND "ProductID" = '16')
WHERE ("SiteTree"."ClassName" IN ('ProductGroup'))
GROUP BY "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription",
"SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile",
"SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID",
"ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END
ORDER BY "Sort" LIMIT 30

Giving....

ERROR: column "Product_ProductGroups.ProductID" must appear in the GROUP BY clause or be used in an aggregate function

********** Error **********

ERROR: column "Product_ProductGroups.ProductID" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803

AND HERE is the correct SQL.....

(Relevent bits marked >>>>>)...

SELECT "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription",
"SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile",
"SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID",
"ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END AS "RecordClassName",
>>>> THIS >>>>> CASE WHEN "ProductID" IS NULL THEN '0' ELSE '1' END AS Checked
FROM "SiteTree"
LEFT JOIN "ProductGroup" ON "ProductGroup"."ID" = "SiteTree"."ID"
LEFT JOIN "Product_ProductGroups" ON ("SiteTree"."ID" = "Product_ProductGroups"."ProductGroupID" AND "ProductID" = '16')
WHERE ("SiteTree"."ClassName" IN ('ProductGroup'))
GROUP BY "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription",
"SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile",
"SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID",
"ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END,
>>>>>> NEEDS THIS >>>>>>> "Product_ProductGroups"."ProductID"
ORDER BY "Sort" LIMIT 30