SUM in Server script

Tony S

Hello,
I would like to know how to do SUM on a joined table ?
example:

Editor::inst( $db, 'A')
        ->fields(
                Field::inst( 'id' )->set( false ),
                Field::inst( 'dated' ),
                Field::inst( 'km' ),
                Field::inst('SUM(B.amount)', 'am' )->set( false )
)
        ->join(
            Mjoin::inst( 'B' )
                ->link( 'A.id', 'B.jour' )
                ->fields(
                        Field::inst( 'id' )
        )

)
    ->process( $_POST )
    ->json();

How to formulate ('SUM (B.amount)'
Thank you.

rf1234

I think you can't do that with Editor. But there is a way to do something similar. Create a field instance like this:

Field::inst( 'id AS mySum' )->set( false )
->getFormatter( function ($val, $data, $opts) {
        execute your proprietary SQL-statement with your own db handler
        global yourDbHandler;
        ....
        return yourResult;
})

Of course you can also use an Mjoin to return a number of individual values to the client and then do the sum manually by adding the stuff up client side. That is cumbersome ... and not needed I think.

rf1234

Here is an example from my coding. I need to get an aggregated value from a different table which I can't get through a simple join or an MJoin. Aliasing an existing sql table column is the way to do it.

Field::inst( 'contract.follow_up_days_creditor AS max_exp_date' )->set( false )
    ->getFormatter( function($val, $data, $opts) {
            return getFormatterMaxExpDate($data['contract.id']);
    }),

function getFormatterMaxExpDate($contractId) {
    global $dbh;  
    $dbh->query('SELECT MAX(end_date) AS max_date   
                 FROM fixed
                 LEFT JOIN contract_has_fixed ON contract_has_fixed.fixed_id = fixed.id
                 LEFT JOIN contract ON contract_has_fixed.contract_id = contract.id
                 WHERE contract.id = :contractId 
                   AND fixed.plan_element < 1 
                    UNION
                 SELECT MAX(end_date) AS max_date   
                 FROM variable
                 LEFT JOIN contract_has_variable ON contract_has_variable.variable_id = variable.id
                 LEFT JOIN contract ON contract_has_variable.contract_id = contract.id
                 WHERE contract.id = :contractId 
                  AND  variable.plan_element < 1 
                 ORDER BY 1 DESC
                 LIMIT 1');
    $dbh->bind(':contractId', $contractId);
    $row = $dbh->singleAssoc(); // a one-dimensional array is returned SINGLE
    return getFormatterDate($row['max_date']);
}

If I had been a little smarter by the time I wrote this I could have written:

Field::inst( 'contract.id_creditor AS max_exp_date' )->set( false )
    ->getFormatter( function($val, $data, $opts) {
            return getFormatterMaxExpDate($val);
    }),

That also passes contract.id into the function.

Tony S

Thank you rf1234,
It seemed to me that it is impossible, I am disappointed by Editor, because they are classic functions of every day ...
I am only amateur with javascript, do you think it's easier with jquery client side to recover SUM?
I already used this example with length (count) :

var jTable = $('#A').DataTable( {
dom: 'Bfrtip',
ajax: 'php/A.php',
columns: [
{data: 'dated', className: 'dt-body-center'},
{data: 'km', className: 'dt-body-center'},
{data: 'B', className: 'dt-body-center', render: function ( d ) {return d.length} }
],

Knowing that B is a Mijoin table.

rf1234

I would get rid of the Mjoin in this case and use your own SQL as suggested by me above. To manually sum up a variable on the client side is not very efficient.

Tony S

Ok I'll try this tomorrow

allan

Hi,

rf1234 is correct - Editor's libraries won't let you do a sum on the Mjoined data (its actually two SQL queries which is why that isn't possible).

Instead, I'd suggest using a relatively simple rendering method on the client-side to do the summation and display the result from the Mjoined data:

{
  data: 'B',
  render: function (data, type, row) {
    return data.reduce( function (accum, item) {
      return accum + item.amount;
    }, 0 );
  }
}

Just make sure you include a field for amount in the field list for the Mjoin.

Allan

Tony S

Hi,
Thank you Allan, actually your solution works very well:

Tony S

{  data: 'B',
  render: function (data, type, row) {
    return data.reduce( function (a, b) {
      return a+ (b.amount)*1;
    }, 0 );
  }
}

Tony S

By cons I have difficulties to place:

render: $.fn.dataTable.render.number(' ', '.', 2, '',' €')

shatrughan

@Allen Sir,

the above function

{
data: 'B',
render: function (data, type, row) {
return data.reduce( function (accum, item) {
return accum + item.amount;
}, 0 );
}
}

produces sum of distinct values only i.e. child rows with equal amount is taken once in total. Please guide how to take all 'amount' in accumulated value.

allan

I'm afraid I don't understand what you are looking to do. Could you show me the data you are using and also the calculation you want to perform based on that data?

Thanks,
Allan