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.

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));

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?

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 :)

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.

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

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.

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