allowing AVG and COUNT on selects

javismiles

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

tangerine

See the documentation:
https://editor.datatables.net/manual/php/getting-started#SQL-functions

javismiles

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?

javismiles

trying to do that but anyway its not working

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

allan

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

javismiles

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

allan

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

javismiles

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