How to sum the value of two columns from two different tables using Laravel's Eloquent

I am using the jQuery datatables plugin and adding the column valores_receber (values_to_receive) using a foreach loop that do the sum but in a separate way as you can see in the following code.

public function anyDataReceber() {

$clientes = Partner::whereHas('faturamentos', function($q){
        $q->where('status', '<>', 'CANCELADO');
    })
    ->orWhereHas('faturamentosLivres', function ($q) {
        $q->where('status','<>','CANCELADO');
    })
    ->with('faturamentos')
    ->with('faturamentosLivres');

return Datatables::of($clientes)
    ->addColumn('valores_receber', function ($clientes) {
        $total = 0;
        foreach($clientes->faturamentos as $fatura1) {
            if ($fatura1->status != 'CANCELADO') $total += $fatura1->total_usd - $fatura1->valor_pago;
        }
        foreach($clientes->faturamentosLivres as $fatura2) {
            if ($fatura2->status != 'CANCELADO') $total += $fatura2->total_usd - $fatura2->valor_pago;
        }
        return number_format($total,2,',','.');
    })
    ->addColumn('action', function ($clientes) {
        $actions = '';
        $actions = '<a href="/admin/financeiro-matriz/contas-receber/'.$clientes->id.'" class="btn btn-xs btn-primary"><i class="fa fa-eye"></i> Visualizar</a>';
        return $actions;
    })
    ->make(true);

}

The problem here is that the datatable can not order the column valores_receber because this very column does not exist in the result query from Eloquent.

I researched about the SUM() function from mySQL but I can't make a solution using Eloquent and the tables relationships.

I checked the following answers that should be in the right track but are using normal SQL instead and from what I researched it needs some sort of join or union statements, but how to perform that in Eloquent?

• How to calculate sum of two columns from two different tables without where clause?
• How to sum data of two different columns of two different tables?

So for the datatable to be able to order the column valores_receber, I need that column to show up in the results of some sort of Eloquent statement.

What I am trying to achieve is:

• make the query using Eloquent that sums the values of the columns total_usd that is present in both tables faturamentos and faturamentos_livres (invoices and free_invoices)
• those tables need to be restricted by the status column that must be any value but CANCELADO (Canceled). This status column is an ENUM type

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2m7DYvt
via IFTTT