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); }