Additional join clause in belongsTo-join

I have a problem with the following restriction:

For BelongsTo and HasOne associations only the where and select clauses are used when loading the associated records. For the rest of the association types you can use every clause that the query object provides.

The simply question: why? :slight_smile:

The problem:

I have 2 tables (orders and orderPositions). An order belongsTo an orderPosition. Each orderPosition has a price. The grand total of an order is the sum of these prices. To get this, i use a joinTable which summarize all the prices and the order table joins with this table.

OrdersTable

public function findAll (Query $query, array $options) { $query ->join([ 'o' => [ 'table' => TableRegistry::get('OrderPositions') ->find() ->select([ 'grand_total' => 'SUM(price)' ]) ->group([ 'OrderPositions.order_id' ]), 'type' => 'LEFT', 'conditions' => 'Orders.id = o.id' ] ]) ->select([ 'Orders__grand_total' => 'IFNULL(o.grand_total, 0)' ]) ->enableAutoFields(true); return $query; }

Controller:

TableRegistry::get('Orders')->find()->first()->grand_total; // -- No problem TableRegistry::get('OrderPositions')->find()->contain(['Orders'])->first()->order->grand_total; // -- Error: grand_total is no field name

But how could i solve the problem? The problem is caused by the restriction, but how could i get the grand total of an order with an additional join (no subquery!)?

Thanks a lot.