You can have count query in select_command and have aliased column mapped to grid column.

$grid->select_command = "select staging.*, (SELECT count(*) FROM studies WHERE studies.rs_number = staging.rs) as c from staging";

$col = array();
$col["title"] = "Count";
$col["name"] = "c";
$col["width"] = "45";
$col["sortable"] = true;
$col["search"] = true;
$col["export"] = true;
$cols[] = $col;

My workaround was to build the count into the sql statement that generated the table, e.g.

$grid->select_command = "SELECT staging.*, COUNT(studies.PMID) AS refcount FROM staging LEFT JOIN studies ON studies.rs_number = staging.rs GROUP BY staging.idstaging";

And I used 'refcount' as an aliased column.

This satisfies the problem I was having but doesn't answer my question. Is it possible to have an alternative sql query mapped to an aliased column? That would be very useful. If not its fine, as the workaround from mysql should accomodate most requirements.

Thank you.

Pasting from docs …

For extended conditional data, you can also have callback function, that will allow you to display based on row data. For e.g.

$col["on_data_display"] = array("display_keyword","");

function display_keyword($data)
{
$kw = $data["keyword_name"];
$numKeywords = count(explode("n",$pass));
if ($numKeywords > 3)
return $numKeywords." Keywords";
else
{
$pass = str_replace("+"," ",$pass);
return $pass;
}
}

Based on demo code above, you can have myssql_query called inside callback function and return result value.
This will be less optimized way as query will get executed each time it renders columns data.

Could you please show how the sql query,

SELECT staging.*, COUNT(studies.PMID) AS refcount FROM staging LEFT JOIN studies ON studies.rs_number = staging.rs GROUP BY staging.idstaging

would be implemented using the callback function method you've described

Thank you

I've not tested it on your code but it would be like:

function display_count($data)
{
// $data contains all columns names

$rs = $data["rs"];
$r = myysql_fetch_assoc(mysql_query("SELECT COUNT(studies.PMID) AS refcount FROM staging LEFT JOIN studies ON studies.rs_number = staging.rs WHERE staging.rs = '".$rs."' GROUP BY staging.idstaging"));
return $r["refcount"];
}