How Editor returns SUM of column?

How Editor returns SUM of column?

fmshighcompanyfmshighcompany Posts: 11Questions: 4Answers: 0
edited November 2018 in Editor

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.

Answers

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Hi @fmshighcompany ,

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

    Cheers,

    Colin

  • fmshighcompanyfmshighcompany Posts: 11Questions: 4Answers: 0

    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.

  • allanallan Posts: 61,449Questions: 1Answers: 10,055 Site admin

    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

  • fmshighcompanyfmshighcompany Posts: 11Questions: 4Answers: 0
    edited November 2018

    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.

  • fmshighcompanyfmshighcompany Posts: 11Questions: 4Answers: 0

    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?

  • allanallan Posts: 61,449Questions: 1Answers: 10,055 Site admin

    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

  • fmshighcompanyfmshighcompany Posts: 11Questions: 4Answers: 0
    edited November 2018

    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
            )
    )
    
    
  • allanallan Posts: 61,449Questions: 1Answers: 10,055 Site admin

    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

This discussion has been closed.