Sling Academy
Home/PHP/Eloquent: Filtering query results with whereColumn() method

Eloquent: Filtering query results with whereColumn() method

Last updated: January 16, 2024

Introduction

The Eloquent ORM (Object-Relational Mapping) that comes with Laravel is well-loved for its ability to interact with database objects using a simple and expressive syntax. One of the dynamic methods Eloquent provides is whereColumn(), which is exceptionally useful for comparison between columns. In this article, we’ll explore the whereColumn() method with multiple code examples, ranging from basic to advanced.

Basic Usage of whereColumn

The whereColumn() method in Eloquent allows developers to create a query condition where two columns are compared to each other. Here’s the basic syntax:

$users = User::whereColumn('first_name', 'last_name')->get();

The above query will retrieve all users where the value in the ‘first_name’ column is equal to the value in the ‘last_name’ column.

Comparison Operators

whereColumn() can also be used with different comparison operators, just like the where() method. Common operators include ‘=’, ‘<‘, ‘>’, ‘<=’ and ‘>=’.

$users = User::whereColumn('updated_at', '>', 'created_at')->get();

This query fetches users who have an updated_at timestamp greater than their created_at timestamp.

Multiple Conditions

To include multiple column comparison conditions, you can pass an array of conditions to the whereColumn() method:

$users = User::whereColumn([
    ['first_name', '!=', 'last_name'],
    ['updated_at', '>', 'created_at']
])->get();

This will return users whose first name does not match their last name and who have been updated after they were created.

Advanced whereColumn Usage

Beyond basic column comparisons, Eloquent’s whereColumn() can be combined with other query builder methods to create more sophisticated queries.

Using with Joins

$orders = Order::select('orders.*')
    ->join('products', 'products.id', '=', 'orders.product_id')
    ->whereColumn('orders.quantity', '>', 'products.stock')
    ->get();

This query can be used to find all orders where the quantity ordered is greater than the product stock available.

WhereColumn with Aggregates

You can even use whereColumn() with aggregate functions by utilizing Eloquent’s raw expressions:

$users = User::select('users.*')
    ->havingRaw('MAX(age) > MIN(age)')
    ->groupBy('country_id')
    ->get();

This effectively filters the groups of users by countries where there’s at least one user older than another within the same country.

WhereColumn Inside Scopes

Eloquent’s model scopes can incorporate whereColumn() as a way to wrap conditional checks into reusable query logic. Let’s say you want to define a scope that encapsulates a common column comparison:

class User extends Model
{
    public function scopeNameMismatch($query)
    {
        return $query->whereColumn('first_name', '!=', 'last_name');
    }
}

$users = User::nameMismatch()->get();

The nameMismatch scope filters out users where the first and last names are the same.

Conclusion

Mastering whereColumn() enhances your Eloquent queries by efficiently comparing columns within your database. We’ve gone from basic usage to more complex scenarios such as combining it with joins and aggregates, and embedding it within scopes. Through its versatile options, Eloquent’s whereColumn() can significantly improve the precision and performance of our data queries.

Next Article: Eloquent: Sorting Results with orderBy() Method

Previous Article: Eloquent: Selecting Specific Columns from a Table

Series: Laravel & Eloquent Tutorials

PHP

You May Also Like

  • Pandas DataFrame.value_counts() method: Explained with examples
  • Constructor Property Promotion in PHP: Tutorial & Examples
  • Understanding mixed types in PHP (5 examples)
  • Union Types in PHP: A practical guide (5 examples)
  • PHP: How to implement type checking in a function (PHP 8+)
  • Symfony + Doctrine: Implementing cursor-based pagination
  • Laravel + Eloquent: How to Group Data by Multiple Columns
  • PHP: How to convert CSV data to HTML tables
  • Using ‘never’ return type in PHP (PHP 8.1+)
  • Nullable (Optional) Types in PHP: A practical guide (5 examples)
  • Explore Attributes (Annotations) in Modern PHP (5 examples)
  • An introduction to WeakMap in PHP (6 examples)
  • Type Declarations for Class Properties in PHP (5 examples)
  • Static Return Type in PHP: Explained with examples
  • PHP: Using DocBlock comments to annotate variables
  • PHP: How to ping a server/website and get the response time
  • PHP: 3 Ways to Get City/Country from IP Address
  • PHP: How to find the mode(s) of an array (4 examples)
  • PHP: Calculate standard deviation & variance of an array