Laravel Eloquent: Integrating CASE WHEN Statements with raw() for Advanced Querying

Laravel Eloquent: When working with Laravel, it’s normal to need to write complex queries with Eloquent, and there are times when you need to use raw SQL expressions. This article will talk about a real-life example of how to use the raw() method in Laravel Eloquent to add a CASE WHEN statement to a query.

The Challenge:

Our goal is to make a query that uses CASE WHEN to pick data from a table and include a conditional expression. The task is to add this CASE WHEN statement to the query without any problems so that the desired results are returned.

Laravel Eloquent Real-World Code Example:

Consider the following tables:

CREATE TABLE `shares` (
    `id` INT UNSIGNED NOT NULL,
    `status` VARCHAR(255) NOT NULL,
    `deleted` INT NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `connected_user_id` INT UNSIGNED,
    `original_language_id` INT UNSIGNED,
    `image` VARCHAR(255),
    `created_at` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
    FOREIGN KEY (`connected_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL,
    `first_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `location_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `locations` (
    `id` INT UNSIGNED NOT NULL,
    `city` VARCHAR(255) NOT NULL,
    `province_id` INT UNSIGNED NOT NULL,
    `country_id` INT UNSIGNED NOT NULL,
    `lat` DECIMAL(10, 7) NOT NULL,
    `lng` DECIMAL(10, 7) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`province_id`) REFERENCES `provinces` (`id`),
    FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `provinces` (
    `id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `countries` (
    `id` INT UNSIGNED NOT NULL,
    `code` VARCHAR(3) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Consider a scenario where we have a “shares” table, and we want to select specific columns while incorporating a conditional check based on the current user. The objective is to include a column named “is_user” which evaluates to 1 when the user_id matches the current user and 0 otherwise.

The Solution:

To ensure that the CASE WHEN statement is properly recognized, we need to move the raw() call inside the SELECT statement. This adjustment ensures that the conditional expression is included in the list of selected columns.

$shares = Share::where('shares.status', '=', SHARE_STATUS_APPROVED)
    // ... other conditions ...
    ->join('users', 'shares.user_id', '=', 'users.id')
    ->join('locations', 'locations.id', '=', 'users.location_id')
    ->select('shares.id AS share_id', 'users.id AS user_id', 'shares.connected_user_id',
        // ... other columns ...
        DB::raw('(CASE WHEN users.id = ' . $user . ' THEN 1 ELSE 0 END) AS is_user')
    )
    ->orderBy('shares.created_at', 'desc')
    // ... other order conditions ...
    ->get();

Conclusion:

By understanding how to integrate CASE WHEN statements with raw() in Laravel Eloquent, developers can enhance their ability to create complex queries tailored to specific requirements. This real-world example showcases the practical implementation of this technique for efficient and flexible querying.

For additional insights into Laravel Eloquent and improving query efficiency, explore “Understanding Laravel’s Eloquent: get() vs find()” at pranabkalita.com.