1

I want to sum up a field in cakephp 3.2. But the field is present in hasOne model association.

I want to do it in the bind query .

I have a order table and i am doing model bind with collection table .

Here i want to sum up all the due_amount in collections table

I have tried but its not working.

Please check my code,Is there any thing wrong with it.?

 $this->Orders->hasOne('Collections', [
                'className' => 'Collections',
                'foreignKey' => 'order_id',
                'strategy' => 'select',
                'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
                    $query->order(['Collections.id' => 'ASC']);
                    return [];
                }
                    ]);
            $get_total_sales = $this->Orders->find('all')->where($condition)->select(['id', 'region_id', 'net_total'])->contain(['Collections' => ['queryBuilder' => function ($q) {
                                return $q->select(['id', 'order_id', 'total_sale_amount', 'due_amount']);
                            }]])->order(['Orders.due_date DESC']);


$res2 = $get_total_sales->select(['total_due' =>$get_total_sales->func()->sum('collection.due_amount')])->first(); 

 echo  $due = $res2->total_due;//its showing column not find error.
Is it working in order table instead of collections table?
How can i do it for collection table using the $get_total_sales listing results?



Below some out put 

[
    {
        "id": 40,
        "region_id": 2,
        "net_total": 2899.12,
        "collection": {
            "id": 182,
            "order_id": 40,
            "total_sale_amount": 2899.12,
            "due_amount": 1990
        },

    },
    {
        "id": 38,
        "region_id": 2,
        "net_total": 110,
        "collection": {
            "id": 181,
            "order_id": 38,
            "total_sale_amount": 110,
            "due_amount": 10
        },

    },
    {
        "id": 39,
        "region_id": 2,
        "net_total": 16670,
        "collection": {
            "id": 190,
            "order_id": 39,
            "total_sale_amount": 16670,
            "due_amount": 16630.99
        },

    },

Here i want to sum up all the due_amount in collections table

Thank you

sradha
  • 2,216
  • 1
  • 28
  • 48
  • can you please share the db schema as well for these 2 tables and also there is one doubt, if it there is hasOne relation, then obviously you always will have only one result from the associated table then why do you need the sum, Please explain the scenario better and your desired result. – Rohit Ailani Oct 04 '16 at 13:50
  • @Rohit Ailani Actually one record has may collections ,but i need the last one . So i have made it has one ,and got the required data . But i want to sum up all the records and here sum up will be done in the child table . – sradha Oct 04 '16 at 13:56
  • @Rohit Ailani I have one table one is order and one order has multiple collections – sradha Oct 04 '16 at 13:57
  • ok In that case you can use the virtual field of the child table, in your case Collections table. writing the conditions with the main table shall not work. In the virtual field you can write the sum function. – Rohit Ailani Oct 04 '16 at 13:59
  • [http://book.cakephp.org/3.0/en/orm/entities.html#creating-virtual-fields] you can check how to create virtual fields in cake3 here. – Rohit Ailani Oct 04 '16 at 14:03
  • did this serve the purpose I am writing it as a solution for your query and if you want I can help out as well. – Rohit Ailani Oct 04 '16 at 15:16

1 Answers1

1

You can use the virtual field of the child table, which in your case is the Collections table.

You can check the documentation to create virtual fields in cakephp3 here

And in virtual field you can specify the sum function. To write the sum function you can refer here

Rohit Ailani
  • 910
  • 1
  • 6
  • 19
  • yes virtual field is one way and i have discovered some way without using vf. so thank you :) – sradha Oct 04 '16 at 15:40
  • please share the other way as well, so if anyone else requires, they can access both. – Rohit Ailani Oct 04 '16 at 15:41
  • i have accepted your answer as it is one solution to my problem. – sradha Oct 04 '16 at 15:43
  • 1
    yes and so far what I know this is the best solution, otherwise you could have done a hack around by component or some other solution. But this is the best solution by creating virtual field. As I have seen many coders stay away from this approach. – Rohit Ailani Oct 04 '16 at 15:44