Forums (Obsolete)

Member

Jason Simone - 4/22/2011 6:08:13 PM

Sorting multiple document types in one repeater

Hello,

I am trying to collect multiple document types into a single repeater and retain the ability to sort on them. I want to combine recent News documents and Event documents an display them together in order of creation / occurrence. I feel like I am close, but can't quite close the gap.

First I tried a CMSRepeater:

<%@ Register Src="~/CMSWebParts/Viewers/Documents/cmsrepeater.ascx" TagName="repeater" TagPrefix="uc1" %>

<uc1:repeater
runat="server"
ID="whatsNew"
ClassNames="CMS.News;CMS.Event"
TransformationName="CMS.MenuItem.WhatsNewItem"
SelectedItemTransformationName="CMS.MenuItem.WhatsNewItem"
QueryName="CMS.MenuItem.selectWhatsNew"
PagerPosition="bottom"
ResultsPosition="top"
RelationshipName="isrelatedto"
ShowNewButton="True"
NewButtonText="Add Entry<br />"
ShowEditDeleteButtons="True"
EnableViewState="false"
Path="%" />

Note the non-existent property QueryName in the repeater. While I can get two document types to display together using this method, and display them how I want with a transformation, thee is no way for me to sort them together.

Next I tried a QueryRepeater:

<cms:QueryRepeater
runat="server"
ID="whatsNew2"
TransformationName="CMS.MenuItem.WhatsNewItem"
QueryName="CMS.MenuItem.selectWhatsNew"
Path="%" />

However, this results in a runtime error stating "Type 'CMS.Controls.QueryRepeater' does not have a public property named 'Path'." While I can return the proper results and sort them accordingly, I cannot limit the results by location within the CMS site tree. For my repeater, it is necessary to be able to do both.

So, my question is, what is the best or easiest way to achieve this functionality?

Kentico Support

kentico_jurajo - 4/25/2011 1:31:42 PM

RE:Sorting multiple document types in one repeater

Hi,

1) Where did you get the QueryName property? I am looking at the repeater web part and control and these do not have this property by default. Anyway, in the repeater web part you can use the "OrderBy" property and specify a field you want to sort the items by. In your case e.g. DocumentCreatedWhen

2) Query repeater does not have the Path property because you can pulling the data using custom SQL query you want exactly (so you can specify e.g. a macro in the WEHRE condition to limit the documents by alias path), so there is no need for this property.

Best regards,
Juraj Ondrus

Member

Jason Simone - 4/25/2011 1:49:25 PM

RE:Sorting multiple document types in one repeater

1)

Note the non-existent property QueryName in the repeater.

Yes I know that the repeater's query is not customizable. I'm wishing it was and trying to relay conceptually what I am trying to do. DocumentCreatedWhen is a useful field, so thank you. I see now that the Repeater uses the [View_CONTENT_..._Joined] structures instead of the tables? While I still think I would have more control if I had the ability to substitute my own query, this may work for my purposes.

2)
I don't understand exactly what you mean about specifying a macro in the WHERE condition. What I have done is used:

WhereCondition="NodeAliasPath Like '/Path-to-target/%'" />

Is there an easier way?

Member

Jason Simone - 4/25/2011 6:03:23 PM

RE:Sorting multiple document types in one repeater

So I looked a little further into this, and DocumentCreatedWhen just won't work for my situation. I need to be able to pick the columns that are getting combined in the sorting criteria. For example, I created a test page and posted two press releases. One was posted Monday, and one on Wednesday. Then afterward, I created an Event which occurs on Wednesday. When I display these three items on my test page, I need the dates to appear in chronological order, but in the same list.

I came up with a trick to perform this effect in my query, by summing the two columns together. (ORDER BY EventDate + NewsReleaseDate DESC). However, This again won't work in the CMSRepeater. I receive the following error.

[DataConnection.ExecuteQuery]: Query: SELECT * FROM View_CMS_Tree_Joined_Versions INNER JOIN CONTENT_news ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = CONTENT_news.[NewsID] WHERE ((SiteName = N'county') AND (DocumentCulture = N'en-US')) AND (ClassName = 'CMS.News') ORDER BY (EventDate + NewsReleaseDate) DESC : caused exception: Invalid column name 'EventDate'.

Based on this, it looks to me like the repeater is constructing two separate queries (each with it's own join) and then it concatenates them together into one dataset manually. However, this seems unnecessary. The end result should be the same if it instead performed a single query with LEFT JOINS. In fact, because there is only one round-trip to disk, this should be quicker as well. For my purposes, I will use this query:

SELECT * FROM View_CMS_Tree_Joined
LEFT OUTER JOIN CONTENT_Event ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_Event.[eventID] AND ClassName = 'CMS.Event'
LEFT OUTER JOIN CONTENT_News ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_News.NewsID AND ClassName = 'CMS.News'
WHERE ClassName IN ('CMS.Event', 'CMS.News')

I will be using this query in an QueryRepeater to get past this limitation in the CMSRepeater. I would suggest that yout team considers modifying the CMSRepeater to work in this way as I believe it would be superior.


Member	Jason Simone - 4/25/2011 6:04:44 PM RE:Sorting multiple document types in one repeater Then afterward, I created an Event which occurs on Wednesday. In my example, I meant to say that the Event occurs on Tuesday.

Kentico Support

kentico_jurajo - 4/26/2011 9:06:12 AM

RE:Sorting multiple document types in one repeater

Hi,

You need to use one field for both document types - since the repeater is getting data from both document types tables (couples data table) and you will use a field from the other one, you will get the erro you are getting.

There is very simple solution - use field for both types with the same attribute name, e.g. justa "ReleaseDate" and then you can use in the order by expression just OrderBy: ReleaseDate.

The query macro - sorry for the confusion, I meant that you can use the SQL wild characters like '%%' so you won't need to specify exact, absolute path (e.g. NodeAliasPath LIKE '%/path/%').

Best regards,
Juraj Ondrus