laravel5.3 一对多表 分页查询

2019独角兽企业重金招聘Python工程师标准>>>

// 表结构

mysql> desc modules;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| module_dec | int(10) unsigned | NO   |     | NULL    |                |
| module_hex | varchar(10)      | NO   |     | NULL    |                |
| msg        | varchar(255)     | NO   |     | NULL    |                |
| name       | varchar(20)      | NO   |     | NULL    |                |
| status     | tinyint(1)       | YES  |     | 0       |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
| deleted_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

mysql> desc objects;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| module_dec | int(10) unsigned | NO   | MUL | NULL    |                |
| object_dec | int(10) unsigned | NO   |     | NULL    |                |
| object_hex | varchar(255)     | NO   |     | NULL    |                |
| name       | varchar(20)      | NO   |     | NULL    |                |
| msg        | varchar(255)     | NO   |     | NULL    |                |
| status     | tinyint(1)       | YES  |     | 0       |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
| deleted_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

mysql> desc errors;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| module_dec | int(10) unsigned | NO   | MUL | NULL    |                |
| object_dec | int(10) unsigned | NO   |     | NULL    |                |
| hex        | varchar(20)      | NO   |     | NULL    |                |
| dec        | int(10) unsigned | NO   |     | NULL    |                |
| hex_code   | varchar(10)      | NO   |     | NULL    |                |
| dec_code   | int(10) unsigned | NO   |     | NULL    |                |
| name       | varchar(20)      | NO   |     | NULL    |                |
| msg        | varchar(255)     | NO   |     | NULL    |                |
| status     | tinyint(1)       | YES  |     | 0       |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
| deleted_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
13 rows in set (0.01 sec)

// 关系模型的定义, 逆向的
Module.php
      public function  errors(){
        return $this->hasMany('App\Object','module_dec','module_dec');
    }

Object.php
     /**
     *  对象 & 错误码.
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public  function errors(){
        return $this->hasMany('App\Error','object_dec','object_dec');
    }

Error.php
    /**
     * 错误码 & 模块
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function  modules(){
        return $this->belongsTo('App\Module','module_dec','module_dec');
    }


    /**
     * 错误码 & 对象.
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function objects(){
        return $this->belongsTo('App\Object','object_dec','object_dec');
    }

// 查询处理

        $res =\App\Error::with(['objects'=>function($query){
            $query->with('modules');
        }])->paginate(2);
       
         return View('you page name',['res'=>$res]);   # 为了衔接页面上的变量.

// 页面解析过程

 @foreach ($res as $user)
                <tr>
                    <td>{{ $user->name }}</td>
                    <td>{{ $user['modules']['name'] }}</td>
                    <td>{{ $user['modules']['module_dec'] }}</td>
                    <td>{{ $user['objects']['name'] }}</td>
                    <td>{{ $user['objects']['object_dec'] }}</td>
                    <td>{{ $user->dec }}</td>
                    <td>{{ $user->hex }}</td>
                    <td>{{ $user->hex_code }}</td>
                    <td>{{ $user->dec_code }}</td>
                    <td>{{ $user->msg }}</td>
                    <td>@if( !$user->status ) 启用 @else 禁用 @endif</td>
                    <td>
                        <a href="{{ URL::route('showErrors',[$user->id]) }}">编辑</a>
                        <a href="{{ URL::route('deleteError',[$user->id]) }}">删除</a>
                    </td>
                </tr>
            @endforeach
        </table>
    </div>
    {{ $error->links() }}

###############################
1  写在最后,主要是页面上解析的时候,用的是数组的方式表达的,
2  如果模型中使用了软删除的特性, 那么在该查询的过程中是会过滤掉被软删除的,请注意,
3  如果要添加新的条件可以自己参考手册写,
4  $res = $this->model->with('objects','modules')->paginate(2);  这样的也是可以的,
5  如果查询的过程中使用了groupBy,出现mysql(1055)错误,请参考链接
   https://github.com/laravel/framework/issues/14997

 

转载于:https://my.oschina.net/u/1579560/blog/834881