Laravel 8: MySQL Database Query Tip Sheet #3

5 min readApr 18, 2021

Case Scenario:

Before looking at returning 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();

Query Builder Returning Methods

1. get()

This method returns all rows that satisfy constraints specified in a query. Here is an example of the method in use.

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

2. first()/firstOrFail()

This method returns the first row that satisfies constraints specified in a query. The first() method fails silently if there are no results while the firstOrFail() method will throw an exception. Here is an example of the first() method in use:

DB::table('students')->first();

Here are the results returned by the query

3. value()

This method returns the value for a specified column in the first row that satisfies constraints specified in a query. Here is an example of the method in use:

DB::table('students')->value('email');

Here are the results returned by the query:

4. find(id)/findOrFail(id)

This method returns all rows that have a provided id. The find() method fails silently if there are no results while the findOrFail() method will throw an exception. Here is an example of the find() method in use:

DB::table('students')->find('2');

Here are the results returned by the query:

5. pluck()

The pluck() method returns all values for a specific table column that satisfy the constraints specified by a query. Here is an example of the pluck() method in use:

DB::table('students')->pluck('email');

Query Builder Aggregation Methods

An aggregate method combines values of multiple rows to form a single summary value. These methods act as returning methods. Using these methods therefore means you will not need a returning method.

min()/max()

The min() and max() functions respectively return the minimum and maximum() value for a particular table column for all rows that satisfy constraints specified by a given query. Here is an example:

DB::table('students')->max('age');

It returns the following result:

2. count()

This method returns the number of rows that satisfy constraints specified by a given query. Here is an example query:

DB::table('students')->count();

It returns the following result:

3. sum()

This method returns the sum of all values for a particular table column for all rows that satisfy constraints specified by a given query. Here is an example query:

DB::table('students')->sum('age');

It returns the following result:

4. avg()

This method returns the average value of all values for a particular table column for all rows that satisfy constraints specified by a given query. Here is an example query:

DB::table('students')->avg('age');

It returns the following result:

Query Builder Unions

The union() method can be used to conduct a union of two query results. As explained by the set theory, a union operation of two sets yields a set consisting of all elements that belong to either the first set, the second set or both.

The red portion indicates a union of two sets

The union() method ensures that elements with duplicates occur only once. You have to make sure, however, that you are selecting the same table columns for all queries participating in the union or there will be an error. Here is an example of the union() method in use. Here is an example of the union() in use:

$first = DB::table('students')->select('name', 'email')->where('name', 'Arvilla Willms Sr.');$second = DB::table('students')->select('name', 'email')->where('age', '<', 21);$students = $first->union($second)->get();return $students;

Notice that the get() method is only called once (after the union method in the chain). Here are the results returned by the query:

Query Builder Joins

Joins are used to combine rows from two or more tables, based on a related column between them. Checkout this article for an SQL-based explanation. The Laravel 8 Query Builder comes with a join() method for conducting inner joins. The join() method would be used to join two tables in the following format:

DB::table(table1_name)
    ->join(table2_name, table1_name.column, =, table2_name.column)
    ->get();

Assuming a students and grades table as shown below,

an example of a join on the two tables using the Query Builder can be the following:

DB::table('students')
            ->join('grades', 'students.id', '=', 'grades.id')
            ->get();

It will yield the following results:

To constrain the query results to specific columns, the select() method can be used. To clarify which table each column name passed to the select() method belongs to, use a table_name.column_name format thus;

DB::table(table1_name)
    ->join(table2_name, table1_name.column, =, table2_name.column)
    select(table_name.column_name, table_name.column_name, ...)
    ->get();

Here is an example:

DB::table('students')
    ->join('grades', 'students.id', '=', 'grades.id')
    ->select('students.id', 'students.name', 'grades.subject', 'grades.grade')
    ->get();

This will yield the following results: