Javascript/Sequelize/MySql Help with Select and Update in same query

Good Day Experts!

I have quite the dilemma.   I am working on a Javascript project using express, sequelize and mysql.  Generating an order is part of the functionality.  The database has a header and detail table.  I have a separate table that holds the order number.  When a new order is placed, I would like to grab the current order number in the table then increment it.  Then use that current order number in my posts for the header and detail inserts.

Is this possible to achieve using the technologies I am using?

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
 
Julian HansenConnect With a Mentor Commented:
The usual practice for this is to create the header record and then ask the database for the last insert id. You then use this as your order reference for all other orders.

This thread has a discussion on how to do this in Sequelize

The gist of the discussion is that you can pass the following as an option
{ type: sequelize.QueryTypes.INSERT }

Open in new window

And the query returns the last insert id
For example
sequelize.query(
      'INSERT INTO OrderHeader(orderdata) VALUES(\'Data\')', {
        type: Sequelize.QueryTypes.INSERT
      }
    ).then(function (data) {
      console.log(data); // Outputs last insert id - use this value for your inserts into your order data

Open in new window

0
 
Julian HansenCommented:
Ideally you should be using the DB to generate the order numbers with an auto increment. If this is not suitable you will need to implement some synchronisation code to ensure you don't have a collision on order number when more than one request is made to the server at the same time.


Having said that - are you asking if it is possible to query a MySQL database from Express - if so the answer is yes.
0
 
leakim971Connect With a Mentor PluritechnicianCommented:
You should let Sequelize managing the increment and the fact order number must be unique.
below we have, an orderId for "internal" use, you don't need to show it to client side and orderStrPart and orderNumber to represent your order number.
more information on this page : http://docs.sequelizejs.com/manual/tutorial/models-definition.html

const Project = sequelize.define('Orders', {
 orderId: { type: Sequelize.INTEGER, unique: true, autoIncrement: true },
 orderStrPart: { type: Sequelize.STRING(3),  unique: 'orderNumber' },
 orderNumPart: { type: Sequelize.INTEGER, unique: 'orderNumber', autoIncrement: true },

Open in new window


you should create order after the user validate a quote or its order, not before, else you are going to increment the order number too quickly each time a user start an order but don't necessarily end it.
0
 
Jimbo99999Author Commented:
Hello. I understand I could use the auto-increment in the db.  But the problem is I need to insert into the OrderHeader table and then use the same orderid when I insert into the OrderDetail table.  Is there a better way than generating the orderid first so I could use it on both inserts?

I understand and have been querying mysql from express.  But I was wanting to select the value in the table then update(increment) it all in one statement.  

Thanks,
jimbo99999
0
 
leakim971Connect With a Mentor PluritechnicianCommented:
You can do One-To-One associations  : http://docs.sequelizejs.com/manual/tutorial/associations.html
OrderDetail.belongsTo(OrderHeader); / Will add a orderHeaderId attribute to OrderDetail to hold the primary key value for OrderHeader

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.