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.

Customising the CMS

Database INNER JOIN


Reply

4 Posts   1285 Views

Avatar
JMagnusson

5 April 2011 at 4:12am Community Member, 29 Posts

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

Avatar
JMagnusson

7 April 2011 at 4:24am Community Member, 29 Posts

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

Avatar
johnofjack

29 October 2011 at 4:02pm Community Member, 22 Posts

Did you ever get this working?

Avatar
JMagnusson

30 October 2011 at 6:36am Community Member, 29 Posts

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