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:

Go to End


5 Posts   866 Views

Avatar
markuse

Community Member, 4 Posts

4 September 2012 at 5:03am

hi all
in a getCMSFields i need to exclude some data from my query.
This snippet works:
$cat = Category::get()->where("ProjectID = $this->ID")->leftJoin("Project_Categories", "\"Category\".\"ID\" = \"Project_Categories\".\"CategoryID\" ");
But i need the opposite

...where("ProjectID != $this->ID")...

nether != nor NOT works. is there a simple solution?
best regards
markus

Avatar
(deleted)

Community Member, 473 Posts

4 September 2012 at 8:28am

Instead of using where(), you should be using filter()/exclude(). $list->filter('ProjectID', $this->ID) will filter to values with that specific ProjectID, whereas $list->exclude('ProjectID', $this->ID) will exclude them.

Avatar
markuse

Community Member, 4 Posts

5 September 2012 at 12:55am

Thanks Simon!
works - basically. But probably I have another problem which I cam across by this.... Probably it's a SS3 problem - by the way it's really great!
I have a ProjectPage.php with a has_many realtionship to Project and Category. Project.php itself has a many_many relationship to Category.php.

In the backend Project section I can add a Project and, after that, assign Categories with the new "Add Category" or auto fill out/"Link Existing" tool. Added or linked Categories are shown in a list beneath - everything works fine. But to have an overview, which categories altogether exist I like to have a list with all other Categories. For that reason I try to get my results from the leftJoin described above now with the exclude()" clause.

But: it only works with new added Categories, not with "Link Existing". They still appear in my list with the excluded Categories.

Proect.php

class Project extends DataObject {

static $db = array(
'ProjectTitle' => 'Varchar',
'Teaser' => 'HTMLText'
);

static $has_one = array (
'ProjectPage' => 'ProjectPage'
);

static $many_many = array(
'Categories' => 'Category'
);

public function getCMSFields() {
$fields = parent::getCMSFields();
$unlinkedCats = Category::get()->leftJoin("Project_Categories", "\"Category\".\"ID\" = \"Project_Categories\".\"CategoryID\" ")->exclude( "ProjectID",$this->ID);
$fields->addFieldToTab('Root.Categories',new GridField('Unlinked Categories', null , $unlinkedCats));
return $fields;
}
….
}

Category.php

class Category extends DataObject {

static $db = array(
'Title' => 'Varchar',
'Description' => 'Text'
);
}

Avatar
(deleted)

Community Member, 473 Posts

5 September 2012 at 11:27am

An easier way to get this (rather than fiddling with joins or subqueries yourself, since a left join won't work here), is to subtract the items you don't want. DataList has a nice method for this already, so you can use $unlinkedCats = Category::get()->subtract($this->Categories());

Avatar
markuse

Community Member, 4 Posts

5 September 2012 at 9:20pm

that's cool!
thanks
markus