Jump to:

1856 Posts in 1610 Topics by 562 members

Blog Module

SilverStripe Forums » Blog Module » JOIN Not Working on Function BlogEntries

Discuss the Blog Module.

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

Page: 1
Go to End
Author Topic: 1223 Views
  • Garrett
    Avatar
    Community Member
    245 Posts

    JOIN Not Working on Function BlogEntries Link to this post

    Hi,

    I recently upgraded my Blog module from v0.1 to v0.4 and I'm seeing some problems. Previously I had modified the function Entries() to include a LET JOIN on the File table so that I can get a listing image field that I added to BlogEntry and that I use on the front end. This function used to be in BlogHolder, and now lives in a new class BlogTree. Regardless, however, as soon as I add in my Join:

    return DataObject::get('BlogEntry', $filter, $order, 'LEFT JOIN File ON BlogEntry.ListingImageID = File.ID', $limit);

    I get Database errors. Specifically, "Column 'ParentID' in where clause is ambiguous". But this isn't even the part of the SQL query to which I have access, so i don't really know how to debug this. Here's the whole error:

    [User Error] Couldn't run query: SELECT "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."ParentID", "Page_Live"."Headline", "BlogEntry_Live"."Date", "BlogEntry_Live"."Author", "BlogEntry_Live"."Tags", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName" FROM "SiteTree_Live" LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID" LEFT JOIN "BlogEntry_Live" ON "BlogEntry_Live"."ID" = "SiteTree_Live"."ID" LEFT JOIN File ON BlogEntry.ListingImageID = File.ID WHERE ("SiteTree_Live"."ClassName" IN ('BlogEntry')) AND ("ParentID" IN (576) ) GROUP BY "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."ParentID", "Page_Live"."Headline", "BlogEntry_Live"."Date", "BlogEntry_Live"."Author", "BlogEntry_Live"."Tags", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END ORDER BY "BlogEntry_Live"."Date" DESC LIMIT 0,32 Column 'ParentID' in where clause is ambiguous

    Anyone else able to successfully add a JOIN to this DataObject::get()?

    Thanks,
    Garrett

  • Willr
    Avatar
    Forum Moderator
    5511 Posts

    Re: JOIN Not Working on Function BlogEntries Link to this post

    Easiest way is to avoid using a join I guess. Make a custom function which returns the file object rather than joining.

  • Garrett
    Avatar
    Community Member
    245 Posts

    Re: JOIN Not Working on Function BlogEntries Link to this post

    So you're just saying that doesn't work? Why not? The join is an argument of DataObject::get(). Custom function... how would I go about that? Would I call that from the template or from within BlogEntries()?

  • pter
    Avatar
    Community Member
    38 Posts

    Re: JOIN Not Working on Function BlogEntries Link to this post

    Does the following link help? You must be defining a ParentID defined in your $filter variable that needs to be changed to "SiteTree"."ParentID". I've been rabbiting on about it over here (and in links from that)...

    http://www.silverstripe.org/upgrading-silverstripe/show/293640#post293640

    UPDATE - hmmm... this might just be my site's reaction to 2.4.2 and Whuff knows why it is happening. Following up elsewhere.

  • Garrett
    Avatar
    Community Member
    245 Posts

    Re: JOIN Not Working on Function BlogEntries Link to this post

    Unfortunately, no, this thread doesn't help me as I haven't added any extensions or anything like that. That thread is kind of over my head, honestly. Let's take a simpler view:

    After I upgraded cms and sapphire folders to 2.4.2 (was running 2.3.6), I was getting all sorts of PHP notices and errors from my blogs (I run 5 or 6 on this site), so I decided to upgrade That module (the blog) from 0.1 to 0.4 (the latest) and then added back in all my custom functions and queries, and although the previous errors Did go away, it's only if I don't use the JOIN to the File table, which means I can't get the images that go with the blog posts, and also, possibly even more strangely/importantly, I am not getting the TAGS, which would be considered central to a list of blog posts. The Tags field is right there IN the BlogEntry table. So I just don't see the issue here. I think whoever maintains the blog module needs to make sure it works with the latest version of SilverStripe. I followed the upgrade procedure to a T.

    Please keep yammering with @willr about this, because it's a major issue. We shouldn't be getting SQL errors from canned queries.

    //Garrett

  • Willr
    Avatar
    Forum Moderator
    5511 Posts

    Re: JOIN Not Working on Function BlogEntries Link to this post

    Garrett you should contact the module maintainer of blog (Phalkunz and Carlos) to see whats up.

    Have you tried the latest daily build of the module?

    it's only if I don't use the JOIN to the File table, which means I can't get the images that go with the blog posts,

    So where are you using the File object? If its the template or in the PHP even you shouldn't have to join the file table as the ORM will handle getting all the file data for you. Unless you're using the join to filter the results or join on a table which is not already joined then you won't have to manually join.

  • Garrett
    Avatar
    Community Member
    245 Posts

    Re: JOIN Not Working on Function BlogEntries Link to this post

    What's up @willr. Thanks again for your reply.

    Umm, I was not aware that this query would automatically return any file/image objects associated with the BlogEntry page. Huh. Well I already backed out the upgrade as I was having too many issues. As I mentioned, the Tags weren't showing up either, which was just as bad, and they're right there IN the BlogEntry table.

    So you're saying that in my template, I can just refer to "$Filename," instead of joining the File table? You're saying the File table is already joined?

    I wish there were an easier way to see the SQL in DataObject::get(). Is there? I only see the SQL statement when it fails!

    I will now remove the join in my current version (2.3.6) of the site and try using $Filename in the template. I have my doubts!

    //Garrett

    //Garrett

  • pter
    Avatar
    Community Member
    38 Posts

    Re: JOIN Not Working on Function BlogEntries Link to this post

    Thanks to Willr I'm enlightened (even if not happy as now I have to find another way to do it). My problem turns out to be caused by my approach to the module I was making. I extended the Hierarchy object then used add_extension in my module's _config.php to add to, e.g., Page.

    Previous versions up to and including 2.4.0 didn't mind but, and I have to presume this is correct behaviour that was not being followed by previous versions, SS generates a linking ParentID variable in the extended class. Does not happen for SiteTree which already has a ParentID from including Hierarchy. I now try to find a different way to extend the Hierarchy object's stageChildren() function (in Yet Another Hide Pages In The CMS Module).

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