CODEWITHSUNDEEP
mongodbpostgresqlsequelize.js
Aleksandar Grbic
Aleksandar Grbic

Reputation: 212

Does Sequelize.js have anything similar to .populate() in mongoose.js

I've recently switched from mongoDB (Mongoose.js) to PostgreSQL (Sequelize.js) and I'm dealing with more of a "philosophical" or to say, "design" issues and I could use your help.

Here is an example:

User has 1:M relationship with Articles

Articles can have many Tags

Tags in my system are finite and static, meaning, there's around 40 that I've populated my database with, and they won't be changing.

My article table has a field called "tags" and right now, I made it to be an array of integers.

Then I store the tags like

tags: [1, 14, 5]

Now, when I'm querying, I'd simply like to populate those ids, or in simple words, to actually replace them with the actual tags of those ids. The closest I got with Sequelize is to simply loop through each of the article's tags array and fetch each of the articles. While this works and is really simple, for some reason, it just feels wrong.

I'm aware that I could use a third (junction/join) table to solve this, but I don't really want to store tag objects inside of the actual articles. I simply want to "reuse" the "static" tags I have and not create new "sub-documents" every time a new article is created.

So my design question here, how do you deal with this scenario and how common is it in SQL in general to store e.g. an array of primary or foreign keys? For example in mongoDB, this is very common due to lack of JOINS and relations, so it ends up being the only way to deal with such requirement.

How would you solve this yourselves, and am I even thinking this through properly.

Thanks in advance, Alex

Upvotes: 0

Views: 494

Answers (1)

Navjot Sharma
Navjot Sharma

Reputation: 26

While this works and is really simple, for some reason, it just feels wrong.

Yes you are right about this part as this is not the most sql approach.

I'm aware that I could use a third (junction/join) table to solve this, but I don't really want to store tag objects inside of the actual articles.

Also this part is not clear in the question as how will adding third table will require you to store tag object in article.

In sql, common approach is to use 3 tables where a junction table will hold primary keys of both Articles and Tags. Then you can create association in tags model like this.

Structure (you may also add primary key if you want)

tagId: tags table primaryId
taggableId: Article table primaryId
taggableType: 'Article' or any other table you may wanna associate in future

/** Association */

this.belongsToMany(Article, {
  through: {
    model: 'Taggable',
    attributes: []
  },
  otherKey: 'taggableId',
  targetKey: 'id',  // primaryKey of articles table
  sourceKey: 'id', // primaryKey of tags table
  foreignKey: 'tagId', // local key on through table - to compare with local table
  constraints: false,
  attributes: [],
  as: 'articles',
  foreignKeyConstraint: false
});

Another approach

If you cannot transfer your tagIds out of article, you can also use array in query.

where: {'tags.id': {in: [1,2,3,4]}},

This way you will need only one query per row instead of one query per tagId.

Upvotes: 1

Related Questions