When you need to retrieve all content of a specific property from a Page/Block type, normally you will use the IContentLoader or IContentRepository, or even IContentModelUsage to get all instances of a content type then select them by property. This is the correct implementation by code.
But what if you only need to check the property’s value for debugging/development purpose? Or generate a quick report for a property’s usage? Then you have to wrap the above implementation inside an API or a custom tool, and then build, deploy, etc. Seems excessive for a single-use requirement, doesn’t it? This article will guide you to go through this by just a SQL script.
In this article, we will try to get all MetaDescription of Standard Pages inside the Alloy template:
Before we start
In order to execute the script, we need to know the Primary Key ID of both Standard Page and MetaDescription Property.
For the Standard Page, we can easily spot its Primary Key by going to the Admin Section -> Content Types -> StandardPage, it’s displaying in the URL. For my local environment, the Primary Key is 20
Choosing a Global Software Development Partner to Accelerate Your Digital Strategy
To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.
For the MetaDescription property, we need to query the table tblPropertyDefinition, to get all property’s definitions of the StandardPage.
select * from tblPropertyDefinition where fkContentTypeID = 20 -- standard page's pkId
As you can see, the Primary Key ID of MetaDescription property is 138 in my local machine.
Now we have all the necessary IDs, it’s time for the script:
select c.pkID as PageID, wc.Name as PageName, wcp.LongString as MetaDescription from tblContent c inner join tblWorkContent wc on c.pkID = wc.fkContentID inner join tblWorkContentProperty wcp on wcp.fkWorkContentID = wc.pkID where c.fkContentTypeID = 20 -- standard page type ID and wc.Status = 4 -- last publish version and wcp.fkPropertyDefinitionID = 138 -- metadescription property ID
To get the data we need, we have to do an inner join between 3 different tables: tblContent, tblWorkContent and tblWorkContentProperty.
- tblContent: This is one of the most important tables in Optimizely Content Cloud. Each row contains all crucial information of a content. It includes the ID (pkId) of the content and this table is considered as a root to connect with other tables in the system. In this example we will use the condition where tblContent.fkContentTypeID = 20 to only get contents of type Standard Page.
- tblWorkContent: This table contains all versions of content in the CMS. When you Save/Publish a page or a block, a new record will be inserted to the tblWorkContent. Or when you browsing the Versions add-on in Edit mode, you are actually seeing data from this table. For this script, we use the condition where tblWorkContent.Status = 4 to retrieve only the last publish version of a content.
- tblWorkContentProperty: This table also contains data of versions. But unlike tblWorkContent, this one store the data of each property. When a page/block is published, there could be one or more properties get modified within a version, so this table will keep track of all modified properties for a specific version. For the purpose of this scenario, will will use the condition where tblWorkContentProperty.fkPropertyDefinitionID = 138 to only take the MetaDescription property.
That’s it! Now you have the magic spell in your hand, feel free to discover your database whenever you need to. Happy coding!