How can I query on data in the json field

[afarral]

Hi I am getting all the modifications using

$changes = Modification::with('modifiable')->get();

I am using your package for a HR System, so the records that are being added are Absence related, my modal includes an employee_id field which tells me which employee the records is for, this is therefore in the json field, i.e

"modifications" => "{"note": {"modified": null, "original": null}, "reason": {"modified": null, "original": null}, "enddate": {"modified": "14-05-2020", "original": null}, "duration": {"modified": "1", "original": null}, "startdate": {"modified": "14-05-2020", "original": null}, "employee_id": {"modified": "6", "original": null}, "duration_unit": {"modified": "Hour(s)", "original": null}, "absence_code_id": {"modified": "2", "original": null}}

I have tried without success to modify the code used to get all to add a query to return just the modifications where employee_id = $myValue.

I wondered if this is actually possible and if so any pointers,i have looked at jsonwhere but could not get it to work e.g

 $absence->modifications()->whereJsonContains('employee_id', '$employee_id')->get();

or would the better option be to add a new field to the modifications table and try to populate by trying to edit your package.

Many Thanks in advance for any help you can give Adam

[stephenlake]

I haven't really catered for queries against the JSON. However you should be able to achieve what you're trying to do using the JSON queries as you've illustrated except your example is not querying a JSON column, you should change your query to something like:

- >whereJsonContains('modifications->employee_id', $employee_id)->get();

Since the modifications column is the JSON data. The relation name and the column name being the same do make it a little confusing, I admit that.

P.S. I welcome pull requests to improve the package.

[afarral]

Thanks you very much for the pointers, I decided that querying the json fields wont work, as then a records is updated, the diff removes the fields that i would need. So i am looking to add a new field in modifications table: modifiable_parent_id and modifiable_parent_type I believe i need to populate these fields in the captureSave function

class Absence extends Model

    public function employee()    
    {
        return $this->belongsTo('\Modules\HRModule\Entities\Employee','employee_id','employee_id'); 
    }

It was silly to have emploee_id as same name in both in retrospect!!

In captureSave I can access and save using:

        $modification->modifiable_parent_id = $item->employee->employee_id;
        $modification->modifiable_parent_type = get_class($item->employee); 

but ideally i want it to be generic, any ideas? I realise i am asking lot of you, many thanks Adam

[maggz69]

I dont get why you're adding the modifiable_parent_id and modifiable_parent_type to the original migrations. Is all you want to do seearch the json fields?

[stephenlake]

Closing due to inactivity, additionally this is more of an implementation issue than package.