jeudi 12 juillet 2018

Incorrect Syntax adding AND operator to Eloquent Raw query MySQL JSON field

I am trying to figure out the correct syntax to add an AND operator to the following Eloquent Raw query in which I am querying a MySQL (5.7.9) table's JSON field. In doing so, I would like to be able to have case insensitivity capabilities.

After doing initial research as to how to achieve this, I have my code working in a basic way like this:

$user = Users::whereRaw('lower(info_json->"$.full_name") like lower(?)', ["%{$user_name}%"])

But my goal is to add an AND operator to narrow down my results further with a non-JSON varchar column.

I have tried this (and other variations without success):

$user = Users::whereRaw('lower(info_json->"$.full_name") like lower(?)', 'and user_type = admin', ["%{$user_name}%"])

Which gives me an error: "Array to string conversion"

I have also tried:

$user = Users::whereRaw('lower(info_json->"$.full_name") like lower(?) and user_type = admin', ["%{$user_name}%"])

Which give me the following error: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user_type' in 'where clause'"

How can I successfully adjust my syntax to query an additional non-JSON field? Also, is doing a raw query like this the most effective way to do so with Eloquent, MySQL (5.79) and Laravel 5.6?

Thank you for any and all help/direction offered! :)



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire