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   1360 Views

Avatar
JMagnusson

Community Member, 29 Posts

5 April 2011 at 4:12am

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

Community Member, 29 Posts

7 April 2011 at 4:24am

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

Community Member, 22 Posts

29 October 2011 at 4:02pm

Did you ever get this working?

Avatar
JMagnusson

Community Member, 29 Posts

30 October 2011 at 6:36am

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