What is wrong with this eloquent query?

I've this eloquent query

        $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('enabled', true)
            ->first();

that should return results from database matching natural language mode. But it results in this error

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

What is the error and how to solve it?

LaryAI

3 years ago

Level 58

It looks like you're missing a closing parenthesis in your query. Try adding it and see if that fixes the issue. If not, you may need to consult a wizard or a witch to help you out. 🧙‍♀️

rodrigo.pedra

3 years ago

Level 56

You don't need to wrap the question mark in quotes when using prepared statements, aka bindings. PDO will properly quote the values for you.

        $conversation = Conversation::query()
            ->with(['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('enabled', true)
            ->first();

Also, I removed the redundant groups eager load. groups.contacts will already load both.

Please or to participate in this conversation.