Umbraco SQL query for AncestorContentOrSelf

gistfile1.sql

SELECT this_.Id                              as Id16_9_,
       this_.DateCreated                     as DateCrea2_16_9_,
       this_.DefaultName                     as DefaultN3_16_9_,
       this_.AttributeSchemaDefinition_id    as Attribut4_16_9_,
       this_.NodeId                          as NodeId16_9_,
       attribalia3_.NodeVersionId            as NodeVers3_11_,
       attribalia3_.Id                       as Id11_,
       attribalia3_.Id                       as Id5_0_,
       attribalia3_.AttributeDefinitionId    as Attribut2_5_0_,
       attribalia3_.NodeVersionId            as NodeVers3_5_0_,
       attributed7_.AttributeId              as Attribut4_12_,
       attributed7_.Id                       as Id12_,
       attributed7_.Id                       as Id1_1_,
       attributed7_.Value                    as Value1_1_,
       attributed7_.ValueKey                 as ValueKey1_1_,
       attributed7_.AttributeId              as Attribut4_1_1_,
       attributed7_.LocaleId                 as LocaleId1_1_,
       attributed8_.AttributeId              as Attribut4_13_,
       attributed8_.Id                       as Id13_,
       attributed8_.Id                       as Id0_2_,
       attributed8_.Value                    as Value0_2_,
       attributed8_.ValueKey                 as ValueKey0_2_,
       attributed8_.AttributeId              as Attribut4_0_2_,
       attributed8_.LocaleId                 as LocaleId0_2_,
       attributei6_.AttributeId              as Attribut4_14_,
       attributei6_.Id                       as Id14_,
       attributei6_.Id                       as Id3_3_,
       attributei6_.Value                    as Value3_3_,
       attributei6_.ValueKey                 as ValueKey3_3_,
       attributei6_.AttributeId              as Attribut4_3_3_,
       attributei6_.LocaleId                 as LocaleId3_3_,
       attributel5_.AttributeId              as Attribut4_15_,
       attributel5_.Id                       as Id15_,
       attributel5_.Id                       as Id4_4_,
       attributel5_.Value                    as Value4_4_,
       attributel5_.ValueKey                 as ValueKey4_4_,
       attributel5_.AttributeId              as Attribut4_4_4_,
       attributel5_.LocaleId                 as LocaleId4_4_,
       attributes4_.AttributeId              as Attribut4_16_,
       attributes4_.Id                       as Id16_,
       attributes4_.Id                       as Id6_5_,
       attributes4_.Value                    as Value6_5_,
       attributes4_.ValueKey                 as ValueKey6_5_,
       attributes4_.AttributeId              as Attribut4_6_5_,
       attributes4_.LocaleId                 as LocaleId6_5_,
       outerhisto2_.Id                       as Id18_6_,
       outerhisto2_.Date                     as Date18_6_,
       outerhisto2_.NodeVersionId            as NodeVers3_18_6_,
       outerhisto2_.NodeVersionStatusTypeId  as NodeVers4_18_6_,
       subselects1_.Id                       as Id19_7_,
       subselects1_.IsSystem                 as IsSystem19_7_,
       subselects1_.Name                     as Name19_7_,
       subselects1_.Alias                    as Alias19_7_,
       node18_.Id                            as Id9_8_,
       node18_.DateCreated                   as DateCrea2_9_8_,
       node18_.IsDisabled                    as IsDisabled9_8_,
       node18_1_.Alias                       as Alias10_8_,
       node18_1_.Description                 as Descript3_10_8_,
       node18_1_.Name                        as Name10_8_,
       node18_1_.Ordinal                     as Ordinal10_8_,
       node18_1_.AttributeSchemaDefinitionId as Attribut6_10_8_,
       node18_2_.Alias                       as Alias11_8_,
       node18_2_.Description                 as Descript3_11_8_,
       node18_2_.Name                        as Name11_8_,
       node18_2_.SchemaType                  as SchemaType11_8_,
       node18_2_.XmlConfiguration            as XmlConfi6_11_8_,
       case
         when node18_1_.NodeId is not null then 1
         when node18_2_.NodeId is not null then 2
         when node18_.Id is not null then 0
       end                                   as clazz_8_
FROM   dbo.NodeVersion this_
       left outer join dbo.Attribute attribalia3_
         on this_.Id = attribalia3_.NodeVersionId
       left outer join dbo.AttributeDecimalValue attributed7_
         on attribalia3_.Id = attributed7_.AttributeId
       left outer join dbo.AttributeDateValue attributed8_
         on attribalia3_.Id = attributed8_.AttributeId
       left outer join dbo.AttributeIntegerValue attributei6_
         on attribalia3_.Id = attributei6_.AttributeId
       left outer join dbo.AttributeLongStringValue attributel5_
         on attribalia3_.Id = attributel5_.AttributeId
       left outer join dbo.AttributeStringValue attributes4_
         on attribalia3_.Id = attributes4_.AttributeId
       inner join dbo.NodeVersionStatusHistory outerhisto2_
         on this_.Id = outerhisto2_.NodeVersionId
       inner join dbo.NodeVersionStatusType subselects1_
         on outerhisto2_.NodeVersionStatusTypeId = subselects1_.Id
       inner join dbo.Node node18_
         on this_.NodeId = node18_.Id
       left outer join dbo.[AttributeDefinitionGroup] node18_1_
         on node18_.Id = node18_1_.NodeId
       left outer join dbo.[AttributeSchemaDefinition] node18_2_
         on node18_.Id = node18_2_.NodeId
WHERE  subselects1_.Alias = 'published' /* @p0 */
       and outerhisto2_.Date > (SELECT coalesce(max(this_0_.Date), '1981-08-01T00:00:00.00' /* @p1 */) as y0_
                                FROM   dbo.NodeVersionStatusHistory this_0_
                                       inner join dbo.NodeVersion negatevers2_
                                         on this_0_.NodeVersionId = negatevers2_.Id
                                       inner join dbo.NodeVersionStatusType negatetype1_
                                         on this_0_.NodeVersionStatusTypeId = negatetype1_.Id
                                WHERE  negatetype1_.Alias in ('unpublished' /* @p2 */)
                                       and this_.NodeId = negatevers2_.NodeId)
       and outerhisto2_.Id in (SELECT TOP (1 /* @p7 */) this_0_.Id as y0_
                               FROM   dbo.NodeVersionStatusHistory this_0_
                                      inner join dbo.NodeVersion innerversi1_
                                        on this_0_.NodeVersionId = innerversi1_.Id
                                      inner join dbo.NodeVersionStatusType innertype2_
                                        on this_0_.NodeVersionStatusTypeId = innertype2_.Id
                               WHERE  innerversi1_.NodeId = this_.NodeId
                                      and innertype2_.Alias = 'published' /* @p3 */
                               ORDER  BY this_0_.Date desc)
       and this_.NodeId in ('4fa7ef7c-7ac9-407a-b5c3-9ffa00a29e70' /* @p4 */, '10000000-0000-0000-0000-000000000002' /* @p5 */, '10000000-0000-0000-0000-000000000001' /* @p6 */)
ORDER  BY outerhisto2_.Date asc