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.

Blog Module /

Discuss the Blog Module.

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

JOIN Not Working on Function BlogEntries


Reply


8 Posts   1272 Views

Avatar
Garrett

Community Member, 245 Posts

5 October 2010 at 4:09am

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

Avatar
Willr

Forum Moderator, 5513 Posts

6 October 2010 at 10:53pm

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

Avatar
Garrett

Community Member, 245 Posts

9 October 2010 at 2:18am

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()?

Avatar
pter

Community Member, 38 Posts

12 October 2010 at 3:45pm

Edited: 12/10/2010 5:28pm

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.

Avatar
Garrett

Community Member, 245 Posts

13 October 2010 at 2:28am

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

Avatar
Willr

Forum Moderator, 5513 Posts

13 October 2010 at 9:59am

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.

Avatar
Garrett

Community Member, 245 Posts

14 October 2010 at 3:20am

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

Avatar
pter

Community Member, 38 Posts

14 October 2010 at 7:51am

Edited: 14/10/2010 7:57am

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