private Sql GetBaseQuery(BaseQueryType queryType, bool includeFilePaths)
{
var sql = new Sql();
sql.Select(queryType == BaseQueryType.Count ? "COUNT(*)" : (queryType == BaseQueryType.Ids ? "cmsContentVersion.contentId" : "*"))
.From <ContentVersionDto>(SqlSyntax)
.InnerJoin <ContentDto>(SqlSyntax)
.On <ContentVersionDto, ContentDto>(SqlSyntax, left => left.NodeId, right => right.NodeId)
.InnerJoin <NodeDto>(SqlSyntax)
.On <ContentDto, NodeDto>(SqlSyntax, left => left.NodeId, right => right.NodeId)
.InnerJoin <ContentTypeDto>(SqlSyntax)
.On <ContentTypeDto, ContentDto>(SqlSyntax, left => left.NodeId, right => right.ContentTypeId);
if (includeFilePaths)
{
sql.InnerJoin <MediaDto>(SqlSyntax)
.On <MediaDto, ContentVersionDto>(SqlSyntax, left => left.VersionId, right => right.VersionId);
}
//TODO: IF we want to enable querying on content type information this will need to be joined
//.InnerJoin<ContentTypeDto>(SqlSyntax)
//.On<ContentDto, ContentTypeDto>(SqlSyntax, left => left.ContentTypeId, right => right.NodeId, SqlSyntax);
sql.Where <NodeDto>(x => x.NodeObjectType == NodeObjectTypeId, SqlSyntax);
return(sql);
}
protected override Sql GetBaseQuery(BaseQueryType queryType)
{
var sql = new Sql();
sql.Select(queryType == BaseQueryType.Count ? "COUNT(*)" : (queryType == BaseQueryType.Ids ? "cmsContentVersion.contentId" : "*"))
.From<ContentVersionDto>(SqlSyntax)
.InnerJoin<ContentDto>(SqlSyntax)
.On<ContentVersionDto, ContentDto>(SqlSyntax, left => left.NodeId, right => right.NodeId)
.InnerJoin<NodeDto>(SqlSyntax)
.On<ContentDto, NodeDto>(SqlSyntax, left => left.NodeId, right => right.NodeId, SqlSyntax)
//TODO: IF we want to enable querying on content type information this will need to be joined
//.InnerJoin<ContentTypeDto>(SqlSyntax)
//.On<ContentDto, ContentTypeDto>(SqlSyntax, left => left.ContentTypeId, right => right.NodeId, SqlSyntax);
.Where<NodeDto>(x => x.NodeObjectType == NodeObjectTypeId, SqlSyntax);
return sql;
}
protected override Sql GetBaseQuery(BaseQueryType queryType)
{
var sql = new Sql();
sql.Select(queryType == BaseQueryType.Count ? "COUNT(*)" : (queryType == BaseQueryType.Ids ? "cmsMember.nodeId" : "*"))
.From <MemberDto>(SqlSyntax)
.InnerJoin <ContentVersionDto>(SqlSyntax)
.On <ContentVersionDto, MemberDto>(SqlSyntax, left => left.NodeId, right => right.NodeId)
.InnerJoin <ContentDto>(SqlSyntax)
.On <ContentVersionDto, ContentDto>(SqlSyntax, left => left.NodeId, right => right.NodeId)
//We're joining the type so we can do a query against the member type - not sure if this adds much overhead or not?
// the execution plan says it doesn't so we'll go with that and in that case, it might be worth joining the content
// types by default on the document and media repo's so we can query by content type there too.
.InnerJoin <ContentTypeDto>(SqlSyntax)
.On <ContentTypeDto, ContentDto>(SqlSyntax, left => left.NodeId, right => right.ContentTypeId)
.InnerJoin <NodeDto>(SqlSyntax)
.On <ContentDto, NodeDto>(SqlSyntax, left => left.NodeId, right => right.NodeId)
.Where <NodeDto>(x => x.NodeObjectType == NodeObjectTypeId, SqlSyntax);
return(sql);
}
/// <summary> /// For Paging, repositories must support returning different query for the query type specified /// </summary> /// <param name="queryType"></param> /// <returns></returns> protected abstract Sql GetBaseQuery(BaseQueryType queryType);
protected override Sql GetBaseQuery(BaseQueryType queryType)
{
return(GetBaseQuery(queryType, false));
}
