Query for getting OrderLineItems in Shopware 6 fails due to missing column 'order_line_item.order.order_line_item_version_id'

Question

I'm trying to get all OrderLineItems in Shopware 6.4.18.1, that are a product of a certain manufacturer and:

• paid for (if prepayment was used)
• or either:
  • paid partially
  • payment in progress
  • payment open

The following code:

<?php

declare(strict_types=1);

namespace MyBundle\App\Query\OrderLineItem;

use Shopware\Core\Checkout\Payment\Cart\PaymentHandler\PrePayment;
use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\EntityCollection;
use Shopware\Core\Framework\DataAbstractionLayer\EntityRepository;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\AndFilter;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\EqualsFilter;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\NorFilter;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\OrFilter;

class OrderLineItemQuery
{
    public const PAID_STATE = 'paid';
    public const PAID_PARTIALLY_STATE = 'paid_partially';
    public const IN_PROGRESS_STATE = 'in_progress';
    public const OPEN_STATE = 'open';

    protected EntityRepository $orderLineItemRepository;

    public function __construct(
        EntityRepository $orderLineItemRepository
    ) {
        $this->orderLineItemRepository = $orderLineItemRepository;
    }

    public function getOrderLineItemsByManufacturer(string $manufacturerId): EntityCollection
    {
        $criteria = new Criteria();

        // default associations needed for following procedures
        $criteria->addAssociations([
            'order.deliveries.shippingOrderAddress.country',
            'order.deliveries.shippingOrderAddress.salutation',
            'order.orderCustomer.customer.salutation',
            'order.salesChannel',
            'order.transactions.stateMachineState',
            'order.transactions.paymentMethod',
            'product.manufacturer',
        ]);

        $criteria->addFilter(
            // only take manufacturer products
            new EqualsFilter('product.manufacturer.id', $manufacturerId),
            // exclude unpaid prepayment orders
            new OrFilter([
                new EqualsFilter('order.transactions.stateMachineState.technicalName', self::PAID_STATE),
                // removing the filters inside here fixes the problem
                new AndFilter([
                    new NorFilter([
                        new EqualsFilter('order.transactions.paymentMethod.handlerIdentifier', PrePayment::class),
                    ]),
                    new OrFilter([
                        new EqualsFilter('order.transactions.stateMachineState.technicalName', self::PAID_PARTIALLY_STATE),
                        new EqualsFilter('order.transactions.stateMachineState.technicalName', self::IN_PROGRESS_STATE),
                        new EqualsFilter('order.transactions.stateMachineState.technicalName', self::OPEN_STATE),
                    ]),
                ]),
            ])
        );

        return $this->orderLineItemRepository->search($criteria, Context::createDefaultContext())->getEntities();
    }
}

But it only returns the Error:

In Exception.php line 18:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_line_item.order.order_line_item_version_id' in 'field list'

Removing everything inside the AndFilter seems to remove the error, but I need to exclude the OrderLineItems from orders where prepayment was choosen as payment method, but not yet paid.

How should I change the query to make it work?

Answer 1

I think the problem here might be a circular reference. Since you're querying mostly associations of the order, I'd recommend basing your query on the order table by using order.repository instead. By passively adding the lineItems association, you can then iterate over the OrderEntity instances and collect all corresponding line items by calling getLineItems().

$criteria = new Criteria();

// default associations needed for following procedures
$criteria->addAssociations([
    'deliveries.shippingOrderAddress.country',
    'deliveries.shippingOrderAddress.salutation',
    'orderCustomer.customer.salutation',
    'salesChannel',
    'transactions.stateMachineState',
    'transactions.paymentMethod',
]);

/**
 * Get an association `lineItems`. (is added by calling the getter as well)
 * Add a filter specifically for that association.
 * Optionally, if you need `product` and `manufacturer` data, 
 * add them as additional associations.
 */
$criteria->getAssociation('lineItems')
    ->addFilter(
        new EqualsFilter('product.manufacturer.id', $manufacturerId)
    )
    ->addAssociation('product.manufacturer');

$criteria->addFilter(
    new OrFilter([
        new EqualsFilter('transactions.stateMachineState.technicalName', self::PAID_STATE),
        new AndFilter([
            new NorFilter([
                new EqualsFilter('transactions.paymentMethod.handlerIdentifier', PrePayment::class),
            ]),
            new OrFilter([
                new EqualsFilter('transactions.stateMachineState.technicalName', self::PAID_PARTIALLY_STATE),
                new EqualsFilter('transactions.stateMachineState.technicalName', self::IN_PROGRESS_STATE),
                new EqualsFilter('transactions.stateMachineState.technicalName', self::OPEN_STATE),
            ]),
        ]),
    ])
);

$orders = $this->orderRepository->search($criteria, Context::createDefaultContext())->getEntities();

$lineItems = new OrderLineItemCollection();
/** @var OrderEntity $order */
foreach ($orders as $order) {
    $lineItems->merge($order->getLineItems());
}

return $lineItems;