Item type dropdown - SQL for Product Category Field Options

Nuno Aguiar

Posted on 16/02/2016 16:59:59

Hi,

There are multiple situations where we would need to create an dropdown/checkbox/radio button list that gets the options based on a SQL query. A lot of them are a "duplication" of the product category dropdown field options.

The problem is that data is stored as an XML (check attachment), so we cannot do that. My feature request is to have that somehow possible (either extend the Source Type or covert the XML into a DB table and columns)

Best Regards,

Nuno Aguiar

Replies

Adnan Catovic

Posted on 14/11/2018 10:55:42

I have exactly the same issue? Any answers to this one?

I have a sql query as a source for check boxes, after adding a value it does not refresh the list until the item type is re-saved?

Nicolai Pedersen

Posted on 14/11/2018 18:27:51

Hi Adna

No news - you cannot get the options using SQL.

The item type caches the result of the SQL to improve performance. We could add a feature to not cache that information.

BR Nicolai

Adnan Catovic

Posted on 14/11/2018 18:43:37

Hi Nikolai,

Thanks a lot for your response.

That would be perfect! I was looking for an option to remove the caching.

Will that come in next release or you are willing to create a patch for this?

In the meantime, if the feature is to wait longer than a week, do you think it would be a good approach if I write code which will change a timestamp on ItemType_[TypeName] file? After the item type is re-saved we get fresh results.

Nicolai Pedersen

Posted on 14/11/2018 19:00:09

It is definitely not a high priority hotfix, so you will not have it in a week :-).

It will probably be part of 9.6 due January 27th earliest.

If changing the timestamp works, please go ahead!

BR Nicolai

Nicolai Pedersen

Posted on 15/11/2018 15:13:55

This post has been marked as an answer

We have looked into this - and this is what our developer found - basically it only caches for 5 minutes.

Namespace Content.Items.Metadata

Public Class FieldOptionMetadataSqlSource

...

Public Overrides Property Values As FieldOptionMetadataCollection

Get

If IsNothing(Cache.Current.Item(CacheKey)) Then

Cache.Current.AddOrUpdate(CacheKey, GetData(), New CacheItemPolicy() With {.AbsoluteExpiration = Date.Now.AddMinutes(5)})

End If

Return Cache.Current.Get(Of FieldOptionMetadataCollection)(CacheKey)

End Get

Set(value As FieldOptionMetadataCollection)

Cache.Current.AddOrUpdate(CacheKey, GetData(), New CacheItemPolicy() With {.AbsoluteExpiration = Date.Now.AddMinutes(5)})

End Set

End Property

By the way It have clear cache api, but it is not so simple to call it

Dynamicweb.Content.Items.Metadata.ItemType itemTypeWithSqlSource = Dynamicweb.Content.Items.ItemManager.Metadata.GetItemType("SQLSource");

Dynamicweb.Content.Items.Metadata.ItemField fieldWithSqlSource = itemTypeWithSqlSource.Fields.FirstOrDefault(field => field.SystemName.Equals("Sqlsourced", StringComparison.OrdinalIgnoreCase));

Dynamicweb.Content.Items.Metadata.FieldOptionMetadataSource sqlOptionSource = fieldWithSqlSource.Options.Source;

sqlOptionSource.Update(); //this clears the cache of specific field source

Also I've tried to check frontend for that issue using Dynamicweb.Rendering.ViewModelTemplate<Dynamicweb.Frontend.PageViewModel> template

and there is no such problem - every time page is loaded options fetched from sql due cache key compose implementation.

Votes for this answer: 1

Adnan Catovic

Posted on 15/11/2018 19:09:40

Hi Nikolai

Thanks a lot for looking into this!
I will defenitely try to use the API to clear the cache. I wll figure out from where I will call it.

We do not have any issues on frontend. Here is the setup: We have a specific item type called colors (see the attachment ColorsItemType.PNG). Then we have the settings item type on which we have use the colors item type as collection (see the attachment Colors and Textures Item Type). Then we have the Product Item Type on which we have colors drop down based on the SQL query (see the attachment ColorsOnProducts.PNG). Finally we define system wide colors in the Settings content node (see the attachment ColorsSetting.PNG) which is a source for the SQL query from the previous step. Finally, we use the colors on the Product content node (see the attachment ColorsOnProductsUsage.PNG) where we use the configured colors in Settings content node.

The idea behind the whole setup is to have system wide colors defined in the settings node and used on product based nodes. The problem with that is that after a color is added in the settings node it does not refresh immediatelly (obviously it will in 5 minutes).

I believe that we can live with 5 minutes cache in this case if the clear cache implementation turns out to be time consuming. It will probably wait for your release in which we will just uncheck the box not to cache SQL query results :).

Thanks!

Developer forum

Item type dropdown - SQL for Product Category Field Options

Replies