Sorting custom result with server side

WoPr

Hi all!

I need to to the following:

1- I have a nice amount of rows (>10000) on a table, so I need to get the data via server side.
2- I need to get 6 fields, but none of them are alone in the database. For example, I have a field with "IdUsuario" numeric, but when I want to show it on datatable, I want to show the real username, so I create a method that receive a numeric id and returns the username as string . The same thing happens when i want to get the status of a particular row, because its a combination of several rows on the database, so I create another method that receives 2 or more int data and returns an string.

To archieve this, I do the following on the server side script:

1- Get all columns that I need on an array:
[code]$aColumns = array('idAnuncio', 'idUsuario', 'referencia', 'validado', 'pendientepago', 'bloqueado', 'rechazado', 'enddate', 'categoria', 'tipo', 'pendiente');[/code]

2- Make the json output:
[code]
while ($aRow = mysql_fetch_array($rResult)) {

//Vars
$estado = Estados::getValue($aRow['validado'], $aRow['pendientepago'], $aRow['bloqueado'], $aRow['rechazado']);
$row = array();

//User
$row[] = Usuario::getUsername($aRow['idUsuario']);

//Ref
$row[] = $aRow['referencia'];

//Status
$row[] = Estados::getString($aRow['validado'], $aRow['pendientepago'], $aRow['bloqueado'], $aRow['rechazado']);

//Enddate
$row[] = date("d/m/Y", $aRow['enddate']);

//SP
$row[] = "Añadir tiempo";

//Pendiente
$row[] = AccionesPendientes::getString($aRow['pendiente'], $aRow['pendientepago']);

//Acciones
$acciones = "";
if ($estado == 1) {
$acciones .= AccionesAdmin::validar($id) . " ";
$acciones .= AccionesAdmin::rechazar($id) . " ";
}
if (($estado == 2) || ($aRow['pendiente'] == 2)) {
$acciones .= AccionesAdmin::validar($id) . " ";
}
if (($estado == 2) && ($aRow['pendiente'] == 2)) {
$acciones .= AccionesAdmin::rechazar($id) . " ";
}
if ($estado == 4) {
$acciones .= AccionesAdmin::bloquear($id) . " ";
}
if ($estado == 5) {
$acciones .= AccionesAdmin::desbloquear($id) . " ";
}

$acciones .= AccionesAdmin::borrar($id) . " ";
$acciones .= AccionesAdmin::previsualizar($id);

$row[] = $acciones;

$output['aaData'][] = $row;
}
[/code]

This works well when I show the data, but I can't get to work the sorting and filtering of datatables. I click on the header and nothing happens, so I want to ask if it's possible to sort a custom generated column that it's not written as is in the database.

The sorting code on server script is:

[code] /*
* Ordering
*/
$sOrder = "";
if (isset($_GET['iSortCol_0'])) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$sOrder .= "`" . $aColumns[intval($_GET['iSortCol_' . $i])] . "` " .
mysql_real_escape_string($_GET['sSortDir_' . $i]) . ", ";
}
}

$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY") {
$sOrder = "";
}
} [/code]

Thanks in advance,
WoPr