Knowledge

“Include” with single join

Let us first see how we can use include when we are joining 2 simple tables.

Note: If you are already aware of single join, then jump to section “Include” with multiple joins

Consider the following two tables,

User Table

const UserModel = db.define(
  "users",
  {
    userId: {
      field: "userId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    firstName: {
      field: "firstName",
      type: sqlOperator.STRING(100),
      require: true
    },
    lastName: {
      field: "lastName",
      type: sqlOperator.STRING(100),
    },
    email: {
      field: "email",
      type: sqlOperator.STRING(255),
      validate: {
        isEmail: true
      }
    },
    {
    tableName: "users",
    timestamps: true
  }
);

Notice Table

[AdSense-A]

const NoticesModel = db.define(
  "notices",
  {
    noticeId: {
      field: "noticeId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    title : {
      field: "title",
      type: sqlOperator.STRING(250),
    },
    notice : {
      field: "notice",
      type: sqlOperator.TEXT,
    }
  },
  {
    tableName: "notices",
    timestamps: true
  }
);

NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeBy",
      allowNull: false
    },
    targetKey: "userId"
  });

When we want to fetch the user who has posted the notice, we will use the following code,

const query ["include"] = [{model: UserModel}]
// query can include all some conditions required, this is out of scope of this tutorial, for the same you can check out this 
const notices = await NoticeModel.findAll(query);

Reference

After executing the above findAll, we will get all the notices and the respective user who has created that.

“Include” with multiple joins

In this section we will learn how to use when there are multiple join of the same table.

We will make some changes in the Notice table as follows,

const NoticesModel = db.define(
  "notices",
  {
    noticeId: {
      field: "noticeId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    title : {
      field: "title",
      type: sqlOperator.STRING(250),
    },
    notice : {
      field: "notice",
      type: sqlOperator.TEXT,
    }
  },
  {
    tableName: "notices",
    timestamps: true
  }
);

NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeBy",
      allowNull: false
    },
    targetKey: "userId"
  });

//Added one more new relation
NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeTo",
      allowNull: false
    },
    targetKey: "userId"
  });

Now we will try to run the same above findAll, we will observe that neither noticeTo nor noticeBy will be filled.

What is happening here is Sequelize finds multiple userModels so it will not be sure which one to fetch.

[AdSense-B]

Solution

To solve the above issue we will have to make the following changes

const NoticesModel = db.define(
  "notices",
  {
    noticeId: {
      field: "noticeId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    title : {
      field: "title",
      type: sqlOperator.STRING(250),
    },
    notice : {
      field: "notice",
      type: sqlOperator.TEXT,
    }
  },
  {
    tableName: "notices",
    timestamps: true
  }
);

NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeBy",
      allowNull: false
    },
    targetKey: "userId",
    as : "noticedBy"
  });

//Added one more new relation
NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeTo",
      allowNull: false
    },
    as: "noticedTo",
    targetKey: "userId"
  });

And in the find we will do this,

const query["include"] = [{model: UserModel, as: "noticedBy"}, {model: UserModel, as: "noticedTo"}]

const notices = await NoticeModel.findAll(query);

Once we add the above changes, both noticedBy and noticeTo will be filled. (Observe we have not kept the alias name same, we have changed them slightly).

Possible errors

uncaughtException: Naming collision between attribute ‘noticeTo’ and association ‘noticeTo’ on model notices. To remedy this, change either foreignKey or as in your association definition\nError: Naming collision between attribute ‘noticeTo’ and association ‘noticeTo’ on model notices

What does the above error mean?

It says that we have given same names to the attribute i.e foreign key and alias. We get this with the following code,

  NoticesModel.belongsTo(UserModel, {
    foreignKey: "noticeTo",
    as: "noticeTo",
    targetKey: "userId"
  });

As we can see the highlighted rows, both have the same names. Solution to this is very simple, just rename alias to something else.

  NoticesModel.belongsTo(UserModel, {
    foreignKey: "noticeTo",
    as: "noticedTo",
    targetKey: "userId"
  });

TADA!!! everything will work fine now.

For More Sequelize Errors: Read More Here

• Facebook