Mapping / Filtering results so that only the column I want visible are returned

Hello, I am trying to figure out the best way to control the data columns that are sent as a response to inertia so that the sensitive data is not visible in the request. I have played around a little, but keep having an issue as I want to control the data of the main query & the related query & each time I limit the response, the related query returns null. This is my working code:

    public function index()
    {
        return Inertia::render('Discussion/Threads/Index', [
            'threads' => Thread::query()
                ->with('user')
                ->latest()
                ->paginate(20)
        ]);
    }

I want to only select title, body & slug in the Thread query & only username & email from the User query.

I tried the following:

public function index()
{
    return Inertia::render('Discussion/Threads/Index', [
        'threads' => Thread::query()
            ->select('title', 'body', 'slug')
            ->with(['user' => function ($query) {
                $query->select('username', 'email');
            }])
            ->latest()
            ->paginate(20)
    ]);
}

However, the user sub query is returning null. I think this is because we need to select the user_id column from the Thread model to be able to find the user. So I don't want to only select the title, body & slug - I want to map/filter the results so that only those 3 columns are returned in the response.

LaryAI

3 years ago

Level 58

The issue is that the user_id column is not being selected in the main query, so the related query is not able to find the user. To solve this, you can select the user_id column in the main query and then use the map method to filter the results to only include the desired columns. Here's an example:

public function index()
{
    $threads = Thread::query()
        ->select('id', 'title', 'body', 'slug', 'user_id')
        ->with('user:id,username,email')
        ->latest()
        ->paginate(20);

    $threads = $threads->map(function ($thread) {
        return [
            'id' => $thread->id,
            'title' => $thread->title,
            'body' => $thread->body,
            'slug' => $thread->slug,
            'user' => [
                'username' => $thread->user->username,
                'email' => $thread->user->email,
            ],
        ];
    });

    return Inertia::render('Discussion/Threads/Index', [
        'threads' => $threads,
    ]);
}

In this example, we select the id, title, body, slug, and user_id columns in the main query. Then, in the with method, we select only the id, username, and email columns from the users table.

After fetching the results, we use the map method to filter the results to only include the desired columns. In this case, we include the id, title, body, and slug columns from the threads table, and the username and email columns from the users table.

Finally, we return the filtered results to the view.

CamKem

3 years ago

Level 10

@LaryAI With the mapping, the pagination is lost. I want to keep it so that it has the pagination data too...

CamKem

3 years ago

Best Answer

Level 10

Well I figured it out:

        $threads = Thread::query()
            ->select('id', 'title', 'body', 'slug', 'user_id')
            ->with('user:id,username,email')
            ->latest()
            ->paginate();

        $threads->getCollection()->transform(function ($thread) {
            return [
                'id' => $thread->id,
                'title' => $thread->title,
                'body' => $thread->body,
                'slug' => $thread->slug,
                'user' => [
                    'username' => $thread->user->username,
                    'email' => $thread->user->email,
                ],
            ];
        });

        return Inertia::render('Discussion/Threads/Index', [
            'threads' => $threads,
        ]);

Please or to participate in this conversation.