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 https://github.com/sebastienros/yessql/issues/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