Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » IS NOT

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

Page: 1
Go to End
Author Topic: 483 Views
  • markuse
    Avatar
    Community Member
    4 Posts

    IS NOT Link to this post

    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

  • simon_w
    Avatar
    Forum Moderator
    467 Posts

    Re: IS NOT Link to this post

    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.

  • markuse
    Avatar
    Community Member
    4 Posts

    Re: IS NOT Link to this post

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

  • simon_w
    Avatar
    Forum Moderator
    467 Posts

    Re: IS NOT Link to this post

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

  • markuse
    Avatar
    Community Member
    4 Posts

    Re: IS NOT Link to this post

    that's cool!
    thanks
    markus

    483 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.