Portal Engine Questions on portal engine and web parts.
Version 7.x > Portal Engine > Order By Multiple Document Types View modes: 
User avatar
Member
Member
jwynveen - 11/19/2013 3:57:46 PM
   
Order By Multiple Document Types
I have a repeater pulling in both CMS.News and CMS.BlogPost documents from my site. I am trying to sort them by their respective date fields, but it appears that the SQL generated only joins the blog table, not the news table. I would like an Order By expression of "coalesce(BlogPostDate,NewsReleaseDate) desc", but the way the query is generated, this doesn't work.

How can I sort them by their date fields in a single list?

User avatar
Member
Member
jwynveen - 11/19/2013 4:42:44 PM
   
RE:Order By Multiple Document Types
I just found a workaround using UNION to merge those two tables together. I put this in a custom query in a document type. This way it merges the column names so I can query against both tables.

For anyone else's reference, here is the query I created:

select ##TOPN## ##COLUMNS##
from (
select
ClassName, NodeID, NodeAliasPath, NodeAlias, NodeOrder, DocumentID, DocumentName, DocumentNamePath,
BlogPostID as ItemID, BlogPostTitle as Title, BlogPostDate as Date, BlogPostSummary as Summary, BlogPostBody as Body, BlogPostTeaser as Teaser, Featured
from View_CONTENT_BlogPost_Joined

union all

select
ClassName, NodeID, NodeAliasPath, NodeAlias, NodeOrder, DocumentID, DocumentName, DocumentNamePath,
NewsID, NewsTitle, NewsReleaseDate, NewsSummary, NewsText, NewsTeaser, Featured
from View_CONTENT_News_Joined
) t
WHERE ##WHERE##
ORDER BY ##ORDERBY##