Jump to:

3460 Posts in 1064 Topics by 739 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Postgresql GROUP BY - missing clauses

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 1732 Views
  • Tony C
    Avatar
    Community Member
    9 Posts

    Postgresql GROUP BY - missing clauses Link to this post

    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>

  • Tony C
    Avatar
    Community Member
    9 Posts

    Re: Postgresql GROUP BY - missing clauses Link to this post

    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

    1732 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.