MySql Comma Separated Column Join In PHP Laravel

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...