Laravel 5 Eloquent sum of multiplied columns for m

2020-03-31 02:45发布

问题:

This is my invoices mongo table 

{
    '_id': ObjectId("565d78336fe444611a8b4593"),
    'TransactionID': 'X020',
    'Type': 'SALESINVOICE',
    'InvoiceNumber': 'ABC020',
    'Date': '2015-11-01 00:00:00',
    'DueDate': '2015-12-01 00:00:00',
    'CurrencyCode': 'GBP',
    'CurrencyRate': NumberLong(1.2),
    'Total': NumberLong(200),
    'PlannedPaymentDate': '',
    'HasAttachments': 0,
    'UpdatedDate': '2015-12-01 10:36:35',
    'AmountDue': NumberLong(200),
    'AmountPaid': 0,
    'Status': 'OPEN',
    'AmountCredited': 0,
    'AmountDebited': 0,
    'Source': 1,
    'InvoiceID': 'csv_1_X020',
    'ExpectedPaymentDate': '2015-12-01 00:00:00',
    'ContactID': 1,
    'ValidateStatus': 'IMPORT',
    'StatusChangeDate': ''
}

What i want the multiplication of two fields (Total * CurrencyRate) I tried this query ::

DB::connection($this->MongoSchemaName)->collection($this->InvoicesTable)->where('Type', 'PAYMENT')->where('ContactID', (int)$customer->ContactID)->select(DB::raw('sum(Total*CurrencyRate)'))->first();

and tried more ::

DB::connection($this->MongoSchemaName)->collection($this->InvoicesTable)->where('Type', 'PAYMENT')->where('ContactID', (int)$customer->ContactID)->sum(DB::raw('Total * CurrencyRate'));

but not getting the exact out put all time i get 0

回答1:

I believe aggregation operators like sum expect exact column name as a parameter. You can try to project the multiplication first, then sum the result:

DB::connection($this->MongoSchemaName)
    ->collection($this->InvoicesTable)
    ->where('ContactID', (int)$customer->ContactID)
    ->project([
        'ContactID' => 1, 
        'TotalInBaseCurrency' => ['$multiply' => ['$Total', '$CurrencyRate']]
    ])
    ->sum('TotalInBaseCurrency')

or use aggregation directly:

DB::connection($this->MongoSchemaName)
    ->collection($this->InvoicesTable)
    ->raw(function($collection) use ($customer){
        return $collection->aggregate([
            ['$match' => [
                    'ContactID' => (int)$customer->ContactID,
                    'Type' => 'PAYMENT'
                ]
            ],
            ['$group' => [
                '_id' => '$ContactID',
                'TotalInBaseCurrency' => [
                        '$sum' => ['$multiply' => ['$Total', '$CurrencyRate']]
                    ]
                ]
            ]
        ]);
    })