How To Filter/Search Single Column Multiple Id Stored Laravel

How to Find/Filter/Search Single Column Multiple Id stored Laravel Or you can say Find JSON multiple id store in a single column. In this post, I will teach you how to search IDs that we stored in a single column multiple ids in JSON format.

Stored Multiple Id single-column search data. Using the laravel whereJsonContains() and orwhereJsonContains() method will help us to find data from the database using the laravel query. In this method, we must need to pass an array with multiple ids which ids we want to find or search from the database.

I have Stored Multiple Ids in single column you can see in below database example post column. You will be very clear about your confusion. By the end of this post, you will understand and easy to find JSON stored multiple ids in single column data from the database.

So let's start by understanding the below examples.

SEARCH MULTI ID IN THE POST COLUMN

DATABASE PREVIEW :

You can see below the example first method I have updated an array of ids using the json_encode() function in the database column.

Laravel JSON Data Search/Filter From Database Example

Now I want to filter among these ids of two ids from this column, If these are both ids available in this column it will return data else not.

In this example first, I updated [1,25,52,45,95] in 1st id post column in JSON format. Now I want to filter among two ids from id which is [1,25] if this both id available in the column then it will return that row else nothing.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\User;

class HomeController extends Controller
{
    public function storeMultiId()
    {
        $ids = [1,25,52,45,95];
        
        User::where('id',1)->update(['post' => json_encode($ids)]);

        dd('Multiple Id updated in single (post) column');
    }

    // FIND MULTILE FIND FROM SINGLE COLUMN
    public function findMultipleIdSingleColumns()
    {
        $user = User::whereJsonContains('post',[1,25])->get();
        
        dd($user);
    }
}

SEARCH MULTIPLE IDS FROM MULTIPLE ROWS

DATABASE PREVIEW :

Look at the above database I have two rows where in the 1st id post column I have added [1,25,52,45,95] while 4th id column id have stored in post-column [95,45,145,199,450]. Now I want to search these IDs using the laravel query.

Below the example, I have updated given an array of ids in the 4th id column in the user's table.

Now, I want to search this array [95,52,199] from the user table. If at least a single id will match it will return that row. Here 95 id in booth rows that's why both rows will return thereof no matter about other ids. It will find at least id then another id whether available or not that doesn't matter.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\User;

class HomeController extends Controller
{
    public function storeMultiId()
    {
        $ids = [95,45,145,199,450];

        User::where('id',4)->update(['post' => json_encode($ids)]);

        dd('Multiple Id updated in single (post) column');
    }

    // FIND MULTIPLE ID FROM MULTIPLE ROWS
    public function findMultipleIdMultileColumns()
    {
        $findIds = [95,52,199]; // THESE IDS WANT TO FIND THE SAME COLUMN(POST) IN ALLa ROWS

        $user = User::whereJsonContains('post',$findIds);
        
        foreach($findIds as $id){

            $user->orWhereJsonContains('post',$id);
        }

        $user = $user->get();

        dd($user);

        // THAT BOTH ROWS APPEAR HERE BECAUSE 95 ID HAVING IN BOTH COLUMN
    }
}

Dharmesh Chauhan

(Swapinfoway Founder)

Hello Sir, We are brothers origin from Gujarat India, Fullstack developers working together since 2016. We have lots of skills in web development in different technologies here I mention PHP, Laravel, Javascript, Vuejs, Ajax, API, Payment Gateway Integration, Database, HTML5, CSS3, and Server Administration. So you need our service Please Contact Us

Haresh Chauhan

(Co-Founder)