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();