k0tkin's avatar

Get data from two models as one variable with pagination

I'm working on a social media app, users have Post.php and PostComment.php models

I want to show user's posts AND comments in their profile at the same time with pagination and sort them by created_at

somehow merging the results from two models but with pagination So if pagination is on 10 and the user had 10 comments recently we will get only 10 comments and no post

I don't want this (I want both of them as one variable with pagination)

User::with('posts', 'postComments')->find($user['id']);

My User.php

    public function posts()
    {
        return $this->hasMany(Post::class);
    }

    public function postComments()
    {
        return $this->hasMany(PostComment::class);
    }
0 likes
5 replies
rodrigo.pedra's avatar

Not sure if this is exactly what you are after, but I would do try this:

Route::get('/test', function () {
    $userId = Auth::id();

    // dynamic relation
    Post::resolveRelationUsing('related', fn ($model) => $model->morphTo('related'));

    $records = Post::query()
        ->with(['related'])
        ->select([
            'id AS related_id',
            DB::raw(sprintf(
                '"%s" AS  related_type', 
                str_replace('\\', '\\\\', (new Post())->getMorphClass())
            )),
            'created_at',
        ])
        ->where('user_id', $userId)
        ->union(
            PostComment::query()
                ->select([
                    'id AS related_id',
                    DB::raw(sprintf(
                        '"%s" AS  related_type', 
                        str_replace('\\', '\\\\', (new PostComment())->getMorphClass())
                    )),
                    'created_at',
                ])
                ->where('user_id', $userId)
                ->toBase()
        )
        ->orderByDesc('created_at')
        ->orderByDesc('related_id')
        ->orderBy('related_type')
        ->simplePaginate();

    $records->getCollection()->transform(fn ($record) => $record->related);

    return $records;
});

The trick here is that we are uniting both tables and ordering by the most recent ones.

The results act like a polymorphic model, and a dynamic relation is defined to allow eager loading the related models.

After paginating, we then unwrap the eager loaded models from the temporary relation.

Some references:

Hope this helps you out.

1 like

Please or to participate in this conversation.