CODEWITHSUNDEEP
silverstripe
allenmccabe
allenmccabe

Reputation: 43

SortableGridField causing SQL error

After a lot of searching, I found: https://github.com/UndefinedOffset/SortableGridField/blob/master/docs/ManyManyExample.md for an example of usage.

However, implementing this code results in:

[User Error] Couldn't run query: SELECT DISTINCT MAX("SortOrder") FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') Column 'SortOrder' in field list is ambiguous

I'm using SS3 and have SortableGridField installed (though no messages indicated successful installation upon running dev/build/?flush=all).

With Dev mode off, the CMS does not load ("Server Error"), however with Dev mode enabled, I get the above SQL error. Regardless, it seems to be working properly, I just need to find out why this error is being thrown.

UPDATE

Below is the top part of the stack traces generated on this error (there are 2 [User Error] blue headers on the page):

[User Error] Couldn't run query: SELECT DISTINCT MAX("SortOrder") FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') Column 'SortOrder' in field list is ambiguous
Couldn't run query: SELECT DISTINCT MAX("SortOrder") FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') Column 'SortOrder' in field list is ambiguous 
MySQLDatabase.php:580
MySQLDatabase->databaseError(Couldn't run query: SELECT DISTINCT MAX("SortOrder") FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') | Column 'SortOrder' in field list is ambiguous,256) 
MySQLDatabase.php:132
MySQLDatabase->query(SELECT DISTINCT MAX("SortOrder") FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2'),256) 
DB.php:200
DB::query(SELECT DISTINCT MAX("SortOrder") FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2'),256) 
SQLQuery.php:949
SQLQuery->execute() 
DataQuery.php:372
DataQuery->aggregate(MAX("SortOrder")) 
DataQuery.php:338
DataQuery->max(SortOrder) 
DataList.php:676
DataList->max(SortOrder) 
GridFieldSortableRows.php:148
GridFieldSortableRows->fixSortColumn(GridField,ManyManyList) 
GridFieldSortableRows.php:99
GridFieldSortableRows->getManipulatedData(GridField,ManyManyList) 
GridField.php:215
GridField->getManipulatedList() 
GridField.php:255
GridField->FieldHolder() 
...

[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "Sidebar"."ID") AS "0" FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') AND ("SortOrder"=0) Column 'SortOrder' in where clause is ambiguous

Couldn't run query: SELECT DISTINCT count(DISTINCT "Sidebar"."ID") AS "0" FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') AND ("SortOrder"=0) Column 'SortOrder' in where clause is ambiguous 
MySQLDatabase.php:580
MySQLDatabase->databaseError(Couldn't run query: SELECT DISTINCT count(DISTINCT "Sidebar"."ID") AS "0" FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') AND ("SortOrder"=0) | Column 'SortOrder' in where clause is ambiguous,256) 
MySQLDatabase.php:132
MySQLDatabase->query(SELECT DISTINCT count(DISTINCT "Sidebar"."ID") AS "0" FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') AND ("SortOrder"=0),256) 
DB.php:200
DB::query(SELECT DISTINCT count(DISTINCT "Sidebar"."ID") AS "0" FROM "Sidebar" INNER JOIN "TwoColumn_Sidebars" ON "TwoColumn_Sidebars"."SidebarID" = "Sidebar"."ID" WHERE ("TwoColumn_Sidebars"."TwoColumnID" = '2') AND ("SortOrder"=0),256) 
SQLQuery.php:949
SQLQuery->execute() 
SQLQuery.php:1054
SQLQuery->count(DISTINCT "Sidebar"."ID") 
DataQuery.php:329
DataQuery->count() 
DataList.php:666
DataList->count() 
GridFieldSortableRows.php:150
GridFieldSortableRows->fixSortColumn(GridField,ManyManyList) 
GridFieldSortableRows.php:99
GridFieldSortableRows->getManipulatedData(GridField,ManyManyList) 
GridField.php:215
GridField->getManipulatedList() 
GridField.php:255
GridField->FieldHolder() 
ViewableData.php:366
ViewableData->obj(FieldHolder,,,1) 
...

Upvotes: 0

Views: 972

Answers (4)

Isaac Rankin
Isaac Rankin

Reputation: 121

I have experienced this problem and it was being caused by duplicate SortOrder columns.

You could manually delete the SortOrder column on the Sidebar DataObject table and remove the field from the class.

Only keep the SortOrder field on the relationship table by adding this to TwoColumn:

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

Upvotes: 1

gaethofs
gaethofs

Reputation: 21

For those who are still encountering the same problem after dumping the related tables from the database, make sure you haven't set any 'SortOrder' field on the '$belongs_many_many' class of the relation. When following the example of this module, this field is created by setting the '$many_many_extraFields' in the '$many_many' class.

UPDATE: The difference is in the type of relationship you're creating. In the examples of the module itself you can see that, for a has_many relationship, the second object (TestObject) still explicitly defines the 'SortOrder' field.

/*** TestPage.php ***/
class TestPage extends Page {
    public static $has_many=array(
        'TestObjects'=>'TestObject'
    );
}


/*** TestObject.php ***/
class TestObject extends DataObject {
    public static $db=array(
        'Title'=>'Text',
        'SortOrder'=>'Int'
    );

    public static $has_one=array(
        'Parent'=>'TestPage'
    );

    public static $default_sort='SortOrder';
}

For the many_many relationship however, this field is not explicitly defined for the class (TestObject), but it is defined on the object relationship.

/*** TestPage.php ***/
class TestPage extends Page {
    public static $many_many=array(
        'TestObjects'=>'TestObject'
    );

    public static $many_many_extraFields=array(
        'TestObjects'=>array(
            'SortOrder'=>'Int'
        )
    );

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


/*** TestObject.php ***/
class TestObject extends DataObject {
    public static $db=array(
        'Title'=>'Text'
    );

    public static $belongs_many_many=array(
        'TestPages'=>'TestPage'
    );
}

You can see the 'TestObject' no longer defines the 'SortOrder' field, but that it is now defined on the relationship in the '$many_many_extraFields' method. If the 'SortOrder' field is defined as in '$db' also, the database wil have two 'SortOrder' fields for this relationship, resulting in the error above.

Upvotes: 0

Tanger
Tanger

Reputation: 31

For anybody encountering this issue, it can be resolved by dumping both your DataObject and many_many relation tables, ie the SideBar and TwoColumn_SideBar tables. Both tables will likely have a SortOrder Column and the mysql query can't figure out which one you want.

Upvotes: 3

munomono
munomono

Reputation: 1245

I've used ajshorts gridfieldextensions after I had troubles with SortableGridField. https://github.com/ajshort/silverstripe-gridfieldextensions

Upvotes: 1

Related Questions