Sequelize MySQL – Get all associations that contain an ID (belongsToMany)

Issue

I am building an application where I want to build a user filter. Right now I am struggling with querying all users that speak a language with a specific id. I want to return that user with ALL the languages he knows how to speak, however sequelize currently only returns the user with the particular language.

I have read that in Postgres the problem can be easily solved by the [Op.contains] operator but since I am using MySQL, this operator is not available.

I have a model User and Language that are connected by the join table UserLanguage.

Many thanks in advance!

User.tsx

const User = sequelize.define<UserInstance>(
  "user",
  {
    id: {
      type: DataTypes.UUID,
      allowNull: false,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true,
    },
    firstName: {
      type: DataTypes.STRING,
      field: "first_name",
    },
    ...
  },
  {
    timestamps: false,
  }
);

Language.tsx

const Language = sequelize.define<LanguageInstance>(
  "language",
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
    name: {
      type: DataTypes.STRING,
    },
  },
  {
    timestamps: false,
  }
);

UserLanguage.tsx

const UserLanguage = sequelize.define<UserLanguageInstance>(
  "user_language",
  {},
  {
    timestamps: false,
  }
);

Language.belongsToMany(User, {
  foreignKey: "fk_language",
  through: UserLanguage,
});
User.belongsToMany(Language, {
  foreignKey: "fk_user",
  through: UserLanguage,
});

My query:

await User.findAll({
      attributes: [
        "id",
        "firstName",
      ],
      include: [
        {
          model: Language,
          through: {
            attributes: [],
            where: {
              fk_language: 1,
            }
          },
          required: true,
        },
      ],
    })

When I filter for languages with the id=1, this would be the desired output:

[
  {
    "id": {
      "type": "Buffer",
      "data": [
        80, 21, 118, 100, 88, 177, 72, 2, 160, 130, 111, 173, 245, 65, 109, 244
      ]
    },
    "firstName": "Candace",
    "languages": [
      {
        "id": 1,
        "name": "en"
      },
      {
        "id": 5,
        "name": "fr"
      },
      {
        "id": 8,
        "name": "jp"
      }
    ]
  },
  {
    "id": {
      "type": "Buffer",
      "data": [
        144, 63, 87, 23, 56, 130, 67, 28, 161, 194, 38, 191, 212, 53, 31, 60
      ]
    },
    "firstName": "Walter",
    "languages": [
      {
        "id": 1,
        "name": "en"
      },
      {
        "id": 2,
        "name": "es"
      },
      {
        "id": 4,
        "name": "de"
      },
      {
        "id": 5,
        "name": "fr"
      }
    ]
  }
]

However, I will just get the languages array with the one element that has id=1:

[
  {
    "id": {
      "type": "Buffer",
      "data": [
        80, 21, 118, 100, 88, 177, 72, 2, 160, 130, 111, 173, 245, 65, 109, 244
      ]
    },
    "firstName": "Candace",
    "languages": [
      {
        "id": 1,
        "name": "en"
      }
    ]
  },
  {
    "id": {
      "type": "Buffer",
      "data": [
        144, 63, 87, 23, 56, 130, 67, 28, 161, 194, 38, 191, 212, 53, 31, 60
      ]
    },
    "firstName": "Walter",
    "languages": [
      {
        "id": 1,
        "name": "en"
      }
    ]
  }
]

Solution

To me it sounds like you need either nested selects or a join. If you have a table user a link table user-language and a language table, then you can use JOIN to join the tables together, assuming you have a userID, and a langID in respective tables.

So:

SELECT * from UserTbl 
JOIN UserLanguageLinkTbl on UserTbl.UserID=UserLanguageLinkTbl.UserID 
JOIN LanguageTbl on UserLanguageLinkTbl.LangID=LanguageTbl.LangID
WHERE UserID=1

Above will give you a row for each language the specific user (UserID=1) knows.

Answered By – Sourcery

Answer Checked By – Robin (AngularFixing Admin)

Leave a Reply

Your email address will not be published.