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>