Hello i wrote a SQL for getting information from 2 tables but when i want to return the data i don't know the index to do it at the right moment. what should i do and is there anyway to get Json response from the first instead of each method?
my function:
const searchAntibodies = (
index: number,
amount: number,
information: string,
startDate: string,
endDate: string,
) => {
return new Promise<Antibodies[]>((resolve, reject) => {
let antibodies: Antibodies[] = [];
db.serialize(() => {
db.each(`SELECT id, name as antibodyName FROM Antibodies WHERE
id IN
(SELECT id FROM Antibodies WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedColors WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedReactivities WHERE name LIKE ?)
AND dateOfCreation >= ? AND dateOfCreation <= ?
ORDER BY dateOfCreation DESC LIMIT ? OFFSET ?;`
, [`%${information}%`, `%${information}%`, `%${information}%`, startDate, endDate, amount, index]
, (err, antibody: Antibodies) => {
if (err) {
reject(err.message);
} else {
db.all('SELECT name, locations, colorId FROM AssignedColors WHERE antiId = ?', [antibody.id], (err, colors) => {
if (err) {
reject(err.message);
} else {
antibody.colors = colors;
antibodies.push(antibody);
if (antibodies.length === 10) {
resolve(antibodies)
}
}
});
}
});
});
});
}
my expected result:
[ {
id: 1999,
antibodyName: 'Antibody 1999',
colors: [ [Object], [Object], [Object], [Object], [Object] ] }, {
id: 1995,
antibodyName: 'Antibody 1995',
colors: [ [Object], [Object], [Object], [Object], [Object] ] }, {
id: 1994,
antibodyName: 'Antibody 1994',
colors: [ [Object], [Object], [Object], [Object], [Object] ] }, {
id: 1993,
antibodyName: 'Antibody 1998',
colors: [ [Object], [Object], [Object], [Object], [Object] ] }, {
id: 1997,
antibodyName: 'Antibody 1997', } ]
Ok after searching a lot found the answer. Actually if you don't set types for the first callback function, you can get a second callback function for when it is completed. Here is my result: