DB Query inside a foreach loop is making my application slow

The code bellow is used to show a table with 15 results in a page. For this, i’m using two different databases, one is a wordpress database, the other is a personal database i created.

The first Query is used to get the table values from the wordpress database, but it doesn’t get the user name, since wordpress only stores the user ID in that table. The only place where i have the correct user name is on my second personal database.

To do this, i used foreach loop to replace the IDs for the user names. So, inside the loop, there’s a new query used to get the user names.

My problem is that, every time a load the page, i’m running 16 DB queries at once, so it makes 16 database requests, and this is making my page slow.

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 is very simple, but i can’t come out with a strategy on how to put the second DB Query outside the loop, avoiding all that unecessary database requests.

Pls, help.

Answer

That’s the answer i was expecting 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) );

}