In this blog,we explore how Laravel Eloquent and Query Builder make database interactions seamless. Whether you’re fetching data, filtering records, or performing complex joins, MySQL functions play a crucial role in optimizing your Laravel applications.
We’ll cover essential functions like Functions for Conditions, Functions for Date/Time, Functions for Strings, Functions for JSON —all with practical Laravel examples. If you’re a web developer looking to enhance your database skills, this guide will help you write efficient queries, improve performance, and make the most of MySQL in Laravel. Stay ahead in the world of web development with these must-know techniques!
Functions for Conditions
IN (Checking Against a List)
IN checks if a value matches any item in a list or group, like a quick way to compare.
User::whereIn('country_id', [33,43,55,74,78,83,84,90])->where('is_admin', 0)->pluck('id')->toArray();
Get the IDs of users who are not admins and belong to specific countries.
whereHas& whereNotIn (Excluding Values from a List)
whereHas filters results based on a related model, while code>whereNotIn excludes results matching values in a list.
$users = User::whereHas('roles', function ($query) {
$query->where('name', '!=', 'admin'); // Example condition for relation
})
->where('status', 'active') // Example condition for filtering users
->whereNotIn('role', ['guest', 'banned', 'suspended']) // Excluding specific roles
->orderBy('role', 'ASC')
->get();
This query:
– Filters users who have a related roles record (excluding “admin”).
– Filters only users with status = 'active'.
– Excludes users with specific roles (guest, banned, suspended).
– Orders by role first, then by created_at.
COUNT() & BETWEEN (Filtering in a Range)
COUNT() counts items in a set, and BETWEEN filters values within a specified range.
$userCount = User::whereBetween('created_at', [Carbon::parse('2024-09-04'), Carbon::parse('2025-03-01')])
->where('role', '!=', 'Admin')
->count()
This query counts the number of users
– Filters users created between September 4, 2024, and March 1, 2025.
– Excludes users with the role “Admin”.
when (Conditional Queries)
When in conditional queries runs a condition and applies logic only if that condition is met.
$usersQuery = DB::table('users')
->select('id', 'name', 'email', 'country_id') // Selecting specific fields from the users table
->when($request->name, function ($query, $name) {
return $query->where('name', 'LIKE', "%{$name}%"); // If a name is provided, filter users whose name contains the search term
})
->when($request->email, function ($query, $email) {
return $query->where('email', 'LIKE', "%{$email}%"); // If an email is provided, filter users whose email contains the search term
})
->get()
->toArray();
This query:
– Fetching Users: This query retrieves users from the users table.
– Selecting Specific Fields: It only selects id, name, email, and country_id instead of fetching all columns.
– Filtering by Name (Optional): If $request->name is present, it filters users whose name partially matches the given value.
– Filtering by Email (Optional): If $request->email is present, it filters users whose email partially matches the given value.
– Retrieving Data: Finally, it fetches the filtered list of users as an array.
IF (Conditional Queries)
IF in queries checks a condition and returns a value based on whether it’s true or false.
$query = User::query()
->select([
'users.name',
('admin' || 'editor') ? 'users.role' : DB::raw('COUNT(DISTINCT users.role) as roles_count'),
DB::raw('COUNT(users.id) as total_users'),
DB::raw('SUM(IF(users.is_admin = 1, 1, 0)) as admin_count'),
DB::raw('SUM(IF(users.role = "editor", 1, 0)) as editor_count'),
DB::raw('SUM(IF(users.role = "subscriber", 1, 0)) as subscriber_count'),
DB::raw('SUM(IF(users.is_active = 1, 1, 0)) as active_users'),
DB::raw('SUM(IF(users.is_active = 0, 1, 0)) as inactive_users'),
])
->leftJoin('companies', 'companies.id', '=', 'users.company_id')
->whereHas('posts') // Assuming users have posts
->groupBy('users.name')
->get();
This query:
– Conditional Column Selection → If 'admin' or 'editor' exists, show role; otherwise, count distinct roles.
– Role-Based Summation → Counts total admins, editors, subscribers.
– Active/Inactive Users → Counts users based on their is_active status.
– Uses whereHas() → Ensures only users who have posts are included.
– Uses groupBy() → Groups results by user name for better aggregation.
IFNULL (Conditional Queries)
IFNULL checks if a value is NULL; if so, it returns a default value instead.
$query = User::query()
->select([
'users.id',
'users.name',
'users.email',
DB::raw('IFNULL(users.phone, "No Phone Provided") as phone_number'),
DB::raw('IFNULL(profiles.bio, "No Bio Available") as user_bio'),
DB::raw('COUNT(posts.id) as total_posts'),
DB::raw('SUM(IF(users.is_active = 1, 1, 0)) as active_users'),
DB::raw('SUM(IF(users.is_verified = 1, 1, 0)) as verified_users'),
])
->leftJoin('profiles', 'profiles.user_id', '=', 'users.id')
->leftJoin('posts', 'posts.user_id', '=', 'users.id')
->groupBy('users.id')
->orderBy('users.name', 'asc')
->get();
This query:
– Uses IFNULL() → If phone is NULL, replace it with "No Phone Provided".
– Handles Profile Data → If bio is NULL, replace it with "No Bio Available".
– Counts Related Posts → Calculates the total number of posts per user.
– Checks Active & Verified Status → Counts how many users are active and verified.
– Uses leftJoin() → Ensures profile and post data are included.
– Groups Data by users.id → Prevents duplicate user rows.
CASE – (Complex Conditional Logic)
CASE is like an “if-else” for databases. It checks conditions and returns different values based on them.
User::select(
'users.*',
'companies.country_id',
DB::raw("IF(users.company_id IS NULL, NULL, IFNULL(companies.name, users.company_name)) as company_name"),
DB::raw("(CASE
WHEN EXISTS (
SELECT 1
FROM user_restrictions
WHERE user_restrictions.pages = 'Users'
AND users.created_at BETWEEN user_restrictions.start_date AND user_restrictions.end_date
)
THEN TRUE
ELSE FALSE
END) as locked")
)
->leftJoin('companies', 'companies.id', '=', 'users.company_id')
->get()->toArray();
This query:
– Retrieves all users with their company’s country.
– Displays the correct company_name, handling null cases.
– Checks if a user is “locked” based on a user_restrictions table.
– Calculates salary_with_bonus by adding a percentage-based bonus to the user’s salary.
EXISTS – Check If a Subquery Returns Any Rows
EXISTS checks if a subquery returns any rows. If it does, the main query runs.
$query = User::query()
->select([
'users.id',
'users.name',
'users.email',
DB::raw('(EXISTS (
SELECT 1
FROM orders
WHERE orders.user_id = users.id
LIMIT 1
)) as has_orders'),
DB::raw('COUNT(orders.id) as total_orders')
])
->leftJoin('orders', 'orders.user_id', '=', 'users.id')
->groupBy('users.id')
->orderBy('users.name', 'asc')
->get();
This query:
– Uses EXISTS() → Checks if at least one order exists for the user.
– Returns has_orders as true/false → Efficient way to check if a user has orders.
– Counts Orders → Includes the total number of orders per user.
– Uses leftJoin() → Ensures users without orders are still included.
– Uses groupBy() → Prevents duplicate user rows.
NOT EXISTS – Check If No Rows Exist
NOT EXISTS checks if a subquery returns no rows, meaning the condition inside doesn’t match any data.
$query = User::query()
->select([
'users.id',
'users.name',
'users.email',
DB::raw('(NOT EXISTS (
SELECT 1
FROM orders
WHERE orders.user_id = users.id
LIMIT 1
)) as has_no_orders'),
DB::raw('COUNT(orders.id) as total_orders')
])
->leftJoin('orders', 'orders.user_id', '=', 'users.id')
->groupBy('users.id')
->orderBy('users.name', 'asc')
->get();
This query:
– Uses NOT EXISTS() → Returns true if no orders exist for a user.
– Returns has_no_orders as true/false → Indicates whether a user has any orders.
– Counts Total Orders → Even users with no orders are included.
– Uses leftJoin() → Ensures users without orders remain in the result.
– Uses groupBy() → Prevents duplicate user rows.
LEAST() – Get the Minimum Value Among Multiple Values
The LEAST() function returns the smallest value from a set of values you provide.
$query = User::query()
->select([
'users.id',
'users.name',
'users.email',
DB::raw('LEAST(users.age, users.experience_years, users.membership_duration) as min_value'),
DB::raw('COUNT(posts.id) as total_posts'),
DB::raw('IF(users.is_active = 1, "Active", "Inactive") as status')
])
->leftJoin('posts', 'posts.user_id', '=', 'users.id')
->groupBy('users.id')
->orderBy('min_value', 'asc')
->get();
This query:
– Uses LEAST() → Finds the minimum value among age, experience_years, and membership_duration.
– Includes User Info → Fetches name, email, and status.
– Counts Posts → Calculates the total number of posts per user.
– Uses IF() → Converts is_active into "Active" or "Inactive".
– Sorts by min_value → Arranges users by their smallest value.
Functions for Date/Time
NOW() Get the Current Date & Time and CURDATE() Get the Current Date
$query = DB::table('users')
->select([
'users.id',
'users.name',
'users.email',
DB::raw('NOW() as current_datetime'), -- Fetches current date and time
DB::raw('CURDATE() as current_date'), -- Fetches only the current date
DB::raw('DATEDIFF(CURDATE(), users.created_at) as days_since_joined') -- Calculates days since the user joined
])
->where('users.status', 'active')
->get();
This query:
– NOW() → Fetches the full date and time (YYYY-MM-DD HH:MM:SS).
– CURDATE() → Fetches only the date (YYYY-MM-DD).
– (CURDATE(), users.created_at) → Calculates how many days a user has been registered.
This query helps track active users along with when they joined.
DATEDIFF() and TIMEDIFF()
You have a users table with created_at (account creation time) and last_login (last time they logged in). You want to:
1. Find the number of days since the user created the account (DATEDIFF).
2. Find the time difference between their last login and midnight of the same day (TIMEDIFF).
$query = User::query()
->select([
'id',
'name',
'created_at',
'last_login',
DB::raw('DATEDIFF(NOW(), created_at) as days_since_signup'),
DB::raw('TIMEDIFF(last_login, CURDATE()) as login_time_difference')
])
->orderBy('days_since_signup', 'DESC')
->get();
– DATEDIFF(NOW(), created_at)</>→ Counts the number of days since the account was created.
– TIMEDIFF(last_login, CURDATE())→ Calculates the time difference between last_login and midnight of the current date.
– Sorting by days_since_signup→ Users who signed up the earliest appear first.
ADDDATE() – Add Days to a Date
Adds a specific number of days to a date.
SELECT ADDDATE('2025-03-27', INTERVAL 10 DAY); -- Output: 2025-04-06
SUBDATE() – Subtract Days from a Date
Subtracts a specific number of days from a date.
SELECT SUBDATE('2025-03-27', INTERVAL 5 DAY); -- Output: 2025-03-22
Functions for Strings
CONCAT() – Combine Strings
The CONCAT() function joins two or more strings together into one string.
$users = DB::table('users')
->select('*', DB::raw("GROUP_CONCAT(CONCAT('\"', name, '\"') SEPARATOR ', ') as <code>names<code>"))
->where([
'status' => false,
'deleted_at' => null
])
->groupBy('role_id') // Assuming you want to group by <code>role_id<code>, change as needed
->get()
->toArray();
LENGTH() – Get String Length
The LENGTH() function in SQL returns the number of characters in a string, counting spaces and special characters.
$users = DB::table('users')
->select('id', 'name', DB::raw("LENGTH(name) as name_length"))
->get()
->toArray();
This query:
– Selects id and name from the users table.
– Uses LENGTH(name) to get the number of bytes in each user’s name.
– Returns the result as an array.
LOWER() / UPPER() – Change Case
$users = DB::table('users')
->select('id', 'email',
DB::raw("LOWER(name) as lowercase_name"),
DB::raw("UPPER(name) as uppercase_name")
)
->get()
->toArray();
SUBSTRING() / SUBSTR() – Extract a Part of a String
SUBSTRING() or SUBSTR() extracts a specific portion from a string, starting at a given position for a certain length.
$users = DB::table('users')
->select('id', 'name', DB::raw("SUBSTRING(name, 1, 5) AS short_name"))
->get()
This query:
– Retrieves id and name from the users table.
– Uses SUBSTRING(name, 1, 5) to get the first 5 characters of each user’s name.
– Returns the result as short_name.
LOCATE() / POSITION() – Find Position of a Substring
$users = DB::table('users')
->select('id', 'name', DB::raw("LOCATE('a', name) as position"))
->get()
->toArray();
TRIM() – Remove Spaces
The TRIM() function removes extra spaces from the beginning and end of a text string.
$users = DB::table('users')
->select('id', DB::raw("TRIM(name) as trimmed_name"))
->get()
->toArray();
REPLACE() – Replace Part of a String
Replaces a substring within a string.
SELECT REPLACE('Hello World', 'World', 'MySQL') AS replaced;
-- Output: Hello MySQL
REVERSE() – Reverse a String
Flips the string backward.
SELECT REVERSE('Laravel') AS reversed;
-- Output: levaraL
FORMAT() – Format Number with Commas
Formats numbers with commas and decimal places.
SELECT FORMAT(1234567.89, 2) AS formatted; -- Output: 1,234,567.89
FIELD() – Find Position in a List
The FIELD() function in SQL returns the position of a value in a list of values.
$users = DB::table('users')
->select('id', 'name', DB::raw("FIELD(role, 'admin', 'editor', 'subscriber') AS role_position"))
->orderBy('role_position')
->get();
This query:
1. FIELD(role, 'admin', 'editor', 'subscriber')
– This assigns a position based on the order in the list:
– admin → Position 1
– editor → Position 2
– subscriber → Position 3
2. orderBy('role_position')
– Sorts users based on their role’s position.
Functions for JSON
JSON_OBJECT() – Create a JSON Object
Converts key-value pairs into a JSON object.
SELECT JSON_OBJECT(
'id', users.id,
'name', users.name,
'email', users.email,
'role', users.role
) AS user_json
FROM users
WHERE users.id = 1;
Output
{
"id": 1,
"name": "jerry Damodar",
"email": "john@example.com",
"role": "admin"
}
JSON_ARRAY()/JSON_ARRAYAGG() – Create a JSON Array
Difference Between JSON_ARRAYAGG() and JSON_ARRAY()
1 JSON_ARRAY(email) Returns a JSON Array for Each Row
If you use JSON_ARRAY(email), MySQL treats email as a single value and wraps it inside an array for each row.
SELECT id, name, JSON_ARRAY(email) AS user_emails FROM users;
Output:
Each row gets a separate JSON array with only one email inside it
2 JSON_ARRAYAGG(email) Combines All Emails into One JSON Array
If you want one single JSON array containing all emails, you should use JSON_ARRAYAGG(email)
SELECT JSON_ARRAYAGG(email) AS user_emails FROM users;
Output (Single Row):
["madhav@example.com", "damodar@example.com", "govid@example.com"]
All emails are combined into one JSON array.
JSON_EXTRACT() – Get Value from JSON
Extracts values from a JSON column using path expressions.
$users = User::select('id', 'name',
DB::raw("JSON_EXTRACT(metadata, '$.preferences.theme') AS theme")
)->get();
Let’s suppose we store the data in the metadata column in the following JSON format:
like this
'{"preferences": {"theme": "dark", "language": "English"}}'
This query:
DB::raw("JSON_EXTRACT(metadata, '$.preferences.theme') AS theme")
– Uses raw SQL inside Laravel to run a MySQL JSON function.
– JSON_EXTRACT(metadata, '$.preferences.theme'):
Extracts the value of the theme key inside the preferences object in the metadata JSON column.
– AS theme: Renames the extracted value as theme in the output.
Output
[
{ "id": 1, "name": "Alice", "theme": "dark" },
{ "id": 2, "name": "Bob", "theme": "light" }
]
JSON_UNQUOTE() – Remove Quotes from JSON Output
removes double quotes from JSON-extracted values.
$users = User::select('id', 'name')
->whereRaw("JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.preferences.theme')) LIKE ?", ['%dark%'])
->get();
You have a users table with a metadata column storing JSON data, and you want to find users where their preferred theme contains “dark”.
This query:
– select('id', 'name') → Fetches only id and name, excluding other columns.
– whereRaw(...) → Uses MySQL JSON functions inside WHERE:
– JSON_EXTRACT(metadata, '$.preferences.theme') → Extracts theme value.
- JSON_UNQUOTE(...) → Removes double quotes/ from the JSON output.
- LIKE '%dark%' → Searches for any theme containing "dark" (e.g., "dark-mode", "dark-theme").
JSON_SET() – Update/Add a JSON Key-Value
Modifies a JSON field by setting a value at a given path.
Example: Update the user’s preferred language
UPDATE users SET metadata = JSON_SET(metadata, '$.preferences.language', 'English') WHERE id = 1;
Before:
{"preferences": {"theme": "dark"}}
After:
{"preferences": {"theme": "dark", "language": "English"}}
JSON_REMOVE() – Remove a Key from JSON
Deletes a key-value pair from a JSON field.
Example: Remove ‘language’ from user preferences
UPDATE users SET metadata = JSON_REMOVE(metadata, '$.preferences.language') WHERE id = 1;
Removes the language key from JSON data
JSON_CONTAINS() – Check If JSON Contains a Value
Checks if a JSON array contains a specific value.
Example: Check if a user has ‘dark mode’ enabled
SELECT JSON_CONTAINS(metadata, '"dark"', '$.preferences.theme') AS has_dark_mode FROM users WHERE id = 1;
Output: 1 (true) if "dark" exists.
JSON_LENGTH() – Count Elements in JSON
Counts the number of elements in a JSON object or array.
Example: Count the number of preferences a user has
SELECT JSON_LENGTH(metadata->'$.preferences') AS preference_count FROM users WHERE id = 1;
JSON_KEYS() – Get All Keys of a JSON Object
Returns an array of all keys inside a JSON object.
Example: Get all preference keys for a user
SELECT JSON_KEYS(metadata->'$.preferences') AS keys FROM users WHERE id = 1;
Output: ["theme", "language"]
JSON_MERGE_PRESERVE() – Merge Two JSON Objects
Combines JSON objects while keeping all values.
Example: Merge two JSON objects
SELECT JSON_MERGE_PRESERVE(
'{"name": "John", "age": 30}',
'{"city": "New York", "age": 32}'
) AS merged_json;
Output:
{"name": "John", "age": [30, 32], "city": "New York"}
