17488 Posts in 4473 Topics by 1978 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 1746 Views |
-
help querying data from many-many relationships
26 November 2008 at 6:00am Last edited: 26 November 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 -
Re: help querying data from many-many relationships
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 -
Re: help querying data from many-many relationships
26 November 2008 at 8:42am Last edited: 26 November 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...
| 1746 Views | ||
|
Page:
1
|
Go to Top |
