Hello,

I try to get a link to my next product (dataobject) in the category. The products are stored in the table "products" and is connected to the table "categorypage_products" via the ProductID. But to have a correct URL i need the URLSegment for the category, stored in the sitetree table. How can i join these three tables?

Now I have this, but i doesn't work:
$Join = "(INNER JOIN `categorypage_products` ON `product`.ID = `categorypage_products`.ProductID )INNER JOIN `sitetree` ON `categorypage_products`.CategoryPageID = `sitetree`.ID";

This is the rest of my code:
$CurrentProduct = DataObject::get("product","Title = '" .$Product->Title ."'",'Title ASC', $Join, 1);
$Where = "Title BETWEEN '" .$CurrentProduct->Title ."ö' AND 'Ööö' && 'CategoryPageID' = '" .$CurrentProduct->CategoryPageID ."'";
$NextProduct = DataObject::get("product",$Where,'Title ASC', $Join, 1);

Thanks, Johan

I have understand that I can't use DataObject::get, because it will not join the data. So now this is my code:

	$Breadcrumbs = parent::Breadcrumbs();
	if($Product = $this->getCurrentProduct())
	{
		$Parts = explode(SiteTree::$breadcrumbs_delimiter, $Breadcrumbs);
		$NumOfParts = count($Parts);
		$CategoryTitle =  $Parts[$NumOfParts-1];  
	}
	
		
	//Current product
	$query = new SQLQuery(); 
		
	$query->from('`product`'); 
	$query->select("product.* , sitetree.URLSegment AS SURLSegment"); 
	$query->innerJoin('categorypage_products', '(product.ID=categorypage_products.ProductID)'); 
	$query->innerJoin('sitetree', '(sitetree.ID=categorypage_products.CategoryPageID)'); 
	$query->where("product.Title = '" .$Product->Title ."' AND '". $CategoryTitle. "'= sitetree.Title" ); 
	//Doesn't work: $query->where = array('product.Title = '.$Product->Title, $CategoryTitle.'= sitetree.Title');

	$result = $query->execute(); 
	$CurrentProduct = singleton('product')->buildDataObjectSet($result);
		
	//Next product
	$query = new SQLQuery(); 
		
	$query->from('`product`'); 
	$query->select("product.* , sitetree.URLSegment AS SURLSegment"); 
	$query->innerJoin('categorypage_products', '(product.ID=categorypage_products.ProductID)'); 
	$query->innerJoin('sitetree', '(sitetree.ID=categorypage_products.CategoryPageID)'); 
		
	//Doesn't work: $query->where("( Title BETWEEN '" . $CurrentProduct->Title ."z' AND 'zzz') AND sitetree.Title = '" . $CurrentProduct->SURLSegment ."'" ); 

	$query->orderby('product.Title ASC'); 
	$query->limit(1); 

	$result = $query->execute(); 
	$NextProduct = singleton('product')->buildDataObjectSet($result);

My problem is with WHERE. I don't know how to use an array there and how to get the data from $CurrentProduct.
Please help

/Johan

Did you ever get this working?

No, I had to remove my link to the next product. I think that there must be a way to solve this, but I almost know anything about databases and php...
I can describe my tables if anyone wants to solve this. But I dont need this anymore.
/Johan