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`
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!