How to use multiple combination of where, whereJsonDoesntContain and whereJsonContains correctly

Question

PHP Version 7.2.10 Laravel Version 5.6.39

I have something like this:

$statuses = [4, 5, 6, 7, 14, 15, 16, 17, 18, 19];
$notStatues = [8, 9, 10, 11, 12, 13];

$posts = Post::query();

if ( isset( $queryParams['category'] ) ){
    $posts = $posts->where('post_category', '=', $queryParams['category']);
}
if ( isset( $queryParams['language'] ) ){
    $posts = $posts->where('post_language', '=', $queryParams['language']);
}

//  $posts = $posts->jsonNotContains($statuses);

$posts = $posts->whereJsonContains('post_status_ids', $statuses[0])
    ->orWhereJsonContains('post_status_ids',  $statuses[1])
    ->orWhereJsonContains('post_status_ids',  $statuses[2])
    ->orWhereJsonContains('post_status_ids',  $statuses[3])
    ->orWhereJsonContains('post_status_ids',  $statuses[4])
    ->orWhereJsonContains('post_status_ids',  $statuses[5])
    ->orWhereJsonContains('post_status_ids',  $statuses[6])
    ->orWhereJsonContains('post_status_ids',  $statuses[7])
    ->orWhereJsonContains('post_status_ids',  $statuses[8])
    ->orWhereJsonContains('post_status_ids',  $statuses[9]);

$posts->whereJsonDoesntContain('post_status_ids', $notStatues[0])
    ->whereJsonDoesntContain('post_status_ids', $notStatues[1])
    ->whereJsonDoesntContain('post_status_ids', $notStatues[2])
    ->whereJsonDoesntContain('post_status_ids', $notStatues[3])
    ->whereJsonDoesntContain('post_status_ids', $notStatues[4])
    ->whereJsonDoesntContain('post_status_ids', $notStatues[5]);

$posts = $posts->orderBy('post_priority', 'desc')->paginate(10);

return $posts;

What is the better way to implement it, for the current code I am even unable to filter out posts that belong to $notStatuses.

score 5 · Accepted Answer · edited Oct 28 '21 at 09:01

5

You have to group the OR clauses:

$posts = $posts->where(function($query) use($statuses) {
    $query->whereJsonContains('post_status_ids', $statuses[0])
        ->orWhereJsonContains('post_status_ids', $statuses[1])
        ->orWhereJsonContains('post_status_ids', $statuses[2])
        ->orWhereJsonContains('post_status_ids', $statuses[3])
        ->orWhereJsonContains('post_status_ids', $statuses[4])
        ->orWhereJsonContains('post_status_ids', $statuses[5])
        ->orWhereJsonContains('post_status_ids', $statuses[6])
        ->orWhereJsonContains('post_status_ids', $statuses[7])
        ->orWhereJsonContains('post_status_ids', $statuses[8])
        ->orWhereJsonContains('post_status_ids', $statuses[9]);
});

Or using foreach:

 $posts = $posts->where(function($query) use($statuses) {
     foreach($statuses as $status) {
        $query->orWhereJsonContains('post_status_ids', $status); 
    }
 }

edited Oct 28 '21 at 09:01

Prafulla Kumar Sahu

9,321
11
68
105

answered Dec 26 '18 at 14:02

Jonas Staudenmeir

24,815
6
63
109

Thank you for your effort, but there is also `whereJsonDoesntContain`, which I want as `&&` and not `||`, so how I can add that here? – Prafulla Kumar Sahu Dec 26 '18 at 14:06
You don't have to group the `whereJsonDoesntContain()` calls because they are `AND` clauses. See https://stackoverflow.com/a/1241158/4848587. – Jonas Staudenmeir Dec 26 '18 at 14:09
Is there any better way like passing an array of status in to it or something like in stead of checking for each? – Prafulla Kumar Sahu Dec 26 '18 at 14:28
No, that's not possible. You can use a loop: `foreach($statuses as $status) { $query->orWhereJsonContains('video_status_ids', $status); }` – Jonas Staudenmeir Dec 26 '18 at 14:32
1

By the way the first one should be whereJsonContains and rest should be orWhereJsonConatins right? – Prafulla Kumar Sahu Dec 26 '18 at 14:40
1

In this case, the first one can also be `orWhereJsonContains()`. Simplifies the code. – Jonas Staudenmeir Dec 26 '18 at 14:43

score 2 · Answer 2 · edited Oct 28 '21 at 09:00

2

Try like this.

$statuses = array();

$posts = DB::table('posts')
                ->where('user_id', $user_id)
                ->where(function ($query) use($statuses, $user_id) {
                  for($i = 0; $i <= 9; $i++){
                    $query->where('user_id', $user_id)
                     ->WhereJsonContains('post_status_ids', $statuses[$i]);
                    $queryResult[$i] = $query;
                  }
                });


$posts = $posts->orderBy('post_priority', 'desc')->paginate(10);

return $posts;

but if you need to do a query on a per-user basis later, you can use my code.:)

Happy coding~!! :)

edited Oct 28 '21 at 09:00

Prafulla Kumar Sahu

9,321
11
68
105

answered Dec 26 '18 at 14:32

JsWizard

1,663
3
21
48

By the way the first one should be whereJsonContains and rest should be orWhereJsonConatins right? – Prafulla Kumar Sahu Dec 26 '18 at 14:40
@PrafullaKumarSahu, yes right, but if you want to make more flexible code that even if there has more than 10 queries :) – JsWizard Dec 26 '18 at 14:52
Check the Jonas's answer, that I find little bit better looking and also using orWhereJsonConatins, so I am using that. – Prafulla Kumar Sahu Dec 26 '18 at 14:55
@PrafullaKumarSahu, yes try it, that is your choice, but if you need to do a query on a per-user basis later, you can use my code.:) – JsWizard Dec 26 '18 at 14:59
I will not need it per user basis. – Prafulla Kumar Sahu Dec 26 '18 at 15:14

How to use multiple combination of where, whereJsonDoesntContain and whereJsonContains correctly

2 Answers2