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.

Data Model Questions /

one-to-many containing one-to-many relationship

Go to End

4 Posts   3056 Views


Community Member, 55 Posts

6 May 2009 at 6:08pm

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!


Community Member, 55 Posts

6 May 2009 at 8:17pm

Edited: 06/05/2009 8:18pm

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:

//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(
				'Location' => 'Location',
				'Fee' => 'Fee'

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

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(
			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:

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.


Forum Moderator, 801 Posts

11 May 2009 at 8:39pm

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.

 * @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");

				function() {
						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.


Community Member, 55 Posts

12 May 2009 at 12:03pm

Thanks Ingo, I really appreciate it!