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

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

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