{"id":1597,"date":"2024-09-13T19:02:48","date_gmt":"2024-09-13T19:02:48","guid":{"rendered":"https:\/\/debugspot.com\/?p=1597"},"modified":"2024-09-13T19:40:47","modified_gmt":"2024-09-13T19:40:47","slug":"search-comma-separated-values-laravel-2","status":"publish","type":"post","link":"https:\/\/debugspot.com\/blogs\/search-comma-separated-values-laravel-2\/","title":{"rendered":"Search Comma-Separated Values in Laravel: A Step-by-Step Guide"},"content":{"rendered":"<p>Searching for comma-separated values in Laravel can be crucial when dealing with database columns storing multiple values. This scenario is common in applications where a single column contains several items separated by commas. In this guide, we will demonstrate how to use the FIND_IN_SET() function along with Laravel&#8217;s whereRaw() method to perform efficient searches within these comma-separated strings.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/debugspot.com\/blogs\/search-comma-separated-values-laravel-2\/#Understanding_the_Use_Case\" >Understanding the Use Case<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/debugspot.com\/blogs\/search-comma-separated-values-laravel-2\/#Setting_Up_the_Example\" >Setting Up the Example<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/debugspot.com\/blogs\/search-comma-separated-values-laravel-2\/#Implementing_the_Search\" >Implementing the Search<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/debugspot.com\/blogs\/search-comma-separated-values-laravel-2\/#Example_Output\" >Example Output<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/debugspot.com\/blogs\/search-comma-separated-values-laravel-2\/#Summary\" >Summary<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/debugspot.com\/blogs\/search-comma-separated-values-laravel-2\/#You_may_also_find_interesting\" >You may also find interesting:<\/a><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Understanding_the_Use_Case\"><\/span>Understanding the Use Case<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Imagine you have two tables: posts and tags. The posts table contains a tag_id column where multiple tag IDs are stored as a comma-separated list. To find posts associated with a specific tag ID, you&#8217;ll need to leverage the FIND_IN_SET() function.<\/p>\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Setting_Up_the_Example\"><\/span>Setting Up the Example<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Let\u2019s start by outlining the database structure:<\/p>\n<ol>\n<li><strong>Posts Table<\/strong>: Contains columns ID, name, and tag_id.<\/li>\n<li><strong>Tags Table<\/strong>: Contains columns ID and name.<\/li>\n<\/ol>\n<p>In the posts table, the tag_id column might look like this:<\/p>\n<table border=\"1\" cellpadding=\"8\" cellspacing=\"0\">\n<thead>\n<tr>\n<th>ID<\/th>\n<th>Name<\/th>\n<th>tag_id<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>Post1<\/td>\n<td>1,2<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>Post2<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>Post3<\/td>\n<td>2,3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Our goal is to find all posts tagged with the ID 1.<\/p>\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Implementing_the_Search\"><\/span>Implementing the Search<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To locate posts associated with a specific tag ID, you can use the following code snippet:<\/p>\n<pre class=\"highlight-height line-numbers language-javascript\"><code class=\"language-javascript\">\r\n$search_id = 1;\r\n$data = \\DB::table(\"posts\")\r\n    ->select(\"posts.*\")\r\n    ->whereRaw(\"FIND_IN_SET('\".$search_id.\"', posts.tag_id)\")\r\n    ->get();\r\n<\/code><\/pre>\n<p>This code utilizes the FIND_IN_SET() function to search for the $search_id within the tag_id column of the posts table. The whereRaw() method allows for the execution of raw SQL queries within Laravel\u2019s query builder.<\/p>\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Example_Output\"><\/span>Example Output<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Here\u2019s what you can expect as the output from the above query:<\/p>\n<pre class=\"highlight-height line-numbers language-javascript\"><code class=\"language-javascript\">\r\nIlluminate\\Support\\Collection Object\r\n(\r\n    [items:protected] => Array\r\n    (\r\n        [0] => stdClass Object\r\n            (\r\n                [id] => 1\r\n                [name] => Post1\r\n                [tag_id] => 1,2\r\n            )\r\n\r\n        [1] => stdClass Object\r\n            (\r\n                [id] => 2\r\n                [name] => Post2\r\n                [tag_id] => 1\r\n            )\r\n    )\r\n)\r\n<\/code><\/pre>\n<p>This result confirms that the search successfully identifies two posts tagged with the ID 1.<\/p>\n<p>For more detail you can write <a href=\"https:\/\/laravel.com\/\" target=\"_blank\" rel=\"noopener\"><strong>Laravel Documentation<\/strong><\/a><\/p>\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Summary\"><\/span>Summary<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Searching comma-separated values in Laravel can be efficiently achieved using the FIND_IN_SET() function combined with whereRaw(). This method provides a powerful way to query data stored in comma-separated columns, making it easier to manage and search through complex data formats.<\/p>\n<p>By following this guide, you should be able to implement and refine searches for comma-separated values in your Laravel applications effectively.<\/p>\n<h5><span class=\"ez-toc-section\" id=\"You_may_also_find_interesting\"><\/span>You may also find interesting:<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<p><a href=\"https:\/\/debugspot.com\/blogs\/integrate-google-calendar-with-laravel\/\" target=\"_blank\" title=\"Sync Google Calendar with Your Laravel Site: A Comprehensive Guide\" rel=\"noopener\">Sync Google Calendar with Your Laravel Site: A Comprehensive Guide<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Searching for comma-separated values in Laravel can be crucial when dealing with database columns storing multiple values. This scenario is common in applications where a single column contains several items separated by commas. In this guide, we will demonstrate how to use the FIND_IN_SET() function along with Laravel&#8217;s whereRaw() method to perform efficient searches within these comma-separated strings. Understanding the Use Case Imagine you have two tables: posts and tags. The posts table contains a tag_id column where multiple tag IDs are stored as a comma-separated list. To find posts associated with a specific tag ID, you&#8217;ll need to leverage the FIND_IN_SET() function. Setting Up the Example Let\u2019s start by outlining the database structure: Posts Table: Contains columns ID, name, and tag_id. Tags Table: Contains columns ID and name. In the posts table, the tag_id column might look like this: ID Name tag_id 1 Post1 1,2 2 Post2 1 3 Post3 2,3 Our goal is to find all posts tagged with the ID 1. Implementing the Search To locate posts associated with a specific tag ID, you can use the following code snippet: $search_id = 1; $data = \\DB::table(&#8220;posts&#8221;) ->select(&#8220;posts.*&#8221;) ->whereRaw(&#8220;FIND_IN_SET(&#8216;&#8221;.$search_id.&#8221;&#8216;, posts.tag_id)&#8221;) ->get(); This code utilizes the FIND_IN_SET() function to &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"rank_math_lock_modified_date":false,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1597","post","type-post","status-publish","format-standard","hentry","category-laravel"],"acf":[],"_links":{"self":[{"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/posts\/1597"}],"collection":[{"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/comments?post=1597"}],"version-history":[{"count":3,"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/posts\/1597\/revisions"}],"predecessor-version":[{"id":1600,"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/posts\/1597\/revisions\/1600"}],"wp:attachment":[{"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/media?parent=1597"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/categories?post=1597"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/debugspot.com\/blogs\/wp-json\/wp\/v2\/tags?post=1597"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}