Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Inquisitive's avatar

Use whereNotIn to check if there is id or not in a specific column

Currently, I am using serverside jquery data table. So, here is the snippet for it.

        if(empty($request->input('search.value'))){
            $posts = Base_voter::offset($start)
            ->limit($limit)
            ->orderBy($order,$dir)
            ->get();
            $totalFiltered = Base_voter::count();
        }else{
            $search = $request->input('search.value');
            $posts = Base_voter::where('name_voter', 'like', "%{$search}%")
            ->orWhere('name_first','like',"%{$search}%")
            ->orWhere('name_last','like',"%{$search}%")
            ->orWhere('home_street_address_1','like',"%{$search}%")
            ->orWhere('mailing_street_address_1','like',"%{$search}%")
            ->orWhere('home_address_city','like',"%{$search}%")
            ->orWhere('mailing_address_city','like',"%{$search}%")
            ->offset($start)
            ->limit($limit)
            ->orderBy($order, $dir)
            ->get();
            $totalFiltered = Base_voter::where('name_voter', 'like', "%{$search}%")
            ->orWhere('name_first','like',"%{$search}%")
            ->orWhere('name_last','like',"%{$search}%")
            ->orWhere('home_street_address_1','like',"%{$search}%")
            ->orWhere('mailing_street_address_1','like',"%{$search}%")
            ->orWhere('home_address_city','like',"%{$search}%")
            ->orWhere('mailing_address_city','like',"%{$search}%")
            ->count();
        }       

But, here i have one column 'deleted_by_org' which store data as in format [1,2,5]. Now, I want to get only those records which is equivalent to where !in_array($org_id,[1,2,5]).

That means, don't fetch this record, if $org_id = 1 or 2 or 5.

0 likes
9 replies
Cronix's avatar

But, here i have one column 'deleted_by_org' which store data as in format [1,2,5].

You mean the deleted_by_org column literally has [1,2,5] as the value? I sure hope not...

Inquisitive's avatar

That is just an example, it might also have [1] or can be even null. Actually, I am trying to store organization id, such as if an organization delete a record, this should not be visible to any member of that organization but should be visible to member of other organization. So, if organization 1 deletes it, delete_by_org will be filled with [1]. Again, if is deleted by org by 2 the previous data will be updated as [1,2]

Cronix's avatar

Those really should be stored in a separate one to many table, where each "delete" is stored as a separate row. You're violating some sql relational database standards, and will make your queries a LOT more complex to work around it.

https://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string

http://makitweb.com/search-value-within-comma-separated-values-mysql/

You will need to write queries using raw statements if you want to continue to do it the way you are. Eloquent only handles stuff that are standard.

Inquisitive's avatar

Ok, how can I handle it with eloquent if i created another one to many table

Inquisitive's avatar

Yeah, that is quite straight forward. But, with another table, i think there should be some relation.

base_record_id | deleted_by_org
12          | 1
12          | 2 
13          | 3
13          | 1

Now, how can I check this, as actually I am retrieving data on base voter table

Cronix's avatar

I don't know your model names, etc., but something like

$ids = [1,2,3];

MainModel::with(['relationship' => function($query) use ($ids) {
    $query->whereNotIn('id', $ids);
}]);

I don't know enough about your app with the information provided to know how to get $ids for you that you want to limit by.

Inquisitive's avatar

I don't know, but it looks like quite opposite scenario. I feels like I require instead lie where $ids does not contain $id

Something like

->where delete_by_org doesnot contain $id

Is there anything in laravel to solve this

staudenmeir's avatar

You could use the BelongsToMany relationship like this:

MainModel::whereDoesntHave(['relationship' => function($query) use($ids) {
    $query->whereIn('id', $ids);
}]);

Please or to participate in this conversation.