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.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

Data Model Questions /

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

SS3 custom joins

Go to End

3 Posts   1362 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');