Order on a unassociated field in doctrine

Question

I have two database tables, orders and orderdetails. The price for a product is set in a single order detail. In my order class I calculate the subTotalAmount (a calculated field) like this:

Order.php

private $subTotalAmount;

public function calculateSubTotalAmount(){

    $subtotal = 0;
    foreach ($this->getOrderDetails() as $detail) {
        $subtotal = $subtotal + $detail->getPrice();
    }
    $subtotal = $subtotal -$this->getDiscount();
    return $subtotal;
}

    public function getSubTotalAmount()
{
    $this->subTotalAmount = $this->calculateSubTotalAmount();
    return $this->subTotalAmount;
}

I would like to be able to order by this field, but given that it is unassociated, when I try I get the error:

[Semantical Error] line 0, col 135 near 'subTotalAmount ': Error: Class App\Entity\Order has no field or association named subTotalAmount

Is there any way it would be possible to order by this field?

The query I would like to implement this in is:

public function searchAndSort($term, $sortAttribute, $sortOrder)
{
    $qb = $this->createQueryBuilder('po');
    $qb->andWhere('po.invoiceNumber LIKE :term or u.email LIKE :term')
        ->LeftJoin('po.orderedBy',  'u')
        ->setParameter('term', '%'.$term.'%')
        ->orderBy($sortAttribute, $sortOrder)
        ->getQuery()->getResult();
    return $qb;
}

Constantin · Answer 1 · 2018-07-13T17:28:48.680

2

It's possible to do it in sql (even with doctrine).
It's also possible to handle this in php.
You have to choose one solution which is easy for you.

In sql / doctrine, you will have to join orderDetails, group by order.id, select order + use the function SUM(orderDetail.id) - order.id as subtotal, and finally orderby subtotal

in php you will have to sort the array of objects by your subtotal method, see this post on how to do this: https://stackoverflow.com/a/4282423/6324627

edited Jul 13 '18 at 17:28

answered Jul 13 '18 at 17:20

Constantin

1,258
10
9

Is there not an easier way? The issue I have is that I would like to use this is a query that I also use to sort other attributes of the order and to return a search result. I have added this query to my question to make it understandable. In my table I already have all attributes (including the sub total). Only the sub total is not sortable. – Dirk J. Faber Jul 13 '18 at 20:10

Order on a unassociated field in doctrine

1 Answers1