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   598 Views

Avatar
vwd

27 September 2013 at 4:58pm (Last edited: 27 September 2013 5:03pm), Community Member, 158 Posts

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

28 September 2013 at 1:49am Forum Moderator, 1091 Posts

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

29 September 2013 at 3:53pm (Last edited: 29 September 2013 3:54pm), Forum Moderator, 474 Posts

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

29 September 2013 at 6:07pm Community Member, 158 Posts

@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

29 September 2013 at 11:39pm Forum Moderator, 1091 Posts

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

29 September 2013 at 11:40pm Community Member, 158 Posts

Thanks for the explanation @martimiz.