CODEWITHSUNDEEP
laravellaravel-5eloquentmysql-json
David Nathanael
David Nathanael

Reputation: 105

Laravel fetching JSON column MariaDB syntax error

Im trying to fetch some datas depending on the JSON column meta. However, something weirds happen around the -> symbol.

File::whereJsonContains('meta->serie', 'value')->toSql();

output

"select * from `files` where json_contains(`meta`->'$.\"serie\"', ?)"

Here is the error I get

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."serie"', ?)' at line 1 (SQL: select * from files where json_contains(meta->'$."serie"', "check_up"))

Schema

class File extends Model {

    protected $fillable = ['filename', 'mime-type', 'path', 'meta', 'type'];

    protected $casts = [
        'meta' => 'array'
    ];

    const PATH = 'files';

    public function uploadable() {
        return $this->morphTo();
    }

    public function receivable() {
        return $this->morphTo();
    }
}

Controller

class FilesController extends Controller {

    public function download(Request $request) {
        $data = $this->validate($request, [
            'type' => 'required|alpha_dash',
            'meta' => 'sometimes|required',
        ]);

        $search = [
            ['type', $data['type']],
        ];

        if ($request->has('meta')) {
            foreach (json_decode($data['meta']) as $key => $value) {
                $search[] = ["meta->$key", 'like', $value];
            }
        }

        $files = File::where($search)->get();

        return response()->json($files);
    }
}

I tried using a regular where but it throws the same error. Any idea?

Upvotes: 0

Views: 1597

Answers (1)

Seva Kalashnikov
Seva Kalashnikov

Reputation: 4402

Try this:

$data = $request->all();

$query = File::where('type', $data['type']);

if ($request->has('meta')) {
    foreach (json_decode($data['meta']) as $key => $value) {
        $query->whereRaw("JSON_CONTAINS(meta, '\"{$value}\"', '$.{$key}')");    
    }
}

$files = $query->get();

Upvotes: 1

Related Questions