CODEWITHSUNDEEP
phpmysqllaravel-5
Summer Developer
Summer Developer

Reputation: 2086

Laravel Eloquent Combine Queries For Activity Feed

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, which means ordering the various data across tables by date.

For illustrative purposes, imagine I have two models, Comment and Like.

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.

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    |                |
+------------+------------------+------+-----+---------+----------------+

My comments table looks like this:

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

My issue is if I just union the two queries, I would only know they are different based on the presence of the content column, which could be in another model, such as Blurb or whatever.

Basically, how do I get multiple queries across models while keeping straight what belongs where, because in my activity feed I want to say, 10 minutes ago you commented, 5 minutes ago you liked, etc.

I don't want to do multiple queries because of inefficiency and I don't want to store all the activities (likes and comments, etc.) in one table either. Is there some kind of alias I can use where instead of renaming a column I insert data using the query for the purposes of the query, so for example a comment selection would add "comment" in a temporary field so that I can access it like

$data->type? I could put a type in all of the tables but then I'd have space being taken up needlessly, as obviously I know a comment is a comment if its in the comment table when that is my only query, but now I am rethinking my structure given I need one query to span multiple tables.

Upvotes: 3

Views: 606

Answers (2)

Sachin Kumar
Sachin Kumar

Reputation: 3240

you can use following code to get user activity feed.

$userId = Auth::id(); //or whatever you want.
    $activity = DB::table('comment as ac')
    ->select(DB::raw('ac.user_id , ac.asset_id , ac.comment , ac.created_at , ac.updated_at , "comment" as activity_type'))
    ->where("ac.user_id", $userId)
    ->union(
    DB::table('like as al')
    ->select(DB::raw('al.user_id , al.asset_id , NULL as comment , al.created_at , al.updated_at , "like" as activity_type'))
    ->where("al.user_id", $userId)
    )
    ->latest()
    ->get();

Upvotes: 1

fubar
fubar

Reputation: 17398

When performing your query, select an addition raw value based on the table name. For example, in raw SQL:

SELECT likes.*, '' AS content, 'like' AS type
FROM likes
WHERE likes.user_id = 1
UNION
SELECT comments.*, 'comment' AS type
FROM comments
WHERE likes.user_id = 1
ORDER BY created_at DESC

The Laravel code (untested) will look something like:

$activity = DB::table('comments')
    ->select("comments.*, 'comment' AS type")
    ->where('comments.user_id', $user->id)
    ->union(
        DB::table('likes')
            ->select("likes.*, '' AS content, 'like' AS type")
            ->where('likes.user_id', $user->id)
    )
    ->orderBy('created_at', 'ASC')
    ->get();

Upvotes: 1

Related Questions