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
Cubase Crack
awesome post
2/6/2018 6:55:10 PM

cialis
Some kind of sexual stimulation should be applied for an erection to take place with .
1/31/2018 9:56:24 AM

download facetime app
Facetime is a Mac and Apple IOS App utilized for video technologies. This program is simply released for citrus solutions.
1/3/2018 11:40:01 AM

Kodi On Roku for PC
Looking for a way to use Kodi on Roku? Read this helpful guide that explains everything you need to know about setting up and using Kodi on your Roku.
1/2/2018 11:23:47 AM

beginner's guide to iq option
If You Are Wondering If Automated Option Robot Trading Software a SCAM or Not? Binary Auto Trading Exposed! We Give You 100% Honest Review Real Result!
12/29/2017 10:27:57 AM

lucky patcher download
New Download Lucky Patcher Apk the latest version for android, ios, windows phone, and PC.
12/29/2017 9:15:05 AM

paytm app download
Now recharge your mobile, DTH or datacard, pay bills and shop online from anywhere, at anytime with Paytm App and Wallet.
12/27/2017 9:37:11 AM

free psn codes list
In any case, if you are not sure to go for it or doubtful about doing this, why don’t try it and see it yourself. Anyways you are not going to lose anything. But yes. You may lose the opportunity to earn your free PSN codes without any repentance.
12/19/2017 10:31:38 AM

buy btc with credit card
Discover top rated binary options robot software in 2017. Compare the best automated trading tools and start using them in your binary strategy.
12/14/2017 4:48:18 AM

BB&T customer service
I haven’t any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us.
12/10/2017 9:44:56 PM

Download Nox App Player
Nox App Player is one of the best and most loved android emulators by Windows and Mac users. Nox Emulator is available for Windows 10/8.1/8/7 & Mac PC.
12/6/2017 7:14:13 AM

viva video
VivaVideo is one of the best Video Editor, Movie Maker, Slideshow/Photo Video Editor with Powerful HD Camera, Professional Video Editing ...
12/4/2017 9:50:06 AM

generic
What is for women - and does it even exist?
11/23/2017 7:38:28 AM

Assignemnt Corp
A Database Table depicts a substance particular to the issue, it tries to handle. For example on the off chance that you had a database table that was to store representatives, the database table would depict the rundown of the considerable number of workers, and each Line would have the points of interest of a representative.
11/17/2017 11:19:51 AM

omega replica
nice!
11/17/2017 3:39:51 AM

gbwhatsapp apk for android
We all have heard the name Whatsapp and almost everyone having a smartphone are using WhatsApp on their device. Whatsapp is regarded as one of the best messaging apps in the world of smartphone.
11/15/2017 11:07:29 AM

Percy Jackson
Fantastic post. Very informative indeed. Thanks
11/11/2017 5:52:50 AM

Decanter
Data plays a vital role in every field of life.I will come back again to read some more interesting things on this topic
Thanks to shares us :)
11/8/2017 11:31:26 AM

ccie wireless 400-351 dumps
It’s very excellent information and more real facts to provided that post.Thank you for sharing this information.
11/4/2017 4:51:59 AM

waqar mazhar
nice post good
10/31/2017 4:23:17 PM

تور تایلند
AMAZING BLOG – KEEP IT UP & THANKS A LOT
10/25/2017 12:18:04 PM

Zucchini
Regular visits listed here are the easiest method to appreciate your energy, which is why why I am going to the website everyday, searching for new, interesting info. Many, thank you!
10/16/2017 8:13:51 AM

How To Make Yourself Pee
Easily, the article is actually the best topic on this registry related issue. I fit in with your conclusions and will eagerly look forward to your next updates.
10/15/2017 5:08:25 PM

Sex Positions
I think it could be more general if you get a football sports activity
10/12/2017 6:10:49 AM

Game System Requirement
The blog is really appreciable and i like to keep on v
10/11/2017 12:37:01 PM

Conversation Starters
I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read!! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site.
10/10/2017 7:57:11 AM

pokemon go download
I have been looking at a couple of your stories and i can state really well done. I will bookmark your web journal
9/26/2017 12:44:43 PM

mobdro download
I was very impressed by this post, this site has always been pleasant news. Thank you very much for such an interesting post. Keep working, great job!
9/26/2017 5:59:28 AM

sara
HI,
This only professional writers can make this kind of material more....
9/20/2017 7:16:09 AM

things consider mountain biking
i love this blog as i love mountain biking
9/7/2017 6:28:01 PM

IRS phone number for live person
Its a good post..keep posting and update the information.selfie stick....
8/8/2017 11:32:36 AM

drug and alcohol programs in Colorado
Your website is this one-stop buy everything educative crafting.
7/26/2017 1:47:15 PM

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

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

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

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

 Security code