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.

Reply


5 Posts   583 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
simon_w

Forum Moderator, 474 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
simon_w

Forum Moderator, 474 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