Node.js用mysql编写api代码(sequelize) [英] Node.js writing api code with mysql(sequelize)

查看：115
本文介绍了Node.js用mysql编写api代码(sequelize)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的mysql表模型:

//mysql
//table structures start

var Contact = sequelize.define('contact', {
    gsm: {
        type: Sequelize.STRING,
        unique: true,
        required: true
    },
    firstName: Sequelize.STRING,
    lastName: Sequelize.STRING,
    street: Sequelize.STRING,
    city: Sequelize.STRING,
})

var Group = sequelize.define('group', {
    groupname: Sequelize.STRING

})

var ContactGroup = sequelize.define('contactgroup', {
    /* Empty */
})

ContactGroup.belongsTo(Contact, {
    onDelete: 'CASCADE'
});

Contact.hasMany(ContactGroup, {
    onDelete: 'CASCADE'
});

ContactGroup.belongsTo(Group, {
    onDelete: 'CASCADE'
});
Group.hasMany(ContactGroup, {
    onDelete: 'CASCADE'
});

这是我的第一个 API代码:

exports.getNewGroup = (req, res) => {
    Group.findAll({
        attributes: ['id', 'groupname', [sequelize.fn('COUNT', sequelize.col('contactgroups.groupId')), 'contactsCount']],
        include: [{
            model: ContactGroup,
            attributes: ['id']
        }],
        group: ['id']
    }).then(data => {
        return res.status(200).send(data);
    }).catch(err => {
        return res.status(400).send(err.message);
    });
};

我得到了这样的第一个api响应:

[
    {
        "id": 14,
        "groupname": "Angular",
        "contactsCount": 2,
        "contactgroups": [
            {
                "id": 1
            }
        ]
    },
    {
        "id": 15,
        "groupname": "React",
        "contactsCount": 1,
        "contactgroups": [
            {
                "id": 3
            }
        ]
    },
    {
        "id": 16,
        "groupname": "Vue",
        "contactsCount": 0,
        "contactgroups": []
    },
    {
        "id": 17,
        "groupname": "NodeJs",
        "contactsCount": 0,
        "contactgroups": []
    },
    {
        "id": 18,
        "groupname": "RxJS",
        "contactsCount": 0,
        "contactgroups": []
    }
]

这是我的第二 API代码:

exports.getNewGroupForProfsms = (req, res) => {
    Group.findAll({
        include: [{
            model: ContactGroup,
            attributes: ['id'],
            include: [{
                model: Contact,
                attributes: ['id', 'gsm']
            }]
        }],
    }).then(data => {
        return res.status(200).send(data);
    }).catch(err => {
        return res.status(400).send(err.message);
    });
};

这是我的第二个api响应:

[
    {
        "id": 14,
        "groupname": "Angular",
        "createdAt": "2017-12-15T15:06:39.000Z",
        "updatedAt": "2017-12-15T15:06:39.000Z",
        "contactgroups": [
            {
                "id": 1,
                "contact": {
                    "id": 20,
                    "gsm": "987654321"
                }
            },
            {
                "id": 2,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 15,
        "groupname": "React",
        "createdAt": "2017-12-15T15:06:45.000Z",
        "updatedAt": "2017-12-15T15:06:45.000Z",
        "contactgroups": [
            {
                "id": 3,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 16,
        "groupname": "Vue",
        "createdAt": "2017-12-15T15:06:51.000Z",
        "updatedAt": "2017-12-15T15:06:51.000Z",
        "contactgroups": []
    },
    {
        "id": 17,
        "groupname": "NodeJs",
        "createdAt": "2017-12-17T16:07:38.000Z",
        "updatedAt": "2017-12-17T16:07:38.000Z",
        "contactgroups": []
    },
    {
        "id": 18,
        "groupname": "RxJS",
        "createdAt": "2017-12-21T05:50:50.000Z",
        "updatedAt": "2017-12-21T05:50:50.000Z",
        "contactgroups": []
    }
]

在上面的代码中，我写了两个api，现在我想写一个api，

我需要这种类型的响应:(我在第一个api响应中有 contactsCount ，我希望在第二个api响应中有)

最后，我希望使用单个api代码进行此类响应:

[
    {
        "id": 14,
        "groupname": "Angular",
        "contactsCount": 2,
        "createdAt": "2017-12-15T15:06:39.000Z",
        "updatedAt": "2017-12-15T15:06:39.000Z",
        "contactgroups": [
            {
                "id": 1,
                "contact": {
                    "id": 20,
                    "gsm": "987654321"
                }
            },
            {
                "id": 2,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 15,
        "groupname": "React",
        "contactsCount": 1,
        "createdAt": "2017-12-15T15:06:45.000Z",
        "updatedAt": "2017-12-15T15:06:45.000Z",
        "contactgroups": [
            {
                "id": 3,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 16,
        "groupname": "Vue",
        "contactsCount": 0,
        "createdAt": "2017-12-15T15:06:51.000Z",
        "updatedAt": "2017-12-15T15:06:51.000Z",
        "contactgroups": []
    },
    {
        "id": 17,
        "groupname": "NodeJs",
        "contactsCount": 0,
        "createdAt": "2017-12-17T16:07:38.000Z",
        "updatedAt": "2017-12-17T16:07:38.000Z",
        "contactgroups": []
    },
    {
        "id": 18,
        "groupname": "RxJS",
        "contactsCount": 0,
        "createdAt": "2017-12-21T05:50:50.000Z",
        "updatedAt": "2017-12-21T05:50:50.000Z",
        "contactgroups": []
    }
]

有人会帮助我吗?我需要单个api代码.

我尝试了嵌套查询，并修改了我的第一个api代码，如下所示:

exports.getNewGroup = (req, res) => {
    Group.findAll({
        attributes: ['id', 'groupname', [sequelize.fn('COUNT', sequelize.col('contactgroups.groupId')), 'contactsCount']],
        include: [{
            model: ContactGroup,
            attributes: ['id'],
            include: [{
                model: Contact,
                attributes: ['id', 'gsm']
            }]
        }],
        group: ['id']
    }).then(data => {
        return res.status(200).send(data);
    }).catch(err => {
        return res.status(400).send(err.message);
    });
};

但是我得到的不是我的要求

[
    {
        "id": 14,
        "groupname": "Angular",
        "contactsCount": 2,
        "contactgroups": [
            {
                "id": 1,
                "contact": {
                    "id": 20,
                    "gsm": "987654321"
                }
            }
        ]
    },
    {
        "id": 15,
        "groupname": "React",
        "contactsCount": 1,
        "contactgroups": [
            {
                "id": 3,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 16,
        "groupname": "Vue",
        "contactsCount": 0,
        "contactgroups": []
    },
    {
        "id": 17,
        "groupname": "NodeJs",
        "contactsCount": 0,
        "contactgroups": []
    },
    {
        "id": 18,
        "groupname": "RxJS",
        "contactsCount": 0,
        "contactgroups": []
    }
]

解决方案

尝试此代码

 exports.getNewGroupForProfsms = (req, res) => {
  Group.findAll({
    include: [{
      model: ContactGroup,
      attributes: ['id'],
      include: [{
        model: Contact,
        attributes: ['id', 'gsm']
      }]
    }],
  }).then(data => {
    // change happen here
    return res.status(200).send(data.map((x) => {
      // assign contactsCount to each row
      return Object.assign({
        contactsCount: x.contactgroups.length,
      }, x.toJSON()) // update toJSON have a try
    }));
  }).catch(err => {
    return res.status(400).send(err.message);
  });
};

My mysql table models :

//mysql
//table structures start

var Contact = sequelize.define('contact', {
    gsm: {
        type: Sequelize.STRING,
        unique: true,
        required: true
    },
    firstName: Sequelize.STRING,
    lastName: Sequelize.STRING,
    street: Sequelize.STRING,
    city: Sequelize.STRING,
})

var Group = sequelize.define('group', {
    groupname: Sequelize.STRING

})

var ContactGroup = sequelize.define('contactgroup', {
    /* Empty */
})

ContactGroup.belongsTo(Contact, {
    onDelete: 'CASCADE'
});

Contact.hasMany(ContactGroup, {
    onDelete: 'CASCADE'
});

ContactGroup.belongsTo(Group, {
    onDelete: 'CASCADE'
});
Group.hasMany(ContactGroup, {
    onDelete: 'CASCADE'
});

This is my first api code:

exports.getNewGroup = (req, res) => {
    Group.findAll({
        attributes: ['id', 'groupname', [sequelize.fn('COUNT', sequelize.col('contactgroups.groupId')), 'contactsCount']],
        include: [{
            model: ContactGroup,
            attributes: ['id']
        }],
        group: ['id']
    }).then(data => {
        return res.status(200).send(data);
    }).catch(err => {
        return res.status(400).send(err.message);
    });
};

I got the first api response like this:

[
    {
        "id": 14,
        "groupname": "Angular",
        "contactsCount": 2,
        "contactgroups": [
            {
                "id": 1
            }
        ]
    },
    {
        "id": 15,
        "groupname": "React",
        "contactsCount": 1,
        "contactgroups": [
            {
                "id": 3
            }
        ]
    },
    {
        "id": 16,
        "groupname": "Vue",
        "contactsCount": 0,
        "contactgroups": []
    },
    {
        "id": 17,
        "groupname": "NodeJs",
        "contactsCount": 0,
        "contactgroups": []
    },
    {
        "id": 18,
        "groupname": "RxJS",
        "contactsCount": 0,
        "contactgroups": []
    }
]

This is my second api code:

exports.getNewGroupForProfsms = (req, res) => {
    Group.findAll({
        include: [{
            model: ContactGroup,
            attributes: ['id'],
            include: [{
                model: Contact,
                attributes: ['id', 'gsm']
            }]
        }],
    }).then(data => {
        return res.status(200).send(data);
    }).catch(err => {
        return res.status(400).send(err.message);
    });
};

This is my second api response:

[
    {
        "id": 14,
        "groupname": "Angular",
        "createdAt": "2017-12-15T15:06:39.000Z",
        "updatedAt": "2017-12-15T15:06:39.000Z",
        "contactgroups": [
            {
                "id": 1,
                "contact": {
                    "id": 20,
                    "gsm": "987654321"
                }
            },
            {
                "id": 2,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 15,
        "groupname": "React",
        "createdAt": "2017-12-15T15:06:45.000Z",
        "updatedAt": "2017-12-15T15:06:45.000Z",
        "contactgroups": [
            {
                "id": 3,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 16,
        "groupname": "Vue",
        "createdAt": "2017-12-15T15:06:51.000Z",
        "updatedAt": "2017-12-15T15:06:51.000Z",
        "contactgroups": []
    },
    {
        "id": 17,
        "groupname": "NodeJs",
        "createdAt": "2017-12-17T16:07:38.000Z",
        "updatedAt": "2017-12-17T16:07:38.000Z",
        "contactgroups": []
    },
    {
        "id": 18,
        "groupname": "RxJS",
        "createdAt": "2017-12-21T05:50:50.000Z",
        "updatedAt": "2017-12-21T05:50:50.000Z",
        "contactgroups": []
    }
]

In above code, i wrote two apis, now i want to write single api,

I need this type of response: (i have contactsCount in first api response, i want that in second api response)

Finally I want this type of response with single api code:

[
    {
        "id": 14,
        "groupname": "Angular",
        "contactsCount": 2,
        "createdAt": "2017-12-15T15:06:39.000Z",
        "updatedAt": "2017-12-15T15:06:39.000Z",
        "contactgroups": [
            {
                "id": 1,
                "contact": {
                    "id": 20,
                    "gsm": "987654321"
                }
            },
            {
                "id": 2,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 15,
        "groupname": "React",
        "contactsCount": 1,
        "createdAt": "2017-12-15T15:06:45.000Z",
        "updatedAt": "2017-12-15T15:06:45.000Z",
        "contactgroups": [
            {
                "id": 3,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 16,
        "groupname": "Vue",
        "contactsCount": 0,
        "createdAt": "2017-12-15T15:06:51.000Z",
        "updatedAt": "2017-12-15T15:06:51.000Z",
        "contactgroups": []
    },
    {
        "id": 17,
        "groupname": "NodeJs",
        "contactsCount": 0,
        "createdAt": "2017-12-17T16:07:38.000Z",
        "updatedAt": "2017-12-17T16:07:38.000Z",
        "contactgroups": []
    },
    {
        "id": 18,
        "groupname": "RxJS",
        "contactsCount": 0,
        "createdAt": "2017-12-21T05:50:50.000Z",
        "updatedAt": "2017-12-21T05:50:50.000Z",
        "contactgroups": []
    }
]

will anyone help me? i need single api code.

I tried nested query, and modified my first api code like this:

exports.getNewGroup = (req, res) => {
    Group.findAll({
        attributes: ['id', 'groupname', [sequelize.fn('COUNT', sequelize.col('contactgroups.groupId')), 'contactsCount']],
        include: [{
            model: ContactGroup,
            attributes: ['id'],
            include: [{
                model: Contact,
                attributes: ['id', 'gsm']
            }]
        }],
        group: ['id']
    }).then(data => {
        return res.status(200).send(data);
    }).catch(err => {
        return res.status(400).send(err.message);
    });
};

But i got this type of response not my requirement

[
    {
        "id": 14,
        "groupname": "Angular",
        "contactsCount": 2,
        "contactgroups": [
            {
                "id": 1,
                "contact": {
                    "id": 20,
                    "gsm": "987654321"
                }
            }
        ]
    },
    {
        "id": 15,
        "groupname": "React",
        "contactsCount": 1,
        "contactgroups": [
            {
                "id": 3,
                "contact": {
                    "id": 21,
                    "gsm": "123456789"
                }
            }
        ]
    },
    {
        "id": 16,
        "groupname": "Vue",
        "contactsCount": 0,
        "contactgroups": []
    },
    {
        "id": 17,
        "groupname": "NodeJs",
        "contactsCount": 0,
        "contactgroups": []
    },
    {
        "id": 18,
        "groupname": "RxJS",
        "contactsCount": 0,
        "contactgroups": []
    }
]

解决方案

try this code

exports.getNewGroupForProfsms = (req, res) => {
  Group.findAll({
    include: [{
      model: ContactGroup,
      attributes: ['id'],
      include: [{
        model: Contact,
        attributes: ['id', 'gsm']
      }]
    }],
  }).then(data => {
    // change happen here
    return res.status(200).send(data.map((x) => {
      // assign contactsCount to each row
      return Object.assign({
        contactsCount: x.contactgroups.length,
      }, x.toJSON()) // update toJSON have a try
    }));
  }).catch(err => {
    return res.status(400).send(err.message);
  });
};

这篇关于Node.js用mysql编写api代码(sequelize)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆