Skip to content

Instantly share code, notes, and snippets.

@sileence
Last active October 26, 2023 06:54
Show Gist options
  • Save sileence/45b3a2cd7d671f3e34b9fed9539f5fda to your computer and use it in GitHub Desktop.
Save sileence/45b3a2cd7d671f3e34b9fed9539f5fda to your computer and use it in GitHub Desktop.
Trait to detect repeated queries (i.e. N+1 problem) inside unit tests
<?php
namespace Tests;
use Illuminate\Support\Facades\DB;
trait DetectRepeatedQueries
{
public function enableQueryLog()
{
DB::enableQueryLog();
}
/**
* Assert there are not repeated queries being executed.
*/
public function assertNotRepeatedQueries()
{
$queries = array_column(DB::getQueryLog(), 'query');
$selects = array_filter($queries, function ($query) {
return strpos($query, 'select') === 0;
});
$selects = array_count_values($selects);
foreach ($selects as $select => $amount) {
$this->assertEquals(
1, $amount, "The following SELECT was executed $amount times:\n\n $select"
);
}
}
public function flushQueryLog()
{
DB::flushQueryLog();
}
}
@browner12
Copy link

is the idea to use this to identify inefficiencies?

@sileence
Copy link
Author

sileence commented Jul 12, 2017

@browner12 yes, to detect possible N+1 problems in your application, you can use this in your tests like so:

        $this->enableQueryLog();
        
        $this->get('posts');

        $this->assertNotRepeatedQueries();

So for example if you forgot to use eager loading in your posts list, and you are loading the same category 10 times, the trait will warn you :D

@decadence
Copy link

Great. Thanks for sharing.
By the way Laravel Debugbar also shows duplicated queries. But only number of them without showing exact repeated SQL.

@decadence
Copy link

Is this comparing query with bindings or it considers query as repeat even if it has other params?

@sileence
Copy link
Author

@decadence: even if they have difference parameters. Because you can be fetching 37 different teams associated to users, they all will have different IDs but the query is the same and could be fixed with eager loading. (Sorry for the extremely late response).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment