Data tables are usually associated with each other, and it is usually easier to use multi table associated query, which is more convenient and simple for more complex model associated query using laravel, Chinese document , take two tables of provinces and cities as an example

Preliminary preparation:

First, create a PlaceController controller and create the index method:

<?php

namespace App\Http\Controllers\Api;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class PlaceController extends Controller
{
    public function index(Request $request)
    {

    }
}

Then create two models of provinces and cities:

<?php
namespace App\Http\Models;
use Illuminate\Database\Eloquent\Model;

class Province extends Model
{
    protected $table = 'province';

    protected $primaryKey = 'id';

    public $timestamps = false;
}

<?php
namespace App\Http\Models;
use Illuminate\Database\Eloquent\Model;

class City extends Model
{
    protected $table = 'city';

    protected $primaryKey = 'id';

    public $timestamps = false;
}

At the end of preparation, to use model association, you must first define the association relationship, that is, define the association method in the model.

One to one: hasOne()

Define the association relationship city() with City model in the Province model:

    public function city()
    {
        return $this->hasOne('App\Http\Models\City', 'pid', 'id'); //Parameter 1: the name of the sub table class to be associated, which must be preceded by namespace parameter 2: the field of the sub table associated with the parent table parameter 3: the field of the parent table associated with the sub table
    }

Use in the index() of the PlaceController controller:

    public function index(Request $request)
    {
        $place = Province::select('*')
            ->with(['city' => function($query) {  //City corresponds to the subquery in the city method name closure defined in the above province model
                return $query->select('*');
            }])
            ->where('id', 11)->first()->toArray(); //Query the province with id 11 and the first city in the province
        dd($place);
    }

array:3 [▼
  "id" => 11
  "pname" => "Zhejiang Province"
  "city" => array:3 [▼
    "id" => 89
    "pid" => 11
    "cname" => "Taizhou City"
  ]
]

In addition, the general methods such as where and orderBy can be used in subquery statements:

    public function index(Request $request)
    {
        $cid = 99;
        $place = Province::select('*')
            ->with(['city' => function($query) use ($cid) { //use() multiple parameters ($cid, $name) for parameter passing
                return $query->select('*')->where('id', $cid);
            }])
            ->where('id', 11)->first()->toArray();
        dd($place);
    }

array:3 [▼
  "id" => 11
  "pname" => "Zhejiang Province"
  "city" => array:3 [▼
    "id" => 99
    "pid" => 11
    "cname" => "Hangzhou City"
  ]
]

One to one definition reverse association: belongsTo()

The above is to check the following cities from the province. The reverse association is to check the identity of the city. First, in the city model, provide () at a fixed time:

    public function province()
    {
        return $this->belongsTo('App\Http\Models\Province', 'pid', 'id'); //Parameter one:Need associated parent table class name,Namespace attention must be added before:Parameter two:Field parameter 3 of the child table associated with the parent table:Fields of parent table associated with child table
    }

Use in controller:

    public function index(Request $request)
    {
        $place = City::select('*')
            ->with(['province' => function($query) {
                return $query->select('*');
            }])
            ->where('id', 99)->first()->toArray();
        dd($place);
    }

array:4 [▼
  "id" => 99
  "pid" => 11
  "cname" => "Hangzhou City"
  "province" => array:2 [▼
    "id" => 11
    "pname" => "Zhejiang Province"
  ]
]

One to many: hasMany()

Define citys() in the province model:

    public function citys()
    {
        return $this->hasMany('App\Http\Models\City', 'pid', 'id');
    }

Use in controller:

    public function index(Request $request)
    {
        $place = Province::select('*')
            ->with(['citys' => function($query) {
                return $query->select('*');
            }])
            ->where('id', 11)->first()->toArray(); //Query the province with id 11 and all cities in the province
        dd($place);
    }

array:3 [▼
  "id" => 11
  "pname" => "Zhejiang Province"
  "citys" => array:11 [▼
    0 => array:3 [▼
      "id" => 89
      "pid" => 11
      "cname" => "Taizhou City"
    ]
    1 => array:3 [▶]
    2 => array:3 [▶]
    3 => array:3 [▶]
    4 => array:3 [▶]
    5 => array:3 [▶]
    6 => array:3 [▶]
    7 => array:3 [▶]
    8 => array:3 [▶]
    9 => array:3 [▶]
    10 => array:3 [▶]
  ]
]

One to many reverse association: belongsTo()

    public function provinces()
    {
        return $this->belongsTo('App\Http\Models\Province', 'pid', 'id'); //Parameter one:Need associated parent table class name,Namespace attention must be added before:Parameter two:Field parameter 3 of the child table associated with the parent table:Fields of parent table associated with child table
    }

Same as one-to-one reverse association

Many to many: belongsToMany()

Many to many is more complex than the above three tables, such as role (role table), role (authority table corresponding to role) and authority (all authority tables). The associated fields of role table (grandfather level) and role (parent level intermediate table) are role (ID), and the associated fields of role (parent level intermediate table) and authority (grandchild level) are authority (ID), Then you can define the relationship in the role table:

    public function role()
    {
        return $this->belongsToMany('App\Http\Models\AdminAuthority', 'admin_role_authority', 'role_id', 'authority_id'); //Parameter one:Table class name of the lowest level(Grandson class)  Parameter two:Intermediate table(Parent level)Table name parameter three of:Parameter 4 of the associated field between the intermediate table and its parent table:Associated fields of the intermediate table and its child table
    }

        $role = AdminRole::select('*')
            ->with(['role' => function($query) {
                return $query->select('*');
            }])
            ->where('role_id', 22)->first()->toArray();
        dd($role);

array:4 [▼
  "role_id" => 22
  "role_name" => "Advertising designer"
  "description" => "Advertising design"
  "role" => array:7 [▼
    0 => array:11 [▼
      "authority_id" => 88
      "name" => "Advertising management"
      "is_active" => 1
      "controller" => null
      "action" => null
      "url" => null
      "icon" => "Hui-iconfont-dangan"
      "parent_id" => 0
      "sort" => 3
      "role_id" => 22
      "pivot" => array:2 [▼
        "role_id" => 22
        "authority_id" => 88
      ]
    ]
    1 => array:11 [▶]
    2 => array:11 [▶]
    3 => array:11 [▶]
    4 => array:11 [▶]
    5 => array:11 [▶]
    6 => array:11 [▶]
  ]
]

Knock on the blackboard and draw the key points:

1. The essence of associated model query is to execute two SQL statements in total, and print the following executed SQL statements:

        DB::connection()->enableQueryLog();#Open execution log
        $role = AdminRole::select('*')
            ->with(['role' => function($query) {
                return $query->select('*');
            }])
            ->where('role_id', 22)->first()->toArray();
        dd(DB::getQueryLog());   //Get query statements, parameters, and execution time

array:2 [▼
  0 => array:3 [▼
    "query" => "select * from `admin_role` where `role_id` = ? limit 1"
    "bindings" => array:1 [▼
      0 => 22
    ]
    "time" => 48.82
  ]
  1 => array:3 [▼
    "query" => "select *, `admin_role_authority`.`role_id` as `pivot_role_id`, `admin_role_authority`.`authority_id` as `pivot_authority_id` from `admin_authority` inner join `admin_role_authority` on `admin_authority`.`authority_id` = `admin_role_authority`.`authority_id` where `admin_role_authority`.`role_id` in (22) ◀"
    "bindings" => []
    "time" => 9.41
  ]
]

2. In the associated query, if select() does not find all fields, the fields to be queried must contain the associated fields between tables before they can be used:

$role = User::select('id', 'name')  //Main table associated field is id
    ->with(['role' => function($query) {
        return $query->select('user_id', 'name'); //The associated field of the sub table is user? ID
    }])
    ->first()->toArray();

3. In addition, more complex queries can be combined, such as three tables A, B and C, one-to-one for table A and table B, one to many for table A and table C, and then leftjoin plus association model one to many queries can be used:

    public function items() {
        return $this->hasMany('App\Http\Models\A', 'aid', 'id');
    }

MallOrder::from('A as a')
                ->leftJoin('B as b','a.id','=','b.aid')
                ->with(['items' => function($query) {
                    return $query->select('*');
                }])->group()->select('*');

Similar to the above combination, you need to open your brain hole to practice. The more you use the model association query, the easier it will be, and the better it will be. It's really a good medicine for home and travel!