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

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!