mysql - Laravel 中的嵌套查询

Question

我有两个表:

表 1:tbluser

ID 名称
1.......名称1
2.......名称2
3.......名称3

另一个链接 tbluser 和 tblrole 的表

表2:tbllinkuserrole
用户ID......角色ID
1.......2
1..........2
1..........2
2..........2
3..........1

我想将 tbluser.Name 和用户角色从 tbllinkuserrole 获取到表中:根据从 tbllinkuser 角色获取的角色，单个表行(用户记录)可以在“角色”列内包含多行。一个用户可以拥有多个角色。你能帮我吗...

Answer 1

Laravel 有模型(Eloquent)，模型类中有一些函数可以方便用户从表中获取数据。 (这适用于 Laravel 5.2)

The Eloquent ORM included with Laravel provides a beautiful, simple ActiveRecord implementation for working with your database. Each database table has a corresponding "Model" which is used to interact with that table. Models allow you to query for data in your tables, as well as insert new records into the table. https://laravel.com/docs/5.2/eloquent

首先，您需要一个用户模型(php artisan make:model User)。您需要将用户模式链接到右侧表格。

//File: /laravel/app/User.php
use Illuminate\Foundation\Auth\User as Authenticatable;
class User extends Authenticatable
{
    protected $table = 'tbluser';

    public function userRoles() {
        //Get the table data with the user roles
        $this->hasMany('App\UserRoles', 'user_id', 'id');
        //First argument gets the UserRoles Modal
        //Second argument get the UserRoles column 'user_id'
        //Third argument get the User table - column id
}

//File: /laravel/app/UserRoles.php
use Illuminate\Database\Eloquent\Model;
class UserRoles extends Model
{
    protected $table = 'tbllinkuserrole';

    public function rank() {
        //Get the table data with role
        $this->hasOne('App\Roles', 'id', 'role_id');
        //First argument gets the Roles Modal (table)
        //Second argument get the Roles column 'id'
        //Third argument get the UserRoles modal (table) - column role_id
}

//File: /laravel/app/Roles.php
use Illuminate\Database\Eloquent\Model;
class Roles extends Model
{
    protected $table = 'tblroles';
}

现在，如果您调用 Auth::user()->userRoles，您将获得一个数组，其中包含表中链接到当前用户的所有行。

当您运行此程序时，您会转储所有用户排名数据。

foreach(Auth::user()->userRoles as $userrole) {
    {{ dump($userrole) }}
    {{ dump($userrole->rank) }}
}

我希望这对你有用!

更多信息可以在 https://laravel.com/docs/5.2/eloquent 上找到