Cakephp 3: sort main table by sub table field (contain)

1

I need to know if it is possible to sort the "sales" table by the "date" field of the related "SaleBooking".

$query = $this->_salesTable->find()
            ->where([
                'product_id IN'     => $ids,
                'status IN'         => $status,
                'type IN'           => $types
            ])
            ->select([
                'id',
                'product_id',
                'entity_id',
                'Sales.type',
                'status',
                'quantity',
            ])
            ->contain([
                'Product',
                'SaleBooking'
            ]);

I have tried with the following code but it orders me the records of the contain and what I need is to order the main table by a field of the related table:

  $query->contain(['SaleBooking' => ['sort' => ['SaleBooking.date' => 'DESC']]]);

The model is:

 class SalesTable extends FilterTable
  {
    public function initialize(array $config)
    {
        $this->table('sa_sales');
        $this->primaryKey('id');
        $this->addBehavior('Timestamp');

        $this->hasMany('SaleBooking', [
            'className' => 'SaleBookings',
            'foreignKey' => 'sale_id',
            'dependent' => true,
            'propertyName' => 'saleBookings',
        ]);
     }
   }

 class SaleBookingsTable extends BaseTable
{
    public function initialize(array $config)
    {
        $this->table('sa_sale_bookings');
        $this->primaryKey('id');
        $this->addBehavior('Timestamp');

        $this->belongsTo('BookingSale', [
            'className' => 'Sales',
            'foreignKey' => 'sale_id',
            'propertyName' => 'saleBookings'
        ]);

    }
}

Thank you very much for the help in advance. Greetings.

    
asked by Mar 31.05.2018 в 20:09
source

1 answer

0

As you call it in the contain you can do it without any problem with the function order

$query = $this->_salesTable->find()
    ->where([
        'product_id IN'     => $ids,
        'status IN'         => $status,
        'type IN'           => $types
    ])
    ->select([
        'id',
        'product_id',
        'entity_id',
        'Sales.type',
        'status',
        'quantity',
    ])
    ->contain([
        'Product',
        'SaleBooking'
    ])
    ->order([
        'SaleBooking.date' => 'DESC'
    ]);

As a separate tip, it is always better to call the fields with their respective alias, for example 'Sales.id' as appropriate

    
answered by 01.06.2018 в 00:23