PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get associations in an array using sequelize findAll()?

I’m pretty new to Sequelize and trying to learn all of the cool tricks you can use to format your queries, but have run into a bit of a wall.

Basically I have a table "invoices". I have another table "invoice_items" which has a relationship many invoice_items to one invoice.

Currently I am trying to include this one to many association in a findAll query such that the invoice_items are nested in an array in each invoice object.

Something along the lines of:

[
  {
    name: invoice1
    invoiceItems: [
      itemOne,
      itemTwo,
    ]
  },
    {
    name: invoice2
    invoiceItems: [
      itemOne,
      itemTwo,
    ]
  }
]

The closest I can get is outputting multiple invoice objects (one for each association). Here is the query, which outputs the object below.

db.invoice.findAll({
    where: {
        userId: req.user.id
    },
    include: "invoiceItems",
    raw : true,
    nest : true
});

[
  {
    name: invoice1
    invoiceItems: itemOne
  },
  {
    name: invoice1
    invoiceItems: itemTwo
  },
  {
    name: invoice2
    invoiceItems: itemOne
  },
  {
    name: invoice2
    invoiceItems: itemTwo
  }
]

Is there anyway to achieve what I am hoping to achieve here? Thanks in advance!

Edit:

I was able to get the desired result by using get() to help process the result. The issue I have been having is raw: true seems to not work with eager loading.

Here is the processed option in case anyone finds it helpful.

db.invoice.findAll({
    where: {
        userId: req.user.id
    },
    include: "invoiceItems",
}).then(results => {
    let processedResults = [];
    for (result of results){
        processedResults.push(result.get({ plain: true }));
    }
    return processedResults;
});
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to get the desired result by using get() to help process the result. The issue I have been having is raw: true seems to not work with eager loading.

    Here is the processed option in case anyone finds it helpful.

    db.invoice.findAll({
    where: {
        userId: req.user.id
    },
    include: "invoiceItems",
}).then(results => {
    let processedResults = [];
    for (result of results){
        processedResults.push(result.get({ plain: true }));
    }
    return processedResults;
});

  2. 0

    If you would like to simplify your code further you can have it like below:

    db.invoice.findAll({
    where: {
        userId: req.user.id
    },
    include: [{model: invoiceItems}]
}).then(results => results);
    • Omitting plain: true, by default Sequelize returns an array of objects so the array defined as "processedResults" is not necessary
    • (results => results) is ES6 short hand for results => { return results }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search