Laravel Framework Learning (Database advanced query)

1. Connection query (join)

A join query refers to a query that associates two or more tables together to obtain data that matches rows from another table. Common connection queries include internal connections (equivalent connections), left (outer) connections, right (outer) connections, and cross connections (full connections).
We associate the user table users and the article table posts together for querying, before we create the posts table with fields and initial values as follows:

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

1.1 Internal Connection

An inner join is used to get the intersection part of two table result sets, and we can use the Join method of the Query builder to make an inner join query:

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

The results appear as follows:

Array:3 [
  0 = {#226
    + "id": 1
    + "name": "Laravel"
    + "email": "laravel@test.com"
    + "password": " 123 "
    +" Remember_token ":" x "
    +" Created_at ":" 0000-00-00 00:00:00 "
    +" Updated_at ":" 0000-00-00 00:00:00 "
    + "title": "Test1"
    + "content": "test1"
    + "user_id": 1
  }
  1 = {#227
    + "id": 2
    + " Name ":" Laravel "
    +" email ":" laravel@test.com "
    +" password ":" 123 "
    +" Remember_token ":" x "+"
    Created_at ":" 0000-00-00 00:00:00 "
    +" Updated_at ":" 0000-00-00 00:00:00 "
    +" title ":" Test2 "
    +" content " : "Test2"
    + "user_id": 1
  }
  2 = {#228
    + "id": 3
    + "name": "Academy"
    + "email": " Academy@test.com "
    +" password ":" 123 "
    +" Remember_token ":" Y "
    +" Created_at ":" 0000-00-00 00:00:00 "
    + "Updated_at": "0000-00-00 00:00:00"
    + "title": "Test3"
    + "content": "test3"
    + "user_id": 2
  }
]

1.2 Left Connection

A 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 Leftjoin method of the Query Builder to make a left connection query:

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

Array:4 [
  0] {#226 ▶}
  1 = {#227 ▶}
  2 = {#228 ▶}
  3 + {#229
    + "id": null
    + "name": "Test3"
    + "email": "test3@test.com"
    + "password": "123456"
    + "Remember_token": "Z"
    + "Created_at": Null
    + "UPDATED_AT": null
    + "title": null
    + "content": null
    + "USER_ID": null
  }
]

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 we add more complex query conditions. Use JoinClause instead of conditional parameters:

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

The output is:

Array:2 [
  0 = {#226
    + "id": 2
    + "name": "Laravel"
    + "email": "laravel@test.com"
    + "password": " 123 "
    +" Remember_token ":" x "
    +" Created_at ":" 0000-00-00 00:00:00 "
    +" Updated_at ":" 0000-00-00 00:00:00 "
    + "title": "Test2"
    + "content": "test2"
    + "user_id": 1
  }
  1 = {#227
    + "id": 3
    + " Name ":" Academy "
    +" email ":" academy@test.com "
    +" password ":" 123 "
    +" Remember_token ":" Y "
    +" Created_at ":" 0000-00-00 00:00:00 "
    +" Updated_at ":" 0000-00-00 00:00:00 "
    +" title ":" Test3 "
    +" content " : "Test3"
    + "user_id": 2
  }
]

2. Union Search (Union)

A union query is used to combine the result set of two or more queries into a single result set that contains all the rows of all queries in a union 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 for the other SELECT statement are ignored, and the other query fields must be the same as the first one. 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);

The output is:

Array:3 [
  0 = {#226
    + "id": 3
    + "name": "test3"
    + "email": "test3@test.com"
    + "password": " 123456 "
    +" Remember_token ":" Z "
    +" Created_at ":" 2016-03-08 09:45:10 "
    +" Updated_at ":" 0000-00-00 00:00:00 "
  }
  1 = {#227
    +" id ": 1
    +" name ":" Laravel "
    +" email ":" laravel@test.com "
    +" password " : "123"
    + "Remember_token": "x"
    + "Created_at": "2016-03-15 09:45:03"
    + "Updated_at": "0000-00-00 00:00:00 "
  }
  2 = {#228
    +" id ": 2
    +" name ":" Academy "
    +" email ":" academy@test.com "
    + "Password": "123"
    + "Remember_token": "Y"
    + "Created_at": "2016-03-13 09:45:07"
    + "Updated_at": " 0000-00-00 00:00:00 "
  }
]

3. WHERE clause

Use the Where method on the Query Builder to add a custom query condition, which requires passing 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 ();

It is important to note that the Query Builder supports the method chain, which means that if there are multiple query criteria and the multiple conditions are and connected, you can use multiple where methods 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 criteria to view Illuminate\database\query\builder source code.

4. Sorting
The Query Builder uses the order by method to sort the results of the query:

$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 two parameters, the first is the sort field, the second is the sort direction, ASC stands for Ascending, and Desc represents the reverse.

5. Grouping

In order to better demonstrate the grouping, we have added two fields to the datasheet posts: cat_id and views, representing the category ID and browse Number:

Grouping is generally used to aggregate queries, and then we use the GroupBy method to group query results, for example, 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);

Array:2 [
  0 = {#226
    + "cat_id": 1
    + "num": 2
  }
  1 = {#227
    + "cat_id": 2
    + "num": 1
  }
]

We can also use the having method to add conditions to the grouping, such as we can count the total number of views greater than 500 of the classification:

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

The output is:

array:1 [
  0 = {#226
    + "cat_id": 1
    + "views": "" "
]

Note: The conditional field in the have must appear in the Select query field, otherwise an error will be added.
6. Paging

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

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

The corresponding output results are:

Array:2 [
  0 = {#225
    + "id": 1
    + "title": "Test1"
    + "content": "test1"
    + "user_id": 1
    + " Created_at ":" 0000-00-00 00:00:00 "
    +" Updated_at ":" 0000-00-00 00:00:00 "
    +" cat_id ": 1
    +" views ":
  1 = {#226
    + "id": 2
    + "title": "Test2"
    + "content": "test2"
    + "user_id": 1
    + "created _at ":" 0000-00-00 00:00:00 "
    +" Updated_at ":" 0000-00-00 00:00:00 "
    +" cat_id ": 2
    +" views ":
]