public void SearchAssociatedProducts_ReturnsProducts()
{
// Arrange
var verifySet = new[] { "prod-1", "prod-2", "prod-3", "prod-4" }.Select(x => new domainModel.CatalogProduct {
Id = x
}).ToArray();
var criteria = new ProductAssociationSearchCriteria
{
ObjectIds = new[] { "prod-1" },
ResponseGroup = domainModel.ItemResponseGroup.ItemInfo.ToString(),
Sort = "Id"
};
var catalogRepository = new Mock <ICatalogRepository>();
catalogRepository.Setup(x => x.Associations).Returns(TestAssociationEntities.AsQueryable());
catalogRepository.Setup(x => x.Items).Returns(TestItemEntities.AsQueryable());
catalogRepository.Setup(x => x.GetAllChildrenCategoriesIds(It.Is <string[]>(ids => ids.SequenceEqual(new [] { "cat-1" })))).Returns(TestChildCategories);
var itemService = new Mock <IItemService>();
itemService.Setup(x => x.GetByIds(It.Is <string[]>(ids => ids.SequenceEqual(verifySet.Select(p => p.Id))), It.IsIn(domainModel.ItemResponseGroup.ItemInfo), It.IsAny <string>()))
.Returns(verifySet);
var sut = new ProductAssociationSearchService(() => catalogRepository.Object, itemService.Object);
// Act
var result = sut.SearchProductAssociations(criteria);
// Assert
Assert.True(result.TotalCount == 4);
Assert.Equal(result.Results, verifySet);
}
public GenericSearchResult <ProductAssociation> SearchProductAssociations(ProductAssociationSearchCriteria criteria)
{
if (criteria == null)
{
throw new ArgumentNullException(nameof(criteria));
}
if (criteria.ObjectIds.IsNullOrEmpty())
{
return(new GenericSearchResult <ProductAssociation>());
}
using (var repository = _catalogRepositoryFactory())
{
//Optimize performance and CPU usage
repository.DisableChangesTracking();
var result = new GenericSearchResult <ProductAssociation>();
var dbResult = repository.SearchAssociations(criteria);
result.TotalCount = dbResult.TotalCount;
result.Results = dbResult.Results.Select(x => x.ToModel(AbstractTypeFactory <ProductAssociation> .TryCreateInstance())).ToList();
return(result);
}
}
public IHttpActionResult SearchProductAssociations(ProductAssociationSearchCriteria criteria)
{
var searchResult = _productAssociationSearchService.SearchProductAssociations(criteria);
var result = new webModel.ProductAssociationSearchResult
{
Results = searchResult.Results.Select(x => x.ToWebModel(_blobUrlResolver)).ToList(),
TotalCount = searchResult.TotalCount
};
return(Ok(result));
}
public void GetCriteria_InvalidArguments_ThrowsArgumentNullException()
{
var criteria = new ProductAssociationSearchCriteria()
{
ObjectIds = new string[] { }.ToList()
};
var catalogRepository = new Mock <ICatalogRepository>();
var itemService = new Mock <IItemService>();
var sut = new ProductAssociationSearchService(() => catalogRepository.Object, itemService.Object);
Assert.Throws <ArgumentNullException>(() => sut.SearchProductAssociations(criteria));
}
public GenericSearchResult <CatalogProduct> SearchProductAssociations(ProductAssociationSearchCriteria criteria)
{
if (criteria.ObjectIds.IsNullOrEmpty())
{
throw new ArgumentNullException($"{ nameof(criteria.ObjectIds) } must be set");
}
var retVal = new GenericSearchResult <CatalogProduct>();
using (var repository = _catalogRepositoryFactory())
{
//Optimize performance and CPU usage
repository.DisableChangesTracking();
var query = repository.Associations.Where(x => criteria.ObjectIds.Contains(x.ItemId));
if (!string.IsNullOrEmpty(criteria.Group))
{
query = query.Where(x => x.AssociationType == criteria.Group);
}
var associationCategoriesIds = query.Where(x => x.AssociatedCategoryId != null)
.Select(x => x.AssociatedCategoryId)
.ToArray();
//Need to return all products from the associated categories (recursive)
associationCategoriesIds = repository.GetAllChildrenCategoriesIds(associationCategoriesIds).Concat(associationCategoriesIds)
.Distinct()
.ToArray();
var itemsQuery = repository.Items.Join(query, item => item.Id, association => association.AssociatedItemId, (item, association) => item)
.Union(repository.Items.Where(x => associationCategoriesIds.Contains(x.CategoryId)));
var sortInfos = criteria.SortInfos;
if (sortInfos.IsNullOrEmpty())
{
sortInfos = new[] { new SortInfo {
SortColumn = "CreatedDate", SortDirection = SortDirection.Descending
} };
}
//TODO: Sort by association priority
itemsQuery = itemsQuery.OrderBySortInfos(sortInfos);
retVal.TotalCount = itemsQuery.Count();
var itemIds = itemsQuery
.Skip(criteria.Skip)
.Take(criteria.Take)
.Select(x => x.Id).ToList();
retVal.Results = _itemService.GetByIds(itemIds.ToArray(), EnumUtility.SafeParse(criteria.ResponseGroup, ItemResponseGroup.ItemInfo))
.OrderBy(x => itemIds.IndexOf(x.Id))
.ToList();
}
return(retVal);
}
protected virtual Task LoadProductsAssociationsAsync(IEnumerable <Product> products, WorkContext workContext)
{
if (products == null)
{
throw new ArgumentNullException(nameof(products));
}
foreach (var product in products)
{
//Associations
product.Associations = new MutablePagedList <ProductAssociation>((pageNumber, pageSize, sortInfos, @params) =>
{
var criteria = new ProductAssociationSearchCriteria
{
PageNumber = pageNumber,
PageSize = pageSize,
ProductId = product.Id,
ResponseGroup = ItemResponseGroup.ItemInfo | ItemResponseGroup.ItemWithPrices | ItemResponseGroup.Inventory | ItemResponseGroup.ItemWithVendor
};
if (!sortInfos.IsNullOrEmpty())
{
criteria.Sort = SortInfo.ToString(sortInfos);
}
if (@params != null)
{
criteria.CopyFrom(@params);
}
var cacheKey = CacheKey.With(GetType(), "SearchProductAssociations", criteria.GetCacheKey());
var searchResult = _memoryCache.GetOrCreateExclusive(cacheKey, cacheEntry =>
{
cacheEntry.AddExpirationToken(CatalogCacheRegion.CreateChangeToken());
cacheEntry.AddExpirationToken(_apiChangesWatcher.CreateChangeToken());
return(_productsApi.SearchProductAssociations(criteria.ToProductAssociationSearchCriteriaDto()));
});
//Load products for resulting associations
var associatedProducts = GetProductsAsync(searchResult.Results.Select(x => x.AssociatedObjectId).ToArray(), criteria.ResponseGroup).GetAwaiter().GetResult();
var result = new List <ProductAssociation>();
foreach (var associationDto in searchResult.Results)
{
var productAssociation = associationDto.ToProductAssociation();
productAssociation.Product = associatedProducts.FirstOrDefault(x => x.Id.EqualsInvariant(productAssociation.ProductId));
result.Add(productAssociation);
}
return(new StaticPagedList <ProductAssociation>(result, pageNumber, pageSize, searchResult.TotalCount ?? 0));
}, 1, ProductSearchCriteria.DefaultPageSize);
}
return(Task.CompletedTask);
}
private static async Task <object> ResolveConnectionAsync(IMediator madiator, IResolveConnectionContext <ExpProduct> context)
{
var first = context.First;
var skip = Convert.ToInt32(context.After ?? 0.ToString());
var criteria = new ProductAssociationSearchCriteria
{
Skip = skip,
Take = first ?? context.PageSize ?? 10,
// We control the resulting product structure by passing IncludeFields, and to prevent forced reduction of already loaded fields, you need to pass ItemResponseGroup.Full
// in any case, the object will be loaded from the index, and the response group will not affect overall performance
ResponseGroup = ItemResponseGroup.Full.ToString(),
Keyword = context.GetArgument <string>("query"),
Group = context.GetArgument <string>("group"),
ObjectIds = new[] { context.Source.CatalogProduct.Id }
};
var response = await madiator.Send(new SearchProductAssociationsRequest { Criteria = criteria });
return(new Connection <ProductAssociation>()
{
Edges = response.Result.Results
.Select((x, index) =>
new Edge <ProductAssociation>()
{
Cursor = (skip + index).ToString(),
Node = x,
})
.ToList(),
PageInfo = new PageInfo()
{
HasNextPage = response.Result.TotalCount > (skip + first),
HasPreviousPage = skip > 0,
StartCursor = skip.ToString(),
EndCursor = Math.Min(response.Result.TotalCount, (int)(skip + first)).ToString()
},
TotalCount = response.Result.TotalCount,
});
}
public async Task <ProductAssociationSearchResult> SearchProductAssociationsAsync(ProductAssociationSearchCriteria criteria)
{
if (criteria == null)
{
throw new ArgumentNullException(nameof(criteria));
}
var cacheKey = CacheKey.With(GetType(), "SearchProductAssociationsAsync", criteria.GetCacheKey());
return(await _platformMemoryCache.GetOrCreateExclusiveAsync(cacheKey, async (cacheEntry) =>
{
cacheEntry.AddExpirationToken(AssociationSearchCacheRegion.CreateChangeToken());
var result = AbstractTypeFactory <ProductAssociationSearchResult> .TryCreateInstance();
if (!criteria.ObjectIds.IsNullOrEmpty())
{
using (var repository = _catalogRepositoryFactory())
{
//Optimize performance and CPU usage
repository.DisableChangesTracking();
var dbResult = await repository.SearchAssociations(criteria);
result.TotalCount = dbResult.TotalCount;
result.Results = dbResult.Results
.Select(x => x.ToModel(AbstractTypeFactory <ProductAssociation> .TryCreateInstance())).ToList();
}
}
return result;
}));
}
public static catalogDto.ProductAssociationSearchCriteria ToProductAssociationSearchCriteriaDto(this ProductAssociationSearchCriteria criteria)
{
var result = new catalogDto.ProductAssociationSearchCriteria
{
Group = criteria.Group,
ObjectIds = new string[] { criteria.ProductId },
ResponseGroup = criteria.ResponseGroup.ToString(),
Skip = criteria.Start,
Take = criteria.PageSize
};
return(result);
}
public async Task <ActionResult <ProductAssociationSearchResult> > Search([FromBody] ProductAssociationSearchCriteria criteria)
{
var result = await _productAssociationSearchService.SearchProductAssociationsAsync(criteria);
return(Ok(result));
}
public virtual async Task <GenericSearchResult <AssociationEntity> > SearchAssociations(ProductAssociationSearchCriteria criteria)
{
var result = new GenericSearchResult <AssociationEntity>();
var countSqlCommandText = @"
;WITH Association_CTE AS
(
SELECT *
FROM Association
WHERE ItemId IN ({0})
"
+ (!string.IsNullOrEmpty(criteria.Group) ? $" AND AssociationType = @group" : string.Empty)
+ (!criteria.Tags.IsNullOrEmpty() ? $" AND exists( SELECT value FROM string_split(Tags, ';') WHERE value IN (@tags))" : string.Empty) +
@"), Category_CTE AS
(
SELECT AssociatedCategoryId Id
FROM Association_CTE
WHERE AssociatedCategoryId IS NOT NULL
UNION ALL
SELECT c.Id
FROM Category c
INNER JOIN Category_CTE cte ON c.ParentCategoryId = cte.Id
),
Item_CTE AS
(
SELECT i.Id
FROM (SELECT DISTINCT Id FROM Category_CTE) c
LEFT JOIN Item i ON c.Id=i.CategoryId WHERE i.ParentId IS NULL
UNION
SELECT AssociatedItemId Id FROM Association_CTE
)
SELECT COUNT(Id) FROM Item_CTE";
var querySqlCommandText = new StringBuilder();
querySqlCommandText.Append(@"
;WITH Association_CTE AS
(
SELECT
Id
,AssociationType
,Priority
,ItemId
,CreatedDate
,ModifiedDate
,CreatedBy
,ModifiedBy
,AssociatedItemId
,AssociatedCategoryId
,Tags
,Quantity
,OuterId
FROM Association
WHERE ItemId IN({0})"
);
if (!string.IsNullOrEmpty(criteria.Group))
{
querySqlCommandText.Append(" AND AssociationType = @group");
}
if (!criteria.Tags.IsNullOrEmpty())
{
querySqlCommandText.Append(" AND exists( SELECT value FROM string_split(Tags, ';') WHERE value IN (@tags))");
}
querySqlCommandText.Append(@"), Category_CTE AS
(
SELECT AssociatedCategoryId Id, AssociatedCategoryId
FROM Association_CTE
WHERE AssociatedCategoryId IS NOT NULL
UNION ALL
SELECT c.Id, cte.AssociatedCategoryId
FROM Category c
INNER JOIN Category_CTE cte ON c.ParentCategoryId = cte.Id
),
Item_CTE AS
(
SELECT
CONVERT(nvarchar(64), newid()) as Id
,a.AssociationType
,a.Priority
,a.ItemId
,a.CreatedDate
,a.ModifiedDate
,a.CreatedBy
,a.ModifiedBy
,i.Id AssociatedItemId
,a.AssociatedCategoryId
,a.Tags
,a.Quantity
,a.OuterId
FROM Category_CTE cat
LEFT JOIN Item i ON cat.Id=i.CategoryId
LEFT JOIN Association a ON cat.AssociatedCategoryId=a.AssociatedCategoryId
WHERE i.ParentId IS NULL
UNION
SELECT * FROM Association_CTE
)
SELECT * FROM Item_CTE WHERE AssociatedItemId IS NOT NULL ORDER BY Priority ");
querySqlCommandText.Append($"OFFSET {criteria.Skip} ROWS FETCH NEXT {criteria.Take} ROWS ONLY");
var countSqlCommand = CreateCommand(countSqlCommandText, criteria.ObjectIds);
var querySqlCommand = CreateCommand(querySqlCommandText.ToString(), criteria.ObjectIds);
if (!string.IsNullOrEmpty(criteria.Group))
{
countSqlCommand.Parameters.Add(new SqlParameter($"@group", criteria.Group));
querySqlCommand.Parameters.Add(new SqlParameter($"@group", criteria.Group));
}
if (!criteria.Tags.IsNullOrEmpty())
{
AddArrayParameters(countSqlCommand, "@tags", criteria.Tags ?? Array.Empty <string>());
AddArrayParameters(querySqlCommand, "@tags", criteria.Tags ?? Array.Empty <string>());
}
result.TotalCount = await DbContext.ExecuteScalarAsync <int>(countSqlCommand.Text, countSqlCommand.Parameters.ToArray());
result.Results = await(DbContext.Set <AssociationEntity>().FromSqlRaw(querySqlCommand.Text, querySqlCommand.Parameters.ToArray())).ToListAsync();
return(result);
}
public GenericSearchResult <dataModel.AssociationEntity> SearchAssociations(ProductAssociationSearchCriteria criteria)
{
var result = new GenericSearchResult <dataModel.AssociationEntity>();
var countSqlCommandText = @"
;WITH Association_CTE AS
(
SELECT *
FROM Association
WHERE ItemId IN ({0})
"
+ (!string.IsNullOrEmpty(criteria.Group) ? $" AND AssociationType = @group" : string.Empty) +
@"), Category_CTE AS
(
SELECT AssociatedCategoryId Id
FROM Association_CTE
WHERE AssociatedCategoryId IS NOT NULL
UNION ALL
SELECT c.Id
FROM Category c
INNER JOIN Category_CTE cte ON c.ParentCategoryId = cte.Id
),
Item_CTE AS
(
SELECT i.Id
FROM (SELECT DISTINCT Id FROM Category_CTE) c
LEFT JOIN Item i ON c.Id=i.CategoryId WHERE i.ParentId IS NULL
UNION
SELECT AssociatedItemId Id FROM Association_CTE
)
SELECT COUNT(Id) FROM Item_CTE";
var querySqlCommandText = @"
;WITH Association_CTE AS
(
SELECT
Id
,AssociationType
,Priority
,ItemId
,CreatedDate
,ModifiedDate
,CreatedBy
,ModifiedBy
,Discriminator
,AssociatedItemId
,AssociatedCategoryId
,Tags
,Quantity
FROM Association
WHERE ItemId IN({0})"
+ (!string.IsNullOrEmpty(criteria.Group) ? $" AND AssociationType = @group" : string.Empty) +
@"), Category_CTE AS
(
SELECT AssociatedCategoryId Id, AssociatedCategoryId
FROM Association_CTE
WHERE AssociatedCategoryId IS NOT NULL
UNION ALL
SELECT c.Id, cte.AssociatedCategoryId
FROM Category c
INNER JOIN Category_CTE cte ON c.ParentCategoryId = cte.Id
),
Item_CTE AS
(
SELECT
a.Id
,a.AssociationType
,a.Priority
,a.ItemId
,a.CreatedDate
,a.ModifiedDate
,a.CreatedBy
,a.ModifiedBy
,a.Discriminator
,i.Id AssociatedItemId
,a.AssociatedCategoryId
,a.Tags
,a.Quantity
FROM Category_CTE cat
LEFT JOIN Item i ON cat.Id=i.CategoryId
LEFT JOIN Association a ON cat.AssociatedCategoryId=a.AssociatedCategoryId
WHERE i.ParentId IS NULL
UNION
SELECT * FROM Association_CTE
)
SELECT * FROM Item_CTE WHERE AssociatedItemId IS NOT NULL ORDER BY Priority " +
$"OFFSET {criteria.Skip} ROWS FETCH NEXT {criteria.Take} ROWS ONLY";
var countSqlCommand = CreateCommand(countSqlCommandText, criteria.ObjectIds);
var querySqlCommand = CreateCommand(querySqlCommandText, criteria.ObjectIds);
if (!string.IsNullOrEmpty(criteria.Group))
{
countSqlCommand.Parameters = countSqlCommand.Parameters.Concat(new[] { new SqlParameter($"@group", criteria.Group) }).ToArray();
querySqlCommand.Parameters = querySqlCommand.Parameters.Concat(new[] { new SqlParameter($"@group", criteria.Group) }).ToArray();
}
result.TotalCount = ObjectContext.ExecuteStoreQuery <int>(countSqlCommand.Text, countSqlCommand.Parameters).FirstOrDefault();
result.Results = ObjectContext.ExecuteStoreQuery <dataModel.AssociationEntity>(querySqlCommand.Text, querySqlCommand.Parameters).ToList();
return(result);
}
public ActionResult SearchProductAssociations(ProductAssociationSearchCriteria criteria)
{
var result = _productAssociationSearchService.SearchProductAssociations(criteria);
return(Ok(result));
}
public async Task <ActionResult <ProductAssociationSearchResult> > SearchProductAssociations([FromBody] ProductAssociationSearchCriteria criteria)
{
var searchResult = await _productAssociationSearchService.SearchProductAssociationsAsync(criteria);
var result = new ProductAssociationSearchResult
{
Results = searchResult.Results,
TotalCount = searchResult.TotalCount
};
return(Ok(result));
}
