Loading

Referenced data retrieval

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.


referenced-data.jpg


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.


Comments
Marketing Assignment Help
Things are very open and intensely clear explanation of issues. was truly information. Your website is very beneficial.
7/7/2017 12:27:53 PM

Human Resource Thesis Help
I must say we should have an online discussion on this.
7/7/2017 12:04:57 PM

Human Resource Thesis Help
only professional writers can make this kind of material, cheers.
7/7/2017 12:04:00 PM

iPhone 8 Price
Definitely a well written post. Thanks for sharing.
6/30/2017 5:09:54 AM

Mass communication colleges in Delhi
Nice post . keep up the good work!
6/7/2017 11:02:54 AM

amsterdam tours
Thanks for sharing the details of referenced data retrieval. I searched my websites to clear my doubt about the data retrieval, but after reading this article I came to know more about this method. Keep sharing more articles like this.
6/5/2017 12:08:15 PM

my world
nice..
6/1/2017 6:28:43 AM

Magento Development company
Excellent is the only word i can give u for this wonderful blog, keep it up. I will come back again to read some more interesting things on this topic.
5/22/2017 5:38:15 AM

internet
s a month, if you can get a prescription for those amounts.
5/12/2017 10:35:27 AM

>Citrix Platinum Partner
Great blog! keep up the great work!
4/24/2017 11:50:43 AM

bestessay.com
The number of people is using the different mechanisms to retrieve or save the date. Today complete database systems are developed and quite popular among companies where they can save the date with higher securities. Thank you so much for sharing the article about the reference of data retrieval.
4/21/2017 7:32:29 AM

IT Business Tracking System in india
I truly like your type of composing.
4/17/2017 11:50:11 AM

it disposal companies
Great things you’ve always shared with us. Just keep writing this kind of posts.The time which was wasted in traveling for tuition now it can be used for studies.Thanks
4/10/2017 11:10:16 AM

cheapest seo
Great things you’ve always shared with us. Just keep writing this kind of posts.The time which was wasted in traveling for tuition now it can be used for studies.Thanks
4/3/2017 10:43:27 AM

impotence
I have a perscription for , but.
3/24/2017 3:52:38 PM

College Credit For Life Experience
If I have not incorrect knowledge about this topic, then reports, plus queries are the two major methods of the retrieved data from any database. There are several similarities among them, but queries usually choose a relatively small ration of the database, whereas reports display greater quantities of data. Queries even present the data in a standard arrangement and frequently saw it on the screen; while reports let formatting of the output but you like plus is usually printed. Reports are designed through a report creator manufactured into the DBMS. You’re working as well stunning what you explained by showing coding and its results what came at the end.
3/22/2017 10:41:05 PM

Jack
Nice post.
3/22/2017 6:27:31 PM

ipad servipad service center in Delhiice center in Delhi
The blog is really appreciable and i like to keep on visiting this site once again that it would help me in further thanks for sharing the info.
3/15/2017 11:00:40 AM

 Security code