We will look at using Laravel’s where clause with MySQL functions in this blog post. To assist you comprehend how to use this potent feature, we will delve into its nuances and offer practical examples.

What is Laravel’s where clause?

The where clause in Laravel is a query builder technique that enables you to filter records according to particular criteria. It can be used to retrieve information from your database that fits specific requirements.

Using MySQL methods in the where clause of Laravel

The where clause in Laravel has a number of powerful capabilities, one of which is the ability to make sophisticated queries using MySQL functions. The built-in features of the MySQL database management system are known as MySQL functions. They give you the ability to work with data, carry out calculations, and extract particular data from your database.

Simply send the function as a parameter to the where clause method in Laravel to use MySQL functions in the where clause. The function will then be run on the chosen column by Laravel, and the outcome will be compared to the supplied value.

Example
The MySQL ‘LEFT’ function can be used to extract the first letter of the name column and compare it to “A” if you have a users database and want to obtain users whose names begin with the letter “A.”

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

In this example

• In order to begin constructing a query on the users table, we use the DB::table(“users”) method.
• We utilize a closure to build a subquery inside the where method.
• The raw MySQL method LEFT(name, 1), which extracts the first letter of the name column, is included in the closure using DB::raw.
• The LEFT function’s output is then compared to the letter “A” using the ->where (DB::raw(“LEFT(name, 1)”), ‘=’, ‘A’) command.
• The query is finally run by calling ->get(), which returns all users with names that begin 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();