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