public virtual IQueryable <Article> PrepareArticleSearchQuery( ArticleSearchContext ctx, IEnumerable <int> allowedUserRolesIds = null, bool searchLocalizedValue = false) { return(PrepareArticleSearchQuery <Article>(ctx, x => x, allowedUserRolesIds, searchLocalizedValue)); }
public virtual int CountArticles(ArticleSearchContext ctx) { Guard.ArgumentNotNull(() => ctx); var query = this.PrepareArticleSearchQuery(ctx); return(query.Distinct().Count()); }
public virtual IQueryable <TResult> PrepareArticleSearchQuery <TResult>( ArticleSearchContext ctx, Expression <Func <Article, TResult> > selector, IEnumerable <int> allowedUserRolesIds = null, bool searchLocalizedValue = false) { Guard.ArgumentNotNull(() => ctx); Guard.ArgumentNotNull(() => selector); if (allowedUserRolesIds == null) { allowedUserRolesIds = _workContext.CurrentUser.UserRoles.Where(cr => cr.Active).Select(cr => cr.Id).ToList(); } // articles var query = ctx.Query ?? _articleRepository.Table; query = query.Where(p => !p.Deleted); if (!ctx.ShowHidden) { query = query.Where(p => p.StatusFormat == StatusFormat.Norma); } if (ctx.IsHot.HasValue) { query = query.Where(p => p.IsHot == ctx.IsHot.Value); } if (ctx.IsRed.HasValue) { query = query.Where(p => p.IsRed == ctx.IsRed.Value); } if (ctx.IsSlide.HasValue) { query = query.Where(p => p.IsSlide == ctx.IsSlide.Value); } if (ctx.IsTop.HasValue) { query = query.Where(p => p.IsTop == ctx.IsTop.Value); } if (ctx.ArticleIds != null && ctx.ArticleIds.Count > 0) { query = query.Where(x => ctx.ArticleIds.Contains(x.Id)); } //The function 'CurrentUtcDateTime' is not supported by SQL Server Compact. //That's why we pass the date value var nowUtc = DateTime.UtcNow; if (!ctx.ShowHidden) { //available dates query = query.Where(p => (!p.StartDateUtc.HasValue || p.StartDateUtc.Value < nowUtc) && (!p.EndDateUtc.HasValue || p.EndDateUtc.Value > nowUtc)); } // searching by keyword if (!String.IsNullOrWhiteSpace(ctx.Keywords)) { query = from p in query join lp in _localizedPropertyRepository.Table on p.Id equals lp.EntityId into p_lp from lp in p_lp.DefaultIfEmpty() // from pt in p.ArticleTags.DefaultIfEmpty() where (p.Title.Contains(ctx.Keywords)) || (ctx.SearchDescriptions && p.ShortContent.Contains(ctx.Keywords)) || (ctx.SearchDescriptions && p.FullContent.Contains(ctx.Keywords)) || // (ctx.SearchArticleTags && pt.Name.Contains(ctx.Keywords)) || //localized values (searchLocalizedValue && lp.LanguageId == ctx.LanguageId && lp.LocaleKeyGroup == "Article" && lp.LocaleKey == "Name" && lp.LocaleValue.Contains(ctx.Keywords)) || (ctx.SearchDescriptions && searchLocalizedValue && lp.LanguageId == ctx.LanguageId && lp.LocaleKeyGroup == "Article" && lp.LocaleKey == "ShortContent" && lp.LocaleValue.Contains(ctx.Keywords)) || (ctx.SearchDescriptions && searchLocalizedValue && lp.LanguageId == ctx.LanguageId && lp.LocaleKeyGroup == "Article" && lp.LocaleKey == "FullContent" && lp.LocaleValue.Contains(ctx.Keywords)) //UNDONE search localized values in associated article tags select p; } //if (!ctx.ShowHidden && !QuerySettings.IgnoreAcl) //{ // query = // from p in query // join acl in _aclRepository.Table on new { pid = p.Id, pname = "Article" } equals new { pid = acl.EntityId, pname = acl.EntityName } into pacl // from acl in pacl.DefaultIfEmpty() // where !p.SubjectToAcl || allowedUserRolesIds.Contains(acl.UserRoleId) // select p; //} if (ctx.SiteId > 0 && !QuerySettings.IgnoreMultiSite) { query = from p in query join sm in _siteMappingRepository.Table on new { pid = p.Id, pname = "Article" } equals new { pid = sm.EntityId, pname = sm.EntityName } into psm from sm in psm.DefaultIfEmpty() where !p.LimitedToSites || ctx.SiteId == sm.SiteId select p; } // category filtering //if (ctx.WithoutCategories) //{ // query = query.Where(x => x.ArticleCategories.Count == 0); //} //else if (ctx.CategoryIds != null && ctx.CategoryIds.Count > 0) //{ // //search in subcategories // if (ctx.MatchAllcategories) // { // query = from p in query // where ctx.CategoryIds.All(i => p.ArticleCategories.Any(p2 => p2.CategoryId == i)) // from pc in p.ArticleCategories // where (!ctx.FeaturedArticles.HasValue || ctx.FeaturedArticles.Value == pc.IsFeaturedArticle) // select p; // } // else // { // query = from p in query // from pc in p.ArticleCategories.Where(pc => ctx.CategoryIds.Contains(pc.CategoryId)) // where (!ctx.FeaturedArticles.HasValue || ctx.FeaturedArticles.Value == pc.IsFeaturedArticle) // select p; // } //} // related articles filtering //if (relatedToArticleId > 0) //{ // query = from p in query // join rp in _relatedArticleRepository.Table on p.Id equals rp.ArticleId2 // where (relatedToArticleId == rp.ArticleId1) // select p; //} // tag filtering //if (ctx.ArticleTagId > 0) //{ // query = from p in query // from pt in p.ArticleTags.Where(pt => pt.Id == ctx.ArticleTagId) // select p; //} return(query.Select(selector)); }
public virtual IPagedList <Article> SearchArticles(ArticleSearchContext ctx) { _eventPublisher.Publish(new ArticlesSearchingEvent(ctx)); //search by keyword bool searchLocalizedValue = false; if (ctx.LanguageId > 0) { if (ctx.ShowHidden) { searchLocalizedValue = true; } else { //ensure that we have at least two published languages var totalPublishedLanguages = _languageService.GetAllLanguages(siteId: ctx.SiteId).Count; searchLocalizedValue = totalPublishedLanguages >= 2; } } //validate "categoryIds" parameter if (ctx.CategoryIds != null && ctx.CategoryIds.Contains(0)) { ctx.CategoryIds.Remove(0); } //Access control list. Allowed customer roles var allowedUserRolesIds = _workContext.CurrentUser.UserRoles .Where(cr => cr.Active).Select(cr => cr.Id).ToList(); if (_commonSettings.UseStoredProceduresIfSupported && _dataProvider.StoredProceduresSupported) { //sited procedures are enabled and supported by the database. //It's much faster than the LINQ implementation below #region Use sited procedure //pass categry identifiers as comma-delimited string string commaSeparatedCategoryIds = ""; if (ctx.CategoryIds != null && !ctx.WithoutCategories) { for (int i = 0; i < ctx.CategoryIds.Count; i++) { commaSeparatedCategoryIds += ctx.CategoryIds[i].ToString(); if (i != ctx.CategoryIds.Count - 1) { commaSeparatedCategoryIds += ","; } } } //pass customer role identifiers as comma-delimited string string commaSeparatedAllowedUserRoleIds = ""; for (int i = 0; i < allowedUserRolesIds.Count; i++) { commaSeparatedAllowedUserRoleIds += allowedUserRolesIds[i].ToString(); if (i != allowedUserRolesIds.Count - 1) { commaSeparatedAllowedUserRoleIds += ","; } } //some databases don't support int.MaxValue if (ctx.PageSize == int.MaxValue) { ctx.PageSize = int.MaxValue - 1; } //prepare parameters var pCategoryIds = _dataProvider.GetParameter(); pCategoryIds.ParameterName = "CategoryIds"; pCategoryIds.Value = commaSeparatedCategoryIds != null ? (object)commaSeparatedCategoryIds : DBNull.Value; pCategoryIds.DbType = DbType.String; var pSiteId = _dataProvider.GetParameter(); pSiteId.ParameterName = "SiteId"; pSiteId.Value = QuerySettings.IgnoreMultiSite ? 0 : ctx.SiteId; pSiteId.DbType = DbType.Int32; var pArticleTagId = _dataProvider.GetParameter(); pArticleTagId.ParameterName = "ArticleTagId"; pArticleTagId.Value = ctx.ArticleTagId; pArticleTagId.DbType = DbType.Int32; var pFeaturedArticles = _dataProvider.GetParameter(); pFeaturedArticles.ParameterName = "FeaturedArticles"; pFeaturedArticles.Value = ctx.FeaturedArticles.HasValue ? (object)ctx.FeaturedArticles.Value : DBNull.Value; pFeaturedArticles.DbType = DbType.Boolean; var pKeywords = _dataProvider.GetParameter(); pKeywords.ParameterName = "Keywords"; pKeywords.Value = ctx.Keywords != null ? (object)ctx.Keywords : DBNull.Value; pKeywords.DbType = DbType.String; var pSearchDescriptions = _dataProvider.GetParameter(); pSearchDescriptions.ParameterName = "SearchDescriptions"; pSearchDescriptions.Value = ctx.SearchDescriptions; pSearchDescriptions.DbType = DbType.Boolean; var pSearchArticleTags = _dataProvider.GetParameter(); pSearchArticleTags.ParameterName = "SearchArticleTags"; pSearchArticleTags.Value = ctx.SearchDescriptions; pSearchArticleTags.DbType = DbType.Boolean; var pUseFullTextSearch = _dataProvider.GetParameter(); pUseFullTextSearch.ParameterName = "UseFullTextSearch"; pUseFullTextSearch.Value = _commonSettings.UseFullTextSearch; pUseFullTextSearch.DbType = DbType.Boolean; var pFullTextMode = _dataProvider.GetParameter(); pFullTextMode.ParameterName = "FullTextMode"; pFullTextMode.Value = (int)_commonSettings.FullTextMode; pFullTextMode.DbType = DbType.Int32; var pLanguageId = _dataProvider.GetParameter(); pLanguageId.ParameterName = "LanguageId"; pLanguageId.Value = searchLocalizedValue ? ctx.LanguageId : 0; pLanguageId.DbType = DbType.Int32; var pOrderBy = _dataProvider.GetParameter(); pOrderBy.ParameterName = "OrderBy"; pOrderBy.Value = (int)ctx.OrderBy; pOrderBy.DbType = DbType.Int32; var pAllowedUserRoleIds = _dataProvider.GetParameter(); pAllowedUserRoleIds.ParameterName = "AllowedUserRoleIds"; pAllowedUserRoleIds.Value = commaSeparatedAllowedUserRoleIds; pAllowedUserRoleIds.DbType = DbType.String; var pPageIndex = _dataProvider.GetParameter(); pPageIndex.ParameterName = "PageIndex"; pPageIndex.Value = ctx.PageIndex; pPageIndex.DbType = DbType.Int32; var pPageSize = _dataProvider.GetParameter(); pPageSize.ParameterName = "PageSize"; pPageSize.Value = ctx.PageSize; pPageSize.DbType = DbType.Int32; var pShowHidden = _dataProvider.GetParameter(); pShowHidden.ParameterName = "ShowHidden"; pShowHidden.Value = ctx.ShowHidden; pShowHidden.DbType = DbType.Boolean; var pWithoutCategories = _dataProvider.GetParameter(); pWithoutCategories.ParameterName = "WithoutCategories"; pWithoutCategories.Value = ctx.WithoutCategories; pWithoutCategories.DbType = DbType.Boolean; #region »ù´¡ÊôÐÔ¹ýÂË var IsHot = _dataProvider.GetParameter(); IsHot.ParameterName = "IsHot"; IsHot.Value = ctx.IsHot.HasValue ? (object)ctx.IsHot.Value : DBNull.Value; IsHot.DbType = DbType.Boolean; var IsRed = _dataProvider.GetParameter(); IsRed.ParameterName = "IsRed"; IsRed.Value = ctx.IsRed.HasValue ? (object)ctx.IsRed.Value : DBNull.Value; IsRed.DbType = DbType.Boolean; var IsTop = _dataProvider.GetParameter(); IsTop.ParameterName = "IsTop"; IsTop.Value = ctx.IsTop.HasValue ? (object)ctx.IsTop.Value : DBNull.Value; IsTop.DbType = DbType.Boolean; var IsSlide = _dataProvider.GetParameter(); IsSlide.ParameterName = "IsSlide"; IsSlide.Value = ctx.IsSlide.HasValue ? (object)ctx.IsSlide.Value : DBNull.Value; IsSlide.DbType = DbType.Boolean; #endregion var pTotalRecords = _dataProvider.GetParameter(); pTotalRecords.ParameterName = "TotalRecords"; pTotalRecords.Direction = ParameterDirection.Output; pTotalRecords.DbType = DbType.Int32; //invoke sited procedure var articles = _dbContext.ExecuteStoredProcedureList <Article>( "ArticleLoadAllPaged", pCategoryIds, pSiteId, pArticleTagId, pFeaturedArticles, pKeywords, pSearchDescriptions, pSearchArticleTags, pUseFullTextSearch, pFullTextMode, pLanguageId, pOrderBy, pAllowedUserRoleIds, pPageIndex, pPageSize, pShowHidden, IsHot, IsRed, IsTop, IsSlide, pWithoutCategories, pTotalRecords); // return articles int totalRecords = (pTotalRecords.Value != DBNull.Value) ? Convert.ToInt32(pTotalRecords.Value) : 0; return(new PagedList <Article>(articles, ctx.PageIndex, ctx.PageSize, totalRecords)); #endregion } else { //sited procedures aren't supported. Use LINQ #region Search articles var query = this.PrepareArticleSearchQuery(ctx, allowedUserRolesIds, searchLocalizedValue); // only distinct articles (group by ID) // if we use standard Distinct() method, then all fields will be compared (low performance) // it'll not work in SQL Server Compact when searching articles by a keyword) query = from p in query group p by p.Id into pGroup orderby pGroup.Key select pGroup.FirstOrDefault(); //sort articles if (ctx.OrderBy == ArticleSortingEnum.Position && ctx.CategoryIds != null && ctx.CategoryIds.Count > 0) { //category position var firstCategoryId = ctx.CategoryIds[0]; // query = query.OrderBy(p => p.ArticleCategories.Where(pc => pc.CategoryId == firstCategoryId).FirstOrDefault().DisplayOrder); } else if (ctx.OrderBy == ArticleSortingEnum.Position) { //parent article specified (sort associated articles) query = query.OrderBy(p => p.DisplayOrder); } else if (ctx.OrderBy == ArticleSortingEnum.Position) { //otherwise sort by name query = query.OrderBy(p => p.Title); } else if (ctx.OrderBy == ArticleSortingEnum.NameAsc) { //Name: A to Z query = query.OrderBy(p => p.Title); } else if (ctx.OrderBy == ArticleSortingEnum.NameDesc) { //Name: Z to A query = query.OrderByDescending(p => p.Title); } else if (ctx.OrderBy == ArticleSortingEnum.CreatedOn) { //creation date query = query.OrderByDescending(p => p.CreatedOnUtc); } else if (ctx.OrderBy == ArticleSortingEnum.CreatedOnAsc) { // creation date: old to new query = query.OrderBy(p => p.CreatedOnUtc); } else { //actually this code is not reachable query = query.OrderBy(p => p.Title); } var articles = new PagedList <Article>(query, ctx.PageIndex, ctx.PageSize); return(articles); #endregion } }
public ArticlesSearchingEvent(ArticleSearchContext ctx) { SearchContext = ctx; }