server side script - sorting data on formatted data value?

Stoule

Hi everybody,

Is it possible to sort datas on the formatted data value?

I explain myself, I have a server side script where I format some datas, example :

array(
'db' => 'actionId', 'dt' => 13,
'formatter' => function ($data) {
return nbFormationsLinked($data);
}
),

function nbFormationsLinked($data)
{
global $bdd;
$requeteTotalFormationsLinked = "SELECT COUNT(formationId) as Total
FROM formation
WHERE formation.formationIdActivite = :actionId";
$sqlTotalFormationsLinked = $bdd->prepare($requeteTotalFormationsLinked);
$sqlTotalFormationsLinked->execute(array('actionId' => $data));
return $sqlTotalFormationsLinked->fetchObject()->Total;
}

so the function return an int but when I sort the table on the field 13, it is on the actionId number not on the return of the function, so is it possible to do that? perhaps on client side?

Thank you for your help.

allan

No. Not with that script. The ordering is done by the SQL database, but the data formatting is being done by the PHP once the data has already been pulled out of the database.

Also, doing so with the above would be exceptionally inefficient. Consider that server-side processing is only really useful with tens of thousands or more rows - in order to do the order correctly, you'd need to run that second count query for every row - ouch .

What I would suggest in such as case is that you create a VIEW with the subquery to get the count, and then run the server-side processing script on the view.

Allan

Stoule

Thank you Allan for your quick reply.

My datas are not so big, max 700 lines but don't know why if I do not use server side, it takes so much time to display them, perhaps the config of my script with lots of functions to render datas.

I will try with a VIEW. Thanks

allan

That count aggregation will slow things down a lot. Try it without the subquery and I reckon it will be really fast. How to optimise the query might be one for Stackoverflow though.

Allan