Laravel Where Clause with MySQL Function Example

Introduction

In this blog article, we will explore how to use Laravel’s where clause with MySQL functions. We will dive into the intricacies of this powerful feature and provide real-life examples to help you understand its usage.

What is Laravel’s where clause?

Laravel’s where clause is a query builder method that allows you to filter records based on specific conditions. You can use it to retrieve data from your database that meets certain criteria.

Using MySQL functions in Laravel’s where clause

One of the powerful features of Laravel’s where clause is the ability to use MySQL functions to perform complex queries. MySQL functions are built-in functions provided by the MySQL database management system. They allow you to manipulate data, perform calculations, and retrieve specific information from your database.

To use MySQL functions in Laravel’s where clause, you simply need to pass the function as a parameter to the where clause method. Laravel will then execute the function on the specified column and compare the result with the given value.

Example

Assuming you have a users table and you want to retrieve users whose names start with the letter “A,” you can use MySQL ‘LEFT‘ function to extract the first letter of the name column and compare it to “A.”

 $usersWithA = DB::table('users')
    ->where(function ($query) {
        $query->where(DB::raw("LEFT(name, 1)"), '=', 'A');
    })
    ->get();

In this example:

1. We use the DB::table('users') method to start building a query on the users table.
2. Inside the where method, we use a closure to create a subquery.
3. Within the closure, we use DB::raw to include a raw MySQL function, in this case, LEFT(name, 1), which extracts the first letter of the name column.
4. We then compare the result of the LEFT function to the letter “A” using ->where(DB::raw("LEFT(name, 1)"), '=', 'A').
5. Finally, we call ->get() to execute the query and retrieve the users whose names start with “A.”

Real Example:-

  $data = DB::table('addprofiles')
                ->leftJoin('countries', 'addprofiles.country_id', '=', 'countries.country_id')
                ->leftJoin('states', 'addprofiles.state_id', '=', 'states.state_id')
                ->leftJoin('cities', 'addprofiles.city_id', '=', 'cities.city_id')
                ->leftJoin('users', 'addprofiles.user_id', '=', 'users.id')
                ->select('addprofiles.*', 'countries.country_name', 'states.state_name', 'cities.city_name', 'addprofiles.file_pic')
                ->where('addprofiles.country_id', $country_id)
                ->orderBy('id', 'desc')
                ->get();