Best practice for Many to Many relationships

[rezaffm]

Hi (Moin),

I have been thinking about this one for a while and I wonder, what is your prefered way of solving this.

Let's say you have posts and tags. We have then four tables right?

posts
-----
id (primary)

post_translations
-----
id
post_id 
locale
content

tags
-----
id (primary)

tag_translations
-----
id
post_id 
locale
name

So, now the question would be, how to relate them to each other?

For instance, one could use a classical pivot table with post_id and tag_id, correct?

post_tags_pivot
----
post_id
tag_id

Let's say you have a German post tagged by several tags.

You would then query the database and also maybe constraint the query by only getting tags that has a German translation - right?

I was wondedering, if it could make sense to add a "locale" column to the pivot table, so this table would become:

post_tags_pivot
----
post_id
tag_id
locale

Of course this would mean that if the German tag would be deleted (and the same would be true for the post accordingly), this pivot table needs to be cleaned up as well.

Maybe let's try to make an example, shall we?

We have a post with the id = 1 and two related translations. One with the post_translations id = 1 (the german version) and the otherone in english, with the post_translations i = 2. Consequently, both translations have a post_id = 1.

I have a tag that has been translated in English. This means, our tables would look like this:

post_translations (skipped posts table)
----
id post_id locale content
1    1        en     en content
2    1        de     de inhalt

tag_translations (skipped tags table)
----
id tag_id locale content
1    1        en     en tag

post_tags_pivot
----
post_id tag_id locale
1             1          en

In a classical belongsToMany relationship one could now do the following:

return $this->belongsToMany('App\Models\Tag')->wherePivot('locale', App::getLocale());

...and now the key question would be.

Does that make sense?

Or would that be an overkill?

Thank You.

[Gummibeer]

I would recommend you to stick with the Laravel/Eloquent default and use the translatedIn() scope. https://docs.astrotomic.info/laravel-translatable/package/scopes#translatedin-string-usdlocale-null https://github.com/Astrotomic/laravel-translatable/blob/f7b294cb7b2a853cb11b0e51bc60d9170f2b11a0/src/Translatable/Traits/Scopes.php#L84-L91

public function tags()
{
    return $this->belongsToMany(\App\Models\Tag::class);
}

$tags = $post->tags()->translatedIn()->get();

This way you relate tags to the post and the relation doesn't have to be updated when translations change or you end with different tags in different locales. But you can still filter your tags to only contain translated ones in the current locale.

[rezaffm]

Makes sense - I actually implemented it that way, I was just a bit curious what's your typical way of solving this.

Danke Dir.

[Gummibeer]

The locale in the relation/pivot table would only make sense if you would want different tags per locale. So not only translated but really different ones.