Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » help querying data from many-many relationships

Our old forums are still available as a read-only archive.

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

Page: 1
Go to End
Author Topic: 2078 Views
  • ChrisBryer
    Avatar
    Community Member
    95 Posts

    help querying data from many-many relationships Link to this post

    Hi Everyone,
    I am building a slightly unconventional navigation renders n-n relationships.

    on pages that 'belong_many_many', i am passing in an ID for the page that 'has_many_many' in the url. What i want to do is display links to all the siblings to the page that we are on, from the n-n relationship.

    because of the n-n relationship, the page ID's are located in a seperate table, and I'm not sure if i should be using sqlQuery or DataObject::get commands, and I'm getting messed up on the WHERE statements. here is the code:

    class ProductPage extends Page {
       static $has_one = array(
          'ProductImage' => 'Image'
       );
       
       static $belongs_many_many = array(
          'PProductCategory' => 'ProductCategory',
          'PMarkets' => 'MarketPage'
       );
       
       function getCMSFields(){
          $fields = parent::getCMSFields();
          $fields ->addFieldToTab("Root.Content.Image", new ImageField('ProductImage', 'Select an image to represent this product category.' ));
          
          return $fields;
       }

    }

    class ProductPage_Controller extends Page_Controller {
       
       function MarketPT(){
          return $this->renderWith(array("ProductPageAsMarket","Page"));
       }
       function NavigationFromMarket(){
       
          $marketLevel = DataObject::get_one("MarketsHolder");
          $markets = DataObject::get("MarketPage", "ParentID = $marketLevel->ID", "", "");
          $output = "";
          $output = $this->makeList($markets);
          return $output;
       }
       
       private function makeList($pages){
          $output = "";
          if(count($pages)) {
             $output = '
             <ul>';
             foreach($pages as $page) {
                if(!($page instanceof ErrorPage) && $page->ShowInMenus){
                   $output .= '
                   <li><a href="'.$page->URLSegment.'" title="Go to the '.Convert::raw2xml($page->Title).' page">'.Convert::raw2xml($page->MenuTitle).'</a>';
                   if($page->ID == $_GET['MarketID']){
                   

    //HERE IS WHERE I NEED HELP WITH THE SQL QUERY
                      $childPages = DataObject::get("ProductPage");
                      $output .= $this->makeChildList($childPages);
                      
                   }
                   $output .= '
                   </li>';
                }
             }
          $output .= '
          </ul>';
          }
          return $output;
       }
       
       private function makeChildList($pages){
          $MarketIdURL = $_GET['MarketID'];
          $output = "";
          if(count($pages)) {
             $output = '
             <ul>';
             foreach($pages as $page) {
                if(!($page instanceof ErrorPage) && $page->ShowInMenus){
                   $output .= '
                   <li><a href="'.$page->URLSegment.'/MarketPT?MarketID='.$MarketIdURL.'" title="Go to the '.Convert::raw2xml($page->Title).' page">'.Convert::raw2xml($page->MenuTitle).'</a>';
                   $output .= '
                   </li>';
                }
             }
          $output .= '
          </ul>';
          }
          return $output;
       }
       
    }

    I would appreciate any help on this.
    thanks alot,
    -Chris

  • ChrisBryer
    Avatar
    Community Member
    95 Posts

    Re: help querying data from many-many relationships Link to this post

    so i got this to work with a SQLQuery, but i am not sure if using DataObject:get or SQLQuery is the preffered way to do it.

    function MarketProducts()
       {
          $sqlQuery = new SQLQuery();
          $sqlQuery->select = array("*");
          $sqlQuery->from = array("ProductPage LEFT JOIN MarketPage_MProducts ON ProductPage.ID = MarketPage_MProducts.ProductPageID");
          $sqlQuery->where = array("MarketPage_MProducts.MarketPageID = ".$_GET['MarketID']);
          $res = $sqlQuery->execute();
          
          $dos = new DataObjectSet();
          foreach ($res as $record) {
             $dos->push( new DataObject( array("ProductName"=>$record['Name']) ));
          }
          return $dos;
       }

    any advice?
    thanks alot,
    -Chris

  • ChrisBryer
    Avatar
    Community Member
    95 Posts

    Re: help querying data from many-many relationships Link to this post

    I keep answering my questions. incase it helps anyone, here is the syntax for the DataObject::get method, for the above SQLQuery

    $childPages = DataObject::get("ProductPage", "MarketPage_MProducts.MarketPageID = ".$_GET['MarketID'], "", "LEFT JOIN MarketPage_MProducts ON `ProductPage`.ID = `MarketPage_MProducts`.ProductPageID", "");

    I ended up using the DataObject because somehow i was able to access the URLSegment, etc.. with DataObject , while SQLQuery with the joins only returned the extended class records, and not down to the siteTree and I wasnt able to access things like Title, URLSegment, metadata, etc...

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