CODEWITHSUNDEEP
phpsymfonydoctrine
Dirk J. Faber
Dirk J. Faber

Reputation: 4701

Order on a unassociated field in doctrine

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;
}

Upvotes: 0

Views: 47

Answers (1)

Constantin
Constantin

Reputation: 1304

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

Upvotes: 2

Related Questions