How to join another table in Sequelize query

Question

I have Items, Orders and OrderRows in my models. This query gives me orderRows by order_id given as a parameter, but what I still need is a item´s (productname) not only a item_id which is normally in orderRow.

I have tried the include and required in different places but it didnt work.

Query is here:

// GET ORDER-ROWS BY ORDER_ID
orderRowRouter.get('/order/:orderid', async (req, res) => {
    try {
        const rowsByOrder = await Orderrow.findAll({
            where: {
                order_id: req.params.orderid
            }
        })
        return res.json(rowsByOrder)
    }
    catch (exception) {
        res.json(exception)
    }
})

Response is like:

[
    {
        "orderrow_id": "1fe71649-d0f1-4cd7-b987-7190e88deede",
        "item_id": "02d5a0ee-f19f-458c-98d2-df51fea551e7",
        "order_id": "9fb9d681-7555-4e6c-a92b-921aa008fc19",
        "amount": 2,
        "createdAt": "2021-06-11T21:25:45.604Z",
        "updatedAt": "2021-06-11T21:25:45.604Z"
    }
]

In the place of item_id I would like to have productName which is in Item model and db table.

Does this answer your question? [How to make join queries using Sequelize on Node.js](https://stackoverflow.com/questions/20460270/how-to-make-join-queries-using-sequelize-on-node-js) — frozen, Jun 12 '21 at 19:27

Simo Siren · Answer 1 · 2021-06-13T20:34:34.233

I finally used raw SQL query like so:

//added to imports
const db = require('../models/index')

................

orderRowRouter.get('/order/:orderid', async (req, res) => {
    try {
        const [results, metadata] = await db.sequelize.query
             ("SELECT * FROM orderrow LEFT JOIN item ON item.item_id = orderrow.item_id WHERE  order_id = " + "'" + orderid + "'")

        console.log(results)
        return res.json(results)
    }
    catch (exception) {
        console.log(exception)
        res.json(exception)
    }
})

How to join another table in Sequelize query

1 Answers1