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.

Archive /

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

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

help querying data from many-many relationships


Reply


3 Posts   2105 Views

Avatar
ChrisBryer

Community Member, 95 Posts

26 November 2008 at 6:00am

Edited: 26/11/2008 7:29am

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

Avatar
ChrisBryer

Community Member, 95 Posts

26 November 2008 at 8:03am

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

Avatar
ChrisBryer

Community Member, 95 Posts

26 November 2008 at 8:42am

Edited: 26/11/2008 8:43am

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