Sales Pack - Adding a primary filter to sales order items

[ibdev]

Hello,

I am trying to show a list of order items under account where the related sales order status is Completed and I added the following:

custom/Espo/Custom/Resources/metadata/clientDefs/Account.json

"relationshipPanels": {
"salesOrderItems":
{
"name": "salesOrderItems",
"label": "Completed Sales Order Items",
"view": "custom:views/account/panels/completed-order-items",
"rowActionsView": "views/record/row-actions/view-only",
"selectPrimaryFilterName": "salesOrderCompleted",
"filterList": ["salesOrderCompleted"]
}
}
}

custom/Espo/Custom/Select/SalesOrderItem/PrimaryFilters/SalesOrderCompleted.php

<?php

namespace Espo\Custom\Select\SalesOrderItem\PrimaryFilters;
use Espo\Core\Select\Primary\Filter;
use Espo\ORM\Query\SelectBuilder;
use Espo\ORM\Query\Part\Condition as Cond;


class SalesOrderCompleted implements Filter
{
public function apply(SelectBuilder $queryBuilder): void
{

$queryBuilder->where(
Cond::in(
Cond::column('salesOrderStatus'),
['Completed']
));


}
}
​

custom/Espo/Custom/Resources/metadata/selectDefs/SalesOrderItem.json

{
"primaryFilterClassNameMap": {
"salesOrderCompleted": "Espo\\Custom\\Select\\SalesOrderItem\\PrimaryFilters\\SalesOrderCompleted"
}
}
​

custom/Espo/Custom/Resources/metadata/clientDefs/SalesOrderItem.json

"filterList":[
{
"name":"salesOrderCompleted"
}
]​

The problem is that I get Error 500 when I view any account with this error in the logs:

ERROR: (42S22) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'salesOrder.status' in 'where clause'; GET /Account/661edb6c6b94fce1a/salesOrderItems; line: 76,

After debugging and logging the sql I found that the problem is with the count query, it does not have the necessary joins.
Here are the two queries that get executed when the panel is rendered:

SELECT salesOrderItem.id AS `id`, salesOrderItem.created_by_id AS `createdById`, salesOrderItem.with_related AS `withRelated`, salesOrderItem.commission AS `commission`, salesOrderItem.payment_term AS `paymentTerm`,salesOrderItem.unit_price_currency AS `unitPriceCurrency`, salesOrderItem.unit_price AS `unitPrice`, salesOrder.status AS `salesOrderStatus`, salesOrderItem.sales_order_id AS `salesOrderId`, salesOrder.name AS `salesOrderName`,salesOrderItem.supplier_id AS `supplierId`, supplier.name AS `supplierName`, salesOrderItem.name AS `name`, salesOrderItem.created_at AS `createdAt` FROM `sales_order_item` AS `salesOrderItem` LEFT JOIN `supplier` AS `supplier` ON salesOrderItem.supplier_id = supplier.id LEFT JOIN `sales_order` AS `salesOrder` ON salesOrderItem.sales_order_id = salesOrder.id WHERE
salesOrder.status IN ('Completed') AND salesOrderItem.account_id = '661edb6c6b94fce1a' AND salesOrderItem.deleted = 0 ORDER BY salesOrderItem.created_at DESC, salesOrderItem.id DESC LIMIT 0, 5


SELECT COUNT(salesOrderItem.id) AS `value` FROM `sales_order_item` AS `salesOrderItem` WHERE salesOrder.status IN ('Completed') AND salesOrderItem.account_id = '661edb6c6b94fce1a' AND salesOrderItem.deleted = 0
​

Any idea how to solve this issue? Maybe I am missing something

Thanks

[yuri]

Join 'salesOrder' link, assign some unique alias.

->join('salesOrder', 'salesOrderMy')

Then

->where(['salesOrderMy.status' => 'Completed'])

[ibdev]

Join 'salesOrder' link, assign some unique alias.

->join('salesOrder', 'salesOrderMy')

Then

->where(['salesOrderMy.status' => 'Completed'])

This worked.

Thank you