How to relate two tables in Sequelize with a specific order of relations?-CodePudding

I am attempting to relate two tables - Books and Pages - in a Postgresql database via Sequelize. If this were a standard one-to-many relationship, all of the pages could simply have a 'bookId' column referencing the book they belong to. However, I need to preserve the order of the pages in the book. I checked the Sequelize documentation for advanced relations, but was unable to find relevant info.

Approach 1: My first thought was to use an array of pageIds on the Book model. However, I was unable to figure out how to correctly write the relation in this way - I am not sure if it is even possible.

const Book = db.define("book", {
    title: {
        type: Sequelize.STRING,
        allowNull: false,
    },
    pages: {
        type: Sequelize.ARRAY(Sequelize.INTEGER),
        defaultValue: [],
    }
});
const Page = db.define("page", {
    content: {
        type: Sequelize.TEXT,
        allowNull: false,
    },
});

Page.belongsTo(Book, { /* Unsure */ });
Book.hasMany(Page, { /* Unsure */ });

Approach 2: A teammate of mine suggested we create a separate 'through' table to store the relation. We did not discuss it in detail, but I imagine it would look something like this:

const Book = db.define("book", {
    title: {
        type: Sequelize.STRING,
        allowNull: false,
    },
});
const Page = db.define("page", {
    content: {
        type: Sequelize.TEXT,
        allowNull: false,
    },
});
const BookContent = db.define("BookContent", {
    bookId: {
        type: Sequelize.INTEGER,
        allowNull: false,
    },
    pageId: {
        type: Sequelize.INTEGER,
        allowNull: false,
    },
    pageNumber: {
        // This would refer to order of page in book
        type: Sequelize.INTEGER,
        allowNull: false,
    }
});

Page.belongsTo(Book, { through: "BookContent" });
Book.hasMany(Page, { through: "BookContent" });

I did not like this second approach initially, but I am unsure if the first one is possible. Open to any help with these approaches, or new approaches to this problem entirely.

CodePudding user response：

the simplest solution is to have page_number be a field on Page.

const Page = db.define("page", {
    content: {
        type: Sequelize.TEXT,
        allowNull: false,
    },
    pageNumber: {
        // This would refer to order of page in book
        type: Sequelize.INTEGER,
        allowNull: false,
    },
    bookId: {
        type: Sequelize.INTEGER,
        allowNull: false,
    }
});