Get your next remote job on LaraJobs.
1 contributor Edit on GitHub Laravel

Unlock the power of Laravel's query builder where clauses

Table of contents:

Unlock the power of Laravel's query builder where clauses

Introduction

One of the most basic yet powerful features of Laravel’s query builder, that I and other developers use all the time, is the ability to utilize “where” clauses.

If you’re eager to optimize your Laravel apps, understanding their nuances is essential. So, let’s dive deep into the realm of the “where” and unlock its full potential!

The essentials of where clauses

Basic where clauses

The foundation of any query is its conditions. In Laravel Laravel’s query builder, the basic structure of where clauses is intuitive and expressive. Simply put, you mention the column, the operator, and the value you want to compare.

For instance, imagine fetching comments with 100 votes:

$comments = Comment::where('votes', '=', 100)->get();

I think it’s nice. What about you? But that’s not it. Laravel lets you simplify where equals clauses:

$comments = Comment::where('votes', 100)->get();

If you’re just checking for equality, Laravel assumes you mean the ‘=’ operator.

And what if you want to combine where clauses?

Foo::query()
->where('foo', 'bar')
->where('bar', 'baz')
->get();

There is the beauty of Laravel’s query builder!

Learn more about basic where clauses.

Or where clauses

Life isn’t always about “and”. Sometimes, it’s about “or” (pardon my philosophical side). And Laravel’s query builder gracefully understands that. While chaining multiple where methods will join them using “and”, there’s an elegant way to use the “or” condition: the orWhere method.

Here’s a quick example:

$users = User::query()
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();

This fetches users who either have votes more than 100 or are named John. Handy, right?

Learn more about orWhere clauses.

Where not clauses

Sometimes, it’s not about what something is, but what it’s not (I did it again…). That’s where the whereNot clauses come into play. They negate a set of conditions, making exclusions a breeze.

For instance, if you wish to exclude products on clearance or priced below ten, it’s as straightforward as:

$products = Product::query()
->whereNot(function (Builder $query) {
$query->where('clearance', true)
->orWhere('price', '<', 10);
})
->get();

Learn more about whereNot clauses.

JSON where clauses

With the digital age’s demands, databases have evolved, and so has Laravel. Modern databases often use JSON column types, and Laravel’s query builder supports querying these like a champ! Be it MySQL, PostgreSQL, or even SQLite, you can fetch data with ease.

Looking for users who prefer a salad meal? There you go:

$users = User::query()
->where('preferences->dining->meal', 'salad')
->get();

Learn more about JSON where clauses.

Additional where clauses

Laravel’s query builder’s where capabilities don’t just stop at the basics. It offers a plethora of options to cater to different scenarios:

  • Between values: The whereBetween method checks if a column’s value lies between two given values. Similarly, whereNotBetween ensures the column’s value is outside those two values.
  • In or not in: The whereIn method is perfect when you want to check if a column’s value exists in a given array. Its counterpart, whereNotIn, does the exact opposite.
  • Date & time specific: Methods like whereDate, whereMonth, and whereTime make it easy to fetch records based on specific dates, months, or times.
  • Comparing columns: With the whereColumn method, you can effortlessly compare two columns in the same table. Be it checking for equality or any other relation.

Learn more about additional where clauses.

Benjamin Crozat

Written by Benjamin Crozat

Indie hacker, blogger, and AI enthusiast building things with the TALL stack. 🔥

Follow me on:

Recommended articles

Efficient data filtering with whereIn() in Laravel Efficient data filtering with whereIn() in Laravel

Explore using Laravel's whereIn() method to filter database queries efficiently.

Published on Nov 17, 2023

20+ Laravel best practices, tips and tricks to use in 2023 20+ Laravel best practices, tips and tricks to use in 2023

Learning a framework can be overwhelming, but time and execution will make you a master. Here are some best practices to help you toward your goal.

Modified on Oct 17, 2023

Laravel 10 is out! Here are every new features and changes. Laravel 10 is out! Here are every new features and changes.

Laravel 10 has been released on February 14, 2023. Let's dive into every relevant new features and changes.

Modified on Nov 2, 2023

How does Laravel work? A crystal clear explanation. How does Laravel work? A crystal clear explanation.

Discover my step by step and simple explanation of how Laravel makes your life easier.

Published on Oct 31, 2023

Laravel interview questions and answers for 2023 Laravel interview questions and answers for 2023

Nailing a Laravel job interview can be a daunting task, but with the right preparation and mindset, you can set yourself up for success.

Modified on Sep 19, 2023 Audio available

Gold sponsors New

  • Wire Elements
    Beautiful handcrafted Livewire components.
    Check site
Your business here

Partners

If you buy from one of my partners below, I will be compensated at no cost to you. These are services I use or used, and 100% stand behind.

  • Scalable and reliable VPS hosting.
    Bonus: $200 of free credits
    Check site
  • The Google Analytics alternative without compromise.
    Free trial: 30 days
    Bonus: $10 off your first invoice
    Check site
  • Flare
    Track PHP and JavaScript errors in one place.
    Free trial: 10 days
    Check site
  • Keep track of your Google rankings.
    Free trial: 7 days
    Promo code: WELCOME30
    Check site
- / -