CODEWITHSUNDEEP
phplaraveleloquent
Prafulla Kumar Sahu
Prafulla Kumar Sahu

Reputation: 9701

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

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.

Upvotes: 1

Views: 6853

Answers (2)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25926

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); 
    }
 }

Upvotes: 7

JsWizard
JsWizard

Reputation: 1749

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~!! :)

Upvotes: 2

Related Questions