Calculable fields or sql functions

mom

Hello,

I read in another forum thread here that Editor does not support mysql functions as fields. So I am looking for an alternative at least for the simpler cases where we don't need the result of a sql-specific function but it could be achieved with simple arithmetics or a php function.
Let's say I have DB columns a, and b, and I want to return (to the clientside) both fields and their difference as a new field.
(Yes, I know I could do the calculation with javascript on the client side but this example is much more simplified than the real scenario I'm working on)
I need something like this:

$editor = Editor::inst( $db, 'document' )
    ->fields(
        Field::inst( 'a' ),
        Field::inst( 'b' ),
        Field::inst( 'diff' )
            ->getValue( field('a') - field('b') )
        )

But how can I refer to the field values inside the getValue() call? $editor and $GLOBALS['editor'] are not defined in that context.

Another possible way I thought of, was not to create a new field instance but append it to the result - I saw this in the manual:

http://editor.datatables.net/manual/php/getting-started#Return-data
A data() method is also available if you want to access the data before it is sent back to the client-side, useful if you need to send extra data, for example.

but I couldn't find any documentation or example how to use this data() method.

Any help is welcome... otherwise I'll have to query the database directly and construct a proper json array to return, without using the Editor classes.

allan

Hi,

It looks like your approach is basically heading along the correct lines - however, as you say, unfortunately getValue() when used with a closure doesn't actually pass anything other than the database value for that field in.

However, you can use a getFormatter method to do this:

->getFormatter( $val, $data, $opts ) {
  return $data['a'] - $data['b'];
}

Allan

mom

Hmm, yeah, I didn't come to my mind that getFormatter has access to the full row data not just the current field. This will do the job, thanks.

mom

Hi Allan,

While the getFormatter method can be used to return the calculated data, Editor is now throwing error that such field does not exist as a column in the database. Is it not possible to create a field instance over an unexisting table column with the sole purpose to have the formatter return other values?

And while we are at this, is there a way to 'rename' columns, Editor's equivalent to this SQL: SELECT column AS newname...
It will be a read-only instance to feed a datatable, without create/update functionality.

allan

Oh I see - sorry, I've misunderstood slightly.

In this case you actually want to just have the calculation done on the client-side - use columns.render as a function.

As you say the server-side libraries expect there to be a field in the database if given. I'l take a look at how the full row information can be given to getValue() as that could be useful.

Allan

mom

As a temporary workaround, I am currently instatiating the field (serverside) over an existing table field which is not used in the frontend, with the getFormatter method as discussed above. Which however is far from the perfect solution, as it might create a slight confusion, arising from the different field name (especially if another developer needs to deal with my code). And not all times there may be such "unused" fields to be used for such purposes.

I know how to use columns.render on the clientside, but as I noted in the first post, the real scenario (and transformation function) is much more sophisticated than a simple difference of two values - I just didn't want to throw it all here as it is irrelevant to the question.

I suggest you consider adding these options for next versions of Editor's serverside libraries - the ability to use mysql functions as fields, and the ability to use column aliases (which in turn unlocks the ability to have the same DB column appearing as 2+ fields)
I believe there will be many developers who'll love to see them.