in src/Microsoft.Health.Fhir.SqlServer/Features/Search/Expressions/Visitors/QueryGenerators/SqlQueryGenerator.cs [601:796]
private void HandleTableKindInclude(
SearchParamTableExpression searchParamTableExpression,
SearchOptions context,
string referenceSourceTableAlias,
string referenceTargetResourceTableAlias)
{
var includeExpression = (IncludeExpression)searchParamTableExpression.Predicate;
_includeCteIds = _includeCteIds ?? new List<string>();
_includeLimitCtesByResourceType = _includeLimitCtesByResourceType ?? new Dictionary<string, List<string>>();
_includeFromCteIds = _includeFromCteIds ?? new List<string>();
StringBuilder.Append("SELECT DISTINCT ");
if (includeExpression.Reversed)
{
// In case its revinclude, we limit the number of returned items as the resultset size is potentially
// unbounded. we ask for +1 so in the limit expression we know if to mark at truncated...
StringBuilder.Append("TOP (").Append(Parameters.AddParameter(context.IncludeCount + 1, includeInHash: false)).Append(") ");
}
var table = !includeExpression.Reversed ? referenceTargetResourceTableAlias : referenceSourceTableAlias;
StringBuilder.Append(VLatest.Resource.ResourceTypeId, table).Append(" AS T1, ")
.Append(VLatest.Resource.ResourceSurrogateId, table)
.AppendLine(" AS Sid1, 0 AS IsMatch ");
StringBuilder.Append("FROM ").Append(VLatest.ReferenceSearchParam).Append(' ').AppendLine(referenceSourceTableAlias)
.Append("INNER JOIN ").Append(VLatest.Resource).Append(' ').AppendLine(referenceTargetResourceTableAlias);
using (var delimited = StringBuilder.BeginDelimitedOnClause())
{
delimited.BeginDelimitedElement().Append(VLatest.ReferenceSearchParam.ReferenceResourceTypeId, referenceSourceTableAlias)
.Append(" = ").Append(VLatest.Resource.ResourceTypeId, referenceTargetResourceTableAlias);
delimited.BeginDelimitedElement().Append(VLatest.ReferenceSearchParam.ReferenceResourceId, referenceSourceTableAlias)
.Append(" = ").Append(VLatest.Resource.ResourceId, referenceTargetResourceTableAlias);
}
using (var delimited = StringBuilder.BeginDelimitedWhereClause())
{
if (!includeExpression.WildCard)
{
delimited.BeginDelimitedElement().Append(VLatest.ReferenceSearchParam.SearchParamId, referenceSourceTableAlias)
.Append(" = ").Append(Parameters.AddParameter(VLatest.ReferenceSearchParam.SearchParamId, Model.GetSearchParamId(includeExpression.ReferenceSearchParameter.Url), true));
if (includeExpression.TargetResourceType != null)
{
delimited.BeginDelimitedElement().Append(VLatest.ReferenceSearchParam.ReferenceResourceTypeId, referenceSourceTableAlias)
.Append(" = ").Append(Parameters.AddParameter(VLatest.ReferenceSearchParam.ReferenceResourceTypeId, Model.GetResourceTypeId(includeExpression.TargetResourceType), true));
}
}
AppendHistoryClause(delimited, referenceTargetResourceTableAlias);
AppendHistoryClause(delimited, referenceSourceTableAlias);
AppendDeletedClause(delimited, referenceTargetResourceTableAlias);
table = !includeExpression.Reversed ? referenceSourceTableAlias : referenceTargetResourceTableAlias;
// For RevIncludeIterate we expect to have a TargetType specified if the target reference can be of multiple types
var resourceTypeIds = includeExpression.ResourceTypes.Select(x => Model.GetResourceTypeId(x)).ToArray();
if (includeExpression.Reversed && includeExpression.Iterate)
{
if (includeExpression.TargetResourceType != null)
{
resourceTypeIds = new[] { Model.GetResourceTypeId(includeExpression.TargetResourceType) };
}
else if (includeExpression.ReferenceSearchParameter?.TargetResourceTypes?.Count > 0)
{
resourceTypeIds = new[] { Model.GetResourceTypeId(includeExpression.ReferenceSearchParameter.TargetResourceTypes.ToList().First()) };
}
}
delimited.BeginDelimitedElement().Append(VLatest.ReferenceSearchParam.ResourceTypeId, table)
.Append(" IN (")
.Append(string.Join(", ", resourceTypeIds))
.Append(")");
// Get FROM ctes
string fromCte = _cteMainSelect;
if (includeExpression.Iterate)
{
// Include Iterate
if (!includeExpression.Reversed)
{
// _include:iterate may appear without a preceding _include, in case of circular reference
// On that case, the fromCte is _cteMainSelect
if (TryGetIncludeCtes(includeExpression.SourceResourceType, out _includeFromCteIds))
{
fromCte = _includeFromCteIds[++_curFromCteIndex];
}
}
// RevInclude Iterate
else
{
if (includeExpression.TargetResourceType != null)
{
if (TryGetIncludeCtes(includeExpression.TargetResourceType, out _includeFromCteIds))
{
fromCte = _includeFromCteIds[++_curFromCteIndex];
}
}
else if (includeExpression.ReferenceSearchParameter?.TargetResourceTypes != null)
{
// Assumes TargetResourceTypes is of length 1. Otherwise, a BadRequest would have been thrown earlier for _revinclude:iterate
List<string> fromCtes;
var targetType = includeExpression.ReferenceSearchParameter.TargetResourceTypes[0];
if (TryGetIncludeCtes(targetType, out fromCtes))
{
_includeFromCteIds.AddRange(fromCtes);
}
_includeFromCteIds = _includeFromCteIds.Distinct().ToList();
fromCte = _includeFromCteIds.Count > 0 ? _includeFromCteIds[++_curFromCteIndex] : fromCte;
}
}
}
if (includeExpression.Reversed)
{
delimited.BeginDelimitedElement().Append(VLatest.ReferenceSearchParam.ResourceTypeId, referenceSourceTableAlias)
.Append(" = ").Append(Parameters.AddParameter(VLatest.ReferenceSearchParam.ResourceTypeId, Model.GetResourceTypeId(includeExpression.SourceResourceType), true));
}
delimited.BeginDelimitedElement().Append("EXISTS( SELECT * FROM ").Append(fromCte)
.Append(" WHERE ").Append(VLatest.Resource.ResourceTypeId, table).Append(" = T1 AND ")
.Append(VLatest.Resource.ResourceSurrogateId, table).Append(" = Sid1");
if (!includeExpression.Iterate)
{
// Limit the join to the main select CTE.
// The main select will have max+1 items in the result set to account for paging, so we only want to join using the max amount.
StringBuilder.Append(" AND Row < ").Append(Parameters.AddParameter(context.MaxItemCount + 1, true));
}
StringBuilder.Append(")");
}
if (includeExpression.Reversed)
{
// mark that this cte is a reverse one, meaning we need to add another items limitation
// cte on top of it
_cteToLimit.Add(_tableExpressionCounter);
}
// Update target reference cte dictionary
var curLimitCte = TableExpressionName(_tableExpressionCounter + 1);
// Add current cte limit to the dictionary
if (includeExpression.Reversed)
{
AddIncludeLimitCte(includeExpression.SourceResourceType, curLimitCte);
}
else
{
// Not reversed and a specific target type is provided as the 3rd part of include value
if (includeExpression.TargetResourceType != null)
{
AddIncludeLimitCte(includeExpression.TargetResourceType, curLimitCte);
}
else if (includeExpression.ReferenceSearchParameter != null)
{
includeExpression.ReferenceSearchParameter.TargetResourceTypes?.ToList().ForEach(t => AddIncludeLimitCte(t, curLimitCte));
}
}
// Handle Multiple Results sets to include from
if (_includeFromCteIds?.Count > 1 && _curFromCteIndex >= 0 && _curFromCteIndex < _includeFromCteIds.Count - 1)
{
StringBuilder.Append($"),{Environment.NewLine}");
// If it's not the last result set, append a new IncludeLimit cte, since IncludeLimitCte was not created for the current cte
if (_curFromCteIndex < _includeFromCteIds?.Count - 1)
{
var cteToLimit = TableExpressionName(_tableExpressionCounter);
WriteIncludeLimitCte(cteToLimit, context);
}
// Generate CTE to include from the additional result sets
StringBuilder.Append(TableExpressionName(++_tableExpressionCounter)).AppendLine(" AS").AppendLine("(");
searchParamTableExpression.AcceptVisitor(this, context);
}
else
{
_curFromCteIndex = -1;
if (includeExpression.WildCard)
{
includeExpression.ReferencedTypes?.ToList().ForEach(t => AddIncludeLimitCte(t, curLimitCte));
}
}
}