how to get all user activity [likes,comments] from his posts

Hello, so I have an application in Laravel in which a lot of user data is stored in separate tables across several models. I now have a requirement to create an activity feed 'like Instagram activity', which means ordering the various data across tables by date.

For illustrative purposes, imagine I have 3 models, Post, Comment and Like, and of course the User. model

I want a feed that combines both by date. merge() is not an option because they may have the same id.

Therefore I could UNION them, but my problem is I won't know what came from what.

So: My posts table:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)          | NO   |     | NULL    |                |
| content    | varchaer(191)    | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

And my likes table looks like this:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)          | NO   |     | NULL    |                |
| asset_id   | int(11)          | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

Finally my comments table:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | int(11)          | NO   |     | NULL    |                |
| user_id    | int(11)          | NO   |     | NULL    |                |
| content    | varchaer(191)    | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

what I need is to get the user feed, let's say I have 2 users if user 1 share a post, then user 2 comment on it

user 1 will get in the activity tab:

'username.of.id.2' Commented  'this is the comment' On Your 'post.id'

Thanks in advance.

alanholmes

6 years ago

Level 35

You could use the Query Builder, and UNION https://laravel.com/docs/6.x/queries#unions

And then provide an extra column that identifies where it comes from, something similar to (not tested):

$first = DB::table('comments')
            ->select(DB::raw('id, "comment" as type'))
            ->whereUserId(1);

$users = DB::table('likes')
            ->select(DB::raw('id, "like" as type'))
            ->whereUserId(1)
            ->union($first)
            ->orderBy('created_at')
            ->get();

Another option would be to have an Activities table, and use a Polymorphic relationship: https://laravel.com/docs/6.x/eloquent-relationships#polymorphic-relationships

If you check out the Activities part of this series https://laracasts.com/series/build-a-laravel-app-with-tdd, IIRC this is something similar to what you are after

el0zahaby

6 years ago

Level 1

@alanholmes, but this will give me the likes and comments that user made, not that he got

alanholmes

6 years ago

Level 35

@el0zahaby

You could still apply similar logic, but instead of getting by user id, you could get by the users posts

el0zahaby

6 years ago

Level 1

@alanholmes , Excuse me but I have been trying for three days and could not make it work .. Can you give me an example of post_id

alanholmes

6 years ago

Best Answer

Level 35

@el0zahaby

It would be roughly something like this (untested), using polymorphic relationship

The migration

Schema::create('activites', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger("post_id")->index();
    $table->string("subject_type");
    $table->unsignedBigInteger("subject_id");
    $table->timestamps();
});

The model:

class Activity extends Model
{
    /**
     * @return \Illuminate\Database\Eloquent\Relations\MorphTo
     */
    public function subject()
    {
        return $this->morphTo();
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function post()
    {
        return $this->belongsTo('App\Post');
    }

   /**
     * @param \Illuminate\Database\Eloquent\Builder $query
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeForUserPosts(Builder $query, $userId)
    {
        return $query->whereHas('post', function($query) use ($userId) {
           $query->where('user_id', $userId);
        });
    }
}

And then this would get all the activities for the users posts:

$activites = Activity::forUserPosts(1)->get()

Then when looping through each activity $activity->subject will return either the like or the comment

Please or to participate in this conversation.