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