URGENT + Need Database query to pull some content

Dileep D

Vote:

There has been a one time need for pulling some content with the criteria so looking for database query:

I need to be able to query data based on a page type and with some other filters:

I have a page type as ContentArticle and need to query all Content Article pages, with data like, Name, id, expiration date, its parent link (type and Name)

#224021

Jun 09, 2020 14:24

Dileep D - Jun 09, 2020 18:27

I have this so far which gives me Id, name and link and parent id too, how do I add parent name and type as a column in this query. This is getting tricky as I need the parent hierarchy until Start page as part of this query.
Select
T.pkID 'Content Id',
TPL.Name 'Content Name',
T.fkParentID 'Parent Id',
TPL.LinkURL 'Link URL'
from tblPage T
inner join tblPageLanguage TPL ON T.pkID = TPL.fkPageID
inner join tblPageType TP
on T.fkPageTypeID = TP.pkID
where TP.Name = 'ContentArticlePage'

David Knipe

Vote:

Are these of any use?

http://kkhan-episerver.blogspot.com/2019/02/episerver-cms-11-useful-sql-queries-1.html

http://kkhan-episerver.blogspot.com/2019/02/episerver-cms-11-useful-sql-queries-2.html

#224023

Jun 09, 2020 19:12

Dileep D

Vote:

I went through the articles and got an idea of what data stays in which table and came up with above query. However from above query, I cannot find parent page names and types all the way till root. That is what am trying to find.

I tried putting the data in temp tables and creating joins but with that I can get only upto one level up.

#224045

Jun 10, 2020 0:15

Dileep D

Vote:

Creating some temp tables and eventually joining on data I need, was able to get this resolved. However big thanks to the articles mentioned above which served as reference (understanding) which table to look for what kind of data.

#224078

Jun 10, 2020 18:04

David Knipe

Vote:

Hi Dileep

Glad those links helped and hope Khurram sees this post one day - thank you Khurram :)

David

#224081

Jun 10, 2020 18:22