Jump to:

3375 Posts in 999 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » SS3 simple InnerJoin problem

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

Page: 1
Go to End
Author Topic: 704 Views
  • MitraX
    Avatar
    Community Member
    20 Posts

    SS3 simple InnerJoin problem Link to this post

    Hi,

    I'm upgrading code of my blog to SilverStripe 3 and currently trying to create a method for getting the most popular blog posts v3 following the logic I described for SS version 2 here

    There are two classes, BlogPostPage and PageCounter, that should be inner joined in order to get the most popular articles.

    Here is the logic that works in version 2:

    ...
    DataObject::get(
       "BlogPostPage",
       "Status = 'Published'",
       "`PageCounter`.Counter DESC",
       "INNER JOIN PageCounter ON `PageCounter`.PageID = `BlogPostPage`.ID",
       $num
    );
    ...

    New version with the most simple inner join:

    $blogs = BlogPostPage::get()->innerJoin("PageCounter", "\"PageCounter\".\"PageID\" = \"BlogPostPage\".\"ID\"");

    The following error occurs:

    [User Error] Couldn't run query: SELECT DISTINCT "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."Locale", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."Version", "SiteTree"."ParentID", "Page"."Date", "BlogPostPage"."BlogPostSummary", "BlogPostPage"."BlogPostThumbnailID", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END AS "RecordClassName" FROM "SiteTree" INNER JOIN "PageCounter" ON "PageCounter"."PageID" = "BlogPostPage"."ID" LEFT JOIN "Page" ON "Page"."ID" = "SiteTree"."ID" LEFT JOIN "BlogPostPage" ON "BlogPostPage"."ID" = "SiteTree"."ID" WHERE ("Status" = 'Published') AND ("SiteTree"."ClassName" IN ('BlogPostPage')) AND ("SiteTree"."Locale" = 'en_US') ORDER BY "SiteTree"."Sort" ASC LIMIT 5 Unknown column 'BlogPostPage.ID' in 'on clause'

    Line 568 in /opt/lampp/htdocs/ss3/framework/model/MySQLDatabase.php

    I tried to use grave accent (`), simple quotes (') and many other syntax variations in the query but without success.

    Does anybody have an idea what could be wrong with the query?

    Thanks

  • UNISOLUTIONS
    Avatar
    Community Member
    2 Posts

    Re: SS3 simple InnerJoin problem Link to this post

    I have the same issue. This is SS3 bug. The way how it prepares SQL is wrong - it adds inner/left joins to ancestral tables after custom joins:

    FROM "SiteTree"
    INNER JOIN "PageCounter" ON "PageCounter"."PageID" = "BlogPostPage"."ID"
    LEFT JOIN "Page" ON "Page"."ID" = "SiteTree"."ID"
    LEFT JOIN "BlogPostPage" ON "BlogPostPage"."ID" = "SiteTree"."ID"

    I had nothing to do but edit DataList.php, DataQuery.php and SQLQuery.php files. But my solutions is "dirty" and non upgrade-safe...

    EDIT

    I made some changes and now it's enough to edit only SQLQuery.php file (function sql()). Here's my code (all changes are in bold):

    SQLQuery.php

       function sql() {
          // TODO: Don't require this internal-state manipulate-and-preserve - let sqlQueryToString() handle the new syntax
          $origFrom = $this->from;


          // helper function for reordering
          $array_insert_after = function ($key, $array, $new_key, $new_value) {
             $new = array();
             foreach ($array as $k => $value) {
                $new[$k] = $value;
                if ($k === $key) {
                   $new[$new_key] = $new_value;
                }
             }
             return $new;
          };

          // reorder all tables in correct way
          $this->from = array();
          foreach ($origFrom as $alias => $join) {
             if (is_array($join)) {
                if(is_string($join['filter'])) $filter = $join['filter'];
                else if(sizeof($join['filter']) == 1) $filter = $join['filter'][0];
                else $filter = "(" . implode(") AND (", $join['filter']) . ")";

                $tables = array();
                preg_match_all('/([0-9a-z_]{2,})[`"\']?\./i', $filter, $tables);

                $tbl = trim($alias != $join['table'] ? $alias : $join['table'], "`'\"");
                $tables = array_filter($tables[1], function($val) use ($tbl) { return $val != $tbl; });

                if (count($tables) > 0) {
                   foreach ($this->from as $search_alias => $search_join) {
                      $tbl = isset($search_join['table']) ? $search_join['table'] : $search_join;
                      $tbl = trim($search_alias != $tbl ? $search_alias : $tbl, "`'\"");
                      if (($i = array_search($tbl, $tables)) !== false) {
                         unset($tables[$i]);
                      }
                      if (empty($tables)) {
                         $this->from = $array_insert_after($search_alias, $this->from, $alias, $join);
                         continue 2;
                      }
                   }
                }
             }
             $this->from[$alias] = $join;
          }

          // Build from clauses
          foreach($this->from as $alias => $join) {
             // $join can be something like this array structure
             // array('type' => 'inner', 'table' => 'SiteTree', 'filter' => array("SiteTree.ID = 1", "Status = 'approved'"))
             if(is_array($join)) {
                if(is_string($join['filter'])) $filter = $join['filter'];
                else if(sizeof($join['filter']) == 1) $filter = $join['filter'][0];
                else $filter = "(" . implode(") AND (", $join['filter']) . ")";

                $aliasClause = ($alias != $join['table']) ? " AS \"" . Convert::raw2sql($alias) . "\"" : "";
                $this->from[$alias] = strtoupper($join['type']) . " JOIN \"" . Convert::raw2sql($join['table']) . "\"$aliasClause ON $filter";
             }
          }

          $sql = DB::getConn()->sqlQueryToString($this);

          if($this->replacementsOld) {
             $sql = str_replace($this->replacementsOld, $this->replacementsNew, $sql);
          }

          $this->from = $origFrom;

          // The query was most likely just created and then exectued.
          if($sql === 'SELECT *') {
             return '';
          }

          return $sql;
       }

    704 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.