I have a many_many relation between Page and PageCategory. A page can have multiple categories and a PageCategory can belong to multiple pages.

MySiteTreeExtension.php

class MySiteTreeExtension extends DataExtension{
	private static $db = array(
		'IncludeInFooterNav' => 'Boolean'
	);
}

PageCategory.php

class PageCategory extends DataObject {
	public static $db = array(
		'Name' => 'Varchar(255)',
	);
	
	public static $belongs_many_many = array(
		'PagesBelongingToCategory' => 'Page'
	);	  
}

Page.php

class Page extends SiteTree {
	
	private static $many_many = array(
		'PageCategories' => 'PageCategory'
	);
}

What I would like to do is get a SiteTree list of all the pages which have IncludeInFooterNav==true and PageCategory=='Vehicle'.

I was hoping to be able to do this via the ORM using a query such as:

class MySiteTreeExtension extends DataExtension{
	public function getFooterNavBasedOnCategory() {
		
		$result = SiteTree::get()					  
			->leftJoin('Page_PageCategories','"SiteTree"."ID" = "Page_PageCategories"."PageCategoryID"')
			->leftJoin('PageCategory','"PageCategory"."ID" = "Page_PageCategories"."PageCategoryID"')
			->filter(array(
				"IncludeInFooterSiteMap" => "1",
				"PageCategory.Name" => "Vehicle"
				));			 
		return $result;
	} 
}	

But I keep getting an error saying "Unknown column 'DataObject.Name' in 'where clause'". Examining the SQL query generated by the ORM it appears that it is only selecting columns from SiteTree and not from the other joined tables.

So a couple of questions:

• 1) How can I modify this ORM query so that I can get the other (joined) tables' column
• 2) Alternatively, is there a more efficient and/or simpler way of getting a SiteTree list of all the pages which have IncludeInFooterNav==true and PageCategory=='Vehicle'.

You could try turning it around: first get the PageCategory, then its pages. Something like

$pages = PageCategory::get()
->filter(array('Name' => 'Vehicle'))
->first()
->PagesBelongingToCategory()

Assuming you're using 3.0:

$pages = Page::get()->filter(array('IncludeInFooterSiteMap' => true, 'PageCategories.Name:ExactMatch' => 'Vehicle'));

With 3.1, the :ExactMatch becomes optional. Notice that the syntax is NameOfRelation.FieldName, not NameOfClass.FieldName.

@martimiz & @simon_w Both solutions work and so are ridiculously elegant, they're almost offensive ;-)

Thanks very much for taking the time to answer my question.

Man, SS3's ORM is great!

Performance: @martimiz's solution generates two SQL queries and @simon_w's produces one. For the small dataset that I have, the total time taken to execute the queries is fairly similar, on average.

@martimiz - just wondering, why is the call to first() necessary after filter(...)?

@simon_w - Am I correct in assuming that simply using NameOfRelation.Fieldname causes the ORM to do the joins on the appropriate tables? If so, that's just a little awesome.

That's because filter() will always return a DataList, no matter how many DataObjects it may contain. first() will then get you the first DataObject in that list.

Thanks for the explanation @martimiz.