private Sql <ISqlContext> GetTaggedEntitiesSql(TaggableObjectTypes objectType, string culture)
{
var sql = Sql()
.Select <TagRelationshipDto>(x => Alias(x.NodeId, "NodeId"))
.AndSelect <PropertyTypeDto>(x => Alias(x.Alias, "PropertyTypeAlias"), x => Alias(x.Id, "PropertyTypeId"))
.AndSelect <TagDto>(x => Alias(x.Id, "TagId"), x => Alias(x.Text, "TagText"), x => Alias(x.Group, "TagGroup"), x => Alias(x.LanguageId, "TagLanguage"))
.From <TagDto>()
.InnerJoin <TagRelationshipDto>().On <TagDto, TagRelationshipDto>((tag, rel) => tag.Id == rel.TagId)
.InnerJoin <ContentDto>().On <TagRelationshipDto, ContentDto>((rel, content) => rel.NodeId == content.NodeId)
.InnerJoin <PropertyTypeDto>().On <TagRelationshipDto, PropertyTypeDto>((rel, prop) => rel.PropertyTypeId == prop.Id)
.InnerJoin <NodeDto>().On <ContentDto, NodeDto>((content, node) => content.NodeId == node.NodeId);
if (culture == null)
{
sql = sql
.Where <TagDto>(dto => dto.LanguageId == null);
}
else if (culture != "*")
{
sql = sql
.InnerJoin <LanguageDto>().On <TagDto, LanguageDto>((tag, lang) => tag.LanguageId == lang.Id)
.Where <LanguageDto>(x => x.IsoCode == culture);
}
if (objectType != TaggableObjectTypes.All)
{
var nodeObjectType = GetNodeObjectType(objectType);
sql = sql.Where <NodeDto>(dto => dto.NodeObjectType == nodeObjectType);
}
return(sql);
}
/// <inheritdoc />
public IEnumerable <ITag> GetTagsForEntityType(TaggableObjectTypes objectType, string?group = null,
string?culture = null)
{
Sql <ISqlContext> sql = GetTagsSql(culture, true);
AddTagsSqlWhere(sql, culture);
if (objectType != TaggableObjectTypes.All)
{
Guid nodeObjectType = GetNodeObjectType(objectType);
sql = sql
.Where <NodeDto>(dto => dto.NodeObjectType == nodeObjectType);
}
if (group.IsNullOrWhiteSpace() == false)
{
sql = sql
.Where <TagDto>(dto => dto.Group == group);
}
sql = sql
.GroupBy <TagDto>(x => x.Id, x => x.Text, x => x.Group, x => x.LanguageId);
return(ExecuteTagsQuery(sql));
}
public IEnumerable <TaggedEntity> GetTaggedEntitiesByTag(TaggableObjectTypes objectType, string tag, string tagGroup = null)
{
var sql = new Sql()
.Select("cmsTagRelationship.nodeId, cmsPropertyType.Alias, cmsPropertyType.id as propertyTypeId, cmsTags.tag, cmsTags.id as tagId, cmsTags." + SqlSyntax.GetQuotedColumnName("group"))
.From <TagDto>(SqlSyntax)
.InnerJoin <TagRelationshipDto>(SqlSyntax)
.On <TagRelationshipDto, TagDto>(SqlSyntax, left => left.TagId, right => right.Id)
.InnerJoin <ContentDto>(SqlSyntax)
.On <ContentDto, TagRelationshipDto>(SqlSyntax, left => left.NodeId, right => right.NodeId)
.InnerJoin <PropertyTypeDto>(SqlSyntax)
.On <PropertyTypeDto, TagRelationshipDto>(SqlSyntax, left => left.Id, right => right.PropertyTypeId)
.InnerJoin <NodeDto>(SqlSyntax)
.On <NodeDto, ContentDto>(SqlSyntax, left => left.NodeId, right => right.NodeId)
.Where <TagDto>(dto => dto.Tag == tag);
if (objectType != TaggableObjectTypes.All)
{
var nodeObjectType = GetNodeObjectType(objectType);
sql = sql
.Where <NodeDto>(dto => dto.NodeObjectType == nodeObjectType);
}
if (tagGroup.IsNullOrWhiteSpace() == false)
{
sql = sql.Where <TagDto>(dto => dto.Group == tagGroup);
}
return(CreateTaggedEntityCollection(
Database.Fetch <dynamic>(sql)));
}
/// <inheritdoc />
public IEnumerable <TaggedEntity> GetTaggedEntitiesByTagGroup(TaggableObjectTypes objectType, string group, string culture = null)
{
var sql = GetTaggedEntitiesSql(objectType, culture);
sql = sql
.Where <TagDto>(x => x.Group == group);
return(Map(Database.Fetch <TaggedEntityDto>(sql)));
}
/// <inheritdoc />
public IEnumerable <TaggedEntity> GetTaggedEntitiesByTag(TaggableObjectTypes objectType, string tag, string group = null, string culture = null)
{
var sql = GetTaggedEntitiesSql(objectType, culture);
sql = sql
.Where <TagDto>(dto => dto.Text == tag);
if (group.IsNullOrWhiteSpace() == false)
{
sql = sql
.Where <TagDto>(dto => dto.Group == group);
}
return(Map(Database.Fetch <TaggedEntityDto>(sql)));
}
private Guid GetNodeObjectType(TaggableObjectTypes type)
{
switch (type)
{
case TaggableObjectTypes.Content:
return(Constants.ObjectTypes.Document);
case TaggableObjectTypes.Media:
return(Constants.ObjectTypes.Media);
case TaggableObjectTypes.Member:
return(Constants.ObjectTypes.Member);
default:
throw new ArgumentOutOfRangeException(nameof(type));
}
}
public IEnumerable <ITag> GetTagsForEntityType(TaggableObjectTypes objectType, string group = null)
{
var sql = GetTagsQuerySelect(true);
sql = ApplyRelationshipJoinToTagsQuery(sql);
if (objectType != TaggableObjectTypes.All)
{
var nodeObjectType = GetNodeObjectType(objectType);
sql = sql
.Where <NodeDto>(dto => dto.NodeObjectType == nodeObjectType);
}
sql = ApplyGroupFilterToTagsQuery(sql, group);
sql = ApplyGroupByToTagsQuery(sql);
return(ExecuteTagsQuery(sql));
}
