Jump to:

7935 Posts in 1536 Topics by 943 members

DataObjectManager Module

SilverStripe Forums » DataObjectManager Module » DataObjectManager unable to sort joined Fields ?

Discuss the DataObjectManager module, and the related ImageGallery module.

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

Page: 1
Go to End
Author Topic: 2726 Views
  • Rufinus
    Avatar
    Community Member
    6 Posts

    DataObjectManager unable to sort joined Fields ? Link to this post

    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

  • UncleCheese
    Avatar
    4085 Posts

    Re: DataObjectManager unable to sort joined Fields ? Link to this post

    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.

  • Rufinus
    Avatar
    Community Member
    6 Posts

    Re: DataObjectManager unable to sort joined Fields ? Link to this post

    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.

  • Rufinus
    Avatar
    Community Member
    6 Posts

    Re: DataObjectManager unable to sort joined Fields ? Link to this post

    Any idea UncleCheese ?

  • UncleCheese
    Avatar
    4085 Posts

    Re: DataObjectManager unable to sort joined Fields ? Link to this post

    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?

  • monkee
    Avatar
    Community Member
    20 Posts

    Re: DataObjectManager unable to sort joined Fields ? Link to this post

    > $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

  • micahsheets
    Avatar
    Community Member
    164 Posts

    Re: DataObjectManager unable to sort joined Fields ? Link to this post

    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?

  • silverseba
    Avatar
    Community Member
    26 Posts

    Re: DataObjectManager unable to sort joined Fields ? Link to this post

    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.

    2726 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.