Forums (Obsolete)

Member

Yehuda - 1/28/2014 10:22:32 PM

Many to Many relationships

Hi,

I need to achieve a way to incorporate many to many relation between a document type and a custom table.
I have both the many to many table and the other side of the relationship as a custom table.
This works fine so far, but I need to have a way of letting content editors choose the items they want to include when editing the document.

I understand I'll need to add this ability myself in the global event handlers, but how can I add a control to choose the items to the edit form so I can then get the chosen item ID's in the event handler?

Thanks

Kentico Consulting

Kentico_RichardS - 1/29/2014 1:50:07 AM

RE:Many to Many relationships

Hi,

Thank you for your message.

Global handlers will almost certainly wont be used here, why whould they be? If I understand your scenario you would like to have a field (drop down list) in your document type where you would offer your editors a selection of items which are stored in a certain custom table?

There is no need for you to customize the Kentico as this is what we already provide. If you use field of type Text -> Drop-down list and the SQL Query as a data source you can easily achieve this kind of scenario.

Let us know if you need further assistance.

Kind regards,
Richard Sustek

Member

Yehuda - 1/29/2014 2:03:53 AM

RE:Many to Many relationships

I thought about it, but in my case the value isn't being stored in the document table.

Basically, I have 3 tables.
I have the document table, a table called Tags, with a PK of TagID, and another table called ArticlesTags with two FKs.
One to the tags table, and one to the document table.

So I first need to find if the tags that were added to the article exist.
If not, add them, and then add/remove the entries from the ArticlesTags table.

I first wanted to use the included Kentico tags mechanism, but I need to have a lot more information on each tag other than just a tag name.

Thanks

Kentico Consulting

Kentico_RichardS - 1/29/2014 2:32:35 AM

RE:Many to Many relationships

Hi,

We need to a little more elaborate on the issue.

First of please confirm that you inded want to have a field in your document which would be populated with some values which would be stored in another custom table. If so, the values do no to be stored in the document type. With the SQL query I mentioned above you can get the values out of the custom table.

Try a little example so that you know how that works.

Create a new field as Text -> Drop down list -> Choose SQL Query as a data source. Now type this in the Data source ->

select UserName, UserName from CMS_User

Save the field and try to create a document with this field. Now you will see that the drop down will be populated with all users.

You can do the same with your tags. You just need to create a SQL query for it. Also you can use use macros in the Data source option, which you will need to use to get the right documents. Simple example with macro can by like this:

select UserName, UserName from CMS_User where UserID = {%cmscontext.currentuser.userid%}

Please note that these are just examples to show you how this works so that you can adjust it to your needs.

Kind regards,
Richard Sustek

Member

Yehuda - 1/30/2014 1:09:23 PM

RE:Many to Many relationships

I have 3 tables with these fields:

Articles -
ArticleID - PK
ArticleName
ArticleText

Tags -
TagID - PK
TagName
TagDescription

Articles_Tags -
ID - PK
ArticleID - FK
TagID - FK

(this is just a sample, I have many more columns, but they're unrelated)

As you can see, I have no information in the articles table about the tags.
I have a custom query written to get the required information when I need it.

What I want to do, is that when an article is being edited/created, to insert the tag if it doesn't exist yet, and then add the tag id and article id to the Articles_Tags table.

Currently I feel that my best option is to add a Tags column to my Articles table, and then add to it the tags ids separated with commas and in my global event handler,
process that and add the tags to the Article_Tags table.
This will not create new tags though.
(in other scenarios I could use the tag name instead of the id, but in this case the tag name is not unique).

Thanks again

Kentico Consulting

Kentico_RichardS - 2/4/2014 1:44:45 AM

RE:Many to Many relationships

Hi,

Thank you for your message.

What I want to do, is that when an article is being edited/created, to insert the tag if it doesn't exist yet, and then add the tag id and article id to the Articles_Tags table.

This can indeed be accomplished with a global handler. You can set up the function so that it will be fired upon document update/insert event as per http://devnet.kentico.com/docs/devguide/event_handlers_overview.htm

Then in this you will need to check whether a Tag is assigned to your document (Article_Tags) table. If the Article_Tags is a custom table mapped inside Kentico you can use our API http://devnet.kentico.com/docs/devguide/custom_tables_api_examples_managing_custom_table_data.htm , if its not linked, you can create your own sql query to determin if the record is there. Simple example:

string sql = "SELECT * from Article_Tags something something";
            DataSet ds = CMS.SettingsProvider.SqlHelperClass.ExecuteQuery(sql, null, QueryTypeEnum.SQLQuery);
            DataRow dr = ds.Tables[0].Rows[0];
            string UserName = dr.ItemArray.GetValue(0).ToString();

Once you find out that the record is not there you can simply insert it using the API mentioned above.

Let me know if you need anything else.

Kind regards,
Richard Sustek


Member	Yehuda - 2/4/2014 1:08:11 PM RE:Many to Many relationships Thanks, but is there a way to pass data (the tags and/or tag ids) from the form to the event handler without having a backing field in the data base? Or the only way I can do that is by having a text field that includes a list of all tags? Thanks again

Kentico Consulting

Kentico_RichardS - 2/4/2014 10:00:02 PM

RE:Many to Many relationships

Hi,

Thank you for your message.

You mean that you want to write or select the tags in the form itself which will be assigned to the document in event handler? If so please see my previous answers where this is explained.

If however you would like to select multiple tags you would need to develop your own form control. In fact it could be best if you would develop it anyways. ->http://devnet.kentico.com/docs/devguide/developing_form_controls.htm

Kind regards,
Richard Sustek

Member

Yehuda - 2/4/2014 10:54:48 PM

RE:Many to Many relationships

Thanks. I guess I'm not clear.
I do have a custom control for that.
But I don't want to have a column on my article that stores the tags I select.
What I want is to insert the selected tag id's to the TagArticles table, which have FKs to both the Articles table and the Tags table.

I have everything I need, but my only issue is how to put my custom control on the edit form, without having a column in the database for it,
and how to get the selected tags in the event handlers.

I can't use e.Node.GetValue("Tags") for example, because there is no column in Articles for that.
Is that even possible?

Hope that was more clear.
Thanks

Kentico Consulting

Kentico_RichardS - 2/4/2014 11:10:40 PM

RE:Many to Many relationships

Hi,

Thank you for being more clear,

This is surely possible. The tables you created are custom created in Kentico, is that right? If so you can use our API to insert, update, delete a custom table items quite easily. Please checkout following guide: http://devnet.kentico.com/docs/devguide/custom_tables_api_examples_managing_custom_table_data.htm

Does this help?

Kind regards,
Richard Sustek


Member	Yehuda - 2/5/2014 12:40:45 AM RE:Many to Many relationships Afraid not. I know how to add data to the custom tables, but I what I want is to pass data from the edit form to the event handler. I guess I'll add a column to my Articles which will have a string with all the tags. But that was something I wanted to avoid, and the purpose of this thread. Anyway, thanks...


Kentico Consulting	Kentico_RichardS - 2/5/2014 2:28:04 AM RE:Many to Many relationships Hi, Just a quick note - if you want to temporarily store some data you can use CookieHelper class and in your custom form control store the value and in the global event check whether it exists. Would this work? Kind regards, Richard Sustek


Member	Yehuda - 2/6/2014 9:04:04 PM RE:Many to Many relationships Thanks. That's an idea. I'll try this out.