Magento - cms/page collection - apply filter to return only pages which are unique to a given store id (ie not also assigned to other stores)

Question

I can use:

Mage::getModel('cms/page')->getCollection()->addStoreFilter($store_id);

to retrieve a collection of CMS pages filtered by Store Id.

But how do I get it to remove ones which are also assigned to other stores?

ie: I do not want it to return items which have 'All Store Views' as the Store View. (Or any other additional store id assigned to that CMS page.) It has to only return pages unique to that one store.

I am extending the Aitoc permissions module, so that Store Admins cant view or edit CMS pages and static blocks which might impact other stores. This involves filtering those items from the grid.

Answer 1

There's no native collection method to do this, so you'll need to

1. Query the cms_page_store table for pages unique to a given store

2. Use the results from above in your filter

I didn't fully test the following, but it should work (and if it doesn't, it'll give you a good start on your own query)

$page     = Mage::getModel('cms/page');
$resource = $page->getResource();
$read     = $resource->getReadConnection();

#$select   = $read->query('SELECT page_id FROM ' . $resource->getTable('cms/page_store') . ' GROUP BY store_id');

//set total count to look for.  1 means the page only appears once.
$total_stores_count_to_look_for = '1';

//get the table name.  Need to pass through getTable to ensure any prefix used is added
$table_name                     = $resource->getTable('cms/page_store');

//aggregate count select from the cmd_page_store database
//greater than 0 ensures the "all stores" pages aren't selected
$select = $read->query('SELECT page_id as total
FROM '.$table_name.' 
WHERE store_id > 0 
GROUP BY page_id
HAVING count(page_id) = ?',array($total_stores_count_to_look_for));

//fetch all the rows, which will be page ids
$ids   = $select->fetchAll(); 

//query for pages using IDs from above
$pages = Mage::getModel('cms/page')->getCollection()->addFieldToFilter('page_id',array('in'=>$ids));

foreach($pages as $page)
{
    var_dump($page->getData());
}

If you have thousands and thousands of CMS pages it may be worth it to alter the cms/page collection's select to join in aggregate table data. I'll leave that as an exercise for the reader, as those sorts of joins can get tricky.