Laravel 8: MySQL Database Query Tip Sheet #2

As promised in the previous tip sheet, this article will cover Query Builder selects in Laravel 8. As we mentioned in the previous article, the Query builder allows users to build complex queries using method chaining. Let’s take a look at how that is applied to select queries.

Select Queries

Query Builder select queries usually look something like this

DB::table(table_name)->where(column_name, column_value)->get();

You have a table() method to specify the table you wish to fetch data from. You then have a chain of constraining methods like the where() method, and then you have a returning method like the get() method that will trigger the execution of your query. Let’s take a look at commonly used constraining methods. As mentioned in the previous article, do not forget to add the following line to files in which you use the Query Builder.

use Illuminate\Support\Facades\DB;

Case Scenario:

Before looking at constraining methods, let us assume the following case scenario. We have a “students” table in a MySQL database called “dummy-data”. We wish to interact with this table using the Query Builder from our Laravel application. Each student has an id, name, age, email and address. Here are all the contents of the table acquired by running the following line of code(the created_at and updated_at columns have been excluded for brevity):

DB::table('students')->select('id', 'name', 'email', 'age', 'address')->get();

Constraining Methods

1. select()

The select method allow you to specify which columns of a table you wish to fetch data from. For example, the following query will return only the name and email values for each row in the students table.

DB::table('students')->select('name', 'email')->get();

By the way, the get() method returns all rows that satisfy the constraining methods in the formats they specify if applicable. The query above will return results that look something like this.

You can also specify new names for the columns to use in your application for example if you find the actual column names too long for example. You can do this by adding “as new_name” to the column name string thus:

DB::table(tablename)->select('column_name as new_name', 'column_name as new_name',...)->get(); 

For example, the following query

DB::table('students')->select('name as n', 'email as e')->get();

will yield the following result:

2. where()

Unlike the select() method which filters query results by columns, the where() method filters query results by rows. It constrains a query to returning only rows that satisfy a specific result. Below is a sample query using the where() method and it results.

DB::table('students')->where('name', 'Arvilla Willms Sr.')->get();

The query above returns all rows that have their “name” column’s value equal to ‘Arvilla Willms Sr.’. To use an operator other than the equality operator (‘=’), a third parameter is introduced between the two used above. Here are more examples:

// Operators: =, >, <, >=, <=, <>
DB::table('students')->where('created_at', '>', now()->subDay())->get(); // Returns all rows created before current time
DB::table('students')->where('age', '<', 21)->get(); // Returns all rows with age values over 21

Concatenating multiple where clauses with the AND operator

To concatenate multiple where clauses with the AND operator, you chain multiple where clauses to your query thus:

DB::table('table_name')->where(condition)->where(condition)->where(condition) ... ->where(condition)->get();

The query below is an example of this chaining that returns all rows for which the age column’s value is not equal to 21 and the name column’s value is not equal to ‘Rosalyn Labadie’.

DB::table('students')->where('name', '<>', 'Rosalyn Labadie')->where('age', '<>', 21)->get();

It returns the following results:

Concatenating multiple where clauses with the OR operator

To concatenate multiple where clauses with the OR operator, you use a variation of the where() method called the orWhere() method thus:

DB::table('table_name')->where(condition)->orWhere(condition)->orWhere(condition) ... ->orWhere(condition)->get();

For example, the query below returns all rows for which the age column’ value is equal to 21 and the name column value is equal to ‘Rosalyn Labadie’.

DB::table('students')->where('name', 'Rosalyn Labadie')->orWhere('age', 21)->get();

3. whereBetween()

This method constrains query results to rows for which a particular column’s values falls between two specified values. It is used in the following format:

DB::table('table_name')->whereBetween('column_name', [value1,value2])->get();

Here is an example and its results:

DB::table('students')->whereBetween('age', [17,20])->get();

4. whereIn() / whereNotIn() / orWhereIn() / orWhereNotIn()

The whereIn method constrains query results to rows that have one out of a set of values for a particular table column. It is used in the following format:

DB::table('table_name')->whereIn(column_name, array of values)->get();

Here is an example and its results:

DB::table('students')->whereIn('age', [13,17,24])->get();

The other method variations should be self explanatory.

5. whereNull() / whereNotNull() / orWhereNull ()/ orWhereNotNull()

This whereNull() constrains query results to rows for which a particular column’s value is null. It is used in the following format:

DB::table('table_name')->whereNull(column_name)->get();

The other method variations should be self explanatory.

6. whereColumn() / orWhereColumn()

The whereColumn() method constrains query results to values for which two specified columns have the same value.

DB::table('users')->whereColumn('first_name', 'last_name')->get();

Check the next tip sheet for information on returning methods and joins.

Related Articles

Laravel 8: MySQL Database Query Tip Sheet #1

Laravel 8: MySQL Database Query Tip Sheet #3

Laravel 8: MySQL Database Query Tip Sheet #4