Forums (Obsolete)

Certified Developer 12

chetan2309-gmail - 10/10/2013 12:27:35 PM

SQL query for Repeater

Hi,

In my document type I have data where in A record can 1 to many relation for certain column value. eg.

Name Category

Aaron Horses
Kate Horses|Aquatics(This is how Kentico is saving in DB)

User while creating record whill have multi select check box for category and can select more than one checkbox.

How can I design a query to fulfill this request. I am using repeater and using where clause like and taking paramter from querystring.

So I want if request is like ?cat=Horses
Both should appear.

?cat=Aquatics ==> Kate result.

Any help will be appreciated.

Chetan

Member

vcarter - 10/10/2013 1:00:24 PM

RE:SQL query for Repeater

Many to Many

That article is a good primer on how to accomplish this task.

I added a split function to my database and was able to then use something like this

SELECT ##TOPN## ##COLUMNS## FROM View_custom_news_Joined 
CROSS APPLY dbo.Custom_Fn_ColumnSplit(View_custom_news_Joined.BusinessUnit,'|') bu
WHERE (##WHERE##) ORDER BY ##ORDERBY##

In my case I am assigning business units to each news article and using this to filter the results as needed.

Hope that helps.

Certified Developer 12

chetan2309-gmail - 10/10/2013 2:26:26 PM

RE:SQL query for Repeater

So in my case I need to put method in DB and call this method from repeater, right. No need to write custom macro resolver.

1. Any links how and where I can add this method.
2. My current code is solving using like this in where clause of repeater

Speciality = case (? Cat|Default)......

I wonder how will accommodate this?

Maybe i need to use custom repeater with query.

Any hints

Member

vcarter - 10/11/2013 7:59:24 AM

RE:SQL query for Repeater

OK, what you need to do is add that Split function to your database. That will allow you to handle the "|" delimited lists(seriously Kentico, how about using comma's like everyone else), and the query snippet I used above is your template for a custom query, which can be used in a repeater with custom query webpart. At which point you can pass in your WHERE parameters manually, or by using a custom filter.

The link above shows this function and the query is an example of what you would use.

So in your case it may look something like:

SELECT ##TOPN## ##COLUMNS## FROM View_custom_Category_Joined 
CROSS APPLY dbo.Custom_Fn_ColumnSplit(View_custom_Category_Joined.Specialty,'|') specialItem
WHERE (##WHERE##) ORDER BY ##ORDERBY##

The reason I am using the joined view is because that returns node data as well as your document type(Category) data, which allows you to treat it like any other type. Using this method, you will have to create your own "detail" page as selected item transformations do not work with query repeaters.

I will check this throughout the day and try to help you as much as possible.


Certified Developer 12	chetan2309-gmail - 10/18/2013 10:04:28 PM RE:SQL query for Repeater Hi vcarter, Somehow I manage to sail across using like query in the repeater. Since we have control our what user will select it made sense to me to use like query. Thanks alot for your effort. I am sure somewhere down the line I would require your solution TIA, Chetan


Member	vcarter - 10/21/2013 8:21:15 AM RE:SQL query for Repeater Using like is a valid option, which I had considered when creating he solution mentioned above. In the end I was not comfortable using string matching to get my results, but for most applications it should work perfectly. Glad you have a solution.