ray ray - 7 months ago 287
MySQL Question

Laravel sql search json type column

The mysql has table 'subscribe' table and it's as follows:

column type
id int
condition json
type_id id

and the example as follows:

"id": "1",
"condition": "{\"id\":\"2\",\"class\":\"master\",\"zone\":\"west\",\"price\":\"511\"}",
"type_id": "1"

and I want to select the column which match in the column condition like "zone"="west"
the laravel 5.2 has support the order

$subscribe = DB::table('subscribe')->where('condition->class', 'master')->get();


Column not found: 1054 Unknown column 'condition->class' in 'where clause'(
SQL: select * from `subscribe` where `condition->class` = master)

I need is get the term which match the condition where conditon->class = master .I need to select the data which match the need in the model.
I don't know what's wrong. Any help would be much appreciated.


The thing is the where clause in query is considering as a column name in condition->class and not as a laravel code. you want to extract the value from json. but the query doesn't understand that.

What you need to do is pass entire table as it is in json format to the view and then extract in the view.

I suggest do something like this : Here, $subscribe has entire table in json . which you can access it by :

$subscribe = DB::table('subscribe')->all();

return response()->json(array('subscribe' => $subscribe));

Then in the view do:

@if($subscribe->condition->class == 'master')

id      : {{ $subscribe->id }}
type id : {{ $subscribe->type_id }}


Updated Code

//get condition in json
$subscribe = DB::table('subscribe')->select('condition')->get();


// convert json to array
$subscribe_array = json_decode($subscribe, true);
//  create a new collection instance from the array
$collection_array = collect($subscribe_array);

if($collection_array['class'] == 'master')

//do something


Something like this do the trick