MySql comma separated column join in PHP Laravel 5

MySql comma separated column join in PHP Laravel 5

In this post we will give you information about MySql comma separated column join in PHP Laravel 5. Hear we will give you detail about MySql comma separated column join in PHP Laravel 5And how to use it also give you demo for it if it is necessary.

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.

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:

Also see:How to use groupby having with DB::raw in Laravel Query Builder?

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

Hope this code and post will helped you for implement MySql comma separated column join in PHP Laravel 5. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs

For More Info See :: laravel And github

Leave a Comment

Your email address will not be published. Required fields are marked *

41  +    =  42

We're accepting well-written guest posts and this is a great opportunity to collaborate : Contact US