Jump to:

3460 Posts in 1064 Topics by 739 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Datamodel Filtering not working on Inner Join

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

Page: 1
Go to End
Author Topic: 242 Views
  • ChristopherW
    Avatar
    Community Member
    3 Posts

    Datamodel Filtering not working on Inner Join Link to this post

    I'm relatively new to tinkering in the data model and I'm trying to get just the child groups of a specific Member group. I'm trying to do this in a GridField so I'm able to modify members/groups using the GridField in the CMS.

    If I do the sql directly on the database, it works and gives me exactly what I want:

    SELECT `Group`.`Title`, `Group`.`Description`, `Parent`.`Title`
    FROM `Group`
    INNER JOIN `Group` AS `Parent`
    ON `Group`.`ParentID` = `Parent`.`ID`
    WHERE `Parent`.`Title` = 'Content Editors'

    It gives me a list of all subgroups under 'Content Editors'. However, if I try and do the same (as far as I can tell I am) using Silverstripe, I get no results.

    Here's the code:

    $Groups = Group::get()
    ->innerJoin("Group", "Group.ParentID = Parent.ID", "Parent")
    ->filter('Parent.Title', 'Content Editors');

    Taking off the filter gives a list of all groups that are a subgroup, but includes other subgroups I don't want to display. That would say to me that none of the records have a 'Parent.Title' matching 'Content Editors'. I know that Silverstripe doesn't return the joined fields, but I only need to filter the results by the join which I believe is the whole point in having joins in Silverstripe. Is it possible? I'm sure it must be and I'm missing something small.

    I don't want to set it to filter by using the ParentID (i.e ParentID = 12) as it will be different on different server installs (between my development sever and production server, for example) and I won't have control over it. However, I can specify the parent group name.

    If it try and run the SQL using DB::query, it completely breaks the GridField. Every time I fix one issue that the GridField is complaining about, it throws a different error. I gave up after about the 5th which was indicating that the data object was not sortable. I kept removing component after component to fix it and it made the GridField unusable for my purposes.

    I appreciate any wisdom you can give me!

    Thanks,

    Chris

  • Mia Strydom
    Avatar
    Community Member
    3 Posts

    Re: Datamodel Filtering not working on Inner Join Link to this post

    Hey! You could try using where instead of filtering.

    $Groups = Group::get()
    ->innerJoin("Group", "Group.ParentID = Parent.ID", "Parent")
    ->where("Parent.Title='Content Editors'");

    Hope that helps!

    242 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.