How to use search value contain in array field using eloquent in Laravel

Question

I'm working on laravel array serialize. Below is serialize in controller.

public function CreateSave(CreateTestTopicRequest $request){
    ...code..
    $testtopic->class_room_id = $request->classroom;
    $testtopic->roomno = serialize($request->roomno);
    ...code..
}

Then, roomno will be saved to database like.

a:2:{i:0;s:1:"1";i:1;s:1:"2";}

I would like to get result. For example class_room_id = 1 and roomno only contain in roomno array. I may use command to get all as below.

$testtopics = TestTopic::where('class_room_id',1)->get();

But, I do not know to get record only class_room_id = 1 and roomno contain in array. Any advice or guidance on this would be greatly appreciated, Thanks

Morteza Negahi · Accepted Answer · 2019-08-24 10:16:57Z

2

You can use like search in json fields

TestTopic::where('class_room_id',1)->where('roomno', 'like', '%"id": 1%')->first()

answered Aug 24, 2019 at 10:16

Morteza Negahi

3,5138 gold badges26 silver badges42 bronze badges

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

Comments

MaartenDev · Accepted Answer · 2019-08-24 09:02:10Z

0

When checking for an array of values the whereIn method can be used:

$roomno = 'a:2:{i:0;s:1:"1";i:1;s:1:"2";}';

$testtopics = TestTopic::where('class_room_id',1)
                    ->whereIn('roomno', unserialize($roomno))
                    ->get();

Multiple where statements can be combined by passing an array:

$roomno = 'a:2:{i:0;s:1:"1";i:1;s:1:"2";}';

$users = TestTopic::where([
    ['class_room_id', '=', '1'],
    ['roomno', '=', $roomno],
])->get();

edited Aug 24, 2019 at 9:02

answered Aug 24, 2019 at 8:48

MaartenDev

5,8305 gold badges23 silver badges36 bronze badges

5 Comments

joenpc npcsolution Over a year ago

Dear MaartenDev. Sorry for unclarify question I meant, I would like find class_room_id = 1 and roomno = 2. But roomno is stored as serialized array. E.g. $testtopics = TestTopic::where('class_room_id',1)->whereIn('roomno', 2)->get(); . This is wrong because 'roomno' is not array.

MaartenDev Over a year ago

I don't really understand what type of data you are trying to store. The serialized object is an array. Do you want to use all values of the array? Or do you want multiple where checks? @joenpcnpcsolution

joenpc npcsolution Over a year ago

Dear MaartenDev, I have one row with two clumns data. first column name=class_room_id (integer) and the second is roomno (store serialized data). I would like to find class_room_id = 1 and roomno = 2. If roomno array is 1 ,2. Then, result found but if I'm looking for class_room_id = 1 and roomno = 3. Then I will get null instead.

MaartenDev Over a year ago

So you want to search though the array values in the roomno column? But if the array only contains 1 and 2, why would it return values for 3? @joenpcnpcsolution

joenpc npcsolution Over a year ago

Dear MaartenDev, Yes search contain. If 3 then return NULL.

Collectives™ on Stack Overflow

How to use search value contain in array field using eloquent in Laravel

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related