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:

DataObjectManager Module /

Discuss the DataObjectManager module, and the related ImageGallery module.

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

Filtering Dataobject on enum field


Go to End


8 Posts   431 Views

Avatar
RobinJG

Community Member, 7 Posts

2 February 2017 at 6:26pm

In the cms I have a list of checkboxes that store values as a comma separated string e.g. 1, 2, 8
When getting the site tree I want to only select pages if they have a certain value in this field.
This is the query I'm currently using:

$links = SiteTree::get("SiteTree", "ShowInMenus = 1")->filter('ParentID' , 21)

I would like to extend this query with something like FIND_IN_SET('6',my_field) or my_field like "%6%"
How can I do this by adding another filter, I've looked at the Enum class but without any luck.

Avatar
martimiz

Forum Moderator, 1391 Posts

3 February 2017 at 4:09am

You should be able to explode the comma-separated list into an array $arrID and then

$links = SiteTree::get("SiteTree", "ShowInMenus = 1")->filter(array('ParentID' => $arrID));

Avatar
RobinJG

Community Member, 7 Posts

3 February 2017 at 10:40am

Edited: 03/02/2017 10:50am

Thanks martimiz.
I think I may have led you astray slightly.

$links = SiteTree::get("SiteTree", "ShowInMenus = 1")->filter(array('ParentID' => $arrID));

Is my starting query that works but I need to query another field called access that is a checkbox field that stores it's data as a comma separated list.
So the extra query / filter would need to be something like this:
->filter( array( '2' => 'access' ) )

But this gives me an error

I have just tried

->filter(array('access:PartialMatch' => '1'))

But I get the error Unknown column 'access' in 'where clause'
This is a field added to a tab Root.Access on each page. How to I query the access field?

Avatar
martimiz

Forum Moderator, 1391 Posts

3 February 2017 at 11:15am

Ok... now I'm not sure if I get it this time, but it doesn't really matter what the name of a tab is - it's just a field holder. What you're querying is checkboxSetField I suspect. So supposedly you created something like this in the Page class:

private static $db = array('Access' => 'Varchar');

And then created a CheckBoxSetField called 'Access' on your Page, then you should be able to query for it with something like

Page::get()->filter(array('Access:PartialMatch' => 2)):

But note that this would find 2 as well as 12 :)

Avatar
RobinJG

Community Member, 7 Posts

3 February 2017 at 12:31pm

Thanks martimiz, you are correct in your assumption of "querying is checkboxSetField" and how it was set up.
Just to explain what I'm doing , I have some values that I am getting from an outside resource ( API call ) that determines what pages the user will be able to see.
For example I login as user and get Login levels from API call of 1 and 2.

In each page there is a list of access levels (checkboxSetField) that will be checked by the website admin, access levels 1 through 8.
So ideally I was to grab only the pages from SiteTree that are for this Login level, login level 1 and 2 to show only pages that have access checboxes 1 or 2 checked.

I hope this has made it a little clearer and not worse.

Avatar
martimiz

Forum Moderator, 1391 Posts

4 February 2017 at 5:07am

Edited: 04/02/2017 5:35am

Your question seems to be shifting a bit :) You were asking howto query the access field, and I suppose I answered that... Have you tried it? So with Page::get() instead of SiteTree::get() and the proper name of the CheckboxSetField?

And your situation is something like this (just that I get it right):

[EDIT FROM HERE]

User has levels 1, 2
Page 1 has field Access containing 1, 4 (comma separated list)
Page 2 has field Access containing 2, 5
Page 3 has field Access containing 6, 7, 8

User can see page 1 and 2 but not 3?

$userArray = array(1,2);
$pages = Page::get()->filterAny(array( 'Access:PartialMatch' => $userArray));

Haven't tested, but under the hood this should do something like ... where Access like '%1%' or Access like '%2%'

(which will still go wrong if access levels rise above 9, but if that's not going to happen...)

Avatar
RobinJG

Community Member, 7 Posts

7 February 2017 at 5:42pm

Thanks martimiz,
That's just what I needed.
I didn't realise that using:

Page::get

gives me the same results as
SiteTree::get

Thank you for all your help.

Avatar
martimiz

Forum Moderator, 1391 Posts

8 February 2017 at 12:13am

Actually Page::get() may give you more then SiteTree::get() because Page::get() will also return fields that are defined in the Page class, which SiteTree:: get() knows nothing about.

When using Page::get() silverstripe will automatically perform a join on the SiteTree table, so you get all fields from both tables...