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

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

SQL error when GridField sorts on <DedrivedClassName>.<BaseClassField>

946 Views

AlphaCactus

Community Member, 12 Posts

10 February 2017 at 9:05am

Edited: 10/02/2017 9:07am

Before posting an issue on GitHub, i just wanted a sanity check to make sure there isn't a workaround on my end.

Given this class Structure:

class Pocketable extends DataObject {
	private static $db = array(
		'Title' => 'Varchar(128)'
	);
}

class FeedParser extends Pocketable {}

class Blueprint extends Pocketable {
	private static $has_one = array(
		'FeedParser' => 'FeedParser'
	);
	static $field_labels = array(
		'FeedParser.Title' => 'Feed Parser'
	);
	static $summary_fields = array(
		'FeedParser.Title'
	);
}

Blueprint is a managed model in a Model Admin and the GridField generated by ModelAdmin automatically makes the Feed Parser column sortable. However, if the user clicks on that column to actually attempt to sort based on that column, this error is returned (line numbers apply to 3.5.2):

SELECT DISTINCT "Pocketable"."ClassName", "Pocketable"."LastEdited", "Pocketable"."Created", "Pocketable"."Title", "Blueprint"."FeedParserID", "Pocketable"."ID", 
			CASE WHEN "Pocketable"."ClassName" IS NOT NULL THEN "Pocketable"."ClassName"
			ELSE 'Pocketable' END AS "RecordClassName", "FeedParser"."Title" AS "_SortColumn0"

FROM "Pocketable"
LEFT JOIN "Blueprint" ON "Blueprint"."ID" = "Pocketable"."ID"
LEFT JOIN "FeedParser" ON "FeedParser"."ID" = "Blueprint"."FeedParserID"

WHERE ("Pocketable"."ClassName" IN (?))

ORDER BY "_SortColumn0" ASC

LIMIT 500

Unknown column 'FeedParser.Title' in 'field list'
IN POST /admin/template/Blueprint/EditForm/field/Blueprint
Line 55 in \framework\model\connect\DBConnector.php

Source
======
  46:  		if (!empty($sql)) {
  47:  			$formatter = new SQLFormatter();
  48:  			$formattedSQL = $formatter->formatPlain($sql);
  49:  			$msg = "Couldn't run query:\n\n{$formattedSQL}\n\n{$msg}";
  50:  		}
  51:  
  52:  		if($errorLevel === E_USER_ERROR) {
  53:  			// Treating errors as exceptions better allows for responding to errors
  54:  			// in code, such as credential checking during installation
* 55:  			throw new SS_DatabaseException($msg, 0, null, $sql, $parameters);
  56:  		} else {
  57:  			user_error($msg, $errorLevel);
  58:  		}
  59:  	}
  60:  
  61:  	/**

Trace
=====
DBConnector->databaseError(Unknown column 'FeedParser.Title' in 'field list',256,SELECT DISTINCT "Pocketable"."ClassName", "Pocketable"."LastEdited", "Pocketable"."Created", "Pocketable"."Title", "Blueprint"."FeedParserID", "Blueprint"."UploadFolderID", "Pocketable"."ID", 
			CASE WHEN "Pocketable"."ClassName" IS NOT NULL THEN "Pocketable"."ClassName"
			ELSE 'Pocketable' END AS "RecordClassName", "FeedParser"."Title" AS "_SortColumn0"
 FROM "Pocketable" LEFT JOIN "Blueprint" ON "Blueprint"."ID" = "Pocketable"."ID" LEFT JOIN "FeedParser" ON "FeedParser"."ID" = "Blueprint"."FeedParserID"
 WHERE ("Pocketable"."ClassName" IN (?))
 ORDER BY "_SortColumn0" ASC
 LIMIT 500,Array)
MySQLiConnector.php:249

MySQLiConnector->preparedQuery(SELECT DISTINCT "Pocketable"."ClassName", "Pocketable"."LastEdited", "Pocketable"."Created", "Pocketable"."Title", "Blueprint"."FeedParserID", "Blueprint"."UploadFolderID", "Pocketable"."ID", 
			CASE WHEN "Pocketable"."ClassName" IS NOT NULL THEN "Pocketable"."ClassName"
			ELSE 'Pocketable' END AS "RecordClassName", "FeedParser"."Title" AS "_SortColumn0"
 FROM "Pocketable" LEFT JOIN "Blueprint" ON "Blueprint"."ID" = "Pocketable"."ID" LEFT JOIN "FeedParser" ON "FeedParser"."ID" = "Blueprint"."FeedParserID"
 WHERE ("Pocketable"."ClassName" IN (?))
 ORDER BY "_SortColumn0" ASC
 LIMIT 500,Array,256)
Database.php:143

SS_Database->{closure}(SELECT DISTINCT "Pocketable"."ClassName", "Pocketable"."LastEdited", "Pocketable"."Created", "Pocketable"."Title", "Blueprint"."FeedParserID", "Blueprint"."UploadFolderID", "Pocketable"."ID", 
			CASE WHEN "Pocketable"."ClassName" IS NOT NULL THEN "Pocketable"."ClassName"
			ELSE 'Pocketable' END AS "RecordClassName", "FeedParser"."Title" AS "_SortColumn0"
 FROM "Pocketable" LEFT JOIN "Blueprint" ON "Blueprint"."ID" = "Pocketable"."ID" LEFT JOIN "FeedParser" ON "FeedParser"."ID" = "Blueprint"."FeedParserID"
 WHERE ("Pocketable"."ClassName" IN (?))
 ORDER BY "_SortColumn0" ASC
 LIMIT 500)
Database.php:193

SS_Database->benchmarkQuery(SELECT DISTINCT "Pocketable"."ClassName", "Pocketable"."LastEdited", "Pocketable"."Created", "Pocketable"."Title", "Blueprint"."FeedParserID", "Blueprint"."UploadFolderID", "Pocketable"."ID", 
			CASE WHEN "Pocketable"."ClassName" IS NOT NULL THEN "Pocketable"."ClassName"
			ELSE 'Pocketable' END AS "RecordClassName", "FeedParser"."Title" AS "_SortColumn0"
 FROM "Pocketable" LEFT JOIN "Blueprint" ON "Blueprint"."ID" = "Pocketable"."ID" LEFT JOIN "FeedParser" ON "FeedParser"."ID" = "Blueprint"."FeedParserID"
 WHERE ("Pocketable"."ClassName" IN (?))
 ORDER BY "_SortColumn0" ASC
 LIMIT 500,Closure,Array)
Database.php:146

SS_Database->preparedQuery(SELECT DISTINCT "Pocketable"."ClassName", "Pocketable"."LastEdited", "Pocketable"."Created", "Pocketable"."Title", "Blueprint"."FeedParserID", "Blueprint"."UploadFolderID", "Pocketable"."ID", 
			CASE WHEN "Pocketable"."ClassName" IS NOT NULL THEN "Pocketable"."ClassName"
			ELSE 'Pocketable' END AS "RecordClassName", "FeedParser"."Title" AS "_SortColumn0"
 FROM "Pocketable" LEFT JOIN "Blueprint" ON "Blueprint"."ID" = "Pocketable"."ID" LEFT JOIN "FeedParser" ON "FeedParser"."ID" = "Blueprint"."FeedParserID"
 WHERE ("Pocketable"."ClassName" IN (?))
 ORDER BY "_SortColumn0" ASC
 LIMIT 500,Array,256)
DB.php:365

DB::prepared_query(SELECT DISTINCT "Pocketable"."ClassName", "Pocketable"."LastEdited", "Pocketable"."Created", "Pocketable"."Title", "Blueprint"."FeedParserID", "Blueprint"."UploadFolderID", "Pocketable"."ID", 
			CASE WHEN "Pocketable"."ClassName" IS NOT NULL THEN "Pocketable"."ClassName"
			ELSE 'Pocketable' END AS "RecordClassName", "FeedParser"."Title" AS "_SortColumn0"
 FROM "Pocketable" LEFT JOIN "Blueprint" ON "Blueprint"."ID" = "Pocketable"."ID" LEFT JOIN "FeedParser" ON "FeedParser"."ID" = "Blueprint"."FeedParserID"
 WHERE ("Pocketable"."ClassName" IN (?))
 ORDER BY "_SortColumn0" ASC
 LIMIT 500,Array)
SQLExpression.php:121

SQLExpression->execute()
DataList.php:666

DataList->toArray()
DataList.php:760

DataList->getIterator()
GridField.php:404

GridField->FieldHolder()
GridField.php:888

GridField->gridFieldAlterAction(Array,CMSForm,SS_HTTPRequest)
GridField.php:120

GridField->index(SS_HTTPRequest)
RequestHandler.php:288

RequestHandler->handleAction(SS_HTTPRequest,index)
RequestHandler.php:200

RequestHandler->handleRequest(SS_HTTPRequest,DataModel)
GridField.php:1019

GridField->handleRequest(SS_HTTPRequest,DataModel)
RequestHandler.php:222

RequestHandler->handleRequest(SS_HTTPRequest,DataModel)
RequestHandler.php:222

RequestHandler->handleRequest(SS_HTTPRequest,DataModel)
Controller.php:158

Controller->handleRequest(SS_HTTPRequest,DataModel)
LeftAndMain.php:464

LeftAndMain->handleRequest(SS_HTTPRequest,DataModel)
AdminRootController.php:92

AdminRootController->handleRequest(SS_HTTPRequest,DataModel)
Director.php:385

Director::handleRequest(SS_HTTPRequest,Session,DataModel)
Director.php:149

Director::direct(/admin/template/Blueprint/EditForm/field/Blueprint,DataModel)
main.php:191

It appears that the query builder is not taking into account that the Title field is in the Pocketable Table rather than the derived class FeedParser table.
Experienced this issue in builds 3.3.2 and 3.5.2
[Edited for code syntax]

Return to top