Editor dealing with a jSON field that is a sum of two table Fields

wbyers

Okay, so I'm planning to add the php editor library to my editors instance. The table I want to be able to modify receives an ajax call that contains some JSON fields that are basically sums of two database fields. Can editor handle that?

allan

Where is the calculation to be done? And where do you want to show the result? If you have the two fields in the JSON data and you only want to show it in the table, then use a renderer to do this.

Allan

wbyers

Calculations are done in the SQL

allan

So the summation is shown in the JSON that is loaded by the client? Just have the table display the data point using columns.data like you would with any other data property then.

Allan

wbyers

I'm asking because I'm using the php editor library

wbyers

As in I'm asking because I'm trying to use the editor's field processing to change table, Does editor support SUMMING? That's what I've been trying to ask.

allan

But it isn't really the Editor that is doing the summing here is it? You said you have two columns of data in your table which you want to add together. As I noted, the way to do that in DataTables is to use a renderer. The "computing values" example here shows how that can be done if the data is in the JSON. I'm not yet clear if those two fields are present in the JSON.

If you can show me a sample of your JSON data I can show you a rendering function that will give you the sum of the two numbers.

Allan

wbyers

yes sure,

{
    "draw": 0,
    "recordsTotal": 399,
    "recordsFiltered": 399,
    "data": [{
                "pid": 52431,
                "catID": 202,
                "pStatus": 0,
                "product_refered": "Y",
                "cap": "15",
                "tot": "5",
                "stock": "10",
                "sales": "0",
                "target": "0",
                "held": "5",
                "spaces": "10",
                "program": "Foundation Multi Sports",
                "sku": "MFO52431",
                "priceOveride": "",
                "price": "500",
                "session_type": "",
                "startDate": "2017-12-08",
                "endDate": "2017-12-15",
                "startTime": "16:00",
                "endTime": "17:00",
                "published": "1",
                "organization": "USG Texas",
                "site": "Mabel Davis District Park",
                "city": "Austin",
                "county": "",
                "state": "TX",
                "zip": "78741",
                "ageFrom": "11.0",
                "ageTo": "18.0",
                "prorated": 0,
                "manager": "simon march"
            },

Again the problem is that I'm trying to get a handle on using EDITOR, and from what I understand I have to match the format of the JSON sent to the table right? cap and tot are precalculated in the sql before being set to the JSON

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

I have to match the format of the JSON sent to the table right?

That is correct. The server need to return the same data object that the DataTable is expecting for the row. So if you use cap and tot in the DataTable and edit the row via Editor, you'd need to include those two fields.

How are they being calculated in the SQL? Are you using a VIEW, or an SQL function or something in the script? Are you using any of the Editor libraries (Node, PHP or .NET)?

Thanks,
Allan

wbyers

They are groups of sums added together in the SQL. The JSON is composed from multiple tables but is sent to the actual table is a single data JSON string with no namespaces. Is it possible to replicate that in editor's JSON return if we use aliases?

allan

It sounds like you are loading JSON for the table from a custom JSON data source, while the Editor Ajax submit is going to the Editor libraries. Is that correct?

The best thing to do is to use the same data source for both the DataTable and Editor fetch of data - even if it means to abstract some other part out so you can query the database on edit the same way you do on read.

Allan

wbyers

okay, so you're saying I should probably not use the editor libraries given the custom data source?

allan

Yes. If the Editor libraries can't generate the JSON that you need, then they wouldn't be a perfect match for the editing aspect either since that also needs the same JSON structure.

Are you using a SELECT directly to read from the db?

Allan

wbyers

yes, I'm using a pre-existing bespoke API we already built for the site to populate the table. That then proceeds to have the actual JSON assembled from the SQL results returned

wbyers

so can I use the primary key retrieve the primary key for the object sent using $_REQUEST ? and further what would be the format to get the object's data using $_REQUEST

allan

What I'd probably do in this case, is something like:

$data = Editor::inst( ... )
  ...
  ->process( $_POST )
  ->data();

Then spin over $data['data'] (its an array) and hit your existing API to get the data object for that id (I presume you can query for a specific id?). Replace the data Editor has pulled out of the database with your API's data object and that would do it.

Allan

wbyers

okay, what do I need aside from Process() and data() do I still need to instance fields or do I just get the JSON using our local API and return that once I do the SQL operation?

allan

You would define the Editor:inst()... to allow editing on any of the fields that you want to allow editing for.

Then once it has done its processing, get the data from it (->data()) and spin over that array, getting a list of the row ids that you want to query your own API for. This page describes the client / server data interchange Editor uses, which you will need to know a little about here.

Allan

wbyers

Thanks