OrderBy List of options

[czcpf]

Hello,

Does anyone know how to specify OrderBy as a list ? For example, on the backend one can do:

$this->entityManager
->getRDBRepository('MyEntity')
->getRelation($entity, 'children')
->order('LIST:machineStatus:IN_USE,STORED_INOPERABLE,TRANSFERRED_SOLD,DISPOSED')
->find();​ 

but how does one do something similar in clientDefs ? The code below doesn't work because it tells me complex expressions cannot be used in orderBy

"relationshipPanels": {
"radiationMachines": {
"filterList": [
"all"
],
"orderBy": "LIST:machineStatus:IN_USE,STORED_INOPERABLE,TRANSFERRED_SOLD,DISPOSE", 
}
}​

It looks like you can specify an OrderItemConverterClass name here and tell espo to use it like in the below example (but I'm not really sure how the below is working or how to modify the code to return a list like above):

Espo\Classes\Select\User\OrderItemConverters\UserN ameOwnFirst.php

<?php
/************************************************************************
* This file is part of EspoCRM.
*
* EspoCRM - Open Source CRM application.
* Copyright (C) 2014-2022 Yurii Kuznietsov, Taras Machyshyn, Oleksii Avramenko
* Website: https://www.espocrm.com
*
* EspoCRM is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* EspoCRM is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with EspoCRM. If not, see http://www.gnu.org/licenses/.
*
* The interactive user interfaces in modified source and object code versions
* of this program must display Appropriate Legal Notices, as required under
* Section 5 of the GNU General Public License version 3.
*
* In accordance with Section 7(b) of the GNU General Public License version 3,
* these Appropriate Legal Notices must retain the display of the "EspoCRM" word.
************************************************************************/

namespace Espo\Classes\Select\User\OrderItemConverters;

use Espo\Core\Select\Order\ItemConverter;
use Espo\Core\Select\Order\Item;

use Espo\ORM\Query\Part\OrderList;
use Espo\ORM\Query\Part\Order;
use Espo\ORM\Query\Part\Expression as Expr;

use Espo\Entities\User;

class UserNameOwnFirst implements ItemConverter
{
private User $user;

public function __construct(User $user)
{
$this->user = $user;
}

public function convert(Item $item): OrderList
{
return OrderList::create([
Order
::create(
Expr::notEqual(
Expr::column('id'),
$this->user->getId()
)
)
->withDirection($item->getOrder()),
Order
::create(Expr::column('userName'))
->withDirection($item->getOrder()),
]);
}
}​

Espo\Resources\metadata\selectDefs\User.json

{
"whereItemConverterClassNameMap": {
"id_isOfType": "Espo\\Classes\\Select\\User\\Where\\ItemConverters\\IsOfType"
},
"accessControlFilterClassNameMap": {
"mandatory": "Espo\\Classes\\Select\\User\\AccessControlFilters\\Mandatory",
"onlyTeam": "Espo\\Classes\\Select\\User\\AccessControlFilters\\OnlyTeam",
"onlyOwn": "Espo\\Classes\\Select\\User\\AccessControlFilters\\OnlyOwn",
"portalOnlyOwn": "Espo\\Classes\\Select\\User\\AccessControlFilters\\PortalOnlyOwn"
},
"primaryFilterClassNameMap": {
"active": "Espo\\Classes\\Select\\User\\PrimaryFilters\\Active",
"activePortal": "Espo\\Classes\\Select\\User\\PrimaryFilters\\ActivePortal",
"activeApi": "Espo\\Classes\\Select\\User\\PrimaryFilters\\ActiveApi",
"portal": "Espo\\Classes\\Select\\User\\PrimaryFilters\\Portal",
"api": "Espo\\Classes\\Select\\User\\PrimaryFilters\\Api",
"internal": "Espo\\Classes\\Select\\User\\PrimaryFilters\\Internal"
},
"boolFilterClassNameMap": {
"onlyMyTeam": "Espo\\Classes\\Select\\User\\BoolFilters\\OnlyMyTeam"
},
"orderItemConverterClassNameMap": {
"userNameOwnFirst": "Espo\\Classes\\Select\\User\\OrderItemConverters\\UserNameOwnFirst"
}
}​

[czcpf]

Hello, for others needing the solution I will post an example below:


Step 1. Define your orderItemConverterClassNameMap and/or ordererClassNameMap for the entity you want custom sorted. For this example you can use either one but I include both for completeness

Espo/Custom/Classes/Select/RadiationMachine/Order/ItemConverters/MachineStatusInUseFirst.php

<?php

namespace Espo\Custom\Classes\Select\RadiationMachine\Order\ItemConverters;

use Espo\Core\Select\Order\ItemConverter;
use Espo\Core\Select\Order\Item;

use Espo\Entities\User;
use Espo\ORM\Query\Part\OrderList;
use Espo\ORM\Query\Part\Order;
use Espo\ORM\Query\Part\Expression as Expr;

class MachineStatusInUseFirst implements ItemConverter
{
private User $user;

public function __construct(User $user)
{
$this->user = $user;
}

public function convert(Item $item): OrderList
{
$GLOBALS['log']->warning('MachineStatusInUseFirst.php ItemConverters - convert() #24 $item = ',[$item->getOrderBy(),$item->getOrder()]);
return match ($item->getOrder()) {
Order::ASC => OrderList::create([
Order::fromString('LIST:machineStatus:DISPOSED,TRANSFERRED_SOLD,STORED_INOPERABLE,IN_USE')
->withDirection(Order::ASC)
]),
default => OrderList::create([
Order::fromString('LIST:machineStatus:IN_USE,STORED_INOPERABLE,TRANSFERRED_SOLD,DISPOSED')
->withDirection(Order::DESC)
]),
};
}​
}​

Espo/Custom/Classes/Select/RadiationMachine/Order/MachineStatus.php

namespace Espo\Custom\Classes\Select\RadiationMachine\Order;

use Espo\Core\Select\Order\ItemConverter;
use Espo\Core\Select\Order\Item;

use Espo\Core\Select\Order\Orderer;
use Espo\ORM\Query\Part\OrderList;
use Espo\ORM\Query\Part\Order;
use Espo\ORM\Query\Part\Expression as Expr;
use Espo\ORM\Query\SelectBuilder;


class MachineStatus implements Orderer
{

public function apply(SelectBuilder $queryBuilder,Item $item): void
{
$GLOBALS['log']->warning('MachineStatus.php Order - apply() #20 $item = ', [$item->getOrderBy(),$item->getOrder()]);

switch ($item->getOrder()) {
case Order::ASC:
$queryBuilder
->order('LIST:machineStatus:DISPOSED,TRANSFERRED_SOLD,STORED_INOPERABLE,IN_USE',Order::ASC);
break;
default:
$queryBuilder
->order('LIST:machineStatus:IN_USE,STORED_INOPERABLE,TRANSFERRED_SOLD,DISPOSED',Order::DESC);
break;
}

}​
}​ 

Step 2. Define these classes in your selectDefs/{yourEntity.json} file

Espo/Custom/Resources/metadata/selectDefs/RadiationMachine.json

{
"orderItemConverterClassNameMap": {
"machineStatusInUseFirst": "Espo\\Custom\\Classes\\Select\\RadiationMachine\\Order\\ItemConverters\\MachineStatusInUseFirst"
},
"ordererClassNameMap": {
"machineStatus": "Espo\\Custom\\Classes\\Select\\RadiationMachine\\Order\\MachineStatus"
}
}​

Step 3. Tell espo you want to use your custom order in entityDefs/{yourEntity.json}

Espo/Custom/Resources/metadata/entityDefs/RadiationMachine.json

"collection": {
"orderBy": "machineStatus", //could also use machineStatusInUseFirst here
"order": "desc"
},​

Step 4. Tell some parent entity you would like to override the default order defined in Step 3 for a child relationship panel (Optional)

Espo/Custom/Resources/metadata/clientDefs/RadiationMachineLocation.json

"relationshipPanels": {
"radiationMachines": {
"filterList": [
"all"
],
"orderBy": "machineStatus", //could also use machineStatusInUseFirst here
"orderDirection": "asc"
}
}​

[rabii]

thanks for sharing