Lucid - Implement a "Stat" that can be used in several Models

#1

Hi.

I want to create a Stat model that can be applied to several Models. This stats will apply to several Models: User, Company, Product. This Stat table will have the views, prints, etc. information of other models.

I’m a bit stuck here as Lucid doesn’t have Many to Many Polimorphic as Laravel. Any suggestion on how to implement this?

B/R.

0 Likes

#2

There is a third party package that provides polymorphic relationships, adonis-lucid-polymorphic. I’m not sure if it actually works, it didn’t seem to last time I looked at it (several weeks ago).

You could use an Exclusive Arc table, rather than a polymophic pivot. Not sure exactly how, or if, it works outside of postgres.

The easiest, and probably most performant, option is to just create several pivot tables & many:many relationships for each thing you’d otherwise polymorph.

So say you have categories, posts and videos, catgories & posts have a many:many relationship as for videos & categories.

You would create your categories, post and videos tables as normal, then create individual pivot tables; post_categories and video_categories

This introduces more tables into the DB, but it’s a much more performant join than having to match a foreign table name and an id as is necessary for polymorphic joins.

0 Likes

#3

Hi @willvincent.
About this example, in the case you delete a category then you will have to go throw the post_categories and video_categories table to delete the category relationship, no?

0 Likes

#4

Not if you have appropriate on delete clauses on your foreign keys.

When you use proper foreign keys in the database, and don’t only rely on the framework to do everything, you can ensure that when a category is remove, all relations referencing it also get removed.

In a pivot table, that’d be as simple as including on delete cascade on both foreign keys so that if either the category or other thing are deleted, the pivot table entry will also be deleted

0 Likes

#5

Hi @willvincent
The solution should be the to include one foreign key per each model in the categories table. In this case, post_id and video_id and then reference those keys to the corresponding table.

Am I right?

0 Likes

#6

No that’s incorrect. If you directly reference a specific post or video in a category you can only have ONE instance of that category.

Instead you want categories, then pivot tables for each relationship of the many to many relationships.

Presumably a video or post can belong to many categories, and categories in turn will have many of both…

The end result here is that even if you have 500 items between videos and posts that are in the “Humor” category, there will only be one single record in the categories table for “Humor”

If instead you put the foreign keys directly into the categories table, you’d have to duplicate that “humor” category 500 times to reference the appropriate record in teh other table.

Also, to allow items to belong to multiple categories, you can’t store the relationship in the root table on the other end either – thus the pivot tables that just contain the two foreign ids. Though, lucid really wants you to have a serial id in those too, so be aware of that.

As for the actual models themselves, they’d have relationships like this:

// Video:

categories () {
  return this.belongsToMany('App/Models/Category')
}
// Post:

categories () {
  return this.belongsToMany('App/Models/Category')
}
// Category:

videos () {
  return this.belongsToMany('App/Models/Video')
}

posts () {
  return this.belongsToMany('App/Models/Post')
}

Then, as shown in the diagram, as you add future things, you’d just add the appropriate tables for the model, and pivot, and add similar relationships… and those new things too then can make use of all the existing categories… along with creating new ones.

Refer to the BelongsToMany docs for more details

1 Like

#7

If in the migration for those pivot tables you include onDelete() clauses for the two foreign keys, you can rest easy knowing that they’ll get removed automatically when you delete either the post, or category, etc.

For example:

up () {
    this.table('category_post', (table) => {
      table.serial()
      table.integer('category_id').unsigned().notNullable()
      table.integer('post_id').unsigned().notNullable()
      table.foreign('category_id').references('id').inTable('categories').onDelete('CASCADE')
      table.foreign('post_id').references('id').inTable('posts').onDelete('CASCADE')
    })
  }
2 Likes

#8

@willvincent thanks a lot for your answer. I solves the BelogsToMany issue perfectly. Master!

0 Likes