Admin order list heavy query

[ulinskas]

Sylius version affected: 1.12.1

Description
When sylius admin orders page fetch orders, it joins unnecessary tables which makes query larger, heavier and longer.

Steps to reproduce
Visit /admin/orders/ page and check query created via doctrine.

1. For counting orders it selects all unnecessary order data + joins order items, products and product variants. This query requires only order id count and order state.
2. Displaying paginated order list. Same problem, fetches too much data + joins products and product variants.

Possible Solution
Changes must be made here:
/Sylius/Bundle/CoreBundle/Doctrine/ORM/OrderRepository.php

From:

    public function createSearchListQueryBuilder(): QueryBuilder
    {
        return $this->createListQueryBuilder()
            ->leftJoin('o.items', 'item')
            ->leftJoin('item.variant', 'variant')
            ->leftJoin('variant.product', 'product')
        ;
    }

To:

    public function createSearchListQueryBuilder(?array $criteria = []): QueryBuilder
    {
        $builder = $this->createListQueryBuilder();

        $hasProductCriteria = $criteria['product'] ?? false;
        $hasVariantCriteria = $criteria['variant'] ?? false;

        if ($hasProductCriteria || $hasVariantCriteria) {
            $builder
                ->leftJoin('o.items', 'item')
                ->leftJoin('item.variant', 'variant')
                ->leftJoin('variant.product', 'product');
        }

        return $builder;
    }

[Rafikooo]

Hello,

Thank you for raising this issue. You're right about this case.

We appreciate your engagement with the Sylius community, and we encourage you to open a pull request with your suggested changes to the code. This will allow us to review the changes and potentially merge them into the codebase 💃

Thank you again for your contribution to Sylius, and please let us know if you have any further questions or concerns 🚀

Best regards,
Rafał