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.

Data Model Questions /

SS3 simple InnerJoin problem


Reply


2 Posts   915 Views

Avatar
MitraX

Community Member, 20 Posts

11 October 2012 at 8:36pm

Edited: 11/10/2012 8:36pm

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 [url=http://www.inforbiro.com/blog-eng/silverstripe-most-popular-articles/]here[/url]

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

Avatar
UNISOLUTIONS

Community Member, 2 Posts

19 October 2012 at 9:00pm

Edited: 19/10/2012 10:28pm

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