UndefinedOffset/SortableGridField

Column 'SortOrder' in field list is ambiguous in a Many_Many relationship Gridfield

Closed this issue · 1 comments

i have this simple code

class ReviewToplist extends DataObject implements PermissionProvider {
private static $many_many = array(
'ToplistItems' => 'ReviewToplistItem'
);

private static $many_many_extraFields=array(
    'ReviewToplistItem'=>array(
        'SortOrder'=>'Int'
    )
);

public function getCMSFields() {
    $fields = parent::getCMSFields();

    if( $this->exists() ) {

        $config = GridFieldConfig_RelationEditor::create();

$config->getComponentByType('GridFieldAddExistingAutocompleter')->setSearchFields(array('Affiliate.Name'))->setResultsFormat('$Affiliate');
$config->addComponent(new GridFieldSortableRows('SortOrder'));

        $gridField =GridField::create( 'ToplistItems', 'ToplistItems', $this->ToplistItems(), $config);

        $fields->addFieldToTab("Root.Main", $gridField); 
    } else {
        $fields->addFieldToTab( 'Root.Main', new LiteralField( 'ToplistItemsMessage', '<p>You must Add the toplist before you can add items!</p>' ) );
    }

    return $fields;
}

public function ToplistItems() {
    return $this->getManyManyComponents('ToplistItems')->sort('SortOrder');
}

}

class ReviewToplistItem extends DataObject {
private static $belongs_many_many = array(
'ReviewToplist' => 'ReviewToplist'
);
}

and when i tried to access that in the admin.. it returns an error like this:

ERROR [User Error]: Couldn't run query:
SELECT DISTINCT MAX("SortOrder")
FROM "ReviewToplistItem"
INNER JOIN "ReviewToplist_ToplistItems" ON "ReviewToplist_ToplistItems"."ReviewToplistItemID" = "ReviewToplistItem"."ID"
WHERE ("ReviewToplist_ToplistItems"."ReviewToplistID" = '18')

Column 'SortOrder' in field list is ambiguous

Any idea why? I just followed the documentation for the many_many relationship here

I've seen this before (issue #50) its a bug/limitation in the SilverStripe core. Basically likely your ReviewTopListItem table also has a column called SortOrder so when I try to get the SortOrder for the relationship the core of SilverStripe does not specifically call the SortOrder column on the relationship table. My suggestion is to either drop the SortOrder column on the ReviewTopListItem table or simply change your relationship's sort column to say SortIndex (aka make it unique).