populate table column with different data source

Khalid Teli

Hi,

I am returning an extra variable from the serverside using rawsql query using:

 $lastyear = "SELECT * FROM agreement  WHERE YEAR(start_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))";

     $data = $db->sql( $lastyear)->fetchAll();
      $json['lastyear_data'] = $db->sql( $lastyear )->fetchAll();

on the clientside I am receiving the data like this:

 ajax: {
url: "terms_fetch.php",
    "dataSrc": function ( json, data  ) {

      var lastyear = json.lastyear_data;
      var thisyear = json.data;

     return json.data
     return json.lastyear_data
    }
  },

I am trying to populatethe last columns of the table with the results from rawsql query json.lastyear_data

something like this:

columns [
{data: "m1"} ,
{data: "m2"} ,
{data: "m3"} ,
{lastyear_data: "m1"},
]

The aim here is to display the finical status from current year (month1_month2....) and the retrieve the same data from last year and compare two columns.

Kind Regards,
KT

colin

The first issue is that you have two returns in your dataSrc function - so line 9 won't be reached.

The second issue is that the lastyear_data in columns won't work, as it's not a supported field.

Could you post some of the data here so we can see what the server is returning, please.

Colin

Khalid Teli

@colin Thank you

This is how the returned data from the server looks like:
it returns two objects data and lastyear_data

{
    "data": [{
        "DT_RowId": "row_568",
        "agreement_id_fk": "123",
        "start_date": "2021-01-23",
        "m1": "0.00",
        "m2": "0.00"

    }, {
        "DT_RowId": "row_569",
        "agreement_id_fk": "123",
        "start_date": "2021-01-23",
        "m1": "0.00",
        "m2": "0.00"

    }, {
        "DT_RowId": "row_570",
        "agreement_id_fk": "123",
        "start_date": "2021-01-23",
        "m1": "0.00",
        "m2": "0.00"

    }],
    "options": [],
    "files": [],
    "searchPanes": {
        "options": []
    },
    "lastyear_data": [{
    "agreement_id_fk": "123"
        "agreement_return_id": "538",
        "start_date": "2020-01-23",
        "m1": "232.00",
        "m2": "44.00"

    }, {
        "agreement_return_id": "539",
    "agreement_id_fk": "123"
        "start_date": "2020-01-23",
        "m1": "0.00",
        "m2": "0.00"

    }]
}

Thank you

kthorngren

There is nothing built in to do what you want. You will need to package the data and the lastyear_data together to build the row data. Some options:

1. In ajax.dataSrc copy the lastyear_data object into a global variable that you can access in columns.render to for the column data.
2. Use one SQL statement with a join to combine the data and the lastyear_data so that each row/record has both sets of data.
3. In your server script loop through the data to add the appropriate lastyear_data.
4. In ajax.dataSrc loop through the data to add the appropriate lastyear_data.

Kevin

colin

Can you give an example of how a record should look, please? There are two m1 fields, but only one m1 column - so how would you want them to look?

Colin

Khalid Teli

@colin I am sorry If I was not clear, I will try to explain as much as I can.

if you see the image attached, I am populating the table like a normal using
data object like this https://editor.datatables.net/examples/simple/server-side-processing.html

Now the last column in the table (Total L/Y-Last Year), I want to populate this particular column with the data from same sql table data but with the data from last year (2020).

Total T/Y(this year 2021) = sum of months (m1+m2+m3+m4+m5...m12)

The last column should be like this:
Total L/Y(last year 2020) = sum of months (m1+m2+m3+m4+m5...m12)

For that case , I am querying the same sql table to get data sales (m1+m1...m12) from last year from serverside whichg returns two object data in JSON response.
The sql query loos like this:

$lastyear = "SELECT * FROM agreement_return  WHERE YEAR(start_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))";


    $data = $db->sql( $lastyear)->fetchAll();
     $json['lastyear_data'] = $db->sql( $lastyear )->fetchAll();

And the JSON response contains two objects one is data and other is lastyear_data

What I am trying to achieve is to use the lastyear_data object and populate the Total L/Y column with that data.

The JSON response looks like this ( data for just one row as I have removed some data to make it smaller):
{
"data": [{

        "DT_RowId": "row_564",
        "agreement_id_fk": "1234",
        "member": "Foodservice",
        "supplier": "",
        "start_date": "2021-01-02",
        "end_date": "2021-03-04",
        "m1": "8.00",
        "m2": "0.00",
        "m3": "0.00",
        "m4": "0.00",
        "m5": "0.00",
        "m6": "0.00",
        "m7": "0.00",
        "m8": "0.00",
        "m9": "0.00",
        "m10": "0.00",
        "m11": "0.00",
        "m12": "0.00",
        "adv": "6",
        "other_income": "0",
        "brand_support": "0",
        "data": "0",
        "q1_total": "8.00",
        "q4_total": "0.00",
        "status": "0",
        "percentage": "0"
    }
    ...
    ...
    ...

    ],
    "options": [],
    "files": [],
    "searchPanes": {
        "options": []
    },
    "draw": 1,
    "recordsTotal": "26",
    "recordsFiltered": "26",
    "lastyear_data": [{
        "agreement_return_id": "583",       
        "member": "Foodservice",
        "supplier": "",
        "start_date": "2020-01-02",
        "end_date": "2020-03-04",
        "m1": "32.00",
        "m2": "0.00",
        "m3": "0.00",
        "m4": "0.00",
        "m5": "0.00",
        "m6": "0.00",
        "m7": "0.00",
        "m8": "0.00",
        "m9": "15.00",
        "m10": "0.00",
        "m11": "0.00",
        "m12": "0.00",
        "adv": "6",
        "other_income": "0",
        "brand_support": "0",
        "data": "0",
        "q1_total": "8.00",
        "q4_total": "0.00",
        "status": "0",
        "percentage": "0"
        "agreement_id_fk": "1234"
    }

    ....
    ....
    ....]
}

Also, what @kthorngren suggested, I used to implement option:!

In ajax.dataSrc copy the lastyear_data object into a global variable that you can access in columns.render to for the column data.

           {data: null ,
                 "render": function (data, type, row ) {

        var i ;
                  var ly = lastyear_data;
                  var lastyear;
        for (i = 0; i < ly.length; i++) {

            var ly = Object.values(ly);

             var lastyear = ly[i].m1;
          console.log(lastyear);
    return lastyear;

        }


        }

        },

not sure if the approach is right as it is now working, i can get the required values console.log(lastyear); but not working as the whole column is populated with the just first value(i=0)

Thank you

Khalid Teli

Also figured out that it can be done using SQL sum query, just wondering if there is an easy way around.

just an example :

select agreement_return_id    
     , member_name     
     , start_Date   
     , sum(
         case when YEAR(start_date) = 2020
              then m1 
              else 0 end
          ) as salesLY
     , sum(
         case when YEAR(start_date) = 2021
              then m1 
              else 0 end
          ) as salesTY

  from agreement_return
group by member_name

kthorngren

Your lastyear_data is an array of objects. Likely you won't want to loop through the array for each row in columns.render. A more efficient option is to loop through the array once in ajax.dataSrc and convert it to object structured data with the key being field that ties this to the data. For example, assuming agreement_return_id is the field:

{
  { "583":
        "agreement_return_id": "583",      
        "member": "Foodservice",
        "supplier": "",
        "start_date": "2020-01-02",
        "end_date": "2020-03-04",
        "m1": "32.00",
        "m2": "0.00",
        "m3": "0.00",
        "m4": "0.00",
        "m5": "0.00",
        "m6": "0.00",
        "m7": "0.00",
        "m8": "0.00",
        "m9": "15.00",
        "m10": "0.00",
        "m11": "0.00",
        "m12": "0.00",
        "adv": "6",
        "other_income": "0",
        "brand_support": "0",
        "data": "0",
        "q1_total": "8.00",
        "q4_total": "0.00",
        "status": "0",
        "percentage": "0"
        "agreement_id_fk": "1234"
    },
....
}

This way you can just access the correct lastyear_data directly by using something like lastyear_data[ row.agreement_id_fk ]. I'm making assumption sbout your data and what you are trying to do with it. The point of this is to use ajax.dataSrc loop the data once instead of looping it for each row in columns.render.

However its probably more efficient to use a SQL query to fetch the data.

Kevin

Khalid Teli

@kthorngren
Thank you.
I believe it is efficient to use sql query to fetch the data and most probably I will use it.

However, I have implemented the logic like this inside the dataSr function but cant figure the way to use this returned data lastyear inside column :

"dataSrc": function ( json, data  ) {

var convertArrayToObject = (array, key) => {
var initialValue = {};
  return array.reduce((obj, item) => {
    return {
      ...obj,
      [item[key]]: item,
    };
  }, initialValue);
};
      lastyear_data = json.lastyear_data;

    for (var i = 0; i < lastyear_data.length; i++) {

       lastyear =  lastyear_data[i];

  lastyear = convertArrayToObject([lastyear],'agreement_id_fk',)
  console.log(lastyear[12345].m1);

    }
     return json.data
    }

I can access the data using console.log(lastyear); which gives me required data

Thank you in advance

kthorngren

Is there something in the row data that is a key that points to the data you want from lastyear?

Kevin

Khalid Teli

@kthorngren
If I got it correctly, in the row.data, the supplier is the key that points to the data in last year.

This means, from the lastyear I would like to get the data for (m1,m2,m3...) where the supplier in lastyear is the same as key, supplier in row data.

Thank you