Skip to main content

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

DataObjectManager Module

Discuss the DataObjectManager module, and the related ImageGallery module.

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

DataObjectManager unable to sort joined Fields ?


Reply

8 Posts   2779 Views

Avatar
Rufinus

16 July 2009 at 12:33am (Last edited: 16 February 2012 11:16am), Community Member, 6 Posts

Hi,

Imaging the following DataObjectManager Setting:

/** Kurs Tab **/
$pl=new Planitem();
$planitem_fields=$pl->getCMSFields_forPopup($parentid);
$kurs_field = new DataObjectManager(
$this,
'Kurse',
'Planitem',
array('Kursname.Name'=>'Titel/Name','date'=>'Datum','start'=>'Start','end'=>'Ende','level'=>'Level','Kurskategorien.cat'=>'Kategorie','Trainer.Name'=>'Trainer'),
$planitem_fields,
'Planitem.clubID='.$parentid,
'',//,
'
Left Join Trainer ON "Planitem.trainerID=Trainer.ID"
Left Join Kursname "Planitem.kursnameID=Kursname.ID"
Left Join Kurskategorien ON "Planitem.kurskategorienID=Kurskategorien.ID"
'
);

All fields which came from a joined table. like "Title/Name", "Kategorie" and "Trainer" can't be sorted in the backend.
The table header field has no link. The other fields sort normal. Setting a default sort at one of these fields doenst work either. (without error)

Any hints ?

TIA
Rufinus

Avatar
UncleCheese

16 July 2009 at 2:11am (Last edited: 16 February 2012 11:16am), 4085 Posts

Can you confirm that this bug does or does not work when using a ComplexTableField in lieu of a DataObjectManager? Thanks.

Also, you might want to clean up your code before posting it. We try to keep a family-friendly atmosphere in the SS forums.

Avatar
Rufinus

16 July 2009 at 6:40am (Last edited: 16 February 2012 11:16am), Community Member, 6 Posts

Hi,

when i use ComplexTableField instead of DataObjectManager i get the table but no links in header column for sorting. (dont know if this is normal or not, never used ComplexTableField)

Funny enough the links in CTF are working, but are not loaded where they should be. eg. paging next displays the table on a new site, with no SS styles.

tia
Rufinus

PS: sorry, i edited the post. this was for debuging purpose only, and it seems i have forgot to remove it from my code to.

Avatar
Rufinus

20 July 2009 at 10:06pm (Last edited: 16 February 2012 11:16am), Community Member, 6 Posts

Any idea UncleCheese ?

Avatar
UncleCheese

22 July 2009 at 2:37am (Last edited: 16 February 2012 11:16am), 4085 Posts

I've been trying to replicate your use case, but I'm having a hard time, mostly because I've never used a join clause in a DOM or CTF field. The error I'm getting is that the joined table is creating an unknown field SQL error. Use this as an example.

Join clause:
"LEFT JOIN File ON File.ID = MyPage.DocumentID"

My headers array:

array(
'PageField1' => 'Some field on my page',
'PageField2' => 'Another field on my page',
'File.Title' => 'File title'
);

Results in error "Method File() does not exist on MyPage"

If I change that to just 'File' => 'File title' then I get "Unknown column File on MyPage";

What I'm wondering is, do you already have the named relations for Trainer, Kursname, and Kurskategorien? Because Silverstripe will automatically look for RelationName.Field when using that syntax in your headers array. That is, if your page is set up like:

$has_one ('Trainer' => 'TrainerObject')

Then putting "Trainer.Name" in your headers array will work magically, with no need to use a join clause.

Could you clarify?

Avatar
monkee

18 May 2010 at 3:48am (Last edited: 16 February 2012 11:16am), Community Member, 20 Posts

> $has_one ('Trainer' => 'TrainerObject')
> Then putting "Trainer.Name" in your headers array will work magically, with no need to use a join clause.

Thx, i was not aware of that.

Like this Columns get filled correctly... but are no more sortable (sadly). Any Idea how to do that?

Another Way is to include getters in the Dataobject Class:

public function getAuthorName() {
      
   $author = $this->Author(); // from $has_one member...
   $author = $var->getField('Surname').", ".$var->getField('FirstName');
   
return $author;
}

which will work to (using 'AuthorName' in the dataobjectmanager Headings array).
like this it seems to be sortable in the table, but clicking on it fails with a wrong SQL Statement....

...ORDER BY AuthorName ASC LIMIT 10 OFFSET 0 Unknown column 'AuthorName' in 'order clause'

Obviously it takes AuthorName as a normal db field, and not as a has_one relation
how to get sort working?

thanks a lot in advance

Avatar
micahsheets

16 February 2012 at 6:27am (Last edited: 16 February 2012 11:16am), Community Member, 164 Posts

Hello,

I am needing to set a default sort for a DOM where the DataObjects displayed need to be sorted by the order of another set of DataObjects that are related through another DataObject. This seems complex I know but I have it working in ComplexTableField through the use of two JOIN tables. However when I change ComplexTableField to DataObjectManager I get the default sort that DOM uses if nothing is put into the Sort param. I know that DOM is not going to be used in SS 3 but until 3 is completely stable with a release version I won't be able to use it on client sites. I would appreciate any help on this I can get. The DOM I am using was downloaded from github about a month ago. Has this issue been fixed since then?

Avatar
silverseba

14 August 2012 at 3:30am (Last edited: 14 August 2012 3:34am), Community Member, 26 Posts

I found a solution to the problem of sorting Items in DataObjectManager by a field that exists in the Parent / has_one relation of th DataObject.

We take this simple setup:

MyDataObject.php - the DataObject that is rendered via DataObjectManager

class MyDataObject extends DataObject {
   
   public static $db = array (
      'Title' => 'Varchar'
   );
   
   public static $has_one = array(
      'DataObjectToSortBy' => 'DataObjectToSortBy', // our DataObject that we want to sort by
      'MyPage' => 'MyPage' // the Page that contains the DataObjectManager
   );
   
   public function getCMSFields_forPopup() {
      $f = new FieldSet();
      $f->push(new Textfield('Title', 'Title');
      return $f;
   }
}

DataObjectToSortBy.php - the DataObject that contains the field we want the DataObjectManager to sort by

class DataObjectToSortBy extends DataObject {
   
   public static $db = array (
      'SortTitle' => 'Varchar'
   );
   
   public function getCMSFields() {
      $f = new FieldSet();
      $f->push(new Textfield('SortTitle', 'SortTitle');
      return $f;
   }
}

MyPage.php - the Page that contains the DataObjectManager

class MyPage extends Page {
   
   public static $has_many = array(
      'MyDataObjects' => 'MyDataObject'
   );
   
   public function getCMSFields() {
      $f = parent::getCMSFields();
      $f->addFieldToTab('Root.Content.MyDataObjects', new ManyManyDataObjectManager(
         $this,
         'MyDataObjects',
         'MyDataObject',
         array(
            'Title' => 'Title,
            'DataObjectToSortBy.SortTitle' => 'SortTitle'
         ),
         'getCMSFields_forPopup',
         null,
         'SortTitle', // DO NOT USE 'DataObjectToSortBy.SortTitle' NOR USE 'ASC' or 'DESC'!! -> This uses field DataObjectToSortBy.SortTitle
         'LEFT JOIN DataObjectToSortBy ON DataObjectToSortBy.MyDataObjectID = MyDataObject.ID'
      );
      return $f;
   }
}

The Solution
The Solution is to use the fieldname of the has_one (or even has_many) relation but

  • WITHOUT using the class name of the object (e.g. 'DataObjectToSortBy.SortTitle' -> ERROR) and
  • WITHOUT using a sort direction (e.g. 'SortTitle ASC' -> ERROR).

Then use a Join clause to join your has_one or has_many DataObject with the DataObject in the DOM.

Downsides

  • The field you want to sort by has to be unique in the MySQL Query (if DataObject has a field 'SortTitle' as well this does not work)
  • You can only sort ascending (as the default dort direction)

It took me hours to figure this out. But this solution is tested and works.
It doen´t break the sorting functionality in the table field labels (you can still click the links) and is doesn´t break loading the popups.

Hope this helps!

@UncleCheese: I know there must be a better solution, as the sorting gets kind of messed up when you add the class name or sorting direction. But I didn´t find the code where this happens.
Just as a starting point: When you use 'DataObjectToSortBy.SortTitle ASC' as sorting clause $this->sourceSort becomes '"DataObjectToSortBy.SortTitle" ASC, DataObjectToSortBy.SortTitle' and produeces an MySQL Error.