I have 3 models, User, Alert, and Category.
The relationship between User and Category is many-to-many.
The relationship between Alert and Category is also many-to-many.
A User should only receive Alert that belongs to the same Category which the User have selected. e.g. If the User have Travel and Food as the Category, then I’d like only Alert with Travel and Food to be shown to the User.
Once the User views an Alert, the Alert should be marked as ‘read’ for that particular User.
Currently I’m doing it like so:
Category.php
public function alerts()
{
return $this->belongsToMany('AppAlert')->withTimestamps();
}
public function users()
{
return $this->belongsToMany('AppUser')->withTimestamps();
}
User.php
public function alerts()
{
return $this->belongsToMany('AppCategory')->withTimestamps();
}
public function categories()
{
return $this->belongsToMany('AppAlert')->withTimestamps()->withPivot('read');
}
Alert.php
public function users()
{
return $this->belongsToMany('AppUser')->withTimestamps()->withPivot('read');
}
public function categories()
{
return $this->belongsToMany('AppCategory')->withTimestamps();
}
With this current implementation, there are 3 pivot tables: alert_category, alert_user, and category_user. alert_user table has an extra field named read which basically determines if the user has read the alert or not. i.e.
alert_user table alert_id user_id read (holds 0 or 1)
This implementation have many flaws, and I don’t think it’s even working at all, since new User won’t receive Alert that were created before they registered, and if Alert changes Category, it won’t be updated. Of course, it could be done by explicitly updating each table, but I don’t believe that’s efficient nor is it architecture.
I’m thinking of refactoring the whole thing into a many-to-many polymorphic relationship. But, how would I determine if the User has read a particular Alert? Where should I place a read field?
What do you think? I’m open to any ideas. Any help would be greatly appreciated.
Edit:
To summarise, I guess what I’m trying to do is, instead of User subscribing to Alert, it should instead subscribe to Category. Therefore, essentially the User will receive all Alert of the same Category.
Advertisement
Answer
If you want the newly registered users to see all previous alerts under chosen categories, it’s basically saying that a logged in user should see all alerts under those categories.
I do not think that associating an alert with a user will give you any sort of advantage. If any, it is giving you an extra layer of complexity.
Now, if you want to keep track of which users read which alert, you could use the alert_user table. Basically, you create this relationship when a user reads an alert. created_at will tell you the exact time the user read the alert and, therefore, a read flag is not necessary as well.
And, to display the alerts, you could query, for example, the alerts that does not have relationship records with the logged in user. You could use whereDoesntHave. For example, something like this:
Alert::whereHas('categories', function($query) use ($category_id_array){
$query->whereIn('category_id', $category_id_array);
})->whereDoesntHave('users', function($query) use ($user_id){
$query->where('user_id', $user_id); //user_id is auth()->user()->id
})->get();
If you only want to display the tweets without excluding the authenticated user:
Alert::whereHas('categories', function($query) use ($category_id_array){
$query->whereIn('category_id', $category_id_array);
})->get();