ItSolutionStuff.com

Sometimes, we require to manage to take single column for multiple integer ids, for example if we have "posts" table and require to set multiple tags on each post, so at that time we can take just single column and store selected tags ids. this way will use less memory. It is simple to store data into row, but as you think how we can search or find specific tag id data from comma separated column value using laravel query builder.

you can use find_in_set() with query builder in laravel 6, laravel 7, laravel 8, laravel 9, laravel 10 and laravel 11 app.

However, It is possible using "find_in_set()" of MySql predefine function, We can use find_in_set() using whereRow() of laravel query builder. In this example i am going to give you how to find value from comma separated values column from mysql table. I already posted "MySql comma separated column join in PHP Laravel 5" few days ago. here we will see how to search value from comma separated column value.

In this post, I will give you full example of how to search data from comma separated column tables value. You will simply understand, how it is works. First i want to introduce two tables("myposts" and "tags") with some dummy data like as bellow screen shot.

Table : myposts

Table : tags

Now we can simple search records that have "1" id of tags column, so let's see:

Laravel Query Builder:

$search = 1;
$data = \DB::table("myposts")
    ->select("myposts.*")
    ->whereRaw("find_in_set('".$search."',myposts.tags)")
    ->get();

Now you can see output will be like as bellow:

Output:

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [id] => 1
                    [name] => How to install Laravel?
                    [tags] => 1,2
                    [created_at] => 2017-01-06 00:00:00
                    [updated_at] => 2017-01-06 00:00:00
                )
            [1] => stdClass Object
                (
                    [id] => 2
                    [name] => How to work with PHP?
                    [tags] => 1
                    [created_at] => 2017-01-06 00:00:00
                    [updated_at] => 2017-01-06 00:00:00
                )
        )
)

I hope it can help you....