Jump to:

3431 Posts in 1058 Topics by 734 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Can't get columns from joined tables in many_many relation join via SS3 ORM

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 542 Views
  • vwd
    Avatar
    Community Member
    158 Posts

    Can't get columns from joined tables in many_many relation join via SS3 ORM Link to this post

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

  • martimiz
    Avatar
    Forum Moderator
    1067 Posts

    Re: Can't get columns from joined tables in many_many relation join via SS3 ORM Link to this post

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

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

  • simon_w
    Avatar
    Forum Moderator
    471 Posts

    Re: Can't get columns from joined tables in many_many relation join via SS3 ORM Link to this post

    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.

  • vwd
    Avatar
    Community Member
    158 Posts

    Re: Can't get columns from joined tables in many_many relation join via SS3 ORM Link to this post

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

  • martimiz
    Avatar
    Forum Moderator
    1067 Posts

    Re: Can't get columns from joined tables in many_many relation join via SS3 ORM Link to this post

    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.

  • vwd
    Avatar
    Community Member
    158 Posts

    Re: Can't get columns from joined tables in many_many relation join via SS3 ORM Link to this post

    Thanks for the explanation @martimiz.

    542 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.