Bdtjtk

PHP 7.2.10

Laravel 5.6.39

  $statuses = [4, 5, 6, 7, 8, 9, 10, 11, 12, 13];

    $notStatuses = [14, 15, 16, 17, 18, 19];



    $videos = Video::query();



    if ( isset( $queryParams['category'] ) ){

        $videos = $videos->where('video_category', '=', $queryParams['category']);

    }

    if ( isset( $queryParams['language'] ) ){

        $videos = $videos->where('video_language', '=', $queryParams['language']);  

    }



    $videos = $videos->where(function($query) use($statuses) {

        foreach($statuses as $status) {

            $query->orWhereJsonContains('video_status_ids', $status);

        }

    });



    $videos = $videos->where(function($query) use($notStatuses) {

        foreach($notStatuses as $status) {

            $query->whereJsonDoesntContain('video_status_ids', $status);

        }

    });





    $videos = $videos->orderBy('video_priority', 'desc')->latest()->paginate(10);



    return $videos;

language and category are optional filters in current condition let's consider language filter, if there is no video with the selected language, even then I will get videos with status 10 and 11,

if I will use whereJsonContains above the filters then also it is giving wrong result, how I should use whereJsonContains, whereJsonDoesNotContains and optional wheres correctly?

Note: may be helpful to get the issue correctly

You can also reverse the situation

 $statuses = [4, 5, 6, 7, 8, 9, 10, 11, 12, 13];

 $notStatuses = [14, 15, 16, 17, 18, 19];

below image showing manual tagger hindi videos, but it can be seen the choosen filters are auto tagger and Indonesian.

Query generated for

http://mysite.dev/admin/tagging-in-progress?_token=d4jQOLDixYSEXhMR2z8OihVPQ2ZKTezJhk3tIWJg&category=manual_tagger&language=maltese&video_status=&submit=

is

select * from `videos` where `video_category` = 'manual_tagger' and `video_language` = 'english' and json_contains(`video_status_ids`, 9) or json_contains(`video_status_ids`, 29)  order by `video_priority` desc, `created_at` desc limit 10 offset 0  

just try this one

$video = $videos->where(function($query) use($statuses) {

        foreach($statuses as $status) {

            $query->orWhereJsonContains('video_status_ids', $status);

        }

    })->where(function($query) use($notStatuses) {

        foreach($notStatuses as $status) {

            $query->whereJsonDoesntContain('video_status_ids', $status);

        }

    })

->orderBy('video_priority', 'desc')->latest()->paginate(10);

try after change :

WhereJsonContains('video_status_ids', $status)

to

whereRaw('JSON_CONTAINS(video_status_ids, "$status")')

But, Best way is normalize DB structure.

Example:

post like table :

+---------+---------+

| user_id | post_id |

+---------+---------+

|       1 |     1,2 |

|       2 |     1,5 |

+---------+---------+

normalize tabel

+---------+---------+

| user_id | post_id |

+---------+---------+

|       1 |       1 |

|       1 |       2 |

|       2 |       1 |

|       2 |       5 |

+---------+---------+

I don't know is it usable in your condition or not. but if is it then normalize table