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
(select ##TOPN## ##COLUMNS##
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.