Jump to:

3429 Posts in 1057 Topics by 734 members

Data Model Questions

SilverStripe Forums » Data Model Questions » one-to-many containing one-to-many relationship

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

Page: 1
Go to End
Author Topic: 2853 Views
  • david_nash
    Avatar
    Community Member
    55 Posts

    one-to-many containing one-to-many relationship Link to this post

    Hi, I think I'm thinking about this the wrong way and I'm wondering if someone could help or make a suggestion.

    I want to model some educational 'courses'.

    Each Course has a title and description (I'm using CoursePage [extends Page] for this)
    Each Course also has many Location/Fee pairs (I'll call this CourseLocation).
    Each of those CourseLocations will have many Dates. These are the days the courses will run.

    I think I've got the relationships correct but I'm not sure about how the form fields should work.

    For CoursePage I use a ComplexTableField that shows the Location/Fee pairs.

    But how do I list the multiple dates for each of those pairs?

    I tried using another ComplexTableField inside the popup but it the first CTF says 'unknown column `0` in database'.

    I have a feeling that I've made this way more complicated than it needs to be. Please help!

  • david_nash
    Avatar
    Community Member
    55 Posts

    Re: one-to-many containing one-to-many relationship Link to this post

    I think I was on the right track, and I reckon I have a pretty good solution. I hope someone can have a look at this and give me some pointers.

    I created a third class called CourseDate that just contains a date. The reason I did this was so that I could use it in my $has_many array, which needs to have the $has_one relation in the other direction.

    Instead of using a ComplexFieldTable on the second one-to-many relation, I used a TableField.

    Here's my code:

    <?php
    //file mysite/code/CoursePage.php
    class CoursePage extends Page {
       static $db = array();
       static $has_one = array();

       static $has_many = array(
          'Courses' => 'Course'   
       );
       
       //list the location and fee entries in a table
       function getCMSFields() {
          $fields = parent::getCMSFields();
          $tablefield = new ComplexTableField(
             $this,
             'Courses',
             'Course',
             array(
                'Location' => 'Location',
                'Fee' => 'Fee'
             ),
             'getCMSFields_forPopup'
          );

          $tablefield->setAddTitle( 'A Course' );
          $fields->addFieldToTab( 'Root.Content.Courses', $tablefield );
          return $fields;

       }

    }

    class CoursePage_Controller extends Page_Controller {
    }
    ?>

    Next I have a 'Course' which is made up of multiple fee/location pairings. The reason for this is that courses in different locations may have different costs. I extend DataObject because I don't need all the Page stuff.

    <?php
    class Course extends DataObject {

       public static $db = array(
          'Location' => 'Text',
          'Fee' => 'Currency'
       );

       public static $has_one = array(
          'CoursePage' => 'CoursePage'
       );

       public static $has_many = array(
          'CourseDates' => 'CourseDate'
       );

       //when an entry in the 'Courses' tab is clicked, I want to show a table of dates
       function getCMSFields_forPopup() {
          $fields = new FieldSet();
          $fields->push( new TextField( 'Location' ) );
          $fields->push( new CurrencyField( 'Fee' ) );

          $ctf = new TableField(
             'CourseDates',
             'CourseDate',
             array( 'Date' => 'Date' ),
             array( 'Date' => 'DateField' )
             //array( 'Date' => 'CalendarDateField' ) //the bottom of this gets cut off obscuring the dates!
          );

          $fields->push( $ctf );
          return $fields;
       }
    } //end class

    Now this bit I'm not 100% on but it works:

    <?php
    class CourseDate extends DataObject {

       //it's just a 'Date' named 'Date'
       public static $db = array(
          'Date' => 'Date'
       );

       //make the link back to the 'parent'
       public static $has_one = array(
          'Course' => 'Course'
       );

    } //end class

    Now this all seems pretty good except that when I use CalendarDateField in my Course object the bottom of the calendar gets cut off.

    My second choice would be the composite date field, but I can't get that to load the date correctly. So I fall back on DateField.

  • Ingo
    Avatar
    Forum Moderator
    801 Posts

    Re: one-to-many containing one-to-many relationship Link to this post

    Nesting ComplexTableField popups is not really supported. You could try to use a ModelAdmin interface to link each CTF row into a new ModelAdmin edit form instead of a popup.

    But yeah, a TableField seems a good option to. We've had troubles with DateField calendar popup styling in the past, here's a custom class that uses jQuery instead, which has a bit more robust CSS.

    <?php
    /**
    * @todo Hardcoded to NZ date format
    */
    class JQueryCalendarDateField extends DateField {
       
       protected $extraClasses = array('jquery-datepicker');
       
       function Field() {
          Requirements::javascript(THIRDPARTY_DIR . "/jquery/jquery.js");
          Requirements::javascript(THIRDPARTY_DIR . "/jquery/jquery_improvements.js");
          Requirements::javascript(THIRDPARTY_DIR . "/jquery/plugins/livequery/jquery.livequery.js");
          Requirements::javascript(THIRDPARTY_DIR . "/jquery/ui/ui.datepicker.js");
          Requirements::javascript(THIRDPARTY_DIR . "/jquery/ui/ui.core.js");
          Requirements::css(THIRDPARTY_DIR . "/jquery/themes/default/ui.all.css");

          Requirements::customScript("
             jQuery('input.jquery-datepicker').livequery(
                function() {
                   jQuery(this).datepicker('destroy');
                   jQuery(this).datepicker({
                      dateFormat: 'dd/mm/yy',
                   });            
                }
             );
             ",
             'JQueryCalendarDateField' // unique identifier makes sure rule is only called once
          );

          return parent::Field();
       }
       
    }
    ?>

    Careful, its fairly hacky (re-applying livequery and re-assigning plugins), but it does the job here.

  • david_nash
    Avatar
    Community Member
    55 Posts

    Re: one-to-many containing one-to-many relationship Link to this post

    Thanks Ingo, I really appreciate it!

    2853 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.