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

Avatar
ChrisBryer

26 November 2008 at 6:00am (Last edited: 26 November 2008 7:29am), Community Member, 95 Posts

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

26 November 2008 at 8:03am Community Member, 95 Posts

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

26 November 2008 at 8:42am (Last edited: 26 November 2008 8:43am), Community Member, 95 Posts

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