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   605 Views

Avatar
EdP

Community Member, 13 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, 27 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, 13 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');