sequelize join up the tables

Question

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

customer
library
books
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?

First of all there is no need to register associations every time you execute a query. Just register them one time at the startup. You can get the idea how to do it here https://stackoverflow.com/a/61710568/1376618 — Anatoly, Jan 02 '23 at 14:27
Second, you don't need `where: { book_id: book_id}` in the include with `Books` model — Anatoly, Jan 02 '23 at 14:28
would the association reflect on the production db? Do I need migrations of any sort? — SunAns, Jan 02 '23 at 14:30
If you already have all needed foreign keys in DB and you don't use `sync` then associations are used by Sequelize only for joining tables correctly to get associated model's records — Anatoly, Jan 02 '23 at 14:32
I dont have the foreign keys as I mentioned the db was already setup. — SunAns, Jan 02 '23 at 14:33

Anatoly · Accepted Answer · 2023-01-02T14:35:00.337

1

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
            },
          ],
        },
      ],
    });```

edited Jan 02 '23 at 14:35

answered Jan 02 '23 at 14:32

Anatoly

20,799
3
28
42

This the second code block of mine. it was already working. I was asking about the first one. – SunAns Jan 02 '23 at 14:34
The one where I have to look up to two more models – SunAns Jan 02 '23 at 14:34
Yes, my bad, I corrected my answer – Anatoly Jan 02 '23 at 14:35
Considering the last comment you made to my question, I guess its ok to keep the association right? I removed the `where: { book_id: book_id}` in the include with `Books` model but still getting this error. `column excerpt.library_id does not exist` . Arent the `include` statements nested? – SunAns Jan 02 '23 at 14:40
1

You also make mistake in `association: this.model.belongsTo(this.db.Library, { foreignKey: 'library_id', }),` because you needed to indicate `this.db.Books.belongsTo(this.db.Library` – Anatoly Jan 02 '23 at 14:45

sequelize join up the tables

1 Answers1