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);