Fulfillment lines relation combined with lines relation on order causes quadratic database response growth

[Dennor]

Small preface, I'm not sure if it's a bug as it mostly is a performance issue but it does not feel like feature either.

Describe the bug
Fetching an order from admin panel with fulfillment lines relation (default order view in admin panel due to Relations decorator) causes the database response from findOne to grow quadratically with the number of order lines.

For example. Given 20 lines in order, 2 channels, 2 prices in each channel, 2 translations for variant, 2 translations for shipping method and created fulfillment the query will return:

20 (number of order lines) * 2 (number of channels) * 2 (number of prices in each channel) * 2 (number of translations for variants) * 2 (number of translations for shipping method) * 20 (number of fulfillment lines) = 6400 rows

Instead of required 20 rows

Basically the number of rows returned by order queries (both findOne and findAll) with fulfillment lines and regular order lines can be summarized as:

n - Number of order lines

n^2 * count_of_variant_prices_for_given_variant * num_of_translations_for_given_variant * num_of_translations_for_shipping_method

To Reproduce
Steps to reproduce the behavior:

1. Create an order with 20 lines
2. Go to order view.
3. Create fulfillment.
4. Go to order view again.
5. Log out the OrderService.findOne query and run it against database directly.

Expected behavior
No overfetch.

Environment (please complete the following information):

• @vendure/core version: 2.3 (but I believe it also aplies to 3+ as there was no change to that particular logic here)
• 22.14.0
• Database (mysql/postgres etc): postgresql in my case, but probably applies to all?

Additional context
An ugly snippet like this:

    private restrictOrderJoins(ctx: RequestContext, qb: {
        expressionMap: QueryExpressionMap,
        getParameters: () => Record<string, unknown>,
        setParameter: (key: string, value: unknown) => unknown,
    }): void {
        if (!qb.getParameters()['languageCode']) {
            qb.setParameter('languageCode', ctx.languageCode)
        }

        // fulfillments.lines will cause the quadratic growth of response if queried along with
        // lines relation without additional restrictions. So we intentially modify that relation.
        // Additionally prevent overfetching of unnecessary translations and prices.
        qb.expressionMap.joinAttributes.forEach((join) => {
            if (!join.condition) {
                const inverseTarget = join.relationCache?.inverseEntityMetadata.target;
                if (inverseTarget) {
                    if (!join.condition && [
                            ProductVariantTranslation,
                            ShippingMethodTranslation,
                            PromotionTranslation,
                        ].find((el) => el === inverseTarget)
                    ) {
                        join.condition = `"${join.alias.name}"."languageCode" = :languageCode`
                    }
                }
                if (inverseTarget === ProductVariantPrice) {
                    join.condition = `"${join.alias.name}"."currencyCode" = "order"."currencyCode" AND "${join.alias.name}"."channelId" = :channelId`
                }
                if (inverseTarget === FulfillmentLine) {
                    const lines = qb.expressionMap.joinAttributes.find((join) => join.relationCache?.inverseEntityMetadata.target === OrderLine);
                    if (lines) {
                        join.condition = `"${join.alias.name}"."orderLineId" = "${lines.alias.name}"."id"`;
                    }
                }
            }
        });
    }

did make the query return expected number of rows.

[RoyalFoxy]

We have a similar issue but related to the surcharges. We haven't yet found a proper fix for this sadly :(

[Dennor]

Eh, that's much worse than mine above. On the order line <- fulfillment relation we can avoid cartesian product for fulfillment ... because there's a relation with order line.

On surcharge, aside from some artificial discriminator like below (meaning handcrafting some monstrosity SQL that I will not event attempt at this point):

SELECT row_number() over () as discriminator, * FROM order_line ol WHERE ol."orderId" = :id;

I don't think anything can be done in terms of SQL query. That means that pretty much always, the order will be a cartesian product of order lines and surcharges (and promotions, and shipping lines, and fulfillment lines, and payments for that order ... that's a lot 😄 not to forget translations and multiple prices for variants 🤷 ) if someone just asks for it in GraphQL query.

I don't think there's anything to be done here aside from going nuclear and removing Relations decorator all together and leaving the resolution of relations to GraphQL which would be pretty much be equivalent to query splitting (which is the only reasonable solution in this case I think?).

That, or allow Relations decorator to exclude certain relations that would lead to this pitfal? Basically any request to one of one to many of the fields using this decorator for entities that have more than one one to many relations?

Fields using Relations(Order) decorator:

• Query.orders, Query.orderByCode from shop api (Query.activeOrder does not use the relations arg depsite having it declared)
• Query.order from admin-api
• Customer.orders from all apis
• OrderLine.order from all apis (OrderLine.fulfillmentLines does not use the arg)

[github-actions]

Closed automatically because there has been no activity for {{days-before-close}} days after a reminder. Please comment with new information and remove the label to re-open.