SequelizeDatabaseError: operator does not exist uuid = integer

Question

I'm trying to make a join with two tables based on UUID (I have id too), those tables have some difficult relations...

When I run the query I get the error.

The first table is called users, and his UUID is called registry_uuid. The second table beneficiaries but this table has two UUID, uuid_beneficiary and uuid_benefactor.

Why? because the first table has a column user_type_id and with this we can know if it's a user beneficiary or benefactor. And the second table is to know which users are related.

Model Users:

const User = sequelize.define('users', {
registry_uuid: {
    type: Sequelize.UUIDV4,
    defaultValue: Sequelize.UUIDV4,
    allowNull: false
    },
user_type_id: {
    type: Sequelize.INTEGER,
    defaultValue: 1,
    allowNull: false
    }
}

Model Beneficiaries:

const Beneficiary = sequelize.define('beneficiaries', {
uuid_benefactor: {
    type: Sequelize.UUIDV4,
    defaultValue: Sequelize.UUIDV4,
    allowNull: false
    },
uuid_beneficiary: {
    type: Sequelize.STRING,
    defaultValue: Sequelize.UUIDV4,
    allowNull: false
    },
created_at: {
    type: Sequelize.DATE,
    defaultValue: Sequelize.NOW
    },
disassociation_date: {
    type: Sequelize.DATE,
    defaultValue: null
    }
}

Query:

async function getBenefactorOfBeneficiary (benefactorUuid, arrayAttributes) {
arrayAttributes = arrayAttributes || ['registry_uuid', 'name', 'last_name', 'picture']

return UserModel.findOne({
  where: {
    registry_uuid: {
      [Op.eq]: benefactorUuid
    }
  },
  include: [{
    model: BeneficiaryModel,
  }],
  attributes: arrayAttributes,
  raw: true
})
}

Relation:

UserModel.hasMany(beneficiaryModel, {foreignKey: 'uuid_benefactor'})
beneficiaryModel.belongsTo(UserModel, {foreignKey: 'registry_uuid'})

I expect the output:

Example:

{
  "name": "string",
  "lastName": "string",
  "picture": "string",
  "created_at" "string"
}

obviously I modify the response in controller

Answer 1

You should first check the models that you are including and their ID types. They must have same type. Beside that, let's say we have User and Role models. Every User can have only one role and a Role can be used by several Users. In this case, you will get this error if you write the associations wrong.

Wrong version:

// Under the user model associations 
user.hasOne(models.role, { foreignKey: "roleId" });
// this will try to compare your userId with roleId of Role table

// Under the Role model associations 
role.hasMany(models.user, { foreignKey: "roleId" });

Right version should be like:

// Under the user model associations 
user.hasOne(models.role, { foreignKey: "roleId" });
// this will try to compare your roleId from User model with roleId of Role table

// Under the Role model associations 
role.hasMany(models.user, { foreignKey: "roleId" });

If you need more details, read https://sequelize.org/master/manual/assocs.html