Hello Dev,

"Occasionally, we encounter scenarios where we need to manage multiple integer IDs within a single column, such as when associating multiple tags with a post in a 'posts' table. Storing these IDs in a single column can help optimize memory usage. While inserting data into such a column is straightforward, querying specific tag IDs from a comma-separated column value using Laravel's query builder may seem challenging.

Fortunately, we can leverage MySQL's predefined function, 'FIND_IN_SET()', to achieve this functionality. By using 'FIND_IN_SET()' within the 'whereRaw()' method of Laravel's query builder, we can effectively search for values within comma-separated column values.

In this tutorial, I'll provide a comprehensive example demonstrating how to search for data within comma-separated column values in MySQL tables. By following this example, you'll gain a clear understanding of how this approach works. First, let's introduce two tables, 'myposts' and 'tags', along with some sample data, as depicted in the screenshot below."

Assuming you have two tables:

myposts: Contains posts with a tags column storing comma-separated tag IDs.
tags: A separate table for managing tags.

Your myposts table might look something like this:

CREATE TABLE myposts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    tags VARCHAR(255) -- Comma-separated tag IDs
);

And your tags table could be structured as follows:

CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

Insert some dummy data into your tables for demonstration purposes:

DB::table('myposts')->insert([
    ['title' => 'Post 1', 'tags' => '1,2'],
    ['title' => 'Post 2', 'tags' => '2,3'],
    ['title' => 'Post 3', 'tags' => '1,3']
]);

DB::table('tags')->insert([
    ['name' => 'Tag 1'],
    ['name' => 'Tag 2'],
    ['name' => 'Tag 3']
]);

To search for posts that contain specific tags, you can use the find_in_set() function within a raw expression in your query. Here's how you can do it in Laravel:

$searchTagIds = [1, 2]; // Array of tag IDs you're searching for

$posts = DB::table('myposts')
    ->select('*')
    ->whereRaw("FIND_IN_SET(?, FIND_IN_SET(tags, ',')) > 0", [$searchTagIds])
    ->get();

$search = 1;
$data = \DB::table("myposts")
    -<select("myposts.*")
    -<whereRaw("find_in_set('".$search."',myposts.tags)")
    -<get();

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [id] => 1
                    [name] => How Title 1
                    [tags] => 1,2
                    [created_at] => 2017-01-06 00:00:00
                    [updated_at] => 2017-01-06 00:00:00
                )
            [1] => stdClass Object
                (
                    [id] => 2
                    [name] => How Title 2
                    [tags] => 1
                    [created_at] => 2017-01-06 00:00:00
                    [updated_at] => 2017-01-06 00:00:00
                )
        )
)

Thank you for your encouragement! If you have any questions or need further assistance, feel free to ask. I'm here to help!