使用Knex.js创建嵌套的返回模型 [英] Create a nested return model with Knex.js
问题描述
我正在使用Knex.js在Hapi.js路由中查询MySQL数据库.以下代码有效，但需要嵌套查询:
I'm using Knex.js to query a MySQL database in a Hapi.js route. The following code works but requires a nested query:
{
path: '/recipes',
method: 'GET',
handler: (req, res) => {
const getOperation = Knex.from('recipes')
// .innerJoin('ingredients', 'recipes.guid', 'ingredients.recipe')
.select()
.orderBy('rating', 'desc')
.limit(10)
.then((recipes) => {
if (!recipes || recipes.length === 0) {
res({
error: true,
errMessage: 'no recipes found'
});
}
const recipeGuids = recipes.map(recipe => recipe.guid);
recipes.forEach(r => r.ingredients = []);
const getOperation2 = Knex.from('ingredients')
.whereIn('recipe', recipeGuids)
.select()
.then((ingredients) => {
recipes.forEach(r => {
ingredients.forEach(i => {
if (i.recipe === r.guid) {
r.ingredients.push(i);
}
});
});
res({
count: recipes.length,
data: recipes
});
});
});
}
}
是否可以使用Knex.js创建一个返回模型，该模型具有与父代ID/GUID匹配的嵌套对象，所以我没有嵌套的Promise?
Is there a way to create a return model with Knex.js that has nested objects that match the parent's id/guid so that I don't have nested promises?
推荐答案
简短答案:否.
使用Knex，您可以检索与SQL相同的数据，它是基于记录的，而不是基于对象的，因此，最接近的可能是使用联接只允许执行一次select来检索单个数组的操作.具有元素:食谱，guid，成分.这将重复配方&每种成分的guid，可避免使用嵌套对象. (有关此示例，请参见下面@Fazal的答案.)
With Knex, you can retrieve data the same as with SQL, which is record based, not object based, so the closest that you could come would be to use a join to allow doing just a single select to retrieve a single array having elements: recipes, guids, ingredients. This would repeat the recipe & guid for each ingredient, which you avoid by using nested objects. (See the answer below by @Fazal for an example of this.)
作为另一种选择，您可以将成分存储为配方表中的"blob"字段，但是我不认为MySQL允许您创建Array字段，因此在检索数据时，您必须将字段转换为数组.并在将其更新到表之前将其从Array转换.像:storableData = JSON.stringify(arrayData)和arrayData = JSON.parse(storableData)
As another alternative, you could store the ingredients as a 'blob' field in the recipe table, but I don't believe that MySQL would allow you to create an Array field, so when retrieving the data, you would have to do a transform of the field into the array. And transform it from the Array before updating it into the table. Like: storableData = JSON.stringify(arrayData) and arrayData = JSON.parse(storableData)
尽管如此，我还是建议您做一些其他事情来帮助您改进代码. (是的，我知道，不是这里的问题):
There are a few other things that I would suggest to help you improve the code though. (Yeah, I know, not really the question here):
- 将路由功能与数据处理分开.
- 此外，将数据处理功能与检索分开.
- 使用throw& .catch用于创建和处理失败的响应.
路由，数据检索，数据操作的分离使测试，调试和将来的理解变得更加容易，因为每个功能都具有更基本的目的.
The separation of routing, data retrieval, data manipulation makes testing, debugging, and future comprehension easier as each function has a more atomic purpose.
抛出/.catching不成功的处理条件通过允许您(大部分时间)将单个.catch放入路由器响应处理中，使得进行更全面的错误处理变得更加简单(Hapi.js甚至可以执行此.catch给你的?).
Throwing/.catching unsuccessful process conditions makes it much simpler to have more comprehensive error processing by allowing you to put (most of the time) a single .catch in your router response handling (Hapi.js may even do this .catch for you???).
此外，请参阅我为记录错误添加的其他.catch和.on('query-error'.您可能要使用与控制台不同的日志记录机制.我用温斯顿.请注意，.on('query-error'不是.catch.仍然会抛出一个Error()，并且必须在某个地方进行处理，这将为您提供有关源头附近故障的良好信息.
Also, see the other .catch and .on('query-error' that I added for logging errors. You may have a different logging mechanism you want to use rather than the console. I use Winston. And note that .on('query-error' is NOT a .catch. There will still be an Error() that is thrown, and must be handled somewhere, this will just give you good info about the failure close to the source.
(对不起，下面的代码未经测试)
(Sorry, the below code is untested)
path: '/recipes',
method: 'GET',
handler: (req, res) => {
return getRecipeNIngredients()
.then((recipes) => {
res({
count: recipes.length,
data: recipes
});
})
.catch((ex) => {
res({
error: true,
errMessage: ex.message
});
});
};
function getRecipeNIngredients() {
let recipes = null;
return getRecipes()
.then((recipeList) => {
recipes = recipeList;
const recipeGuids = recipes.map(recipe => recipe.guid);
recipes.forEach(r => r.ingredients = []);
return getIngredients(recipeGuids);
})
.then((ingredients) => {
recipes.forEach(r => {
ingredients.forEach(i => {
if (i.recipe === r.guid) {
r.ingredients.push(i);
}
});
});
return recipes;
})
.catch((ex) => {
console.log(".getRecipeNIngredients ERROR ex:",ex); // log and rethrow error.
throw ex;
});
};
function getRecipes() {
return Knex.from('recipes')
// .innerJoin('ingredients', 'recipes.guid', 'ingredients.recipe')
.select()
.orderBy('rating', 'desc')
.limit(10)
.on('query-error', function(ex, obj) {
console.log("KNEX getRecipes query-error ex:", ex, "obj:", obj);
})
.then((recipes) => {
if (!recipes || recipes.length === 0) {
throw new Error('no recipes found')
}
})
};
function getIngredients(recipeGuids) {
return Knex.from('ingredients')
.whereIn('recipe', recipeGuids)
.select()
.on('query-error', function(ex, obj) {
console.log("KNEX getIngredients query-error ex:", ex, "obj:", obj);
})
};
我希望这是有用的！ 加里.
I hope this is Useful! Gary.
这篇关于使用Knex.js创建嵌套的返回模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
