How to handle complex queries in Orchard Core?

[craftyweb]

Hi, I'm using Orchard Core for my webshop project and now I'm in a situation that I want to create a query so that I can get my products but also variations when a product has selected ShowVariationsInOverview

I'm trying to do something like this but it doesn't work

When I try a less complex query like

It works

[hishamco]

What is the exception you got? Could you please write a unit test in YesSQL? To make it clear is it YesSQL bug or not

[mariojsnunes]

This is probably the same issue as #472

[craftyweb]

Thanks for the reply.

I'm not getting an exception but I'm not getting the correct results.

Here is my complete code:

var excludedProductsQuery = _session.Query<ContentItem>().All(
	x => x.With<ContentItemIndex>().Where(ci => ci.Published && ci.ContentType == nameof(Product)),
	x => x.With<BooleanFieldIndex>().Where(b => b.ContentField == nameof(ProductPart.ShowInStore) && b.Boolean == true),
	x => x.With<BooleanFieldIndex>().Where(b => b.ContentField == nameof(ProductPart.ShowVariationsInOverview) && b.Boolean == true),
	x => x.Any(
		x => x.With<DateTimeFieldIndex>().Where(d => d.ContentField == nameof(ProductPart.AvailableStartDate) && d.DateTime == null),
		x => x.With<DateTimeFieldIndex>().Where(d => d.ContentField == nameof(ProductPart.AvailableStartDate) && now >= d.DateTime)
	),
	x => x.Any(
		x => x.With<DateTimeFieldIndex>().Where(d => d.ContentField == nameof(ProductPart.AvailableEndDate) && d.DateTime == null),
		x => x.With<DateTimeFieldIndex>().Where(d => d.ContentField == nameof(ProductPart.AvailableEndDate) && now <= d.DateTime)
	)
);
                                            

var excludedProducts = (await excludedProductsQuery.ListAsync()).Select(c => c.ContentItemId);

var query = _session.Query<ContentItem>().Any(
	x => x.All(
		x => x.With<ContentItemIndex>(ci => ci.Published && ci.ContentType == "ProductVariation"),
		x => x.With<ContainedPartIndex>(c => c.ListContentItemId.IsIn(excludedProducts))
	),
	x => x.All(
		x => x.With<ContentItemIndex>().Where(ci => ci.Published && ci.ContentType == nameof(Product)),
		x => x.With<BooleanFieldIndex>().Where(b => b.ContentField == nameof(ProductPart.ShowInStore) && b.Boolean == true),
		x => x.With<ContentItemIndex>().Where(ci => ci.ContentItemId.IsNotIn(excludedProducts))
	)
);

What I try to do is retrieving:

Products

• BooleanField ShowInStore = true
• BooleanField ShowVariationsInStore = false

ProductVariations (ContentItems in ListPart attached to Product):

• Only showing ProductVariations when the parent product has BooleanField ShowVariationsInStore = true

When I execute this query I don't get an exception but I just don't get the complete results.

When I try it in Microsoft SQL Server Management Studio it works

SELECT c.DisplayText
FROM ContentItemIndex c INNER JOIN (
	SELECT d.Id
	FROM Document d
		INNER JOIN ContentItemIndex c1 on c1.DocumentId = d.Id
		INNER JOIN ContainedPartIndex c2 on c2.DocumentId = d.Id
	WHERE c1.Published = 1
		AND c1.ContentType = 'ProductVariation'
		AND c2.ListContentItemId IN (
			SELECT c1.ContentItemId
            FROM Document d
                INNER JOIN ContentItemIndex c1 on c1.DocumentId = d.Id
                INNER JOIN BooleanFieldIndex b1 on d.Id = b1.DocumentId
          		INNER JOIN BooleanFieldIndex b2 on d.Id = b2.DocumentId
                INNER JOIN DateTimeFieldIndex d1 on d1.DocumentId = d.Id
                INNER JOIN DateTimeFieldIndex d2 on d2.DocumentId = d.Id
            WHERE c1.Published = 1
                AND c1.ContentType = 'Product'
                AND b1.ContentField = 'ShowInStore'
                AND b1.Boolean = 1
          		AND b2.ContentField = 'ShowVariationsInOverview'
                AND b2.Boolean = 1
                AND d1.ContentField = 'AvailableStartDate'
                AND (d1.DateTime IS NULL OR GETDATE() >= d1.DateTime)
                AND d2.ContentField = 'AvailableEndDate'
                AND (d2.DateTime IS NULL OR GETDATE() <= d2.DateTime)
		)
	UNION
	SELECT d.Id
	FROM Document d
		INNER JOIN ContentItemIndex c1 on c1.DocumentId = d.Id
		INNER JOIN BooleanFieldIndex b1 on d.Id = b1.DocumentId
		INNER JOIN DateTimeFieldIndex d1 on d1.DocumentId = d.Id
  		INNER JOIN BooleanFieldIndex b2 on d.Id = b2.DocumentId
		INNER JOIN DateTimeFieldIndex d2 on d2.DocumentId = d.Id
    WHERE c1.Published = 1
      	AND c1.ContentType = 'Product'
      	AND b1.ContentField = 'ShowInStore'
      	AND b1.Boolean = 1
  		AND b2.ContentField = 'ShowVariationsInOverview'
  		AND b2.Boolean = 0
      	AND d1.ContentField = 'AvailableStartDate'
      	AND (d1.DateTime IS NULL OR GETDATE() >= d1.DateTime)
      	AND d2.ContentField = 'AvailableEndDate'
      	AND (d2.DateTime IS NULL OR GETDATE() <= d2.DateTime)
) r on c.DocumentId = r.Id
ORDER BY c.ContentItemId, c.DisplayText

If there is some possibility to do a union on multiple queries so I can order the results of the two queries before I do paging that would be good as well I think