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.

Data Model Questions /

Can't get columns from joined tables in many_many relation join via SS3 ORM


Reply


6 Posts   714 Views

Avatar
vwd

Community Member, 160 Posts

27 September 2013 at 4:58pm

Edited: 27/09/2013 5:03pm

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

Avatar
martimiz

Forum Moderator, 1132 Posts

28 September 2013 at 1:49am

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

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

Avatar
simon_w

Forum Moderator, 474 Posts

29 September 2013 at 3:53pm

Edited: 29/09/2013 3:54pm

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.

Avatar
vwd

Community Member, 160 Posts

29 September 2013 at 6:07pm

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

Avatar
martimiz

Forum Moderator, 1132 Posts

29 September 2013 at 11:39pm

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.

Avatar
vwd

Community Member, 160 Posts

29 September 2013 at 11:40pm

Thanks for the explanation @martimiz.