how to search comma separated values in laravel

In this article, we’ll explore how to search comma-separated values in Laravel, a common scenario in applications that store multiple values in a single database column. We’ll focus on using the FIND_IN_SET() function in conjunction with Laravel’s whereRaw() method to efficiently find specific values within these comma-separated strings.

Understanding the Use Case

Suppose you have two tables: posts and tags. In the posts table, the tag_id column contains a list of tag IDs separated by commas. Your task is to search for posts associated with a specific tag ID. This is where FIND_IN_SET() comes into play.

Setting Up the Example

Let’s start by defining our database structure:

• Posts Table: Contains columns ID, name, and tag_id.
• Tags Table: Contains columns ID and name.

We want to find all posts that are tagged with the ID 1.

Read More:

Implementing the Search

To find posts tagged with a specific ID, you can use the following code:

  $search_id = 1;
  $data = \DB::table("posts")
      ->select("posts.*")
      ->whereRaw("find_in_set('".$search_id."',posts.tag_id)")
      ->get();          

This code uses the FIND_IN_SET() function to search for the $search_id within the tag_id column of the posts table. The whereRaw() method allows us to execute raw SQL queries within Laravel’s query builder.

Example Output

   Illuminate\Support\Collection Object
    (
        [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [id] => 1
                    [name] => post1
                    [tag_id] => 1,2
                )
    
            [1] => stdClass Object
                (
                    [id] => 2
                    [name] => post2
                    [tag_id] => 1
                )
        )
    )                

This output shows that the search successfully found two posts tagged with the ID 1.

Read More: