Level 12
Just to add to this: Using the DatabaseMigrations trait instead solved the issue, however I'm still curious as to why I was having issues with my Note::search method when using the RefreshDatabase trait
Feb 2, 2018
16
Level 12
No matches from fulltext search index query within laravel test suite
So I have a very simple search method on a "Note" model that looks like this:
public static function search($query)
{
$results = self::whereRaw("Match(name,body) AGAINST('$query')")
->get();
return self::processSearchResults($results, $query);
}
The processSearchResults() just attaches some extra fields onto the results - nothing to worry about.
I'm trying to create a simple unit test which creates 2 notes, and then does a search which should return one of the notes. I've hit a hurdle where my search method is returning an empty collection which only occurs when I use the RefreshDatabase trait (to clear out test data).
My first thoughts was that the data wasn't being persisted to the database but a simple Note::all() call proved that the 2 records were present. My unfinished test looks like this:
public function test_searching_notes_returns_results_with_matches_in_title()
{
// GIVEN 2 note records, one containing the word "PHP" in the title and one doesn't
factory(Note::class, 1)->create([
'name' => "Something PHP Something"
]);
factory(Note::class, 1)->create([
'name' => "Something C# Something"
]);
// WHEN we search for the word PHP
$results = Note::search("PHP");
// THEN it should return the correct note
// This works when I don't use the RefreshDatabase trait, otherwise it's empty
print_r($results->toArray());
}
Any ideas what might be wrong here
Level 1
I have the same issue with RefreshDatabase trait. I can get all the records which is a join between two tables, but when I try to filter the records using full-text search (MATCH (...) AGAINST) it returns nothing in the test. When I removed use RefreshDatabase; from the test it worked fine.
Laravel version: 5.5 Phpunit version: 6.0
Level 12
Interesting to know, it could be an issue with using full-text search within a transaction however I couldn't find anything on Google about that.
Another thought was that it's due to a quirk with mysql full-text search which returns an empty result set if the search term exists in 50%+ records but after trying with a larger data set I can rule that one out.
Level 1
Yes, I spent entire yesterday debugging and investigating this. It works if you use where clause but doesn't like the full-text search. Also can I ask which database connection you use for tests? mine is mysql. maybe it works with sqlite. Anyway it's good to know that I'm not alone!
Level 1
I'm experiencing the same issue with Laravel 5.7 and PHPUnit 7.4, also when using MATCH ... AGAINST with RefreshDatabase. Thank you @joeyrush for the solution using DatabaseMigrations!
Is there a clear reason why the one works and the other doesn't? The docs are sparse.
Level 24
Is the table you are searching in using the MyISAM engine?
Level 1
Nope. InnoDB.
My tests that make use of MATCH ... AGINST work fine if I use the DatabaseMigrations, but the same tests return empty results if I use RefreshDatabase instead.
I'm wondering if this is by design, but I've found no clear docs that specify the difference.
Another possible factor is that this is happening with HTTP tests.
The table rows are created within a PHPUnit class using factories. The SQL query is made within a controller accessed from that class using $response = $this->get('/api/endpoint');
Would that make a difference?
Level 24
Could you create a simple application that lets us reproduce this issue and upload it somewhere?
Level 1
Sure. Try this. List of concerned files and steps to reproduce found in the file RECREATE-ISSUE-README.txt.
https://www.mediafire.com/file/ssaub4cuer34qqy/refreshdbtest.zip/file
Level 1
BTW: Run the PostTest once using the RefreshDatabase trait and the DatabaseMigrations commented out, then again with RefreshDatabase commented out and using the DatabaseMigrations trait.
Level 24
FULLTEXT indexes don't work in transactions: https://dev.mysql.com/doc/refman/en/innodb-fulltext-index.html#innodb-fulltext-index-transaction
If you want to use RefreshDatabase, you have to commit the transaction and then clean up the database yourself:
public function testMatchAgainstViaHttp()
{
factory(Post::class, 50)->create([
'title' => 'Mauris accumsan eros quis efficitur'
]);
factory(Post::class, 7)->create([
'title' => 'Lorem ipsum dolor sit amet'
]);
\DB::commit();
$this->assertCount(57, Post::all());
$response = $this->withHeaders(['Accept' => 'application/json'])
->get('/matchagainst');
$response->assertJsonCount(7);
Post::truncate();
}
12 likes
Level 1
Thank you for the clarification!
Level 19
Thanks dude! I've spent all the afternoon with this issue.
Level 12
Just circling back to this thread because I've stumbled on it a few times via google since I originally posted it..
Thanks @staudenmeir for your suggestion, it's been a life saver!
Just to add one more detail, the manual cleanup step after can be replaced with an easy one-liner to refreshTestDatabase (in recent tests I've been seeding probably 20 or more tables, so this is useful to know)
// Seed your data
// Commit the changes
DB::commit();
// Run the assertions against your fulltext indexes
// And cleanup in one line
$this->refreshTestDatabase();
3 likes
Level 1
Since this is still the top ranked result for this problem. Another solution is to use Illuminate\Foundation\Testing\DatabaseMigrations instead of Illuminate\Foundation\Testing\RefreshDatabase You don't need to worry about manual commit/rollback then as DatabaseMigrations does that.
But this will do a full migration for each test, which means it is a bit slower as mentioned in the docs, so make sure it's only imported on tests that actually need to do it. There may be scenarios where manual commit will work better, didn't play around with it much.
Please or to participate in this conversation.