CODEWITHSUNDEEP
sqlblogskenticosocial-media
Rob M
Rob M

Reputation: 311

Link Kentico blog posts to corresponding social media post

My Kentico site has a blog that uses Kentico's Social & Community apps to synchronize its posts to Twitter, LinkedIn, and/or Facebook. In the transformation (Page types -> Blog post -> Transformations -> Default) that displays a blog post, I wish to render an icon for the corresponding social media site(s) where the post also appears. The icon(s) will link directly to this post on the corresponding site.
It appears that the transformation we are using is able to render fields from the dbo.CONTENT_BlogPost table. However, the information we need is in:

dbo.SM_FaceBookPost.FacebookPostExternalID

dbo.SM_LinkedInPost.LinkedInPostURL

dbo.SM_TwitterPost.TwitterPostExternalID

I assume if I could find the query used by this transformation, I could add some left joins to get the data I need. But although I see its class name is 'cms.blogpost', I am unable to locate that class through the Modules application. Where can I find the query, and can I modify it? Or is there an alternative approach I should be taking?

Upvotes: 0

Views: 102

Answers (2)

Brenden Kehren
Brenden Kehren

Reputation: 6117

You will not find the cms.blogpost class in the modules application simply because it is a page type and not a custom class from a module. You can find the actual definition of the blog post in the Page Types application and the content for the blog posts in the content_blogpost table in the database.

To find the data you need you can join the data in a SQL query like so:

SELECT TwitterPostID
FROM View_CMS_Tree_Joined
    INNER JOIN SM_TwitterPost ON DocumentGuid = TwitterPostDocumentGuid

Or you can use an object query like so:

int twitterPostID = CMS.SocialMarketing.TwitterPostInfoProvider.GetTwitterPostInfosByDocumentGuid(Eval<Guid>("DocumentGUID"), CMS.SiteProvider.SiteContext.CurrentSiteID).FirstObject.TwitterPostID

Upvotes: 1

Trevor F
Trevor F

Reputation: 1437

Not sure if you will be able to find that query as that may be hidden. The BlogPost is just a Page Type so there isn't a module that will have that class, it's contained in the CMS_Document, CMS_Tree, and CONTENT_BlogPost tables. So if the query is available, it may be under the BlogPost page type, or the CMS.Core page type.

You do have a couple options to get the information. You can use Macros in a Text/XML transformation (or using CMS.MacroEngine.MacroContext.Current.ResolveMacros("") ) to get the SM_FacebookPost items and perform a .Where("") on it. It may be a lengthy macro but you can use the System -> Macros -> Console to do some testing.

You can create a custom transformation method for ASCX transformations to perform the lookups and logic using the API (or if no API for it, using the CMS.DataEngine.QueryInfoProvider.ExecuteQuery() ).

Lot of options to get it, but may require at least some advanced macros or custom coding.

Upvotes: 1

Related Questions