Jump to:

5540 Posts in 1738 Topics by 1224 members

Customising the CMS

SilverStripe Forums » Customising the CMS » Database INNER JOIN

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 1264 Views
  • JMagnusson
    Avatar
    Community Member
    29 Posts

    Database INNER JOIN Link to this post

    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

  • JMagnusson
    Avatar
    Community Member
    29 Posts

    Re: Database INNER JOIN Link to this post

    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

  • johnofjack
    Avatar
    Community Member
    22 Posts

    Re: Database INNER JOIN Link to this post

    Did you ever get this working?

  • JMagnusson
    Avatar
    Community Member
    29 Posts

    Re: Database INNER JOIN Link to this post

    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

    1264 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.