query throws error on testing but works on browser

I use pestphp for my test suite and I have this query in a controller method

        $conversation = Conversation::query()
            ->with(['groups', 'groups.contacts'])
            ->selectRaw('*, MATCH (hear) AGAINST (? IN NATURAL LANGUAGE MODE) AS score', [$hear])
            ->whereRaw('MATCH (hear) AGAINST (? IN NATURAL LANGUAGE MODE)', [$hear])
            ->havingRaw('score > ?', [0])
            ->orderByRaw('score DESC')
            ->where('match_message', 0)
            ->where('enabled', 1)
            ->first();

When I run it in browser the query works fine and I get the row returned successfully. but when I run testing in terminal I get this error thrown

  SQLSTATE[HY000]: General error: 1 near "AGAINST": syntax error (SQL: select *, MATCH hear AGAINST (test IN NATURAL LANGUAGE MODE) AS score from "conversations" where MATCH hear AGAINST (test IN NATURAL LANGUAGE MODE) and "match_message" = 0 and "enabled" = 1 having score > 0 order by score DESC limit 1)

any idea what's the problem and how to solve it?

LaryAI

3 years ago

Level 58

It looks like the issue is with the MATCH clause in your query. It's possible that the version of MySQL you're using in your test environment is not compatible with the MATCH clause.

You can try replacing the MATCH clause with a LIKE clause instead. For example:

$conversation = Conversation::query()
            ->with(['groups', 'groups.contacts'])
            ->selectRaw('*, MATCH (hear) AGAINST (? IN NATURAL LANGUAGE MODE) AS score', [$hear])
            ->where('hear', 'LIKE', '%'.$hear.'%')
            ->havingRaw('score > ?', [0])
            ->orderByRaw('score DESC')
            ->where('match_message', 0)
            ->where('enabled', 1)
            ->first();

click

3 years ago

Level 35

but when I run testing in terminal

How do you run this in the terminal? You are running unit tests? Are you using different databases between your "live" app and your tests? Maybe mysql vs sqlite?

sh1r3f

3 years ago

Level 1

the problem was because my testing suite was using sqlite connection that seems like it doesn't support this kind of queries. switched testing connection to MySQL and worked

Please or to participate in this conversation.