Populate Datatable from 2 different sources using jQuery

Question

I need to populate https://datatables.net/ from 2 different api endpoints from jQuery.

First endpoint (Owner Api) retrieves only "Driver name", the second endpoint (VehicleDetail Api) retrieves "Vehicle Name and Number Plate"

The database on the server is as follow, and it's a 1 to many relationship (1 owner may have multiple vehicles)

1. TableOwner :

  Fields
  -----------
  Id 
  Name

2. TableVehicleDetail :

  Fields
  -----------
  Id
  Name
  NumberPlate
  OwnerId       (Foreign Key)

My datatable has 3 columns (Driver Name, VehicleName, Number Plate), and I need to populate it from the two api endpoints.

Driver Name data must come from endpoint api called Owner,

VehicleName, Number Plate must come from endpoint api called VehicleDetail

I read that Datatable has a property called 'ajax source', I tried it but I couldn't achieve.

Regarding jQuery API, I'm using jquery Fetch API to retrieve data from endpoints api.

Below is the code I use. I have two similar code on the same page for both endpoints (Owner and VehicleDetail api).

// ******** Owner Start

        getData();

        async function getData() {
            const response = await fetch('https://localhost:44389/api/owners');
            const settings = {
                method: 'get',
                headers: {
                    'Accept': 'application/json, text/plain, */*',
                    'Content-Type': 'application/json'
                }
            };
            const owner = await response.json();

            var arrayToString = JSON.stringify(Object.assign({}, owner));       // convert array to string
            var stringToJsonObject = JSON.parse(arrayToString);                 // convert string to json object

            return stringToJsonObject;
        }

        getData().then(owner => {
            owner;

            $.each(owner, function (index, value) {
                console.log(value.name);                // Here data can be displayed on console.log
            });

        });

// ******** Owner End

// ******** VehicleDetail Start

    getData();

    async function getData() {
        const response = await fetch('https://localhost:44389/api/vehicleDetail');
        const settings = {
            method: 'get',
            headers: {
                'Accept': 'application/json, text/plain, */*',
                'Content-Type': 'application/json'
            }
        };
        const vehicle = await response.json();

        var arrayToString = JSON.stringify(Object.assign({}, vehicle));       // convert array to string
        var stringToJsonObject = JSON.parse(arrayToString);                 // convert string to json object

        return stringToJsonObject;
    }

    getData().then(vehicle => {
        vehicle;

        $.each(vehicle, function (index, value) {
            console.log(value.name);                // Here data can be displayed on console.log
        });

    });

// ******** VehicleDetail End

You'll need to merge the data before sending it to DataTables, probably. Have you tried that? — isherwood
– isherwood, Commented Jun 4, 2021 at 19:09
you have not provided any detail how you have consumed the vehicle API? — Sachin Vishwakarma
– Sachin Vishwakarma, Commented Jun 4, 2021 at 20:35
it's the same as you see for owner api. Just added the code. — CodingValue
– CodingValue, Commented Jun 4, 2021 at 20:44

andrewJames · Accepted Answer · 2021-06-05 12:41:37Z

1

Assuming your JSON from the two different data sources looks like the following:

[
  {
    "id": "123",
    "driverName": "John"
  },
  {
    "id": "456",
    "driverName": "Mary"
  }
]

and:

[
  {
    "id": "1234",
    "vehicleName": "Ford",
    "ownerId": "123",
    "numberPlate": "ABC 555"
  },
  {
    "id": "2345",
    "vehicleName": "Citroen",
    "ownerId": "123",
    "numberPlate": "DEF 678"
  },
  {
    "id": "3456",
    "vehicleName": "Toyota",
    "ownerId": "456",
    "numberPlate": "GHK 987"
  }
]

Then you can merge them by adding the owner name to each vehicle record - so, something like this:

function fetchUrl(url) { 
  return $.ajax( {
    url: url,
    type: 'GET'
  } );
};

function merge(owners, vehicles) {
  // create a map, for owner ID to owner name lookups:
  let ownerMap = new Map();
  owners.forEach( (owner) => { 
    ownerMap.set(owner.id, owner.driverName);
  } )

  // add owner name to vehicle record:
  vehicles.forEach( (vehicle) => { 
    vehicle['driverName'] = ownerMap.get(vehicle['ownerId']);
  } )

  return vehicles;  
}

This is a very basic approach and does not try to account for cases where there may be an owner with no vehicle, a vehicle with no owner, one vehicle with multiple owners, and so on (so, no data quality checks).

Looking at the end-to-end approach:

Fetching the separate data sets:

async function getTableData() {

  var ownersUrls = '[your URL here]';
  var vehiclesUrl = '[your URL here]';

  try {
    let owners = await fetchUrl(ownersUrls);
    let vehicles = await fetchUrl(vehiclesUrl);
    let dataSet = merge(owners, vehicles);
    initializeTable(dataSet);
  } catch(err) {
    console.log(err);
  }

}

function fetchUrl(url) { 
  return $.ajax( {
    url: url,
    type: 'GET'
  } );
};

To pass the data to DataTables, I would add this at the start of the script:

getTableData();

And finally, the table itself:

function initializeTable(dataSet) {
  $(document).ready(function() {
    $('#example').DataTable( {
      data: dataSet,
      columns: [
        { title: 'Driver Name', data: 'driverName' },
        { title: 'Vehicle Name',  data: 'vehicleName' },
        { title: 'Number Plate', data: 'numberPlate' }
      ]
    } );
  } );
}

Update

Adjusting for the comments in the answer, we can handle both sources containing a name field by renaming the owner name field when we merge owners into vehicle records.

My updated JSON structures, which now use name in both data sets:

[
  {
    "id": "123",
    "name": "John"
  },
  {
    "id": "456",
    "name": "Mary"
  }
]

and:

[
  {
    "id": "1234",
    "name": "Ford",
    "ownerId": "123",
    "numberPlate": "ABC 555"
  },
  {
    "id": "2345",
    "name": "Citroen",
    "ownerId": "123",
    "numberPlate": "DEF 678"
  },
  {
    "id": "3456",
    "name": "Toyota",
    "ownerId": "456",
    "numberPlate": "GHK 987"
  }
]

Here is the full code:

<!doctype html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%"></table>

</div>

<script type="text/javascript">

getTableData();

function initializeTable(dataSet) {
  $(document).ready(function() {
    $('#example').DataTable( {
      data: dataSet,
      columns: [
        { title: 'Driver Name', data: 'driverName' },
        { title: 'Vehicle Name',  data: 'name' },
        { title: 'Number Plate', data: 'numberPlate' }
      ]
    } );
  } );
}

async function getTableData() {

  var ownersUrls = 'your URL goes here';
  var vehiclesUrl = 'your URL goes here';

  try {
    let owners = await fetchUrl(ownersUrls);
    let vehicles = await fetchUrl(vehiclesUrl);
    let dataSet = merge(owners, vehicles);
    initializeTable(dataSet);
  } catch(err) {
    console.log(err);
  }

}

function fetchUrl(url) { 
  return $.ajax( {
    url: url,
    type: 'GET'
  } );
};

function merge(owners, vehicles) {

  // create a map for owner ID to owner name:
  let ownerMap = new Map();
  owners.forEach( (owner) => { 
    ownerMap.set(owner.id, owner.name);
  } )

  // add owner name to vehicle record:
  vehicles.forEach( (vehicle) => { 
    vehicle['driverName'] = ownerMap.get(vehicle['ownerId']);
  } )

  return vehicles;  
}

</script>

</body>
</html>

The name issue is handled by simply creating a field called driverName here:

vehicle['driverName'] = ...

and placing the owner's name into that field.

The DataTable refers to the field name we created:

{ title: 'Driver Name', data: 'driverName' }

edited Jun 5, 2021 at 12:41

answered Jun 4, 2021 at 21:04

andrewJames

22.6k11 gold badges32 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

CodingValue Over a year ago

Thank you for helping me. Indeed I'm getting error saying javascript reference error: merge is not defined at getTableData. I tried to declare let dataSet as global variable but still getting the same error. I'm using jquery-3.5.1.js

CodingValue Over a year ago

I updated to the latest jquery 3.6.0 code.jquery.com/jquery-3.6.0.js but still getting the same error.

CodingValue Over a year ago

I tried to use merge with no data and I also tried with this small code taken here for test purpose but still getting the same error. let obj1 = { foo: 'bar', x: 42 }; let obj2 = { foo: 'baz', y: 13 }; let mergedObj1 = merge(obj1, obj2); console.log(mergedObj1);

CodingValue Over a year ago

I used Object.assign and it has worked. but I still have a small issue. The real api endpoint / database table both has one property called "name", so merge function cannot have same property name into one object. I updated the OP.

andrewJames Over a year ago

I provided an Update section at the end of my answer - and I showed the overall code, so you can see how all the different sections fit together.

|

Collectives™ on Stack Overflow

Populate Datatable from 2 different sources using jQuery

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related