Group by in Editor

Group by in Editor

antoniocibantoniocib Posts: 277Questions: 62Answers: 1

Hi guys,
I look same disussion of clause Group by in Editor, and my question is : now this clause work?

This question has accepted answers - jump to:

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    No Antonio, it doesn't. You'll need to use a view like the one I posted for you the other day ...

    What you can also do:
    Let's assume you want to select the sum of an amount and group it by creditor or whatever.
    You could also read the creditor, do an mJoin to the table with the amounts and then sum the amounts up client side and display them. This would be a "manual" group by if you will.

    Alternatively ... and that is the easier version in my opinion since you don't want to update the individual amounts: Just read the creditor-table and select an aliased field "amount_sum" or whatever and read the sum yourself with proprietary SQL. Same result, easier to code and no summing up client side.

    Please don't ask me for an example on this :smile: I have none ...
    But you'll find something on mJoin and also on summing up

    Oh, just found one and modified it briefly. So this is the "manual" group by with two tables "creditor" and "obligation". The id of "creditor" is a foreign key in "obligation" as "creditor_id". The sum of the obligation amounts is returned as "debt".

    Editor::inst( $db, 'creditor' )
        ->field(
            Field::inst( 'creditor.id' )->set( false ),
            Field::inst( 'creditor.name' ),
            Field::inst( 'creditor.id AS creditor.debt' )->set( false )
                ->getFormatter( function($val, $data, $opts) use ( $db ) {
                    $stmt = ('SELECT SUM(amount) as debt
                                FROM obligation
                                 AND creditor_id = :id');  
                    $result = $db ->raw()
                                  ->bind(':id', $val)
                                  ->exec($stmt);
                    $row = $result->fetch(PDO::FETCH_ASSOC);
                    if ( (bool)$row ) {
                        return $row["debt"];
                    }
                    return 0;
                }),  
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    Typo: in the SELECT statement replace AND with WHERE.

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    I solved by server side without change the file php of Editor but this query is for a View of the db

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    Thanks for answer!

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited April 2020

    Glad you got it working. This time it's me who doesn't understand your reply :)
    Never mind ... Take care and stay healthy! I am keeping my fingers crossed for Italy in this crisis situation.

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    I was saying I created the VIEW on phpmyadmin with the Group By clause and brought it into the table directly, thank you for your support for Italy!

  • MaxValemboisMaxValembois Posts: 8Questions: 3Answers: 1
    edited November 2020

    Great trick @rf1234
    I used it with succes, but I enconter a big set back, if the end user try to sort the table by the formated field (creditor.debt), he get a sorting by the unformated field (creditor.id) Any idea how to resolve that ?
    I precise I use serveurside processing.
    Max

This discussion has been closed.