Where Clause In Laravel Eloquent Example

Where Clause In Laravel Eloquent Example; In this tutorial, we will learn all possible where clauses in the laravel Eloquent, When are new in laravel, where clauses are a most important part when you make a retrieve data query from the database. Most of where eloquent work is to filter data from the database with the given value.

Where clauses are the most important part while you are learning laravel eloquent query. having ended this post you will be able to find anyway and any type you can make eloquent query.

You can simply use where the First argument will be the column name and the second argument is the value that you want to filter.

Basic Usage

User::where('votes', 100)->get();

Filtering data with a simple where clause, This where causes will found from the database base on the same row, It will return the raw which have votes 100 and age 35.

User::where('votes', '=', 100)->where('age', '>', 35)->get();

With Logical Operator

We can use different logical operators also, You can filter using the "like" operator for the string.

User::where('votes', '>=', 100)->get();

User::where('votes', '<>', 100)->get();

User::where('name', 'like', 'T%')->get();

Advance Usage

Using single where with multiple arrays, three indexes need to pass, the first will be a column name, the second operator, third value compares.

User::where([
     ['status', '=', '1'],
     ['subscribed', '<>', '1'],
])
->get();

Or Where Clauses

You can use the same as where causes, The orWhere method accepts the same argument as the where method.

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

If you want to group an "or" condition within the parentheses, you can pass a closure as given the first argument to the OrWhere Method.

User::where('votes', '>', 100)
     ->orWhere(function($query) {
         $query->where('name', 'Abigail')
               ->where('votes', '>', 50);
     })
     ->get();

Where Not Clauses

The whereNot and orWhereNot methods can be used to integrate a given group of query constraints.

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

JSON Data Where Clauses

JSON data where causes laravel also supports query JSON column type in the database. that provides support for the JSON column datatype.

User::where('preferences->dining->meal', 'salad')->get();

You can use whereJsonContains to query the JSON array stored. This feature is not supported in the SQLite database version 3.38.0 or less version.

User::whereJsonContains('options->languages', 'en')->get();

If you are using MYSQL or PostgresSQl databases, You can pass an array as a value to the whereJsonContains method.

User::whereJsonContains('options->languages', ['en', 'de'])->get();

If you want to get the length, you may use the whereJsonLength method to the query JSON array by their length.

User::whereJsonLength('options->languages', 0)->get();

User::whereJsonLength('options->languages', '>', 1)->get();

Additional Where Clauses

The where between method fetch the rows between two value. You need to pass an array in the method as a value. will return between records.

User::whereBetween('votes', [1, 100])->get();

This is the return of all the records apart from that between the given value.

User::whereNotBetween('votes', [1, 100])->get();

The whereIn find the value given column value is contained with the given array in the method.

User::whereIn('id', [1, 2, 3])->get();

apart from that given array return all the record.

User::whereNotIn('id', [1, 2, 3])->get();

Return all the given column null records.

User::whereNull('updated_at')->get();

Return given column without null all the record means given column return which has value.

User::whereNotNull('updated_at')->get();

This method is for the compare value with a given column date.

User::whereDate('created_at', '2016-12-31')->get();

This method is for the compare a given month within the given column name value.

User::whereMonth('created_at', '12')->get();

This method is the compare date value with the given column name value.

User::whereDay('created_at', '31')->get();

This method compares a given year with the given column name value.

User::whereYear('created_at', '2016')->get();

This method is for the compare time with the given column name value.

User::whereTime('created_at', '=', '11:20:45')->get();

The where column method will return a record base on compare between two columns.

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

Using this method you can pass logical operators also, see the below-given example.

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

You can pass multiple arrays to compare each other columns inside the database table.

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

Logical Grouping

Sometimes you need to group several "where" clauses within the where column to archive your query desire conditional. You always need to call the orWhere method inside the parentheses to avoid unexpected query behavior.

User::where('name', '=', 'John')
     ->where(function ($query) {
         $query->where('votes', '>', 100)
               ->orWhere('title', '=', 'Admin');
     })
     ->get();

Advanced Where Clauses

This will define the where Exists SQL clauses. This method accepts a closure that will receive a query builder instance.

User::whereExists(function ($query) {
     $query->select(DB::raw(1))
          ->from('orders')
          ->whereColumn('orders.user_id', 'users.id');
     })
     ->get();

Subquery Where Clauses

Use where will callback with he subquery condition.

User::where(function ($query) {
     $query->select('type')
          ->from('membership')
          ->whereColumn('membership.user_id', 'users.id')
          ->orderByDesc('membership.start_date')
          ->limit(1);
     }, 'Pro')->get();

See the below query, The query will return all the income records where the income amount is average or less than the average amount.

Income::where('amount', '<', function ($query) {
     $query->selectRaw('avg(i.amount)')->from('incomes as i');
     })->get();

Full Text Where Clauses

The where full-text method and orWhereFullText methods may be used to add full text with "where" clauses to the query for the column that has full-text indexes.

User::whereFullText('bio', 'web developer')->get();

Dharmesh Chauhan

(Swapinfoway Founder)

Hello Sir, We are brothers origin from Gujarat India, Fullstack developers working together since 2016. We have lots of skills in web development in different technologies here I mention PHP, Laravel, Javascript, Vuejs, Ajax, API, Payment Gateway Integration, Database, HTML5, CSS3, and Server Administration. So you need our service Please Contact Us

Haresh Chauhan

(Co-Founder)