Sample data:

13 => {#531 ▼
        +"id": 956
        +"user_type": "App\User"
        +"user_id": 35
        +"event": "updated"
        +"auditable_type": "App\Video"
        +"auditable_id": 136
        +"old_values": "{"video_status_ids":"[4, 16]"}"
        +"new_values": "{"video_status_ids":"[5,16]"}"
        +"url": "http://example.com?vhjb6gsyyas"
        +"ip_address": "106.51.36.44"
        +"user_agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36"
        +"tags": null
        +"created_at": "2019-01-14 11:53:09"
        +"updated_at": "2019-01-14 11:53:09"
      }

Code:

 $audits = DB::table('audits')->where('auditable_type', 'LIKE', 'App%Video')->where('new_values', 'LIKE', '{"video_status_ids":"%')->where(function($query) {
        $query->whereJsonDoesntContain('old_values->video_status_ids', '4')
            ->WhereJsonDoesntContain('new_values->video_status_ids', '5');
    })->paginate(200);

SQL query logged in the log file is:

array (
    'query' => 'select * from `audits` where `auditable_type` LIKE 'App%Video' and `new_values` LIKE '{"video_status_ids":"%'and (not json_contains(`old_values`->\'$."video_status_ids"\', '"[1]"') and not json_contains(`new_values`->\'$."video_status_ids"\', '"[2]"')) and (not json_contains(`old_values`->\'$."video_status_ids"\', '4') or not json_contains(`new_values`->\'$."video_status_ids"\', '5') and not json_contains(`old_values`->\'$."video_status_ids"\', '7') or not json_contains(`new_values`->\'$."video_status_ids"\', '8') and not json_contains(`old_values`->\'$."video_status_ids"\', '10') or not json_contains(`new_values`->\'$."video_status_ids"\', '11') and not json_contains(`old_values`->\'$."video_status_ids"\', '13') or not json_contains(`new_values`->\'$."video_status_ids"\', '14') and not json_contains(`old_values`->\'$."video_status_ids"\', '16') or not json_contains(`new_values`->\'$."video_status_ids"\', '17')) limit 200 offset 0',
    'bindings' => 
    array (
      0 => 'App%Video',
      1 => '{"video_status_ids":"%',
      2 => '"[1]"',
      3 => '"[2]"',
      4 => '4',
      5 => '5',
      6 => '7',
      7 => '8',
      8 => '10',
      9 => '11',
      10 => '13',
      11 => '14',
      12 => '16',
      13 => '17',
    ),

There is a pattern of combinations based on which records I want to get. Like the code above, I want records wherein "old_values->video_status_ids do not contain 4 and new_values->video_status_ids do not contain 5", if there are other combinations instead of 4-5 like 4-6 or 4-7, they should still get retrieved.

Update In the raw query it will be something like:

SELECT * FROM audits WHERE JSON_CONTAINS(new_values->>'$.video_status_ids', '5') AND NOT JSON_CONTAINS (old_values->>'$.video_status_ids', '4') 

Now I want it in the laravel query builder.

0 Answers