public override string BuildQuerySQL(SQLServerVisitor<Models.TextContent> visitor, out IEnumerable<Parameter> parameters) { var innerVisitor = new SQLServerVisitor<Models.TextContent>(visitor.Parameters); innerVisitor.Visite(parentQuery.ChildrenQuery.Expression); var innerExecutor = (new TextContentTranslator()).Translate(parentQuery.ChildrenQuery); var innerQuerySQL = innerExecutor.BuildQuerySQL(innerVisitor, out parameters); string selectClause = "*"; if (visitor.SelectFields != null && visitor.SelectFields.Length > 0) { selectClause = string.Join(",", visitor.SelectFields); } string whereClause = visitor.WhereClause; if (parentQuery.ParentFolder != null) { var paraName = visitor.AppendParameter(parentQuery.ParentFolder.FullName); whereClause = whereClause + " AND FolderName=" + paraName; } //var paraName = visitor.AppendParameter(parentQuery.ChildrenQuery.FullName); //whereClause = whereClause + "AND FolderName=" + paraName; string sql = string.Format(@" SELECT {0} FROM [{1}] parent WHERE EXISTS( SELECT UUID FROM ({2})children WHERE parent.UUID = children.ParentUUID ) AND {3}", selectClause , parentQuery.ParentSchema.GetTableName() , innerQuerySQL , whereClause); return sql; }
public override string BuildQuerySQL(SQLServerVisitor<TextContent> visitor, out IEnumerable<Parameter> parameters) { string selectClause = "*"; if (visitor.SelectFields != null && visitor.SelectFields.Length > 0) { selectClause = string.Join(",", visitor.SelectFields); } string whereClause = visitor.WhereClause; if (textContentQuery.Folder != null) { var paraName = visitor.AppendParameter(textContentQuery.Folder.FullName); whereClause = whereClause + " AND FolderName=" + paraName; } var categoryClauses = string.Join(" AND ", GetCategoryClause(textContentQuery.Repository, visitor.CategoryQueries, visitor.Parameters).ToArray()); if (!string.IsNullOrEmpty(categoryClauses)) { whereClause = whereClause + " AND " + categoryClauses; } parameters = visitor.Parameters; string sql = string.Format("SELECT {0} FROM [{1}] content WHERE {2} ", selectClause, textContentQuery.Schema.GetTableName(), whereClause); return sql; }
public override string BuildQuerySQL(SQLServerVisitor<Models.TextContent> visitor, out IEnumerable<Parameter> parameters) { var innerVisitor = new SQLServerVisitor<Models.TextContent>(visitor.Parameters); innerVisitor.Visite(categorizablesQuery.CategoryQuery.Expression); var innerExecutor = (new TextContentTranslator()).Translate(categorizablesQuery.CategoryQuery); var innerQuerySQL = innerExecutor.BuildQuerySQL(innerVisitor, out parameters); string selectClause = "*"; if (visitor.SelectFields != null && visitor.SelectFields.Length > 0) { selectClause = string.Join(",", visitor.SelectFields); } string whereClause = visitor.WhereClause; var paraName = visitor.AppendParameter(categorizablesQuery.CategorizableFolder.FullName); whereClause = whereClause + " AND FolderName=" + paraName; string sql = string.Format(@" SELECT {0} FROM [{1}] content WHERE EXISTS( SELECT ContentCategory.CategoryUUID FROM {2} ContentCategory, ({3})category WHERE content.UUID = ContentCategory.UUID AND ContentCategory.CategoryUUID = category.UUID ) AND {4}", selectClause , categorizablesQuery.CategorizableFolder.GetSchema().GetTableName() , categorizablesQuery.Repository.GetCategoryTableName() , innerQuerySQL , whereClause); return sql; }
private IEnumerable<string> GetCategoryClause(Repository repository, IEnumerable<IContentQuery<TextContent>> categoryQueries, List<Parameter> parameters) { TextContentTranslator translator = new TextContentTranslator(); List<string> categoryQueryList = new List<string>(); foreach (var categoryQuery in categoryQueries) { var executor = translator.Translate(categoryQuery); var categoryVisitor = new SQLServerVisitor<TextContent>(parameters); categoryVisitor.Visite(categoryQuery.Expression); IEnumerable<Parameter> outParameters; var categoryQuerySQL = executor.BuildQuerySQL(categoryVisitor, out outParameters); categoryQueryList.Add(string.Format(@"EXISTS( SELECT ContentCategory.CategoryUUID FROM [{0}] ContentCategory, ({1})category WHERE content.UUID = ContentCategory.UUID AND ContentCategory.CategoryUUID = category.UUID )", repository.GetCategoryTableName() , categoryQuerySQL)); } return categoryQueryList; }