search

tractorcow-farm / silverstripe-fluent

Multi-language translate module for Silverstripe, without having to manage separate site trees.
BSD 3-Clause "New" or "Revised" License
93 stars 111 forks source link

Can't delete items with SQLite - joins in delete statements aren't supported #410

Open KINKCreative opened 6 years ago

KINKCreative commented 6 years ago

Using SS4.1, SQLIte, SS FLuent.

Can save, but not delete. Same goes with a DataObject. It seems to be an issue with the way the delete queries are prepared, or SQLite is possibly tricky and complains...

[Emergency] Uncaught SilverStripe\ORM\Connect\DatabaseException: Couldn't run query: DELETE "SiteTree" FROM "SiteTree" LEFT JOIN "SiteTree_Localised" ON "SiteTree"."ID" = "SiteTree_Localised"."RecordID" WHERE ("SiteTree"."ID" = ?) AND ("SiteTree_Localised"."ID" IS NULL) near ""SiteTree"": syntax error
POST /admin/pages/settings/EditForm/14/
Line 64 in /Users/knovak/Projects/toro/vendor/silverstripe/framework/src/ORM/Connect/DBConnector.php

Source
55         if (!empty($sql)) {
56             $formatter = new SQLFormatter();
57             $formattedSQL = $formatter->formatPlain($sql);
58             $msg = "Couldn't run query:\n\n{$formattedSQL}\n\n{$msg}";
59         }
60 
61         if ($errorLevel === E_USER_ERROR) {
62             // Treating errors as exceptions better allows for responding to errors
63             // in code, such as credential checking during installation
64             throw new DatabaseException($msg, 0, null, $sql, $parameters);
65         } else {
66             user_error($msg, $errorLevel);
67         }
68     }
69 
70     /**
Trace
SilverStripe\ORM\Connect\DBConnector->databaseError(Couldn't run query: DELETE "SiteTree" FROM "SiteTree" LEFT JOIN "SiteTree_Localised" ON "SiteTree"."ID" = "SiteTree_Localised"."RecordID" WHERE ("SiteTree"."ID" = ?) AND ("SiteTree_Localised"."ID" IS NULL) near ""SiteTree"": syntax error, 256, DELETE "SiteTree" FROM "SiteTree" LEFT JOIN "SiteTree_Localised" ON "SiteTree"."ID" = "SiteTree_Localised"."RecordID" WHERE ("SiteTree"."ID" = ?) AND ("SiteTree_Localised"."ID" IS NULL), Array) 
SQLite3Connector.php:149
SilverStripe\SQLite\SQLite3Connector->preparedQuery(DELETE "SiteTree" FROM "SiteTree" LEFT JOIN "SiteTree_Localised" ON "SiteTree"."ID" = "SiteTree_Localised"."RecordID" WHERE ("SiteTree"."ID" = ?) AND ("SiteTree_Localised"."ID" IS NULL), Array, 256) 
Database.php:168
SilverStripe\ORM\Connect\Database->SilverStripe\ORM\Connect\{closure}(DELETE "SiteTree" FROM "SiteTree" LEFT JOIN "SiteTree_Localised" ON "SiteTree"."ID" = "SiteTree_Localised"."RecordID" WHERE ("SiteTree"."ID" = ?) AND ("SiteTree_Localised"."ID" IS NULL)) 
Database.php:222
SilverStripe\ORM\Connect\Database->benchmarkQuery(DELETE "SiteTree" FROM "SiteTree" LEFT JOIN "SiteTree_Localised" ON "SiteTree"."ID" = "SiteTree_Localised"."RecordID" WHERE ("SiteTree"."ID" = ?) AND ("SiteTree_Localised"."ID" IS NULL), Closure, Array) 
Database.php:169
SilverStripe\ORM\Connect\Database->preparedQuery(DELETE "SiteTree" FROM "SiteTree" LEFT JOIN "SiteTree_Localised" ON "SiteTree"."ID" = "SiteTree_Localised"."RecordID" WHERE ("SiteTree"."ID" = ?) AND ("SiteTree_Localised"."ID" IS NULL), Array, 256) 
DB.php:445
SilverStripe\ORM\DB::prepared_query(DELETE "SiteTree" FROM "SiteTree" LEFT JOIN "SiteTree_Localised" ON "SiteTree"."ID" = "SiteTree_Localised"."RecordID" WHERE ("SiteTree"."ID" = ?) AND ("SiteTree_Localised"."ID" IS NULL), Array) 
SQLExpression.php:115
SilverStripe\ORM\Queries\SQLExpression->execute() 
FluentExtension.php:472
TractorCow\Fluent\Extension\FluentExtension->updateDeleteTables(Array, SELECT DISTINCT "SiteTree"."ClassName", "SiteTree"."LastEdited", "SiteTree"."Created", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."URLSegment" ELSE "SiteTree"."URLSegment" END AS "URLSegment", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."Title" ELSE "SiteTree"."Title" END AS "Title", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."MenuTitle" ELSE "SiteTree"."MenuTitle" END AS "MenuTitle", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."Content" ELSE "SiteTree"."Content" END AS "Content", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."MetaDescription" ELSE "SiteTree"."MetaDescription" END AS "MetaDescription", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."ReportClass" ELSE "SiteTree"."ReportClass" END AS "ReportClass", "SiteTree"."Version", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."EnableCountdown", "SiteTree"."CountdownDate", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."CountdownHeader" ELSE "SiteTree"."CountdownHeader" END AS "CountdownHeader", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."CountdownText" ELSE "SiteTree"."CountdownText" END AS "CountdownText", "SiteTree"."Priority", "SiteTree"."ParentID", CASE WHEN "Page_Localised_es_MX"."ID" IS NOT NULL THEN "Page_Localised_es_MX"."Subtitle" ELSE "Page"."Subtitle" END AS "Subtitle", CASE WHEN "Page_Localised_es_MX"."ID" IS NOT NULL THEN "Page_Localised_es_MX"."Summary" ELSE "Page"."Summary" END AS "Summary", "Page"."ImageID", "Page"."HeroImageID", "Page"."ElementalAreaID", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SilverStripe\CMS\Model\SiteTree' END AS "RecordClassName", 'es_MX' AS "Locale", CASE WHEN "Page_Localised_es_MX"."ID" IS NOT NULL THEN 'es_MX' ELSE NULL END AS "SourceLocale" FROM "SiteTree" LEFT JOIN "Page" ON "Page"."ID" = "SiteTree"."ID" LEFT JOIN "SiteTree_Localised" AS "SiteTree_Localised_es_MX" ON "SiteTree"."ID" = "SiteTree_Localised_es_MX"."RecordID" AND "SiteTree_Localised_es_MX"."Locale" = ? LEFT JOIN "Page_Localised" AS "Page_Localised_es_MX" ON "Page"."ID" = "Page_Localised_es_MX"."RecordID" AND "Page_Localised_es_MX"."Locale" = ? WHERE ("SiteTree"."ID" = ?) AND ("SiteTree"."ClassName" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) ORDER BY "SiteTree"."Sort" ASC <array ( 0 => 'es_MX', 1 => 'es_MX', 2 => 14, 3 => 'Page', 4 => 'ContactPage', 5 => 'HomePage', 6 => 'Events', 7 => 'Gallery', 8 => 'Photos', 9 => 'Videos', 10 => 'SilverStripe\\ErrorPage\\ErrorPage', 11 => 'SilverStripe\\CMS\\Model\\RedirectorPage', 12 => 'SilverStripe\\CMS\\Model\\VirtualPage', )>, , , , , ) 
Extensible.php:472
SilverStripe\View\ViewableData->extend(updateDeleteTables, Array, SELECT DISTINCT "SiteTree"."ClassName", "SiteTree"."LastEdited", "SiteTree"."Created", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."URLSegment" ELSE "SiteTree"."URLSegment" END AS "URLSegment", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."Title" ELSE "SiteTree"."Title" END AS "Title", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."MenuTitle" ELSE "SiteTree"."MenuTitle" END AS "MenuTitle", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."Content" ELSE "SiteTree"."Content" END AS "Content", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."MetaDescription" ELSE "SiteTree"."MetaDescription" END AS "MetaDescription", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."ReportClass" ELSE "SiteTree"."ReportClass" END AS "ReportClass", "SiteTree"."Version", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."EnableCountdown", "SiteTree"."CountdownDate", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."CountdownHeader" ELSE "SiteTree"."CountdownHeader" END AS "CountdownHeader", CASE WHEN "SiteTree_Localised_es_MX"."ID" IS NOT NULL THEN "SiteTree_Localised_es_MX"."CountdownText" ELSE "SiteTree"."CountdownText" END AS "CountdownText", "SiteTree"."Priority", "SiteTree"."ParentID", CASE WHEN "Page_Localised_es_MX"."ID" IS NOT NULL THEN "Page_Localised_es_MX"."Subtitle" ELSE "Page"."Subtitle" END AS "Subtitle", CASE WHEN "Page_Localised_es_MX"."ID" IS NOT NULL THEN "Page_Localised_es_MX"."Summary" ELSE "Page"."Summary" END AS "Summary", "Page"."ImageID", "Page"."HeroImageID", "Page"."ElementalAreaID", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SilverStripe\CMS\Model\SiteTree' END AS "RecordClassName", 'es_MX' AS "Locale", CASE WHEN "Page_Localised_es_MX"."ID" IS NOT NULL THEN 'es_MX' ELSE NULL END AS "SourceLocale" FROM "SiteTree" LEFT JOIN "Page" ON "Page"."ID" = "SiteTree"."ID" LEFT JOIN "SiteTree_Localised" AS "SiteTree_Localised_es_MX" ON "SiteTree"."ID" = "SiteTree_Localised_es_MX"."RecordID" AND "SiteTree_Localised_es_MX"."Locale" = ? LEFT JOIN "Page_Localised" AS "Page_Localised_es_MX" ON "Page"."ID" = "Page_Localised_es_MX"."RecordID" AND "Page_Localised_es_MX"."Locale" = ? WHERE ("SiteTree"."ID" = ?) AND ("SiteTree"."ClassName" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) ORDER BY "SiteTree"."Sort" ASC <array ( 0 => 'es_MX', 1 => 'es_MX', 2 => 14, 3 => 'Page', 4 => 'ContactPage', 5 => 'HomePage', 6 => 'Events', 7 => 'Gallery', 8 => 'Photos', 9 => 'Videos', 10 => 'SilverStripe\\ErrorPage\\ErrorPage', 11 => 'SilverStripe\\CMS\\Model\\RedirectorPage', 12 => 'SilverStripe\\CMS\\Model\\VirtualPage', )>) 
DataObject.php:1604
SilverStripe\ORM\DataObject->delete() 
Versioned.php:2022
SilverStripe\Versioned\Versioned->deleteFromStage(Stage) 
call_user_func_array(Array, Array) 
Extensible.php:144
SilverStripe\View\ViewableData->SilverStripe\Core\{closure}(Page, Array) 
CustomMethods.php:61
SilverStripe\View\ViewableData->__call(deleteFromStage, Array) 
Versioned.php:1350
SilverStripe\Versioned\Versioned->doArchive() 
call_user_func_array(Array, Array) 
Extensible.php:144
SilverStripe\View\ViewableData->SilverStripe\Core\{closure}(Page, Array) 
CustomMethods.php:61
SilverStripe\View\ViewableData->__call(doArchive, Array) 
CMSMain.php:1874
SilverStripe\CMS\Controllers\CMSMain->archive(Array, SilverStripe\Forms\Form, SilverStripe\Control\HTTPRequest, SilverStripe\Admin\LeftAndMainFormRequestHandler) 
FormRequestHandler.php:231
SilverStripe\Forms\FormRequestHandler->httpSubmission(SilverStripe\Control\HTTPRequest) 
RequestHandler.php:319
SilverStripe\Control\RequestHandler->handleAction(SilverStripe\Control\HTTPRequest, httpSubmission) 
RequestHandler.php:201
SilverStripe\Control\RequestHandler->handleRequest(SilverStripe\Control\HTTPRequest) 
RequestHandler.php:225
SilverStripe\Control\RequestHandler->handleRequest(SilverStripe\Control\HTTPRequest) 
Controller.php:212
SilverStripe\Control\Controller->handleRequest(SilverStripe\Control\HTTPRequest) 
LeftAndMain.php:752
SilverStripe\Admin\LeftAndMain->handleRequest(SilverStripe\Control\HTTPRequest) 
AdminRootController.php:123
SilverStripe\Admin\AdminRootController->handleRequest(SilverStripe\Control\HTTPRequest) 
Director.php:361
SilverStripe\Control\Director->SilverStripe\Control\{closure}(SilverStripe\Control\HTTPRequest) 
DetectLocaleMiddleware.php:74
TractorCow\Fluent\Middleware\DetectLocaleMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
InitStateMiddleware.php:54
TractorCow\Fluent\Middleware\InitStateMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
VersionedHTTPMiddleware.php:40
SilverStripe\Versioned\VersionedHTTPMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
BasicAuthMiddleware.php:68
SilverStripe\Security\BasicAuthMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
AuthenticationMiddleware.php:61
SilverStripe\Security\AuthenticationMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
CanonicalURLMiddleware.php:155
SilverStripe\Control\Middleware\CanonicalURLMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
FlushMiddleware.php:26
SilverStripe\Control\Middleware\FlushMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
RequestProcessor.php:66
SilverStripe\Control\RequestProcessor->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
SessionMiddleware.php:20
SilverStripe\Control\Middleware\SessionMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
AllowedHostsMiddleware.php:60
SilverStripe\Control\Middleware\AllowedHostsMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
TrustedProxyMiddleware.php:176
SilverStripe\Control\Middleware\TrustedProxyMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
HTTPMiddlewareAware.php:65
SilverStripe\Control\Director->callMiddleware(SilverStripe\Control\HTTPRequest, Closure) 
Director.php:370
SilverStripe\Control\Director->handleRequest(SilverStripe\Control\HTTPRequest) 
HTTPApplication.php:48
SilverStripe\Control\HTTPApplication->SilverStripe\Control\{closure}(SilverStripe\Control\HTTPRequest) 
call_user_func(Closure, SilverStripe\Control\HTTPRequest) 
HTTPApplication.php:66
SilverStripe\Control\HTTPApplication->SilverStripe\Control\{closure}(SilverStripe\Control\HTTPRequest) 
call_user_func(Closure, SilverStripe\Control\HTTPRequest) 
ErrorControlChainMiddleware.php:56
SilverStripe\Core\Startup\ErrorControlChainMiddleware->SilverStripe\Core\Startup\{closure}(SilverStripe\Core\Startup\ErrorControlChain) 
call_user_func(Closure, SilverStripe\Core\Startup\ErrorControlChain) 
ErrorControlChain.php:236
SilverStripe\Core\Startup\ErrorControlChain->step() 
ErrorControlChain.php:226
SilverStripe\Core\Startup\ErrorControlChain->execute() 
ErrorControlChainMiddleware.php:69
SilverStripe\Core\Startup\ErrorControlChainMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\HTTPApplication->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
HTTPMiddlewareAware.php:65
SilverStripe\Control\HTTPApplication->callMiddleware(SilverStripe\Control\HTTPRequest, Closure) 
HTTPApplication.php:67
SilverStripe\Control\HTTPApplication->execute(SilverStripe\Control\HTTPRequest, Closure, ) 
HTTPApplication.php:49
SilverStripe\Control\HTTPApplication->handle(SilverStripe\Control\HTTPRequest) 
index.php:26
KINKCreative commented 6 years ago

Confirming other Versioned dataobjects including SiteTree experience this. I am unable to unpublish/delete these...

[Emergency] Uncaught SilverStripe\ORM\Connect\DatabaseException: Couldn't run query: DELETE "Element_Live" FROM "Element_Live" LEFT JOIN "Element_Localised_Live" ON "Element_Live"."ID" = "Element_Localised_Live"."RecordID" WHERE ("Element_Live"."ID" = ?) AND ("Element_Localised_Live"."ID" IS NULL) near ""Element_Live"": syntax error
POST /admin/pages/edit/EditForm/10/field/ElementalArea/item/8/ItemEditForm/
Line 64 in /home/torowapo/public_html/vendor/silverstripe/framework/src/ORM/Connect/DBConnector.php

Source
55         if (!empty($sql)) {
56             $formatter = new SQLFormatter();
57             $formattedSQL = $formatter->formatPlain($sql);
58             $msg = "Couldn't run query:\n\n{$formattedSQL}\n\n{$msg}";
59         }
60 
61         if ($errorLevel === E_USER_ERROR) {
62             // Treating errors as exceptions better allows for responding to errors
63             // in code, such as credential checking during installation
64             throw new DatabaseException($msg, 0, null, $sql, $parameters);
65         } else {
66             user_error($msg, $errorLevel);
67         }
68     }
69 
70     /**
Trace
SilverStripe\ORM\Connect\DBConnector->databaseError(Couldn't run query: DELETE "Element_Live" FROM "Element_Live" LEFT JOIN "Element_Localised_Live" ON "Element_Live"."ID" = "Element_Localised_Live"."RecordID" WHERE ("Element_Live"."ID" = ?) AND ("Element_Localised_Live"."ID" IS NULL) near ""Element_Live"": syntax error, 256, DELETE "Element_Live" FROM "Element_Live" LEFT JOIN "Element_Localised_Live" ON "Element_Live"."ID" = "Element_Localised_Live"."RecordID" WHERE ("Element_Live"."ID" = ?) AND ("Element_Localised_Live"."ID" IS NULL), Array) 
SQLite3Connector.php:149
SilverStripe\SQLite\SQLite3Connector->preparedQuery(DELETE "Element_Live" FROM "Element_Live" LEFT JOIN "Element_Localised_Live" ON "Element_Live"."ID" = "Element_Localised_Live"."RecordID" WHERE ("Element_Live"."ID" = ?) AND ("Element_Localised_Live"."ID" IS NULL), Array, 256) 
Database.php:168
SilverStripe\ORM\Connect\Database->SilverStripe\ORM\Connect\{closure}(DELETE "Element_Live" FROM "Element_Live" LEFT JOIN "Element_Localised_Live" ON "Element_Live"."ID" = "Element_Localised_Live"."RecordID" WHERE ("Element_Live"."ID" = ?) AND ("Element_Localised_Live"."ID" IS NULL)) 
Database.php:222
SilverStripe\ORM\Connect\Database->benchmarkQuery(DELETE "Element_Live" FROM "Element_Live" LEFT JOIN "Element_Localised_Live" ON "Element_Live"."ID" = "Element_Localised_Live"."RecordID" WHERE ("Element_Live"."ID" = ?) AND ("Element_Localised_Live"."ID" IS NULL), Closure, Array) 
Database.php:170
SilverStripe\ORM\Connect\Database->preparedQuery(DELETE "Element_Live" FROM "Element_Live" LEFT JOIN "Element_Localised_Live" ON "Element_Live"."ID" = "Element_Localised_Live"."RecordID" WHERE ("Element_Live"."ID" = ?) AND ("Element_Localised_Live"."ID" IS NULL), Array, 256) 
DB.php:445
SilverStripe\ORM\DB::prepared_query(DELETE "Element_Live" FROM "Element_Live" LEFT JOIN "Element_Localised_Live" ON "Element_Live"."ID" = "Element_Localised_Live"."RecordID" WHERE ("Element_Live"."ID" = ?) AND ("Element_Localised_Live"."ID" IS NULL), Array) 
SQLExpression.php:115
SilverStripe\ORM\Queries\SQLExpression->execute() 
FluentExtension.php:472
TractorCow\Fluent\Extension\FluentExtension->updateDeleteTables(Array, SELECT DISTINCT "Element_Live"."ClassName", "Element_Live"."LastEdited", "Element_Live"."Created", CASE WHEN "Element_Localised_es_MX"."ID" IS NOT NULL THEN "Element_Localised_es_MX"."Title" WHEN "Element_Localised_en_US"."ID" IS NOT NULL THEN "Element_Localised_en_US"."Title" ELSE "Element_Live"."Title" END AS "Title", "Element_Live"."ShowTitle", "Element_Live"."Sort", "Element_Live"."ExtraClass", "Element_Live"."Style", "Element_Live"."Version", "Element_Live"."ParentID", CASE WHEN "PhotoSectionElement_Localised_es_MX"."ID" IS NOT NULL THEN "PhotoSectionElement_Localised_es_MX"."Content" WHEN "PhotoSectionElement_Localised_en_US"."ID" IS NOT NULL THEN "PhotoSectionElement_Localised_en_US"."Content" ELSE "PhotoSectionElement_Live"."Content" END AS "Content", "PhotoSectionElement_Live"."ImageID", "Element_Live"."ID", CASE WHEN "Element_Live"."ClassName" IS NOT NULL THEN "Element_Live"."ClassName" ELSE 'DNADesign\Elemental\Models\BaseElement' END AS "RecordClassName", 'es_MX' AS "Locale", CASE WHEN "PhotoSectionElement_Localised_es_MX"."ID" IS NOT NULL THEN 'es_MX' WHEN "PhotoSectionElement_Localised_en_US"."ID" IS NOT NULL THEN 'en_US' ELSE NULL END AS "SourceLocale" FROM "Element_Live" LEFT JOIN "PhotoSectionElement_Live" ON "PhotoSectionElement_Live"."ID" = "Element_Live"."ID" LEFT JOIN "Element_Localised_Live" AS "Element_Localised_es_MX" ON "Element_Live"."ID" = "Element_Localised_es_MX"."RecordID" AND "Element_Localised_es_MX"."Locale" = ? LEFT JOIN "Element_Localised_Live" AS "Element_Localised_en_US" ON "Element_Live"."ID" = "Element_Localised_en_US"."RecordID" AND "Element_Localised_en_US"."Locale" = ? LEFT JOIN "PhotoSectionElement_Localised_Live" AS "PhotoSectionElement_Localised_es_MX" ON "PhotoSectionElement_Live"."ID" = "PhotoSectionElement_Localised_es_MX"."RecordID" AND "PhotoSectionElement_Localised_es_MX"."Locale" = ? LEFT JOIN "PhotoSectionElement_Localised_Live" AS "PhotoSectionElement_Localised_en_US" ON "PhotoSectionElement_Live"."ID" = "PhotoSectionElement_Localised_en_US"."RecordID" AND "PhotoSectionElement_Localised_en_US"."Locale" = ? WHERE ("Element_Live"."ID" = ?) AND ("Element_Live"."ClassName" IN (?)) ORDER BY "Element_Live"."Sort" ASC <array ( 0 => 'es_MX', 1 => 'en_US', 2 => 'es_MX', 3 => 'en_US', 4 => 8, 5 => 'PhotoSectionElement', )>, , , , , ) 
Extensible.php:472
SilverStripe\View\ViewableData->extend(updateDeleteTables, Array, SELECT DISTINCT "Element_Live"."ClassName", "Element_Live"."LastEdited", "Element_Live"."Created", CASE WHEN "Element_Localised_es_MX"."ID" IS NOT NULL THEN "Element_Localised_es_MX"."Title" WHEN "Element_Localised_en_US"."ID" IS NOT NULL THEN "Element_Localised_en_US"."Title" ELSE "Element_Live"."Title" END AS "Title", "Element_Live"."ShowTitle", "Element_Live"."Sort", "Element_Live"."ExtraClass", "Element_Live"."Style", "Element_Live"."Version", "Element_Live"."ParentID", CASE WHEN "PhotoSectionElement_Localised_es_MX"."ID" IS NOT NULL THEN "PhotoSectionElement_Localised_es_MX"."Content" WHEN "PhotoSectionElement_Localised_en_US"."ID" IS NOT NULL THEN "PhotoSectionElement_Localised_en_US"."Content" ELSE "PhotoSectionElement_Live"."Content" END AS "Content", "PhotoSectionElement_Live"."ImageID", "Element_Live"."ID", CASE WHEN "Element_Live"."ClassName" IS NOT NULL THEN "Element_Live"."ClassName" ELSE 'DNADesign\Elemental\Models\BaseElement' END AS "RecordClassName", 'es_MX' AS "Locale", CASE WHEN "PhotoSectionElement_Localised_es_MX"."ID" IS NOT NULL THEN 'es_MX' WHEN "PhotoSectionElement_Localised_en_US"."ID" IS NOT NULL THEN 'en_US' ELSE NULL END AS "SourceLocale" FROM "Element_Live" LEFT JOIN "PhotoSectionElement_Live" ON "PhotoSectionElement_Live"."ID" = "Element_Live"."ID" LEFT JOIN "Element_Localised_Live" AS "Element_Localised_es_MX" ON "Element_Live"."ID" = "Element_Localised_es_MX"."RecordID" AND "Element_Localised_es_MX"."Locale" = ? LEFT JOIN "Element_Localised_Live" AS "Element_Localised_en_US" ON "Element_Live"."ID" = "Element_Localised_en_US"."RecordID" AND "Element_Localised_en_US"."Locale" = ? LEFT JOIN "PhotoSectionElement_Localised_Live" AS "PhotoSectionElement_Localised_es_MX" ON "PhotoSectionElement_Live"."ID" = "PhotoSectionElement_Localised_es_MX"."RecordID" AND "PhotoSectionElement_Localised_es_MX"."Locale" = ? LEFT JOIN "PhotoSectionElement_Localised_Live" AS "PhotoSectionElement_Localised_en_US" ON "PhotoSectionElement_Live"."ID" = "PhotoSectionElement_Localised_en_US"."RecordID" AND "PhotoSectionElement_Localised_en_US"."Locale" = ? WHERE ("Element_Live"."ID" = ?) AND ("Element_Live"."ClassName" IN (?)) ORDER BY "Element_Live"."Sort" ASC <array ( 0 => 'es_MX', 1 => 'en_US', 2 => 'es_MX', 3 => 'en_US', 4 => 8, 5 => 'PhotoSectionElement', )>) 
DataObject.php:1604
SilverStripe\ORM\DataObject->delete() 
Versioned.php:1382
SilverStripe\Versioned\Versioned->doUnpublish() 
call_user_func_array(Array, Array) 
Extensible.php:144
SilverStripe\View\ViewableData->SilverStripe\Core\{closure}(PhotoSectionElement, Array) 
CustomMethods.php:61
SilverStripe\View\ViewableData->__call(doUnpublish, Array) 
VersionedGridFieldItemRequest.php:202
SilverStripe\Versioned\VersionedGridFieldItemRequest->doUnpublish(Array, SilverStripe\Forms\Form, SilverStripe\Control\HTTPRequest, SilverStripe\Forms\FormRequestHandler) 
FormRequestHandler.php:231
SilverStripe\Forms\FormRequestHandler->httpSubmission(SilverStripe\Control\HTTPRequest) 
RequestHandler.php:319
SilverStripe\Control\RequestHandler->handleAction(SilverStripe\Control\HTTPRequest, httpSubmission) 
RequestHandler.php:201
SilverStripe\Control\RequestHandler->handleRequest(SilverStripe\Control\HTTPRequest) 
RequestHandler.php:225
SilverStripe\Control\RequestHandler->handleRequest(SilverStripe\Control\HTTPRequest) 
GridFieldDetailForm.php:118
SilverStripe\Forms\GridField\GridFieldDetailForm->handleItem(SilverStripe\Forms\GridField\GridField, SilverStripe\Control\HTTPRequest) 
GridField.php:1031
SilverStripe\Forms\GridField\GridField->handleRequest(SilverStripe\Control\HTTPRequest) 
RequestHandler.php:225
SilverStripe\Control\RequestHandler->handleRequest(SilverStripe\Control\HTTPRequest) 
RequestHandler.php:225
SilverStripe\Control\RequestHandler->handleRequest(SilverStripe\Control\HTTPRequest) 
Controller.php:212
SilverStripe\Control\Controller->handleRequest(SilverStripe\Control\HTTPRequest) 
LeftAndMain.php:752
SilverStripe\Admin\LeftAndMain->handleRequest(SilverStripe\Control\HTTPRequest) 
AdminRootController.php:123
SilverStripe\Admin\AdminRootController->handleRequest(SilverStripe\Control\HTTPRequest) 
Director.php:361
SilverStripe\Control\Director->SilverStripe\Control\{closure}(SilverStripe\Control\HTTPRequest) 
DetectLocaleMiddleware.php:74
TractorCow\Fluent\Middleware\DetectLocaleMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
InitStateMiddleware.php:54
TractorCow\Fluent\Middleware\InitStateMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
VersionedHTTPMiddleware.php:40
SilverStripe\Versioned\VersionedHTTPMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
BasicAuthMiddleware.php:68
SilverStripe\Security\BasicAuthMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
AuthenticationMiddleware.php:61
SilverStripe\Security\AuthenticationMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
CanonicalURLMiddleware.php:155
SilverStripe\Control\Middleware\CanonicalURLMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
FlushMiddleware.php:26
SilverStripe\Control\Middleware\FlushMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
RequestProcessor.php:66
SilverStripe\Control\RequestProcessor->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
SessionMiddleware.php:20
SilverStripe\Control\Middleware\SessionMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
AllowedHostsMiddleware.php:60
SilverStripe\Control\Middleware\AllowedHostsMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
TrustedProxyMiddleware.php:176
SilverStripe\Control\Middleware\TrustedProxyMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\Director->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
HTTPMiddlewareAware.php:65
SilverStripe\Control\Director->callMiddleware(SilverStripe\Control\HTTPRequest, Closure) 
Director.php:370
SilverStripe\Control\Director->handleRequest(SilverStripe\Control\HTTPRequest) 
HTTPApplication.php:48
SilverStripe\Control\HTTPApplication->SilverStripe\Control\{closure}(SilverStripe\Control\HTTPRequest) 
call_user_func(Closure, SilverStripe\Control\HTTPRequest) 
HTTPApplication.php:66
SilverStripe\Control\HTTPApplication->SilverStripe\Control\{closure}(SilverStripe\Control\HTTPRequest) 
call_user_func(Closure, SilverStripe\Control\HTTPRequest) 
ErrorControlChainMiddleware.php:56
SilverStripe\Core\Startup\ErrorControlChainMiddleware->SilverStripe\Core\Startup\{closure}(SilverStripe\Core\Startup\ErrorControlChain) 
call_user_func(Closure, SilverStripe\Core\Startup\ErrorControlChain) 
ErrorControlChain.php:236
SilverStripe\Core\Startup\ErrorControlChain->step() 
ErrorControlChain.php:226
SilverStripe\Core\Startup\ErrorControlChain->execute() 
ErrorControlChainMiddleware.php:69
SilverStripe\Core\Startup\ErrorControlChainMiddleware->process(SilverStripe\Control\HTTPRequest, Closure) 
HTTPMiddlewareAware.php:62
SilverStripe\Control\HTTPApplication->SilverStripe\Control\Middleware\{closure}(SilverStripe\Control\HTTPRequest) 
HTTPMiddlewareAware.php:65
SilverStripe\Control\HTTPApplication->callMiddleware(SilverStripe\Control\HTTPRequest, Closure) 
HTTPApplication.php:67
SilverStripe\Control\HTTPApplication->execute(SilverStripe\Control\HTTPRequest, Closure, ) 
HTTPApplication.php:49
SilverStripe\Control\HTTPApplication->handle(SilverStripe\Control\HTTPRequest) 
index.php:26
KINKCreative commented 6 years ago

I was toying around --- based on some insight, a way to handle the left join on the delete is to do a select...

// Line 419 of silverstripe-fluent/src/Extension/FluentExtension.php
/**
     * Override delete behaviour
     *
     * @param array $queriedTables
     */
    public function updateDeleteTables(&$queriedTables)
    {
        // Ensure a locale exists
        $locale = Locale::getCurrentLocale();
        if (!$locale) {
            return;
        }

        // Fluent takes over deletion of objects
        $queriedTables = [];
        $localisedTables = $this->getLocalisedTables();
        $tableClasses = ClassInfo::ancestry($this->owner, true);
        foreach ($tableClasses as $class) {
            // Check main table name
            $table = DataObject::getSchema()->tableName($class);

            // Create root table delete
            $rootTable = $this->getDeleteTableTarget($table);
            $rootDelete = SQLDelete::create("\"{$rootTable}\"")
                ->addWhere(["\"{$rootTable}\".\"ID\"" => $this->owner->ID]);

            // If table isn't localised, simple delete
            if (!isset($localisedTables[$table])) {
                $rootDelete->execute();
                continue;
            }

            // Remove _Localised record
            $localisedTable = $this->getDeleteTableTarget($table, $locale);
            $localisedDelete = SQLDelete::create(
                "\"{$localisedTable}\"",
                [
                    '"Locale"' => $locale->Locale,
                    '"RecordID"' => $this->owner->ID,
                ]
            );
            $localisedDelete->execute();

            // Remove orphaned ONLY base table (delete after deleting last localised row)
            // Note: No "Locale" filter as we are excluding any tables that have any localised records
            $rootDelete
                ->addWhere("\"{$rootTable}\".\"ID\" IN (SELECT \"{$rootTable}\".\"ID\" FROM \"{$rootTable}\" LEFT JOIN \"{$localisedTable}\" ON \"{$rootTable}\".\"ID\" = \"{$localisedTable}\".\"RecordID\" WHERE \"{$localisedTable}\".\"ID\" IS NULL)")
                ->execute();
        }
    }

Got this working after quite a bit of playing around but I am not 100% sure if this solves everything.

tractorcow commented 6 years ago

Yes I've spent a bit of time trying to design a "cross-db friendly" way of doing multi-table deletes, but something more advanced needs to exist first in core; Namely the DBQueryBuilder needs to better support join deletes. Some dbs support direct join, some require implicit join format, some require subquery conditionals. It's a problem yet to be solved properly.

robbieaverill commented 5 years ago

@KINKCreative would you like to move this issue into silverstripe/framework?