Laravel 5 Eloquent“with”方法：在连接表中连接多个表和过滤列

I store users and user profiles in two separate tables where Users table has username and email column, UserProfile table has name and employee_no column.

我将用户和用户配置文件存储在两个单独的表中,其中Users表具有用户名和电子邮件列,UserProfile表具有name和employee_no列。

User.php

public function userProfile()
{
    return $this->hasOne('App\Models\Utility\UserProfile', 'user_id', 'id');
}

UserProfile.php

public function users()
{
    return $this->belongsTo('App\Models\Utility\User', 'user_id', 'id');
}

I'm using with method to load profile information with users. Following code loads list of users with profile and can filter username column and email column.

我正在使用with方法向用户加载配置文件信息。以下代码加载具有配置文件的用户列表,并可以过滤用户名列和电子邮件列。

UserManagementController.php

public function getUserProfile(){
    $users = User::with('UserProfile')
            ->where('username', 'LIKE', '%'.Input::get('q').'%')
            ->orWhere('email', 'LIKE', '%'.Input::get('q').'%')
            ->paginate(5);


    return view('user_profile',compact('users'));
}

But I cannot filter name and employee_no field in UserProfile table as I cannot access these fields. So what is Eloquent's way of joining multiple tables and filter columns in joined tables.

但我无法过滤UserProfile表中的name和employee_no字段,因为我无法访问这些字段。那么Eloquent在连接表中连接多个表和过滤列的方式是什么呢?

1 个解决方案

#1

public function getUserProfile(){
$users = User::join('UserProfile', 'users.id', '=', 'UserProfile.user_id')
        ->where('username', 'LIKE', '%'.Input::get('q').'%')
        ->orWhere('email', 'LIKE', '%'.Input::get('q').'%')
        ->orWhere('UserProfile.employee_id', 'LIKE', '%'.Input::get('q').'%')
        ->orWhere('UserProfile.name', 'LIKE', '%'.Input::get('q').'%')
        ->paginate(5);


return view('user_profile',compact('users'));

}

Using join method in Larvel Query Builder solved the problem

在Larvel Query Builder中使用join方法解决了这个问题