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 /

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

SS3 custom joins

Go to End

3 Posts   1337 Views


Community Member, 14 Posts

23 February 2013 at 7:50am

In SS2 I had:

$conductors = DataObject::get('Performer', "PerformerType.IsConductor", "", "INNER JOIN `PerformerType` ON PerformerType.ID = SiteTree.ParentID"

Could someone please give me a clue how to achieve the same thing using (I assume) innerJoin syntax?

I have Performers and PerformerTypes tables (pages) - the latter has a flag (IsConductor). I am trying to extract just those Performers whose PerformerType is IsConductor, for a dropdown field on the back end.

By the way I see backticks generate an error now.

So far I have got:

$conductors = Performer::get()->innerJoin("PerformerType", "PerformerType.ID = SiteTree.ParentID");
$conductors->filter("IsConductor", true);

but the filter is not being applied. There's probably a far more elegant way to do it anyway - having to refer to SiteTree doesn't feel right.



Community Member, 30 Posts

23 February 2013 at 1:49pm

There's a few ways to do what your trying to fix what you have:

$conductors = Performer::get()->innerJoin("PerformerType", "PerformerType.ID = SiteTree.ParentID");
$conductors = $conductors->filter("IsConductor", true);

Or you could do it all in one shot:

$conductors = Performer::get()->innerJoin("PerformerType", "PerformerType.ID = SiteTree.ParentID")->filter("IsConductor", true);

The reason your filter is not being applied is because each time you call a modifier on a DataList (which is what Performer::get() returns) it creates a clone before modifying. Which is why your filter doesn't get applied.


Community Member, 14 Posts

25 February 2013 at 10:24pm

Many thanks - I think that was the one permutation I hadn't tried! And just in case anyone else stumbles here this:

$conductors = $conductors->toDropdownMap('ID', 'Title', '(Select one)', true);

becomes this:

$conductorfield = new DropdownField('ConductorID', 'Conductor', $conductors->map('ID', 'Title'));
		$conductorfield->setEmptyString('(Select one)');
		$fields->addFieldToTab('Root.Main', $conductorfield, 'Content');