Steve Steve - 3 months ago 8
SQL Question

JavaScript: Filtering dropdown list by value of other dropdown list

I'm in need of some expert assistance. I'm using Electron and have the three files shown below that, first, pulls data from a SQLite database (Data.js), then passes the data into a set of arrays and makes the data accessible in a dropdown list (Model.js and View.js). The issue I have is I need the second dropdown list (that displays province names) to be filtered according to the country selected in the first dropdown. As it currently stands, you get the full list of countries and the full list of provinces regardless of the country selected.

I have tried a few different options including using an onchange event listener (which hasn't worked correctly...most likely p.i.c.n.i.c.) along with trying to pass the element value backwards into the SQL query (which also hasn't worked as the value gets lost while traveling). Can someone suggest what a proper method might be for doing this correctly? Your help is much appreciated in advance!

Data.js:

function getCountries(done) {
var sqlite3 = require('sqlite3').verbose();
var file = 'db/locations.sqlite3';
var db = new sqlite3.Database(file);
var larray = [];

db.all('SELECT * FROM Country', function(err, rows) {
// This code only gets called when the database returns with a response.
rows.forEach(function(row) {
larray.push({
cname: row.CountryName,
ccode: row.CountryCode
});
})
return done(larray);
});
db.close();
}

function getProvinces(done) {
var sqlite3 = require('sqlite3').verbose();
var file = 'db/locations.sqlite3';
var db = new sqlite3.Database(file);
var stmt = 'SELECT Country.CountryId, Country.CountryName, Province.ProvinceName, Province.ProvinceCode FROM Province INNER JOIN Country ON Province.CountryId = Country.CountryId'
var larray = [];

db.all(stmt, function(err, rows) {
// This code only gets called when the database returns with a response.
rows.forEach(function(row) {
larray.push({
cname: row.CountryName,
pname: row.ProvinceName,
pcode: row.ProvinceCode
});
})
return done(larray);
});
db.close();


Model.js:

function Countries(done) {
//Pull location values from data
return getCountries(done);
}

function Provinces(done) {
//Pull location values from data
return getProvinces(done);
}


View.js:

function viewCountries() {

var viewCou = Countries(function(results) {
// Code only gets triggered when Countries() calls return done(...);
var container = document.getElementById('country-select');
var fragment = document.createDocumentFragment();

results.forEach(function(loc, index) {
var opt = document.createElement('option');
opt.textContent = loc.cname;
opt.value = loc.ccode;
fragment.appendChild(opt);
});
container.appendChild(fragment);
})
}

function viewProvinces() {
var viewPro = Provinces(function(results) {
// Code only gets triggered when Provinces() calls return done(...);
var container = document.getElementById('province-select');
var fragment = document.createDocumentFragment();

results.forEach(function(loc, index) {
var opt = document.createElement('option');
opt.textContent = loc.pname;
opt.value = loc.pcode;
fragment.appendChild(opt);
});
container.appendChild(fragment);
});
}


HTML:

<p class="col-lg-2 col-md-4 col-sm-8 codes">COUNTRY</p>
<p id="country-value" class="col-lg-2 col-md-2 col-sm-4 fieldval">No value entered</p>
<select required id="country-select" class="col-lg-3 col-md-3 col-sm-8 formselect">
<option selected> ----Select---- </option>
</select>
<label class="col-lg-4 col-md-3 col-sm-8 descr">Country code of sample</label>
<p class="col-lg-2 col-md-4 col-sm-8 codes">PROVINCE</p>
<p id="province-value" class="col-lg-2 col-md-2 col-sm-4 fieldval">No value entered</p>
<select required id="province-select" class="col-lg-3 col-md-3 col-sm-8 formselect">
<option selected> ----Select---- </option>
</select>
<label class="col-lg-4 col-md-3 col-sm-8 descr">Provence code of sample</label>

Answer

You are on the right way.

  1. First of all, you should continue using onchange event. It will listen when you change a country. When this happen, it means it's time to change the province select. Also, always when you change the country, you should reset the province select to avoid trash from previous usage.

<select id="country-select" onchange="onCountryChanged()"></select>

function onCountryChanged() {
    resetProvincesSelect();
    viewProvinces($("#country-select").val());
}

function resetProvincesSelect() {
    var container = document.getElementById('province-select');
    $(container).empty();
}
  1. You must change your viewProvinces function to accept one parameter, the country code, or ccode. Also, you need to .filter() the results array, getting only the provinces that belongs to that country (ccode).
function viewProvinces(ccode) {
    var viewPro = Provinces(function(results) {
        // Code only gets triggered when Provinces() calls return done(...); 
        var container = document.getElementById('province-select');
        var fragment = document.createDocumentFragment();
        results.filter(function(el) {
            return el.ccode === ccode;
        }).forEach(function(loc, index) {
            var opt = document.createElement('option');
            opt.textContent = loc.pname;
            opt.value = loc.pcode;
            fragment.appendChild(opt);
        });
        container.appendChild(fragment);
    });
}
  1. You need to include ccode to getProvinces()
{
    ccode: row.CountryCode,
    cname: row.CountryName,
    pname: row.ProvinceName,
    pcode: row.ProvinceCode
}
  1. You have not provided where you're calling the main function. So, generically it will be in this way

viewCountries();

Optionally you can preload all provinces along side with the countries. So when you select the country, you will not need to fetch the provinces. It is a specific designing choice. You can also make Provinces or getProvinces receive ccode, abstracting this step from the view.

I hope it helps you.

Comments