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