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.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

Data Model Questions /

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

Datamodel Filtering not working on Inner Join

Go to End

2 Posts   1271 Views


Community Member, 3 Posts

25 April 2014 at 3:30pm

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!




Community Member, 8 Posts

12 August 2014 at 9:03am

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!