Working on an already setup db and also new to sequelize. I have like four tables

1. customer
2. library
3. books
4. excerpt

excerpt has redundant book_id from books. books has redundant library_id from library, and library has redundant customer_id from customer. They were not declared foreign in db but its kinda acting the foreign key type and I make the association when I hit the orm functions.

My question: I have a customer_id and book_id, and I have to fetch excerpt records based on book_id but have to go top the db to match the customer_id as well. (for multi tenancy) the flow is: excerpt> book_id - books > library_id - library > customer_id - customer

I have written this code but its not working

async read_book_id(customer_id, book_id) {
    const excerpts = await this.model.findAll({  // this.model being the excerpt model
      where: { book_id: book_id},
      include: [
        {
          model: this.db.Books,
          association:
             this.model.belongsTo(this.db.Books, {
                  foreignKey: 'book_id',
                }),
          where: { book_id: book_id},
          include: [
            {
              model: this.db.Library,
              association: this.model.belongsTo(this.db.Library, {
                foreignKey: 'library_id',
              }),
              where: { customer_id: customer_id },
            },
          ],
        },
      ],
    });

Basically this is something extended from this another code i wrote which is working for me. If I have to check only one level above thats working fine for me e.g

// reading books based on library_id

 async read(customer_id, library_id) {
    const books= await this.model.findAll({
      where: {
        library_id: library_id,
      },
      include: [
        {
          model: this.db.Library,
          association: this.model.belongsTo(this.db.Library, {
            foreignKey: 'library_id',
          }),
          where: { customer_id: customer_id },
        },
      ],
    });
  }

This works fine for me.

Can you please tell how to run the first code block?

CodePudding user response：

Assuming you already registered all associations just like I suggested in the comments above you need to indicate the correct models in include options along with correct conditions:

const excerpts = await this.model.findAll({  // this.model being the excerpt model
      where: { book_id: book_id},
      include: [
        {
          model: this.db.Books,
          include: [
            {
              model: this.db.Library,
              where: { customer_id: customer_id },
              required: true
            },
          ],
        },
      ],
    });```