laravel 从 Eloquent 关系中选择特定列

Select specific columns from Eloquent relations

提问by user3718908

I have the following models in my application:

我的应用程序中有以下模型：

User.php

用户名.php

<?php namespace App\Models;

use Illuminate\Auth\Authenticatable;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Auth\Passwords\CanResetPassword;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;
use Illuminate\Contracts\Auth\CanResetPassword as CanResetPasswordContract;
use Kodeine\Acl\Traits\HasRole;

class User extends Model implements AuthenticatableContract, CanResetPasswordContract {

    use Authenticatable, CanResetPassword, HasRole;

    /**
     * The database table used by the model.
     *
     * @var stringSS
     */
    protected $table = 'users';

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['name', 'email', 'password', 'is_active'];

    /**
     * The attributes excluded from the model's JSON form.
     *
     * @var array
     */
    protected $hidden = ['password', 'remember_token'];

    public function customer_details()
    {
        return $this->hasOne('App\Models\CustomerDetails', 'user_id');
    }

}

CustomerDetails.php

客户详情.php

<?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class CustomerDetails extends Model {

    protected $table = 'customer_details';
    protected $dates = ['created_at', 'updated_at', 'deleted_at'];
    protected $appends = ['full_name'];

    public function getFullNameAttribute()
    {
        return $this->attributes['first_name'] .' '. $this->attributes['last_name'];
    }

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

    public function invoices() {
        return $this->hasMany('App\Models\Invoices', 'customer_id');
    }
}

Now i am trying to run the following query:

现在我正在尝试运行以下查询：

$user = User::select('email', 'phone')->where('id', Auth::user()->id)->with('customer_details')->first();

Now what I want it to do is to select only the emailand phonenumber from my userstable and first_name, last_namefrom my customer_detailstable but whenever I try this it always returns customer_detailsas null. This function will be used on one page alone meaning there are other pages that may require all details but not this one so I want to create a separate Eloquent relation to do this.

现在我想要它做的是只从我的用户表中选择电子邮件和电话号码，从我的customer_details表中选择first_name，last_name但每当我尝试这样做时，它总是将customer_details返回为null。这个函数将单独用于一个页面，这意味着还有其他页面可能需要所有细节，但不是这个，所以我想创建一个单独的 Eloquent 关系来做到这一点。

回答by Josh Janusch

You need to include the idin your select, otherwise the eager loading has nothing to match against. I just tested this against my own project and ran into the same issue, adding the idto the select fixes it.

您需要id在您的选择中包含，否则急切加载无法匹配。我刚刚针对我自己的项目对此进行了测试并遇到了同样的问题，将 加入id到 select 中修复了它。

$user = User::select('id', 'email', 'phone')->where('id', Auth::user()->id)->with('customer_details')->first();

To further explain this, eager loading works by making your parent call first (in this case, the call to users) and then doing a second call like SELECT * FROM customer_details WHERE customer_details.user_id IN (?)where ?is a list of user IDs. It then loops through the results from that second call and attaches them to the appropriate user objects. Without the idon the user objects, there's nothing to match against in the second call or when attaching.

为了进一步解释这一点，预先加载的工作原理是首先调用您的父级调用（在本例中为调用users），然后进行第二次调用，例如SELECT * FROM customer_details WHERE customer_details.user_id IN (?)where ?is a list of user IDs。然后它遍历第二次调用的结果并将它们附加到适当的用户对象。如果没有id用户对象，则在第二次调用或附加时没有任何匹配项。

EDIT

编辑

To select specific columns from an eagerly loaded table, you can use the closure capabilities explained here. So you use the relationship name as the key and a closure that receives a Builderobject as the value, like so:

要从急切加载的表中选择特定列，您可以使用此处解释的闭包功能。所以你使用关系名称作为键和一个接收Builder对象作为值的闭包，如下所示：

$user = User::select('id', 'email', 'phone')
    ->where('id', Auth::user()->id)
    ->with('customer_details' => function (Builder $query) {
        $query->select('id', 'user_id', 'name', 'foo');
    })
    ->first();

Note that you mustselect the relationship column for both, otherwise Laravel has no idea how to connect the two

注意一定要为两者选择关系列，否则Laravel不知道如何将两者连接起来

回答by Morty

I think your select is bad. Use query builder in laravel.

我认为你的选择很糟糕。在 Laravel 中使用查询构建器。

try this

尝试这个

$user=DB::table('users')->join('customer_details','user.id','=','customer_details.id')->select('email','phone','first_name','last_name')->where('id','=',Auth::user()->id)->first();

I suppose that you use also id as primary key in customer_details.

我想您还使用 id 作为 customer_details 中的主键。