我想不使用两个查询来执行此搜索：
app.db('users').where({
id: req.userData.id
}).first().then(result => {
if (!result) {
throw new CompleteError('Usuário não encontrado.', 404)
}
app.db('addresses').where({
id: result.address_id
}).first().then(address_result => {
if (address_result) {
result.address = address_result
res.json(result)
} else {
throw new CompleteError('Falha ao buscar endereço do usuário.', 500)
}
}).catch(e => {
return next(e)
})
}).catch(e => {
return next(e)
})
如果其中一项搜索失败或找不到任何寄存器，则搜索进度将停止并抛出错误。该数据库是postgresql。如果没有这种方法的替代方法，请帮助改善代码，我是入门开发人员，我需要您的意见。拜托，对不起我的英语。
结果必须是这样的：
{
"id": 21,
"name": "Sérgio",
"cpf": "11111111111",
"email": "[email protected]",
"pass": "$2a$10$Jx7wAd3QSMFlkXi3g0eVOOoW8o65OgDZVQ7wTWuxquytVhUhrUf/.",
"phone": "5544313113131",
"last_access": "2020-08-11T10:14:20.207Z",
"created_at": "2020-08-11T10:14:20.207Z",
"deleted_at": null,
"address_id": 21,
"address": {
"id": 21,
"street": "Street Manu",
"number": "851",
"complement": "A",
"city": "Mari Hill",
"state": "Para",
"country": "Brazil",
"last_access": "2020-08-11T10:14:19.837Z",
"created_at": "2020-08-11T10:14:19.837Z",
"deleted_at": null
}
}
您可以使用JOIN执行此查询，然后重新排列结果。
您要查找的查询如下所示：
SELECT *
FROM users
LEFT JOIN addresses ON addresses.id=users.address_id;
（我对MySQL / MariaDB较为熟悉，因此语法可能有所不同）。
这LEFT JOIN意味着，如果找到用户但未找到地址，则地址列将为null。
可以将其转换为以下Knex表达式：
app.db('users')
.leftJoin('addresses', 'addresses.id', '=', 'users.address_id')
.select(
'users.id',
'users.name',
'users.cpf',
'users.email',
'users.pass',
'users.phone',
'users.last_access',
'users.created_at',
'users.deleted_at',
'users.address_id',
'addresses.id AS addr_id',
'addresses.street AS addr_street',
'addresses.number AS addr_number',
'addresses.complement AS addr_complement',
'addresses.city AS addr_city',
'addresses.state AS addr_state',
'addresses.country AS addr_country',
'addresses.last_access AS addr_last_access',
'addresses.created_at AS addr_created_at',
'addresses.deleted_at AS addr_deleted_at'
)
.first()
.then((result) => {
// no result at all? no user found
if (!result) {
throw new CompleteError('Usuário não encontrado.', 404);
}
// no addr_id? user found but not the address
if (!result.addr_id) {
throw new CompleteError('Falha ao buscar endereço do usuário.', 500);
}
// result contains all the fields from BOTH tables as a flat object
// you'll need to rearrange things
return {
// assign properties to new names...
id: result.id,
// and so on...
};
});
注意，我已经重命名addresses了select()呼叫中的所有列。这是因为Knex不会在带有JOIN子句的查询中分配新的列名，除非您明确命名列，否则您将获得重复的列名（以及很多混乱）。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句