ItSolutionStuff.com

If we work in big application or project of Laravel Framework, sometimes we require to manage column value into comma separated because we can save memory of database. But if we store comma separated value in column with ids then it's difficult to inner join or left join and get records from another tables.

you can also use this post in your laravel 6, laravel 7, laravel 8, laravel 9, laravel 10 and laravel 11 version.

However, SQL provide FIND_IN_SET() for checking value one by one, that way we can also search from comma separated value. FIND_IN_SET() will help to join with tables and we can simply get data.

In this post, I will give you full example of how to get 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

Ok now you can understand, what types of data we want to get. If we use normal sql query then we can get like as bellow query:

SQL Query:

SELECT
    `myposts`.*, GROUP_CONCAT(tags.name) as tagsname 
FROM `myposts` 
LEFT JOIN `tags` ON FIND_IN_SET(tags.id,myposts.tags) > '0' 
GROUP BY `myposts`.`id`

Now we can convert this sql query into laravel, So we can use FIND_IN_SET() in Laravel like as bellow query example using Laravel Query Builder:

Laravel Query Builder:

$data = \DB::table("myposts")
        ->select("myposts.*",\DB::raw("GROUP_CONCAT(tags.name) as tagsname"))
        ->leftjoin("tags",\DB::raw("FIND_IN_SET(tags.id,myposts.tags)"),">",\DB::raw("'0'"))
        ->groupBy("myposts.id")
        ->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
                    [tagsname] => PHP,Laravel
                )
            [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
                    [tagsname] => PHP
                )
        )
)

I hope it can help you....