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

SS3 custom joins


Reply

3 Posts   531 Views

Avatar
EdP

23 February 2013 at 7:50am Community Member, 13 Posts

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

23 February 2013 at 1:49pm Community Member, 27 Posts

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

25 February 2013 at 10:24pm Community Member, 13 Posts

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