DEV Community

Cover image for Laravel : Query Comma Separated Column using Eloquent and MySQL find_in_set
Timothy Soladoye
Timothy Soladoye

Posted on • Originally published at github.com

2 1

Laravel : Query Comma Separated Column using Eloquent and MySQL find_in_set

Laravel : Query Comma Separated Column using Eloquent and MySQL find_in_set

Introduction

It is not uncommon to store comma separated values in a mysql column in a Laravel Project

colours : "red,blue,green,yellow,black,white"

users : "77,4,5,688,5454,342,32,332"

tags : "mysql,laravel,css,html"
Enter fullscreen mode Exit fullscreen mode

Querying the Column

MySQL Function FIND_IN_SET() can be used to query

$search;
ModelName::whereRaw("FIND_IN_SET($search,colours)");

ModelName::whereRaw("FIND_IN_SET($search,users)");

ModelName::whereRaw("FIND_IN_SET($search,tags)");
Enter fullscreen mode Exit fullscreen mode

Querying the Column - With Protection against SQL Injection

$search;
ModelName::whereRaw("FIND_IN_SET(?,colours)",[$search]);

ModelName::whereRaw("FIND_IN_SET(?,users)",[$search]);

ModelName::whereRaw("FIND_IN_SET(?,tags)",[$search]);
Enter fullscreen mode Exit fullscreen mode

As Scopes in the Model

    class ModelName extends Model{


        public function scopeContainsTag($query,$tag){
            return $query->whereRaw("FIND_IN_SET(?,tags)",[$tag]);
        } 
    }
Enter fullscreen mode Exit fullscreen mode

You can call the scope while querying the model from a controller

    public function index(Request $request){
        ModelName::containsTag($request->tag_name)->get();
    }
Enter fullscreen mode Exit fullscreen mode

Note

There are other ways of storing data in a column other than using comma separated,
Read Laravel Documentation Array & JSON Casting

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 🕒

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more