How Editor returns SUM of column?

fmshighcompany

I need to show in footer the sum of the column.
But I need the sum of all records (serverside sum).
My backend is EDITOR class.

colin

Hi @fmshighcompany ,

This thread here should help - it's discussing the same issue,

Cheers,

Colin

fmshighcompany

Thanks @colin :-) I already saw that example and I'm trying to figure out how to use it :-)

I use EDITOR for backend php:

(..)
Editor::inst( $db, $tbl , 'id' )
        ->fields(
             Field::inst( 'id'       )
            ,Field::inst( 'name'     )
            ,Field::inst( 'price'    )
            )
        ->process( $_POST )
        ->json();
(..)

So, I'm wondering how to return special infos, like SUMs of some columns withing EDITOR process.

allan

It depends a little on exactly what you want to do. Editor doesn't have the ability to use a GROUP BY clause, so aggregation functions don't work very well. What you could do though is use a VIEW to build what you need and then read back from that.

Can you show me the SQL query you are attempting to use with Editor?

Thanks,
Allan

fmshighcompany

Hi @allan!

I'm using no sql, I just instantiate EDITOR on my backend php.

I'm trying to show SUM(price) (from serverside) in Price column's footer.

I did it with success using JS but my table is LARGE so, the SUM JS presents is the sum of the rows currently loaded. When I navigate through the table, as new rows come from the server, the sum shown in footer keeps changing.

I need one unique value (containning the sum of all the rows into the database table according to the filters applied) and not just those rows being shown on screen.

fmshighcompany

Hi there, any help?

Does anybody else has the same need to show a totalizer in the column's footer (but the sum must come from the server), using the php EDITOR as backend?

allan

Okay - I understand now. Thanks for the clarification.

Use something like:

$data = Editor::inst( $db, $tbl , 'id' )
        ->fields(
             Field::inst( 'id'       )
            ,Field::inst( 'name'     )
            ,Field::inst( 'price'    )
            )
        ->process( $_POST )
        ->data();

// Get the sum from the db here
$data['sum'] = $db->sql( 'select sum(...) from ...' );

echo json_encode( $data );

Allan

fmshighcompany

Thanks @allan :-) almost got it!

How do I include in the sum query the correct WHERE condition from EDITOR constructor?

Because EDITOR constructs the WHERE filter by itself.

For example:

I do have one SearchBox on each column in my tables.

For example, I wrote "VIOLET WHEEL" in Name_Search_Box and "250" in Price_Search_Box, so, my PHP EDITOR received this $POST:

Array
(
    [draw] => 3
    [columns] => Array
        (
            [0] => Array
                (
                    [data] => id
                    [name] =>
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] =>
                            [regex] => false
                        )
                )
            [1] => Array
                (
                    [data] => name
                    [name] =>
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] => VIOLET WHEEL
                            [regex] => false
                        )
                )
            [2] => Array
                (
                    [data] => price
                    [name] =>
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] => 250
                            [regex] => false
                        )
                )
        )
    [order] => Array
        (
            [0] => Array
                (
                    [column] => 0
                    [dir] => desc
                )
        )
    [start] => 0
    [length] => 135
    [search] => Array
        (
            [value] =>
            [regex] => false
        )
)

allan

Are you using server-side processing? That's the only thing that I can think of that would cause Editor to generate its own where condition. If that is the case, then what I think you would need to do is effectively replicate that condition as it isn't exposed by Editor I'm afraid.

This is the code that the PHP libraries use to produce the condition.

Allan