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've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

SS3 custom joins


Go to End


3 Posts   1817 Views

Avatar
EdP

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.

Ed

Avatar
UndefinedOffset

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.

Avatar
EdP

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');