Laravel Query with Function and Where Clause

Laravel Query with Function and Where Clause

To create a Laravel query with a function and a where clause, you can use the db helper function or the query builder. In this example, we will use the query builder to filter records based on a specific condition using a function and a where clause.

Let's assume we have a table named users with the following columns: name, email, age, and created_at. We want to retrieve all users who are older than 25 years and whose name contains the letter 'a'.

First, we will use the query builder to achieve this:

$query = DB::table('users');

// Apply the function (age greater than 25) and where clause (name contains 'a')
$results = $query->where(function($query) {
    $query->where('age', '>', 25)
          ->orWhere(function($query) {
              $query->where('name', 'like', '%a%');
          });
})->get();

// Output the results
dd($results);

In the above example, we used the where function with a closure to apply both conditions using the orWhere method.

Now, let's create a custom function to calculate the user's full name and retrieve users whose full name contains the letter 'a':

// Define a custom function to calculate the full name
function getFullName($name, $last_name)
{
    return $name . ' ' . $last_name;
}

// Use the query builder with the custom function and where clause
$query = DB::table('users');

// Apply the custom function and where clause
$results = $query->where(function($query) {
    $query->where(function($query) {
        $query->select(DB::raw('concat(name, " ", last_name) as full_name'))
             ->where('full_name', 'like', '%a%');
    })
    ->orWhere('age', '>', 25);
})->get();

// Output the results
dd($results);

In this example, we defined a custom function getFullName to calculate the full name by concatenating the name and last_name columns. We then used the select method with the DB::raw helper function to select the calculated full_name column and applied the where clause to filter records based on the custom column.

Both examples demonstrate how to create a Laravel query with a function and a where clause using the query builder.