I'm trying to test a function that tries to fuzzy match a name to a name in the database.
Basically:
match('Peter Joe Pan') should return the record.
The query running on a MySQL db would be
Person::whereRaw('"' . $name . '" like concat(givenName , "%", familyName)')->first();
When I run my test where I test 'Peter Joe Pan', it works.
Recently I changed to using sqlite as a test database. Sqlite doesn't have the concat function, so I modified the function:
Person::whereRaw('"' . $name . '" like givenName || "%" || familyName)')->first();
The test now fails, i.e. the result is NULL.
If I change first() into firstOrFail(), I get an exception.
However, when I log Person::all() I get the "Peter Pan" record.
Also, when I build an sqlite database using Base and I run the query I get the record, i.e. the query is correct.
I'm at a loss what to do. If I cannot solve this problem, the only thing I can think of is switching back to using MySQL as test database.
Any suggestions?
sqlite is good for very, very basic things that are standard across all *sql engines (which is what DB/Eloquent use). It, as you're seeing, falls apart when trying to do anything "custom" (ie using raw statements bc builder doesn't have something like "concat" built-in).
I just use the same db engine for testing as is in production. I feel it's wrong to write tests for one engine while you're actually using another in production. They aren't true 1:1 tests then, so what's the point?
Good point. I'll switch back to MySQL for testing. It was just very convenient because it was fast.
Please or to participate in this conversation.