DB Query inside a foreach loop is making my page slow

Question

The code below is used to show a table with 15 results on a page. For this, I am using two different databases, one of them is a wordpress database and the other is a personal database that I created.

The first query serves to get the values of the wordpress database, but it does not take the usernames, even because wordpress just armezena the user ID in that table. The only place where I cosigo acquire the correct username is in my second personal database.

To do this, I use a foreach loop to replace the Ids with user names. So within the loop, there’s another query to get these user names.

My problem is that every time I upload the page, I’m running 16 db queries at once, and this is making 16 requests in the database, thus making my page slow to load.

public function index() {

    $posts = DB::connection('mysql2')
        ->table('wp_rocketsciencebrposts')
        ->select('ID', 'post_title', 'post_status', 'post_author', 'post_date')
        ->whereIn('post_status', ['publish', 'private'])
        ->where('post_type', 'post')
        ->orderBy('id', 'desc')
        ->paginate(15, ['*'], 'posts');

    $posts = compact('posts');

    foreach($posts['posts'] as &$value){

        //this DB Query is making my page slow, since it's inside this foreach loop, therefore, making 16 database requests
        $value->post_author = DB::connection('mysql')
            ->table('users')
            ->select('name')
            ->where('rsbwordpressid', $value->post_author)
            ->value('name');

    }

    return view('posts/posts', $posts);

}

I’m sure the solution should be simple, but I can’t think of a strategy of how to put the second query out of the foreach loop, thus avoiding all these unnecessary requests to the database.

Help there!

Answer 1

That’s the answer I was hoping to see:

public function index() {

    $posts = DB::connection('mysql2')
    ->table('wp_rocketsciencebrposts')
    ->select('ID', 'post_title', 'post_status', 'post_author', 'post_date')
    ->whereIn('post_status', ['publish', 'private'])
    ->where('post_type', 'post')
    ->orderBy('id', 'desc')
    ->paginate(15, ['*'], 'posts');

    $user_ids = $posts->pluck('post_author')->toArray();

    $users = DB::connection('mysql')
    ->table('users')
    ->whereIn('rsbwordpressid', $user_ids)
    ->pluck('rsbwordpressid', 'name')
    ->toArray();

    $posts = compact('posts');

    $users = array_flip($users);

    foreach($posts['posts'] as $value) {

        $value->post_author = $users[$value->post_author];

    }

    return view('posts/posts', $posts)->with('mensagemSucesso', print_r($users) );

}