Sorting a Virtual Field – is it possible

QuestionsSorting a Virtual Field – is it possible
Paul Hagerty asked 2 years ago

Hello

I have a virtual field, which is a calc or a few different things, which displays fine, but i can not click and sort by it without getting an error

So my field value is made like so

function filter_display($data)
{

foreach($data["params"] as &$d)
{
foreach($d as $k=>$v)
$soldinlast3months = $d[Last3MonthSalesQty];
$soldpermonth = $soldinlast3months / 3;
$howmanymonthsstockhaveigot = $soldpermonth * 12;
$d[MonthsStock] = $howmanymonthsstockhaveigot;
}

}

And i show it on the table like so

$col = array();
$col["title"] = "M Stock";
$col["name"] = "MonthsStock";
$col["hidecol"] = true;
$col["width"] = "12";
$col["editable"] = false;
$col["hidden"] = false;
$col["export"] = true;
$cols[] = $col;

which works, but if i click it, i got the following

Couldn't execute query. Unknown column 'MonthsStock' in 'order clause' – SELECT * from DF_MasterTable3 where Branch='Barnsley' ORDER BY `MonthsStock` ASC LIMIT 20 OFFSET 0

does anyone know how to fix this please 🙂

1 Answers
Abu Ghufran answered 2 years ago

You can try setting:

$col["sortname"] = "((Last3MonthSalesQty/3)*12)";

This will then be replaced in ORDER BY (field-name)
AS sorting is database side work, we must have sql equivalent of it.

You can also have client side sort by it will then sort on available paged dataset and not complete. (except that you fetch all data on client side)

I'm also emailing you latest build that support $col[sortname] function.

Your Answer

12 + 14 =

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!