Repeater custom query

Pantelhs Saoylos asked on November 1, 2019 13:09

Hello , I am having some trouble finding out how custom queries work when using a CMSRepeater. The problem i am facing is i have a CMSRepeater which pulls data from 3 different page type classes which have different column names, what's the correct syntax to specify only the needed columns in order not to pull everything?

Correct Answer

Peter Mogilnitski answered on November 1, 2019 18:17

Documentation on columns says:

If specified, only selected database columns are loaded by the web part, which improves the performance of the system. Specified columns need to be common to all selected page types and entered as a list separated by commas (,). Any user-defined columns must have an alias - anonymous columns are not supported.

There is a topic on that as well. I think it just takes common columns from view_cms_tree_joined, so perhaps you need to use repeater with custom query. For example

SELECT [DocumentName], [NodeAliasPath], MenuItemTeaserImage colmun1, CAST(null as INT) column2
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) 
INNER JOIN CONTENT_MenuItem AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[MenuItemID] AND V.ClassName = N'CMS.MenuItem' 
##WHERE##

UNION ALL

SELECT [DocumentName], [NodeAliasPath],  null, EventCapacity
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) 
INNER JOIN CONTENT_BookingEvent AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[BookingEventID] AND V.ClassName = N'CMS.BookingEvent'
##WHERE##
1 votesVote for this answer Unmark Correct answer

Recent Answers


Pantelhs Saoylos answered on November 4, 2019 14:10

From the topic you linked i see "In the event the Page Types have a common field name (like DocumentName, etc.), the field for each record will be accessible. If one of the PageTypes does not have the filed (Example: Phone does not have the BlogName field), then nothing will be returned for that row." . When i try to add the column found on only one of the tables i get the follwing error: `Message: [DataConnection.HandleError]: --the query-- Caused exception: Invalid column name 'Title'. Invalid column name 'Title'.

Exception type: System.Exception Stack trace:`

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.