Get all items where a certain template appears in its template inheritance

Question

Is there a way to get all items where a certain template appears in its template inheritance?

Say I want to get all items where /sitecore/Templates/Common/Sections/Search appears in that inheritance tree.

Ultimately, what I want to do is get all items in Sitecore that contain a certain field. I tried achieving this with a Sitecore query (/sitecore/templates//*[contains(@__Base template,'{C8615DF9-65D1-4529-859F-9ABB4DA977B8}')]), however, this only returns items that directly inherit from the specified template.

This doesn't give me complete coverage so it seems that I would need something that works recursively...

Answer 1

Sitecore PowerShell Extensions

At first your question made me think of this where the user wants to find media items of a certain length. I encourage you to check that out to see some different ways you can use SPE to query items.

Option 1

Your case is similar but may be a little expensive. In that case the Sitecore Content Search API may be ideal for you.

You'll need a computed field that stores all the base templates in the index. It's very likely the field is simply not being indexed by default and you need to uncomment it as described here. Alternatively, you can add a computed index as seen here.

# Taking a guess at the field name in the search index.
$criteria = @(
    @{Filter="Contains";Field="_templates";Value="c8615df965d14529859f9abb4da977b8"}
)
Find-Item -Index sitecore_master_index -Criteria $criteria

Option 2

Matthew makes a good point in the comment below. Another option would be to query all of the items under the specified root and test for the inheritance.

$templateId = [Sitecore.Data.ID]::Parse("{76036F5E-CBCE-46D1-AF0A-4143F9B557AA}")
$searchPath = "master:/sitecore/content/home"
$allthethings = @(Get-Item -Path $searchPath) + @(Get-ChildItem -Path $searchPath -Recurse)
foreach($onething in $allthethings) {
    $doesItInherit = [Sitecore.Data.Managers.TemplateManager]::GetTemplate($onething.TemplateID, $onething.Database).InheritsFrom($templateId)
    if($doesItInherit) {
        $onething
    }
}

Option 3 :thumbs-up:

Likely the best option is to simply use the Link Database to find all items referenced.

# Sample Item
$template = Get-Item -Path "master:" -ID "{76036F5E-CBCE-46D1-AF0A-4143F9B557AA}"
Get-ItemReferrer -Item $template | Where-Object { $_.Paths.IsContentItem }

A more complete example for looking at all levels of inheritance can be found here.

Answer 2

Not using Sitecore Query

Not using Sitecore Query. The reason is - as you also point out yourself - Sitecore simply does not have a by-query way of recursively deducing the complete template inheritance chain.

The SQL route

But you can rather easily get the Item IDs you want. Your question doesn't mention what the actual purpose is of what you're trying to do here, so I can only point in a direction, hoping you can take it from there.

Sitecore stores all field values in one of three tables in SQL. [SharedFields], [UnversionedFields] and [VersionedFields]. Which one your field belongs to, depends on the settings defined for the field. Like if it is marked Shared, it will be in [SharedFields] and so on.

Knowing this, it becomes rather simple. The [UnversionedFields] table looks like this:

And so querying it for what you want, would be as simple as:

SELECT [ItemId]
  FROM [sc80rev140922_master].[dbo].[UnversionedFields]
  WHERE FieldId='B5E02AD9-D56F-4C41-A065-A133DB87BDEB'

(replace database name and FieldId value to fit your own, obviously)

The caveat

The above will not find any Items, where the field in question has never had a value (is NULL). This may invalidate this solution for your use case, your question isn't 100% clear on that.