private Sql <ISqlContext> GetBaseQuery(Sql <ISqlContext> sql)
{
sql.Select <RelationDto>(x => x.ParentId, x => x.ChildId);
sql.AndSelect <RelationTypeDto>(x => x.Id);
sql.From <RelationDto>();
sql.LeftJoin <NodeDto>().On <RelationDto, NodeDto>((relation, node) => relation.ChildId == node.NodeId || relation.ParentId == node.NodeId);
sql.LeftJoin <RelationTypeDto>().On <RelationDto, RelationTypeDto>((relation, relationType) => relation.RelationType == relationType.Id);
sql.Where <RelationTypeDto>(relationType => relationType.Alias == "umbMedia");
sql.Where <NodeDto>(x => x.Trashed == false);
return(sql);
}
/// <summary>
/// Adds another set of field to select. This method must be used with "Select" when fecthing fields from different tables.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">Sql object</param>
/// <param name="sqlSyntax">Sql syntax</param>
/// <param name="fields">Additional columns to select</param>
/// <returns></returns>
public static Sql AndSelect <T>(this Sql sql, ISqlSyntaxProvider sqlSyntax, params Expression <Func <T, object> >[] fields)
{
return(sql.AndSelect(GetFieldNames(sqlSyntax, fields)));
}
// gets the base SELECT + FROM [+ filter] sql
// always from the 'current' content version
protected Sql <ISqlContext> GetBase(bool isContent, bool isMedia, bool isMember, Action <Sql <ISqlContext> >?filter,
bool isCount = false)
{
Sql <ISqlContext> sql = Sql();
if (isCount)
{
sql.SelectCount();
}
else
{
sql
.Select <NodeDto>(x => x.NodeId, x => x.Trashed, x => x.ParentId, x => x.UserId, x => x.Level,
x => x.Path)
.AndSelect <NodeDto>(x => x.SortOrder, x => x.UniqueId, x => x.Text, x => x.NodeObjectType,
x => x.CreateDate)
.Append(", COUNT(child.id) AS children");
if (isContent || isMedia || isMember)
{
sql
.AndSelect <ContentVersionDto>(x => Alias(x.Id, "versionId"), x => x.VersionDate)
.AndSelect <ContentTypeDto>(x => x.Alias, x => x.Icon, x => x.Thumbnail, x => x.IsContainer,
x => x.Variations);
}
if (isContent)
{
sql
.AndSelect <DocumentDto>(x => x.Published, x => x.Edited);
}
if (isMedia)
{
sql
.AndSelect <MediaVersionDto>(x => Alias(x.Path, "MediaPath"));
}
}
sql
.From <NodeDto>();
if (isContent || isMedia || isMember)
{
sql
.LeftJoin <ContentVersionDto>()
.On <NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current)
.LeftJoin <ContentDto>().On <NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
.LeftJoin <ContentTypeDto>()
.On <ContentDto, ContentTypeDto>((left, right) => left.ContentTypeId == right.NodeId);
}
if (isContent)
{
sql
.LeftJoin <DocumentDto>().On <NodeDto, DocumentDto>((left, right) => left.NodeId == right.NodeId);
}
if (isMedia)
{
sql
.LeftJoin <MediaVersionDto>()
.On <ContentVersionDto, MediaVersionDto>((left, right) => left.Id == right.Id);
}
//Any LeftJoin statements need to come last
if (isCount == false)
{
sql
.LeftJoin <NodeDto>("child")
.On <NodeDto, NodeDto>((left, right) => left.NodeId == right.ParentId, aliasRight: "child");
}
filter?.Invoke(sql);
return(sql);
}
