I am currently facing a challenge in my Phonegap (Cordova) application where I need to display a list of items, each requiring an additional query.
Let me simplify it with an example scenario. Imagine a student can be enrolled in multiple courses and a course can have several students (many-to-many relationship). My goal is to showcase a list indicating which courses each student is registered for:
- Student1: course1, course2
- Student2: course1, course3, course 5
- Student3: course2
- ...
Initially, I need to execute a query to fetch all the students, followed by querying the database for each student's enrolled courses:
db.transaction(function(tx) {
tx.executeSql('SELECT `student`.`id`, `student`.`name` ' +
'FROM `student`',
[],
function(tx, resultSet) {
for(var i = 0; i < resultSet.rows.length; i++) {
tx.executeSql('SELECT `course`.`name` ' +
'FROM `student_has_course` ' +
'INNER JOIN `student` ON `student_has_course`.`student_id` = `student`.`id` ' +
'INNER JOIN `course` ON `student_has_course`.`course_id` = `course`.`id' +
'`WHERE `student`.`id` = ?', [resultSet.rows.item(i).id],
function(tx2, resultSet2) {
// To be implemented
});
}
});
}, function(err) {
showError('Error retrieving student data from the database (' + err.message + ')');
}, function() {
alert('Success!');
});
The issue arises in the second callback function, where the "TODO" comment is found. There is no access to data from the previous query within this context. For instance, attempting to alert(i) would output 76, equivalent to resultSet.rows.length, due to the asynchronous nature of both callback functions. How can I address this challenge and present the desired list format?
Any assistance on this matter would be greatly valued.