Referenced data retrieval

#Custom software development #.NET

SHARE

 
When working with data this is a common case when we need to present some complex object that consists of a couple of logical objects, e.g. book and its author. This scenario is also applicable with Kentico CMS. In this post I'd like to present the approach, that I found useful in some cases.   

 

This is a rare case when we build a standalone object (database table), that is completely independent. Much more often we work with complex data model, with parent-child, many-to-many, etc. relations. Those relations are implemented with references: child object contains a reference to its parent or there is an extra table that keeps references to two or more objects in order to implement many-to-many. 

There are many examples of data referencing in Kentico: page category, page creator, page attachments and many more. Usually Kentico system relations are quite easy to resolve and get referenced object data, e.g. getting page creator data when working with a page. This is possible and quite easy because they are covered by Kentico API and usually available in a context of particular type.

However business needs push us to extend what Kentico gives us out of the box with our custom objects. In this case Kentico doesn't know how to resolve relations between those objects by default, which is fine as it provides us with a couple of mechanisms to handle this.

One of those is to implement a database view, which is possible even without direct access to a database. Another would be usage of custom queries. Also we might consider implementation of custom macro or custom transformation method to present referenced object data on the UI. All those methods are fine in general and each has pros and conf. However I'm going to talk about different one...

Kentico page types, custom tables and custom module classes (which means everything you'd normally used to store data) allows us to create a fields without database representation. This option could be used as auto calculated property or... to store a fields of a referenced object. Let's say we have some brands stored into a custom table and each product contains brand ID in order to reference it. We are not really interested in the ID, as it is just a numeric value, which does not bring any value in presentation layer. So obviously we want to show something meaningful on a page - brand name in this case. 

I suggest adding new field to a product page type calling it, for example, BrandName. As I've already mentioned this field should be a fields without database representation. This gives us a container, but how do we populate it? Kentico allows us to override default queries for object type. So now we need to implement a query that retrieves all objects and a single object and name them 'selectall' and 'select' respectively. Those queries are places, where we populate our container. It is a bit of a challenge to implement a query, that will work for given approach as ##COLUMNS## macro is required. I came out with the following query:

 

select c.*, b.BrandName

from

(select ##TOPN## ##COLUMNS##

from customtable_SampleTable

where ##WHERE##

order by ##ORDERBY##) c

left join customtable_Brand b on c.brandId = b.ItemId

 


Having this set up I added Custom Table Data Grid web to my test page, selected Sample Table and columns I want to show and got needed data on a page.

This approach does not require developer to remember about an extra field that sits in another table, as it always goes along with the main object. In case when we use either custom query or database view we do not get this field by default, but have to take extra steps to fetch it.

Please share your experience or ideas of how fields without database representation could be used.

Author

Check other articles

Bitsorchestra
5 5

What our clients say

Bits Orchestra team are outstanding developers​. They listen carefully to our business needs and easily turns our business objectives into a well thought out and executed development effort. Roman is very bright and definitely the most capable developer that has worked on our site. He is not only a Kentico expert but has successfully tackled other complicated development assignments demonstrating expertise in both front and backend development. Roman takes initiative to suggest enhancements that make site maintenance easier while improving the customer experience. The team is very responsive to our work requests and has great follow up. They have also worked very business partners and this has reflected positively on our company. Roman is a true partner for us and a tremendous asset to our organization. We will continue to work with them and would highly recommend Roman and his team for your development needs. He and his team will exceed your expectations!
 Alan Lehmann
Alan Lehmann
President at In energy sector

What our clients say

The Bits Orchestra team does excellent work. They are always available and I appreciate our frequent calls and screen-shares together. Their dedication to the projects and knowledge of Kentico is outstanding. They truly care about the quality of their work, and became a part of our team easily!
Shena Lowe
Shena Lowe
Managing Partner at Consensus Interactive

What our clients say

We hired Roman for a Kentico analysis project and have been very satisfied. He is very skilled and professional. We are looking to hire him and his team again on future projects.
Sylvain Audet
Sylvain Audet
CEO at MyDevPartner.com

What our clients say

Roman and team have taken over an existing Kentico EMS site for a large US Oil Company. So far, they have handled every single request that we have thrown at them and these were diverse, challenging, often bespoke, usually urgent and almost daily, over the last 11 months. Their work is of an extremely high quality, they are capable, quick and we have great confidence in the support that we are getting.
Jon Hollis
Jon Hollis
Head of Web Development at confidential

What our clients say

Bits Orchestra team was very helpful, they had a good understanding of the brief and deep knowledge of the system. They were always keen to provide advice and recommendations that benefit the project substantially.
Ramon Lapenta
Ramon Lapenta
Senior Front End Developer at Cyber-Duck Ltd