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>