I am trying to get some information using a UNION query by joining multiple tables. The issue is that there might not be any data in some of the tables that are being joined. So i tried to check using if loops if the rows have data, then assign the data otherwise keep the objects null. This is my code:
const guardProfile = (req, res) => {
const guardProfile = {};
const { id } = req.params; // guard ID
try {
let isAdmin = 0;
if (isAdmin === 0) {
connection.query(
`SELECT 'guard' AS tablename, guard.* FROM guard WHERE guardID=${id}
UNION
SELECT 'guardaddress' AS tablename, guardaddress.*, Null as col6, Null as col7, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM guardaddress WHERE fk_guard=${id}
UNION
SELECT 'document' as tablename, document.* FROM document WHERE fk_guard=${id}
UNION
SELECT 'otherdocs' AS tablename, otherdocs.*, Null as col5, Null as col6, Null as col7, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM otherdocs WHERE fk_guard=${id}
UNION
SELECT 'bank' AS tablename, bank.*, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM bank WHERE guard_id=${id}`,
function (err, rows) {
if (!err) {
const guardToken = jwt.sign(
{
id: rows[0].guardID,
isAdmin: rows[0].isAdmin,
isGuard: rows[0].isGuard,
isCompany: rows[0].isCompany,
},
SECRET,
{
expiresIn: "3d",
}
);
let guard = null, address = null, documents = null, otherdocs = null, bank = null;
rows.forEach(row => {
switch (row.tablename) {
case 'guard':
guard = {
guardID: row.guardID,
firstName: row.firstName,
middleName: row.middleName,
lastName: row.lastName,
email: row.email,
password: row.password,
phone: row.phone,
dob: row.dob,
gender: row.gender,
emergencyContact: row.emergencyContact,
};
break;
case 'guardaddress':
address = {
state: rows[1].firstName,
city: rows[1].middleName,
postalCode: rows[1].lastName,
};
break;
case 'document':
documents = {
four82: rows[2].firstName,
PCR: rows[2].middleName,
CPR: rows[2].lastName,
CrowdControl: rows[2].email,
License: rows[2].password,
Firearms: rows[2].phone,
FirstAid: rows[2].dob,
MediCare: rows[2].gender,
Passport: rows[2].status,
ResponsibleAlcohol: rows[2].isAdmin,
Visa: rows[2].admin_id,
WhiteCard: rows[2].address_id,
YellowCard: rows[2].isGuard,
WorkingWithChildren: rows[2].isCompany,
};
break;
case 'otherdocs':
otherdocs = {
name: rows[3].firstName,
document: rows[3].middleName,
};
break;
case 'bank':
bank = {
bankName: rows[4].firstName,
accountTitle: rows[4].middleName,
accountNo: rows[4].lastName,
bsb: rows[4].email,
abn: rows[4].password,
};
break;
default:
guard = {};
address = {};
documents = {};
otherdocs = {};
bank = {};
break;
}
});
res.status(201).json({
status: true,
message: "Got Guard Profile!",
guardProfile: {
guard,
address,
documents,
otherDocs,
bank,
},
guardToken,
});
} else {
res
.status(404)
.json({ success: false, message: "Guard Not Found!" });
}
}
);
}
} catch (error) {
res.status(500);
throw new Error(error);
}
};
The issue now is, that even after placing the checks using if loops, i am still getting the <h1>Incomplete response received from application</h1> error from the page. If i try to request the data for a guard who has data for all the tables, it works fine, but if i try to request data for a guard that doesn't have all the data, i get the same error even though i put in the checks. I am not able to understand my mistake. Can someone point out the mistake for me ? Thanks in advance.
CodePudding user response：
If any of the tables are missing data, that row will be completely missing from the results, it won't be an empty row. So testing row[2].length will not tell you if the third table had results, because that will be the result from the fourth table.
Add an identifying column to each subquery. Then you can tell which tables had data.
`SELECT 'guard' AS tablename, guard.* FROM guard WHERE guardID=${id}
UNION
SELECT 'guardaddress' AS tablename, guardaddress.*, Null as col6, Null as col7, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM guardaddress WHERE fk_guard=${id}
UNION
SELECT 'document' as tablename, document.* FROM document WHERE fk_guard=${id}
UNION
SELECT 'otherdocs' AS tablename, otherdocs.*, Null as col5, Null as col6, Null as col7, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM otherdocs WHERE fk_guard=${id}
UNION
SELECT 'bank' AS tablename, bank.*, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM bank WHERE guard_id=${id}`,
Then loop through the rows, updating the appropriate variables.
let guard = null,
address = null,
documents = null,
otherdocs = null,
bank = null;
rows.forEach(row => {
switch (row.tablename) {
case 'guard':
guard = {
guardID: row.guardID,
firstName: row.firstName,
middleName: row.middleName,
lastName: row.lastName,
email: row.email,
password: row.password,
phone: row.phone,
dob: row.dob,
gender: row.gender,
emergencyContact: row.emergencyContact,
}
break;
// and similar for each other table name
}
});