allowing AVG and COUNT on selects

allowing AVG and COUNT on selects

javismilesjavismiles Posts: 205Questions: 38Answers: 3

this is a request for feature for next version,
using AVG, COUNT and similar mysql functions is very, very common, it would be a huge help allowing these to be
used in iDatatables without having to resort to using Views,
thank u very much

This question has an accepted answers - jump to answer

Answers

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    thank you Tangerine, that's very nice, but actually I didn't phrase my question correctly, actually I need to do , not only AVG and COUNT but to do so of another table and with extra conditions, so within table A, one of the cells of table A has to have an AVG of a field of a different table B and containing extra WHERE conditions,
    thats not possible right now, correct?

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    trying to do that but anyway its not working

    $editor->fields(
    Field::inst( 'SUM(imp.amount)', 'spend' )
    ->set( false )

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you show me the SQL statement that you would normally use for this?

    It sounds like it isn't something that Editor can currently do, but it might be able to if I can see the statement (so I can fully understand it).

    Allan

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3
    edited August 2018

    it is pretty much this:
    what Im trying to do on the sql side is

    SELECT SUM(a.amount) as amount, a.solid from easyimp a where a.type=1 group by a.solid

    but to include that result on a cell of another table: easysol, which is the one being created by Editor

    so:

        else if ($etype=="easysol" && $fields[$i]['i']=="easysol.spend"){
            $editor->fields(
                Field::inst( 'SUM(easyimp.amount)', 'easysol.spend' ))
    //          ->set( false )
                ->where( function ($q) {
                $q->where( 'easyimp.type', '1', '=' );
            }   );          
        }   
    

    and

    if ($etype=="easysol"){
        $editor->leftJoin( 'easyimp', 'easysol.solutionid', '=', 'easyimp.solid' );
    }
    

    this is really the same issue of
    https://datatables.net/forums/discussion/comment/136844#Comment_136844

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    I'm with you - thanks. Unfortunately the PHP libraries for Editor do not support a GROUP BY command at the moment. This is something that would certainly require a VIEW as such.

    Allan

  • javismilesjavismiles Posts: 205Questions: 38Answers: 3

    I get you Allan, that answers it, yes that´s why Im using views and it all works great with views, I just wish I wouldnt have to use them but well for now it will be views ;)
    definitely great idea for future adding the group by, etc feature to editor,

    another thing im researching is how to subtract two select statements in mysql,
    say one select does SUM(field) and another does SUM(field) and you wanna subtract both within the same query, seems that with oracle is easy but with mysql not so much, anyway

    good thing is that now I switched to client mode and I dont need to do that anymore as I can now sort through the extra custom column that subtracts that values from two other columns without using the backend, this did not work in serverside mode but works great in client mode

This discussion has been closed.