Laravel the database by using the Query Builder (grouped together paging)

1. Connection query (join)

A connection query is a query that associates two or more tables together to get data that matches the rows of one table with the rows of another. Common connection queries include inner joins (equivalent connections), left (outside) connections, right (outside) connections, and cross joins (full connections). The following image shows the result sets obtained by these connection queries:

SQL connection Query

Below we simply demonstrate the lower and the left connections. We associate the user table users and the article table posts together to query, before we create a posts table with fields and initial values as follows:

Article table posts

Where user_id corresponds to the user ID in the users table.

1.1 Internal connections

The inner join is used to get the intersection of two table result sets, and we can use the Query Builder join method for the INNER join query:

$users = db::table (' users ')->join (' posts ', ' users.id ', ' = ', ' posts.user_id ')->get ();
DD ($users);

1.2 Left Connection

The left-connected result set specifies all rows of the left table, and if a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null (NULL). We use the Query Builder's Leftjoin method for the left-join query:

$users = db::table (' users ')->leftjoin (' posts ', ' users.id ', ' = ', ' posts.user_id ')->get ();
DD ($users);

The corresponding output results are:

Left JOIN query Results

1.3 More complex connection queries

Many times, the query conditions of the connection query are often more complex, not a simple Join/leftjoin method can be done, then how do we add more complex query conditions? Use JoinClause instead of condition parameters:

$users = db::table (' users ')->join (' posts ', function ($join) {
$join->on (' users.id ', ' = ', ' posts.user_id ')
->where (' posts.id ', ' > ', 1);
})->get ();
DD ($users);

2. Union Query (Union)

A union query is used to combine the result sets of two or more queries into a single result set that contains all the rows of all queries in a federated query. The result set column name of the union is the same as the column name of the result set of the first SELECT statement in the Union operator, and the result set column names of the other SELECT statement are ignored, and the number of other query fields must be the same as the first. In the Laravel Query Builder we use the Union method for federated queries:

$users = db::table (' users ')->where (' id ', ' < ', 3);
$users = db::table (' users ')->where (' id ', ' > ', 2)->union ($users)->get ();
DD ($users);

3, WHERE clause

You can add a custom query condition by using the Where method on the query builder, which requires passing in three parameters: the first column name, the second is the operator, and the third is the comparison value:

$user = db::table (' users ')->where (' name ', ' = ', ' Laravel ')->get ();
DD ($user);

If the operator is "=", the statement can also be simplified to:

$user = db::table (' users ')->where (' name ', ' Laravel ')->get ();

Note that the Query Builder supports the method chain, which means that if there are multiple query conditions and the multiple conditions are connected, you can use more than one where method before get. If multiple conditions are connected using or, you can use the Orwhere method:

$user = db::table (' users ')->where (' name ', ' Laravel ')->orwhere (' name ', ' Academy ')->get ();

More WHERE clause query conditions can be viewed Illuminate\database\query\builder source code.

4, sorting

The Query Builder sorts query results by using the order by method:

$users = db::table (' users ')->orderby (' id ', ' desc ')->get ();
DD ($users);

According to the code you can see that the order by method needs to pass in two parameters, the first is the sort field, the second is the sort direction, the ASC represents the ascending order, the DESC represents the descending order, the above code output is:

5. Group

To better illustrate the grouping, we add two fields to the datasheet posts: cat_id and views, representing the category ID and browse Number:

Posts table new Category ID and browse number

Groups are generally used to aggregate queries, and then we use the GroupBy method to group query results, such as we can count several articles under each category:

$posts = db::table (' posts ')->select (' cat_id ', Db::raw (' COUNT (ID) as num ')->groupby (' cat_id ')->get ();
DD ($posts);

We can also use the having method to add conditions to the grouping, for example, we can count the categories with the total number of views greater than 500:

$posts = db::table (' posts ')->select (' cat_id ', Db::raw (' SUM (views) as views ')->groupby (' cat_id ')->having (' Views ', ' > ',->get ();
DD ($posts);

The output results are:

Grouped query results with criteria

Note: The conditional field in the having must appear in the Select query field or it will be an error.

6, pagination

The Query Builder uses skip and take to page through the query results, which is equivalent to the limit statement in the SQL statement:

$posts = db::table (' posts ')->skip (0)->take (2)->get ();
DD ($posts);