Laravel Eloquent JSON Contains, how to provide WhereIn logic (one of array values)

Question

there is my code:

protected function room_count($room_count)
{
    $query = $this->builder
        ->whereJsonContains('rent_requests.rooms_count', $room_count);

    return $query;
}

There is my filter function. $room_count is array, and for example can be [2,4]. rent_requests.rooms_count is JSON array in MySQL, and it can be for example [2,3]. I need to filter this, to get this advert showed, but whereJSONContains expects that there will be 2 and 4, not 2 or 4. Is there any tricks to make this function work correctly ? Something like json contains whereIn ?) Sorry for my english, im really stuck on this, please help me :)

Donkarnash · Accepted Answer · 2020-12-04 19:29:52Z

14

MySQL and PostgreSQL support whereJsonContains with multiple values:

So if you are using either of the database then you can pass $room_count as the second parameter

// Room Count Filter
protected function room_count($room_count)
{

    return $this->builder
        ->where(function($query) use($room_count){
     
            $query->whereJsonContains('rent_requests.rooms_count', $room_count[0]);
    
            for($i = 1; $i < count($room_count); $i++) {
               $query->orWhereJsonContains('rent_requests.rooms_count', $room_count[$i]);      
            }
    
            return $query;
      });
}

Laravel docs: https://laravel.com/docs/8.x/queries#json-where-clauses

edited Dec 4, 2020 at 19:29

answered Dec 4, 2020 at 17:33

Donkarnash

12.9k5 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

D3V1L Over a year ago

sorry i wrote the wrong version of the function. the original version was exactly that, but it assumes that all array values will be present. but I only need one of the $room_count values present in the JSON Array. i updated the function. is there any way to check, is ONE of $room_count values presented in JSON, not all?

Donkarnash Over a year ago

Does the room_count always have exactly 2 elements/values?

D3V1L Over a year ago

nope, room_count can be [1, 2, 3, 4], its dynamic. its also can be [1, 2, 3, 4] in the database. as for example $room_count can be [2,4] and database json column can be [4, 3]. i need to get this result with my filters. but when i pass 2,4 to json_contains, it expects that 2 and 4 will be at json, not 2 or 4. this is my problem )

Donkarnash Over a year ago

Okay have updated the answer - try and let me know if it works as per your requirement or not

D3V1L · Accepted Answer · 2020-12-04 18:27:59Z

1

but @donkarnash function is slightly changed, here is the final version with this example for those who face the same problem

 // Room Count Filter
protected function room_count($room_count)
{
    return $this->builder
        ->where(function($query) use($room_count) {

            $query->whereJsonContains('rent_requests.rooms_count', $room_count[0]);

            info (count($room_count));

            for($i = 1; $i <= count($room_count) - 1; $i++) {
                $query->orWhereJsonContains('rent_requests.rooms_count', $room_count[$i]);
            }

            return $query;
        });
}

answered Dec 4, 2020 at 18:27

D3V1L

1551 gold badge1 silver badge8 bronze badges

1 Comment

Donkarnash Over a year ago

Good catch - had forgotten to change the variable & column names to match the question context - have updated now

Collectives™ on Stack Overflow

Laravel Eloquent JSON Contains, how to provide WhereIn logic (one of array values)

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related